PostgreSQL Data Types: text, numeric, timestamptz, uuid, IDENTITY, Enums, Arrays and Ranges

Version note: The examples in this post were run on PostgreSQL 18.7 (the official postgres:18 Docker image, built with ICU), in the Bookstore lab we set up in post 02.

Guess First

Bookstore, the online bookstore used as the shared example across this site's series, stores the moment an order was created. The team has two opinions: one says timestamp, the other timestamptz. We put both side by side and wrote the same value with the session time zone set to Istanbul. Then we switched the session time zone first to UTC and then to New York and read the same row:

CREATE TABLE tz_demo (id int, created_ts timestamp, created_tstz timestamptz);
SET TimeZone = 'Europe/Istanbul';
INSERT INTO tz_demo VALUES (1, '2026-11-14 10:00', '2026-11-14 10:00');

SET TimeZone = 'UTC';
SELECT created_ts, created_tstz FROM tz_demo;
SET TimeZone = 'America/New_York';
SELECT created_ts, created_tstz FROM tz_demo;
SELECT pg_column_size(created_ts) AS ts_bytes, pg_column_size(created_tstz) AS tstz_bytes FROM tz_demo;

Which column's value changes? Since timestamptz is "with time zone", does it also store the Istanbul part? How many bytes do the two columns take on disk?

     created_ts      |      created_tstz
---------------------+------------------------
 2026-11-14 10:00:00 | 2026-11-14 07:00:00+00

     created_ts      |      created_tstz
---------------------+------------------------
 2026-11-14 10:00:00 | 2026-11-14 02:00:00-05

 ts_bytes | tstz_bytes
----------+------------
        8 |          8

timestamp always showed the same "10:00"; like a reading of a wall clock, it doesn't know which city it is in. timestamptz showed the same instant in each session's own time zone: 07:00 in UTC, 02:00 in New York. Both columns are 8 bytes, so timestamptz does not store a time zone. The name is misleading but the behavior is clear: on write it converts the incoming value to UTC and stores it as an instant, on read it displays it according to the session's TimeZone setting. In this post we look at the decisions behind the types in the Bookstore schema: time, text, numbers, identity, enums, arrays, ranges and collation with Turkish characters.

timestamp and timestamptz: What Is Stored Is an Instant

For someone coming from SQL Server the closest counterparts are: timestamp is a date and time without a time zone, like datetime2; timestamptz resembles datetimeoffset but doesn't keep the offset, only the instant. In Bookstore every value that is "the same moment everywhere in the world", such as when an order was created, a payment was captured or a parcel was delivered, is timestamptz. The distinction we discussed in the time and time zone post of the Çözüm Desenleri series applies here too: an instant and a local calendar value are different things. For a local date such as "the store opens on 2 January" date is the right type, and for a local time such as "the campaign starts at 09:00 every day" time or timestamp can be.

To convert an instant to the clock of a specific place, use AT TIME ZONE. Watch the result type:

SELECT created_tstz AT TIME ZONE 'Europe/Istanbul' AS istanbul_wall_clock,
       pg_typeof(created_tstz AT TIME ZONE 'Europe/Istanbul')
FROM tz_demo;

 istanbul_wall_clock |          pg_typeof
---------------------+-----------------------------
 2026-11-14 10:00:00 | timestamp without time zone

timestamptz AT TIME ZONE returns a timestamp: "the wall clock in Istanbul showed 10:00". Conversely, timestamp AT TIME ZONE returns a timestamptz. When reports compute day boundaries, such as one day's orders in Turkish time, this conversion has to be done deliberately. The server and session TimeZone setting only affects display and how text without a time zone is interpreted; in the lab the default is Etc/UTC. Npgsql's rules between DateTime and timestamptz (the UTC Kind requirement) are the subject of post 11.

Text: text, varchar(n) and char(n)

In SQL Server there are storage and plan differences between varchar(50) and varchar(max), so giving a length is a habit. In PostgreSQL text, varchar(n) and char(n) share the same storage. The documentation states plainly that apart from the extra space of the blank-padded char(n) and the cost of the length check when writing to a length-limited column, there is no performance difference between them. So varchar(13) is not a performance decision but a constraint. Constraints have surprises too:

CREATE TABLE t_len (isbn varchar(13));
INSERT INTO t_len VALUES ('97800000000011');
INSERT INTO t_len VALUES ('9780000000001  ');
SELECT isbn, length(isbn) FROM t_len;

SELECT 'Dune'::char(10) || '|' AS char_concat, octet_length('Dune'::char(10)) AS char_bytes,
       'Dune'::char(10) = 'Dune  ' AS char_equal;

ERROR:  value too long for type character varying(13)
INSERT 0 1
     isbn      | length
---------------+--------
 9780000000001 |     13

 char_concat | char_bytes | char_equal
-------------+------------+------------
 Dune|       |         10 | t

The 14-digit ISBN was rejected, but the 15-character value with two trailing spaces was truncated to 13 characters without an error: the SQL standard says to truncate when the excess is only spaces. char(10) takes 10 bytes on disk, yet trailing spaces are dropped on concatenation. There is little reason to use char(n); in the Bookstore schema it is only used for country and currency codes (char(2), char(3)), both always full length.

A better way to express the real rule is a CHECK or a domain. A domain is a reusable type together with its constraint:

CREATE DOMAIN isbn13 AS text CHECK (VALUE ~ '^97[89][0-9]{10}$');
SELECT '9780000000001'::isbn13;
SELECT '978-0000000001'::isbn13;

    isbn13
---------------
 9780000000001

ERROR:  value for domain isbn13 violates check constraint "isbn13_check"

The cost of changing a length limit depends on its direction. On a 40,000-row copy we watched the table's physical file number (pg_relation_filenode): changing varchar(13) to varchar(20) or to text didn't touch the file, only the catalog was updated; shrinking back to varchar(13) rewrote the table into a new file. On a large table, shrinking means an ACCESS EXCLUSIVE lock for as long as the rewrite takes.

Numbers: numeric, double precision and Money

SELECT 0.1::double precision + 0.2::double precision = 0.3 AS float_equal,
       0.1::numeric + 0.2::numeric = 0.3 AS numeric_equal;
SELECT sum(price::double precision) AS float_sum, sum(price) AS numeric_sum FROM books;
SELECT 19.905::numeric(10,2) AS rounded, 19.895::double precision::numeric(10,2) AS via_float;

 float_equal | numeric_equal
-------------+---------------
 f           | t

     float_sum     | numeric_sum
-------------------+-------------
 1100346.759999994 |  1100346.76

 rounded | via_float
---------+-----------
   19.91 |     19.90

Use numeric for money and quantities: the sum of 40,000 book prices carries an error below one cent with double precision and is exact with numeric. The last line is sneakier: 19.895 can't be represented exactly in binary floating point, so it is stored as 19.894999... and rounds to 19.90 when cast to numeric(10,2); as a plain numeric it would have been 19.91. Converting a price calculation to float even once is where such cent differences come from. PostgreSQL also has a money type, but its display depends on the lc_monetary setting and it assumes a single currency; since Bookstore holds TRY and EUR, it stores amounts as numeric(12,2) and the currency in a separate column. If you don't specify precision (plain numeric), values are stored as they are with no scale limit; that is exactly the type EF Core generated for a decimal without precision in post 02.

The two remaining basic types are quick: boolean is a real logical type (true, false, NULL) and, unlike bit in SQL Server, can be used directly in a condition as WHERE is_active. bytea is for binary data, the counterpart of varbinary(max); PostgreSQL moves large values out of the row with TOAST, the mechanism that compresses large values and stores them in a separate table.

IDENTITY, serial and uuidv7()

In older PostgreSQL projects you'll see serial. It isn't a real type; it is a shorthand that creates a sequence behind the scenes and sets DEFAULT nextval(...) on the column. That is also where its problem comes from:

CREATE TABLE wishlists_serial (wishlist_id serial PRIMARY KEY, customer_id int NOT NULL);
INSERT INTO wishlists_serial (wishlist_id, customer_id) VALUES (1, 42);
INSERT INTO wishlists_serial (customer_id) VALUES (43);

INSERT 0 1
ERROR:  duplicate key value violates unique constraint "wishlists_serial_pkey"
DETAIL:  Key (wishlist_id)=(1) already exists.

Someone supplied the id by hand, the sequence never heard of it and the next normal insert collided. GENERATED ALWAYS AS IDENTITY prevents manual values with the error we saw in post 02; it is also the SQL standard syntax, its sequence permissions are managed together with the table, and it doesn't cause trouble as a separate object when a table is copied with CREATE TABLE ... (LIKE ...). There is no reason to use serial in new code. GENERATED BY DEFAULT does allow manual values; that's what EF Core's Npgsql provider generates by default.

If you need uuid as an identifier (ids generated on the client, data merged from several sources, URLs that must not be guessable), PostgreSQL 18 offers two functions: the random gen_random_uuid() (version 4, also available as uuidv4() in 18) and the new uuidv7(). The first 48 bits of a version 7 value are a millisecond timestamp, so values increase in creation order:

SELECT gen_random_uuid() AS v4, uuidv7() AS v7a, uuidv7() AS v7b;
SELECT uuid_extract_version(u) AS version, uuid_extract_timestamp(u) AS created
FROM (SELECT uuidv7() AS u) AS s;

                  v4                  |                 v7a                  |                 v7b
--------------------------------------+--------------------------------------+--------------------------------------
 c99536ab-f970-4a98-891a-861170cbb794 | 01a1d7af-09a1-707f-9599-a219669bcebd | 01a1d7af-09a1-7082-87e4-19278cd1ce9c

 version |          created
---------+----------------------------
       7 | 2026-11-14 01:32:15.137+00

That ordering matters for a B-tree index. We inserted one million carts into each of two tables, one with a v4 and one with a v7 primary key, and then measured the indexes with the pgstatindex function of the pgstattuple extension. To install the extension and call this function as the owner role, run CREATE EXTENSION pgstattuple; and GRANT pg_stat_scan_tables TO bookstore_owner; once as postgres:

CREATE TABLE carts_v4 (cart_id uuid PRIMARY KEY DEFAULT gen_random_uuid(), customer_id int NOT NULL);
CREATE TABLE carts_v7 (cart_id uuid PRIMARY KEY DEFAULT uuidv7(), customer_id int NOT NULL);
INSERT INTO carts_v4 (customer_id) SELECT g FROM generate_series(1, 1000000) AS g;
INSERT INTO carts_v7 (customer_id) SELECT g FROM generate_series(1, 1000000) AS g;

SELECT 'v4' AS kind, avg_leaf_density, leaf_pages FROM pgstatindex('carts_v4_pkey')
UNION ALL
SELECT 'v7', avg_leaf_density, leaf_pages FROM pgstatindex('carts_v7_pkey');

 kind | avg_leaf_density | leaf_pages
------+------------------+------------
 v4   |            71.49 |       4828
 v7   |            89.98 |       3832

Random v4 values land all over the index; a full page is split in the middle and pages stay about 71% full on average. v7 values are always appended at the right edge and pages are 90% full. Since v4 is random, its numbers vary slightly from run to run; a second run gave 72.41% and 4,766 pages. The result: for the same data the v7 index has 20% fewer pages, and the inserts wrote 149 MB of WAL instead of 162 MB. In this lab the indexes fit in shared buffers, so the difference is modest; once an index outgrows memory, random inserts start reading a different page from disk every time and the gap widens. The price: the creation time of a record can be read from a v7 value; be careful where you don't want to expose that. In SQL Server NEWSEQUENTIALID() served a similar need; v7 is the standard form of a time-ordered uuid, defined in the current revision of the uuid standard published in 2024.

Enum, Lookup Table, CHECK and Domain

For a fixed list of values such as order status, PostgreSQL has an option of its own: the enum type.

CREATE TYPE order_status AS ENUM ('Pending', 'PaymentPending', 'Shipped', 'Delivered', 'Cancelled');
CREATE TABLE status_demo (order_id int, status order_status);
INSERT INTO status_demo VALUES (1, 'Shipped'), (2, 'Pending'), (3, 'Cancelled');
SELECT order_id, status FROM status_demo ORDER BY status;

INSERT INTO status_demo VALUES (4, 'Returned');
ALTER TYPE order_status ADD VALUE 'Returned' BEFORE 'Cancelled';
ALTER TYPE order_status DROP VALUE 'Returned';

 order_id |  status
----------+-----------
        2 | Pending
        1 | Shipped
        3 | Cancelled

ERROR:  invalid input value for enum order_status: "Returned"
ALTER TYPE
ERROR:  dropping an enum value is not implemented

An enum value takes 4 bytes on disk, sorts in declaration order rather than alphabetically and rejects invalid values. Adding a value and renaming one (RENAME VALUE) are possible, but removing a value is not; to drop a value you have to recreate the type and convert the columns. The criteria from the "Enum or Lookup Table?" post of the Çözüm Desenleri series apply here as well: if the list changes together with the code and is small, use an enum or CHECK; if other data hangs off the value (display name, translations, active flag) or the business manages the list, use a lookup table. In the Bookstore schema orders.status is deliberately text plus CHECK: it causes the fewest problems on the ORM and migration side, and changing the list of values means rewriting a single constraint. We map PostgreSQL enums with Npgsql and EF Core in posts 11 and 12.

Arrays and Ranges

Every type in PostgreSQL has an array type. Let's try a text[] column for book tags:

CREATE TABLE book_tags AS SELECT book_id, title FROM books WHERE book_id <= 6;
ALTER TABLE book_tags ADD COLUMN tags text[] NOT NULL DEFAULT '{}';
UPDATE book_tags SET tags = CASE book_id % 3 WHEN 0 THEN ARRAY['classic', 'bestseller']
                                             WHEN 1 THEN ARRAY['classic']
                                             ELSE ARRAY['fantasy', 'bestseller', 'gift'] END;

SELECT book_id FROM book_tags WHERE 'gift' = ANY (tags) ORDER BY 1;
SELECT book_id FROM book_tags WHERE tags @> ARRAY['classic', 'bestseller'] ORDER BY 1;
SELECT tag, count(*) FROM book_tags, unnest(tags) AS tag GROUP BY tag ORDER BY 2 DESC, 1;

 book_id
---------
       2
       5

 book_id
---------
       3
       6

    tag     | count
------------+-------
 bestseller |     4
 classic    |     4
 fantasy    |     2
 gift       |     2

= ANY asks "is it in the array", @> asks "does it contain all of these", and unnest expands an array into rows. These operators are accelerated by a GIN index, which we see in post 06. Array subscripts start at 1, and an out-of-range subscript returns NULL rather than an error. The right place for an array is a small list of values that has no meaning of its own and doesn't reference another table: tags, allowed formats. We'll see what you lose by putting a relationship into an array in the pitfalls section below.

Range types hold an interval as a single value: int4range, numrange, daterange, tstzrange. Bookstore's campaign periods are exactly that:

CREATE TABLE campaigns
(
    campaign_id int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name        text NOT NULL,
    category    text NOT NULL,
    discount    numeric(4,2) NOT NULL,
    period      daterange NOT NULL
);
INSERT INTO campaigns (name, category, discount, period) VALUES
    ('Back to School', 'Classic', 0.10, '[2026-09-01,2026-09-15)'),
    ('Black Friday',   'SciFi',   0.25, '[2026-11-27,2026-11-30]'),
    ('Winter Reads',   'Classic', 0.15, '[2026-12-01,2027-01-01)');

SELECT name, period FROM campaigns ORDER BY campaign_id;
SELECT name, discount FROM campaigns WHERE period @> date '2026-11-30';
SELECT name FROM campaigns WHERE period && daterange('2026-11-29', '2026-12-03');
SELECT range_agg(period) AS classic_days FROM campaigns WHERE category = 'Classic';

      name      |         period
----------------+-------------------------
 Back to School | [2026-09-01,2026-09-15)
 Black Friday   | [2026-11-27,2026-12-01)
 Winter Reads   | [2026-12-01,2027-01-01)

     name     | discount
--------------+----------
 Black Friday |     0.25

     name
--------------
 Black Friday
 Winter Reads

                   classic_days
---------------------------------------------------
 {[2026-09-01,2026-09-15),[2026-12-01,2027-01-01)}

A square bracket marks an inclusive bound, a parenthesis an exclusive one. [2026-11-27,2026-11-30], which we wrote as "including 30 November", was turned into [2026-11-27,2026-12-01); discrete ranges are always stored in this canonical "start inclusive, end exclusive" form. @> asks "does it contain this date", && asks "do they overlap"; everything you would write in SQL Server with two separate columns and a start <= @d AND @d < end condition becomes a single operator. range_agg merges several intervals into one multirange. The real strength of ranges is on the constraint side: in post 04 we enforce the rule "no two campaigns in the same category may overlap" with an EXCLUDE constraint and with PostgreSQL 18's temporal primary key.

Collation and Turkish: libc, ICU and builtin

A collation decides how text is sorted and compared, and how functions such as upper and lower convert letters. PostgreSQL has three providers: the operating system's C library (libc), ICU, and PostgreSQL's own builtin provider. As we saw in post 02, the lab database was created with libc and en_US.utf8. Let's also look at the ICU collations that come with the image:

SELECT collname, collprovider, colllocale FROM pg_collation
WHERE collname IN ('tr-x-icu', 'und-x-icu', 'pg_c_utf8', 'pg_unicode_fast', 'en_US.utf8');

    collname     | collprovider |   colllocale
-----------------+--------------+-----------------
 pg_c_utf8       | b            | C.UTF-8
 pg_unicode_fast | b            | PG_UNICODE_FAST
 en_US.utf8      | c            |
 und-x-icu       | i            | und
 tr-x-icu        | i            | tr

Turkish's well-known problem is the dotted and dotless i. We ran the same functions with the default collation and with tr-x-icu:

SELECT upper('istanbul') AS default_upper,
       upper('istanbul' COLLATE "tr-x-icu") AS tr_upper,
       lower('ISPARTA') AS default_lower,
       lower('ISPARTA' COLLATE "tr-x-icu") AS tr_lower;

 default_upper | tr_upper | default_lower | tr_lower
---------------+----------+---------------+----------
 ISTANBUL      | İSTANBUL | isparta       | ısparta

With the default (English) rules istanbul becomes "ISTANBUL" in upper case, with Turkish rules "İSTANBUL". We met the same Turkish İ trap on SQL Server in the data types post of the SQL Temelleri series; in PostgreSQL the answer is again to give the expression or the column the right collation. Sorting differs too:

CREATE TABLE words (w text);
INSERT INTO words VALUES ('Cam'), ('Çiçek'), ('Dut'), ('Ilık'), ('İnci'), ('Işık'), ('Su'), ('Şeker'), ('Tuz'), ('Zil');

SELECT string_agg(w, ' ' ORDER BY w) AS default_en_us,
       string_agg(w, ' ' ORDER BY w COLLATE "tr-x-icu") AS tr_icu,
       string_agg(w, ' ' ORDER BY w COLLATE "C") AS c_bytes
FROM words;

                 default_en_us                 |                    tr_icu                     |                    c_bytes
-----------------------------------------------+-----------------------------------------------+-----------------------------------------------
 Cam Çiçek Dut Ilık İnci Işık Şeker Su Tuz Zil | Cam Çiçek Dut Ilık Işık İnci Su Şeker Tuz Zil | Cam Dut Ilık Işık Su Tuz Zil Çiçek İnci Şeker

English rules treat Ş as the same letter as S, put "Şeker" before "Su" and mix up İ and I; Turkish rules give the alphabetical order; C is byte order and pushes the Turkish letters to the end. For a catalog with Turkish content, sorting book titles correctly means giving the column COLLATE "tr-x-icu" or creating the database with ICU from the start (CREATE DATABASE ... LOCALE_PROVIDER icu ICU_LOCALE 'tr-TR' TEMPLATE template0; without template0 you get a provider mismatch error).

For case-insensitive comparison, PostgreSQL's counterpart of SQL Server's _CI_ collations is a nondeterministic collation. Since PostgreSQL 18 these collations also work with LIKE:

CREATE COLLATION tr_ci (provider = icu, locale = 'tr-TR-u-ks-level2', deterministic = false);
CREATE TABLE ci_demo (email text COLLATE tr_ci UNIQUE);
INSERT INTO ci_demo VALUES ('Ada@Example.com');
INSERT INTO ci_demo VALUES ('ada@example.com');
SELECT email FROM ci_demo WHERE email LIKE 'ada@%';
SELECT 'ISTANBUL' = 'istanbul' COLLATE tr_ci AS i_eq, 'İSTANBUL' = 'istanbul' COLLATE tr_ci AS dotted_i_eq;

ERROR:  duplicate key value violates unique constraint "ci_demo_email_key"
DETAIL:  Key (email)=(ada@example.com) already exists.

      email
-----------------
 Ada@Example.com

 i_eq | dotted_i_eq
------+-------------
 f    | t

The unique constraint treated a difference in case as the same value, and LIKE matched. The last line is the result of Turkish rules: the lower case of "ISTANBUL" is "ıstanbul", so it doesn't equal "istanbul", while "İSTANBUL" does. For language-neutral fields such as e-mail this may not be what you want; a language-neutral locale such as und-u-ks-level2 fits better there. The citext extension used to be the tool for this job; the PostgreSQL documentation now recommends nondeterministic collations instead, because citext always lower-cases with the database's LC_CTYPE setting and doesn't handle Unicode special cases correctly.

Two PostgreSQL 18 features touch this topic as well. casefold() does full case folding designed for comparisons and, unlike lower(), covers letters that turn into more than one character. We tried it with pg_unicode_fast and tr-x-icu:

SELECT casefold('Straße' COLLATE "pg_unicode_fast") AS casefold_fast,
       lower('Straße' COLLATE "pg_unicode_fast") AS lower_fast,
       casefold('ISPARTA' COLLATE "tr-x-icu") AS casefold_tr;

 casefold_fast | lower_fast | casefold_tr
---------------+------------+-------------
 strasse       | straße     | ısparta

The German ß expanded into two letters, and in the Turkish ICU collation the letter I became ı. The new builtin PG_UNICODE_FAST collation is a fast option that applies no language rules and doesn't change between versions; if you want Turkish sorting or the I/ı rule, you need ICU.

In Production

Collation versions: libc and ICU collations depend on operating system libraries. When you upgrade the image or the server's operating system, sorting rules can change and B-tree indexes on text columns can silently break. When PostgreSQL notices this it warns about a "collation version mismatch"; the right response is to REINDEX the affected indexes and run ALTER DATABASE ... REFRESH COLLATION VERSION. The builtin provider was added precisely because it doesn't carry this dependency.

Type changes: an ALTER TABLE that changes a column type usually rewrites the table and locks it for the duration. Widening (varchar(13) → varchar(20) or text) is cheap; shrinking and converting types are expensive. On large tables you need the add a new column, copy the data in batches, then switch approach; working safely with large tables is covered in the Veritabanı Performansı ve Scaling series.

Time zone: leave the database TimeZone setting at UTC and convert explicitly in the presentation layer or in the query with AT TIME ZONE. Reporting tools and psql sessions may run in different time zones; with timestamptz the data stays correct and only the display changes.

Common Pitfalls

Writing local time into a timestamp column: datetime2 columns ported from SQL Server often arrive as timestamp. That works if the values are UTC and always stay UTC, but if one day a service writes local time, there is no way left to tell which row is in which time. Convert them to timestamptz when you port.

Using an array instead of a relationship: book_ids int[] looks handy for a wish list, but you can't define a foreign key:

CREATE TABLE wishlists (wishlist_id int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
                        book_ids int[] REFERENCES books (book_id));

ERROR:  foreign key constraint "wishlists_book_ids_fkey" cannot be implemented
DETAIL:  Key columns "book_ids" of the referencing table and "book_id" of the referenced table are of incompatible types: integer[] and integer.

Without the constraint, ARRAY[1, 2, 999999] was stored without complaint; the missing book only showed up in a query as an empty title through a LEFT JOIN. The id of a deleted book also stays silently in the arrays. A link table with one row per entry (wishlist_items) gives you both the foreign key and an index for the "how many people want this book" query.

Calculating money with double precision: the cent difference we saw above turns into reports that don't match accounting once thousands of orders are summed.

Choosing an enum for lists that change often: since values can't be removed, a value added by mistake or no longer used stays in the type for good.

A Small Experiment

★☆☆ In the lab, run SELECT now(), now() AT TIME ZONE 'Europe/Istanbul', pg_typeof(now());. Then run SET TimeZone = 'Asia/Tokyo'; and repeat. Which value changed and which didn't?

★★☆ On a copy of the books table, give the title column COLLATE "tr-x-icu" and compare the result of ORDER BY title LIMIT 20 with the default collation. Then put a normal B-tree index on that column and check with EXPLAIN whether a query with ORDER BY title COLLATE "C" uses the index.

★★★ Write an INSERT that adds an overlapping campaign for the same category to the campaigns table and try to prevent it: first with a trigger, then with a constraint only. We'll see how the second approach works in the next post.

In the next post we move on to PostgreSQL-specific SQL: RETURNING and 18's RETURNING OLD/NEW, upserts with ON CONFLICT, DISTINCT ON, writable CTEs, EXCLUDE constraints and generated columns.