PostgreSQL-Specific SQL and Constraints: RETURNING, ON CONFLICT, DISTINCT ON, Writable CTEs, EXCLUDE and Generated Columns

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

Guess First

We are porting the add-to-cart code of Bookstore, the online bookstore used as the shared example across this site's series, to PostgreSQL. In SQL Server this is usually written in two steps, "update the row if it exists, otherwise insert it", or with MERGE. In PostgreSQL a single statement is enough. The cart table and three commands that add the same book three times:

CREATE TABLE cart_items
(
    cart_id  int NOT NULL,
    book_id  int NOT NULL REFERENCES books (book_id),
    quantity int NOT NULL CHECK (quantity > 0),
    added_at timestamptz NOT NULL DEFAULT now(),
    PRIMARY KEY (cart_id, book_id)
);

INSERT INTO cart_items (cart_id, book_id, quantity) VALUES (1, 7, 1)
ON CONFLICT (cart_id, book_id) DO UPDATE SET quantity = cart_items.quantity + EXCLUDED.quantity
RETURNING book_id, old.quantity AS old_qty, new.quantity AS new_qty, old IS NULL AS inserted;

INSERT INTO cart_items (cart_id, book_id, quantity) VALUES (1, 7, 2)
ON CONFLICT (cart_id, book_id) DO UPDATE SET quantity = cart_items.quantity + EXCLUDED.quantity
RETURNING book_id, old.quantity AS old_qty, new.quantity AS new_qty, old IS NULL AS inserted;

INSERT INTO cart_items (cart_id, book_id, quantity) VALUES (1, 2, 1), (1, 2, 3)
ON CONFLICT (cart_id, book_id) DO UPDATE SET quantity = cart_items.quantity + EXCLUDED.quantity;

How many copies are in the cart after the second command? The third command sends the same book as two rows at once; is the result 4 copies, 3, or an error?

 book_id | old_qty | new_qty | inserted
---------+---------+---------+----------
       7 |         |       1 | t

 book_id | old_qty | new_qty | inserted
---------+---------+---------+----------
       7 |       1 |       3 | f

ERROR:  ON CONFLICT DO UPDATE command cannot affect row a second time
HINT:  Ensure that no rows proposed for insertion within the same command have duplicate constrained values.

The first command inserted the row, the second caught the conflict and raised the quantity to 3, and thanks to PostgreSQL 18's support for RETURNING OLD/NEW it gave us both the old and the new value. The third command failed: a row can't be updated twice within the same statement, because the order in which the result would be produced is undefined. In this post we look at SQL features of PostgreSQL that SQL Server doesn't have or that work differently there: RETURNING, upserts, MERGE, DISTINCT ON, LATERAL, writable CTEs and constraints that have no SQL Server counterpart.

RETURNING and RETURNING OLD/NEW

INSERT, UPDATE, DELETE and MERGE can return the rows they affect with RETURNING. It is the counterpart of SQL Server's OUTPUT inserted.* and OUTPUT deleted.*; until PostgreSQL 18 only the new value could be returned, and 18 added the old and new aliases. Stock reservation in Bookstore is a job made for this feature:

BEGIN;
INSERT INTO authors (name, country) VALUES ('Ursula K. Le Guin', 'US') RETURNING author_id, name;

UPDATE stock_items
SET quantity_on_hand = quantity_on_hand - 2,
    quantity_reserved = quantity_reserved + 2,
    updated_at = now()
WHERE book_id = 1 AND quantity_on_hand >= 2
RETURNING book_id, old.quantity_on_hand AS before, new.quantity_on_hand AS after, new.quantity_reserved AS reserved;

UPDATE stock_items
SET quantity_on_hand = quantity_on_hand - 500
WHERE book_id = 1 AND quantity_on_hand >= 500
RETURNING book_id, old.quantity_on_hand AS before, new.quantity_on_hand AS after;
ROLLBACK;

 author_id |       name
-----------+-------------------
      5001 | Ursula K. Le Guin

 book_id | before | after | reserved
---------+--------+-------+----------
       1 |     36 |    34 |        2

 book_id | before | after
---------+--------+-------
(0 rows)

The reservation is a single statement: the condition (quantity_on_hand >= 2) and the update happen together under the row lock; if two users ask for the last two copies at the same time, only one gets a row back and the other sees an empty result. There is no need for "read first, then write" in the application and the lost update problem that comes with it. An empty result means "not enough stock", and we know it without an extra SELECT. Seeing the previous value used to require either a trigger or a separate read.

One detail: we rolled the transaction back, but author_id 5001 was used up. Sequences are not transactional; a number handed out is not returned on rollback. It is the same behavior as gaps in SQL Server's IDENTITY; don't write code that assumes ids have no gaps.

Upserts with ON CONFLICT

In the commands from "Guess First", ON CONFLICT (cart_id, book_id) says which constraint the conflict is detected on. EXCLUDED stands for the row that was proposed for insertion but conflicted, while cart_items.quantity is the existing value in the table. The conflict target has to match a unique constraint, a unique index or an exclusion constraint:

INSERT INTO cart_items (cart_id, book_id, quantity) VALUES (1, 7, 5)
ON CONFLICT (cart_id, book_id) DO NOTHING
RETURNING book_id, quantity;

INSERT INTO cart_items (cart_id, book_id, quantity) VALUES (1, 7, 5)
ON CONFLICT (book_id) DO NOTHING;

 book_id | quantity
---------+----------
(0 rows)

ERROR:  there is no unique or exclusion constraint matching the ON CONFLICT specification

DO NOTHING does nothing on conflict and RETURNING comes back empty; that's how you can tell the row already existed. Since there is no unique constraint on book_id alone, the second command never ran. The real value of ON CONFLICT is under concurrency: of two sessions inserting the same key at the same time, one inserts, the other waits and then takes the update branch; it doesn't get a unique violation. In SQL Server getting the same guarantee requires adding HOLDLOCK to MERGE or using a serializable transaction.

MERGE versus ON CONFLICT

PostgreSQL 15 brought standard MERGE, and 17 added RETURNING and merge_action(), which tells you which branch ran. Let's apply a price file from a supplier to the catalog:

CREATE TEMP TABLE price_feed (isbn varchar(13), price numeric(10,2));
INSERT INTO price_feed VALUES ('9780000000001', 29.90), ('9780000000002', 39.00), ('9780000099999', 12.50);

BEGIN;
MERGE INTO books AS b
USING price_feed AS f ON b.isbn = f.isbn
WHEN MATCHED AND b.price <> f.price THEN UPDATE SET price = f.price
WHEN MATCHED THEN DO NOTHING
WHEN NOT MATCHED THEN INSERT (isbn, title, author_id, category, price, published_year)
                      VALUES (f.isbn, 'Unknown #' || f.isbn, 1, 'Classic', f.price, 2026)
RETURNING merge_action() AS action, b.book_id, f.isbn, old.price AS old_price, new.price AS new_price;
ROLLBACK;

 action | book_id |     isbn      | old_price | new_price
--------+---------+---------------+-----------+-----------
 UPDATE |       1 | 9780000000001 |     32.77 |     29.90
 INSERT |   40001 | 9780000099999 |           |     12.50

The second book's price was already 39.00, so it took the DO NOTHING branch and doesn't show up in the result. Which one should you use when? MERGE is for comparing a source set with a target table and updating, inserting and deleting according to several conditions: bulk synchronization, ETL. ON CONFLICT is for inserting or updating individual rows safely under concurrency: carts, counters, idempotent message handling. The documentation also points to ON CONFLICT when you need the guarantee of running an update if a concurrent insert happens; MERGE follows the usual isolation rules, and if another session inserted the same key you can get a unique violation.

DISTINCT ON, LATERAL, generate_series and FILTER

In the SQL Temelleri series we solved "latest per group" with a window function. PostgreSQL has a shortcut: DISTINCT ON keeps the first row of each group according to ORDER BY.

SELECT DISTINCT ON (category) category, book_id, title, published_year
FROM books
ORDER BY category, published_year DESC, book_id;

 category | book_id |       title       | published_year
----------+---------+-------------------+----------------
 Classic  |     531 | Ulysses #531      |           2015
 Fantasy  |   13682 | The Hobbit #13682 |           2015
 Horror   |   15030 | Dracula #15030    |           2015
 SciFi    |    1960 | Dune #1960        |           2015

The leading columns of ORDER BY must match the expressions in DISTINCT ON; the remaining columns decide which row counts as "first". Several books share the same year, so we added book_id as well; without it the result might not be the same on every run. If you need more than one row per group, use LATERAL, the counterpart of SQL Server's CROSS APPLY. The last two orders of each customer:

SELECT c.customer_id, c.first_name, o.order_number, o.created_at, o.total_amount
FROM customers AS c
CROSS JOIN LATERAL (
    SELECT order_number, created_at, total_amount
    FROM orders
    WHERE orders.customer_id = c.customer_id
    ORDER BY created_at DESC
    LIMIT 2
) AS o
WHERE c.customer_id IN (2, 3)
ORDER BY c.customer_id, o.created_at DESC;

 customer_id | first_name | order_number |       created_at       | total_amount
-------------+------------+--------------+------------------------+--------------
           2 | Linus      | BK-00999989  | 2026-10-20 20:56:00+00 |        67.36
           2 | Linus      | BK-00999983  | 2026-10-20 20:44:00+00 |        56.75
           3 | Margaret   | BK-00999864  | 2026-10-20 16:46:00+00 |        39.37
           3 | Margaret   | BK-00999724  | 2026-10-20 12:06:00+00 |       119.71

Two more needs come up often in reports: showing empty days too, and conditional counts in a single pass. generate_series produces a date range, and FILTER (WHERE ...) added to an aggregate is a readable version of the SUM(CASE WHEN ... END) idiom:

SELECT d::date AS day,
       count(o.order_id) AS orders,
       count(o.order_id) FILTER (WHERE o.status = 'Cancelled') AS cancelled,
       coalesce(sum(o.total_amount) FILTER (WHERE o.status <> 'Cancelled'), 0) AS revenue
FROM generate_series(timestamptz '2026-10-18', timestamptz '2026-10-22', interval '1 day') AS d
LEFT JOIN orders AS o ON o.created_at >= d AND o.created_at < d + interval '1 day'
GROUP BY d
ORDER BY d;

    day     | orders | cancelled | revenue
------------+--------+-----------+----------
 2026-10-18 |    720 |        29 | 54800.61
 2026-10-19 |    720 |        29 | 55140.97
 2026-10-20 |    640 |        25 | 49863.71
 2026-10-21 |      0 |         0 |        0
 2026-10-22 |      0 |         0 |        0

The lab data ends on the evening of 20 October; 21 and 22 October don't exist in the orders table at all, yet they appear in the report with zeros. GENERATE_SERIES, added in SQL Server 2022, only produces numbers and needs extra arithmetic for dates; in PostgreSQL it works directly with timestamptz and interval.

Writable CTEs: Archive and Delete in One Statement

In PostgreSQL the query inside a WITH can be a DELETE, UPDATE or INSERT, and it can pass the rows it produces to the next step with RETURNING. A single statement that moves the parcels delivered in January 2023 to an archive table and deletes them from the main table:

BEGIN;
CREATE TABLE shipments_archive (LIKE shipments);
WITH archived AS (
    DELETE FROM shipments
    WHERE delivered_at < timestamptz '2023-02-01'
    RETURNING *
), inserted AS (
    INSERT INTO shipments_archive SELECT * FROM archived
    RETURNING shipment_id
)
SELECT count(*) AS moved, min(shipment_id) AS first_id, max(shipment_id) AS last_id FROM inserted;
SELECT (SELECT count(*) FROM shipments) AS live, (SELECT count(*) FROM shipments_archive) AS archived;
ROLLBACK;

 moved | first_id | last_id
-------+----------+---------
 17966 |        1 |   19297

  live  | archived
--------+----------
 936789 |    17966

The delete and the insert run in the same statement with the same snapshot; a parcel another session adds in the meantime is not deleted by mistake, and no row ends up both in the archive and in the main table. In SQL Server DELETE ... OUTPUT deleted.* INTO archive does the same job; in PostgreSQL you can chain more than two steps. We created the archive table with (LIKE shipments); with INCLUDING ALL the identity column would have been copied too, and INSERT ... SELECT * would have raised the GENERATED ALWAYS error we saw in post 02. If millions of rows have to move, run this statement in batches (per day, for example); it shouldn't be one operation that locks a huge number of rows and bloats the WAL.

Constraints: Deferrable Foreign Keys and EXCLUDE

In SQL Server constraints are checked after every statement. In PostgreSQL, if a constraint is declared DEFERRABLE, the check can be postponed until commit. Let's try inserting an order line before its order:

ALTER TABLE order_items ALTER CONSTRAINT fk_order_items_orders DEFERRABLE INITIALLY IMMEDIATE;

BEGIN;
SET CONSTRAINTS fk_order_items_orders DEFERRED;
INSERT INTO order_items (order_id, book_id, quantity, unit_price) VALUES (1000001, 1, 1, 32.77);
INSERT INTO orders (order_id, order_number, customer_id, status, created_at, total_amount, currency)
OVERRIDING SYSTEM VALUE VALUES (1000001, 'BK-01000001', 42, 'Pending', now(), 32.77, 'TRY');
SET CONSTRAINTS fk_order_items_orders IMMEDIATE;
ROLLBACK;

BEGIN;
SET CONSTRAINTS fk_order_items_orders DEFERRED;
INSERT INTO order_items (order_id, book_id, quantity, unit_price) VALUES (1000002, 1, 1, 32.77);
COMMIT;

INSERT 0 1
INSERT 0 1
SET CONSTRAINTS
ROLLBACK
...
INSERT 0 1
ERROR:  insert or update on table "order_items" violates foreign key constraint "fk_order_items_orders"
DETAIL:  Key (order_id)=(1000002) is not present in table "orders".

In the first transaction the check was deferred until SET CONSTRAINTS ... IMMEDIATE, and since the order existed by then it passed. In the second the order was never inserted; the line was accepted but COMMIT failed and the transaction was rolled back. Deferrable constraints make tables that reference each other possible, as well as bulk loads whose order the application doesn't control. Leaving the default as NOT DEFERRABLE is right; postponing the error until commit makes it harder to find the code that produced the bad row. After the experiment we set the constraint back to NOT DEFERRABLE.

There is also a constraint type with no SQL Server counterpart at all: the exclusion constraint. A unique constraint says "there can't be two of the same value"; an exclusion constraint says "there can't be two rows for which all the given operators return true". The rule "no two overlapping campaigns in the same category" on the campaign table from post 03:

ALTER TABLE campaigns ADD CONSTRAINT ex_campaigns_category_period
    EXCLUDE USING gist (category WITH =, period WITH &&);

ERROR:  data type text has no default operator class for access method "gist"
HINT:  You must specify an operator class for the index or define a default operator class for the data type.

An exclusion constraint works on top of a GiST index; GiST support for ranges is built in, but checking equality on text with GiST requires the btree_gist extension. This extension is marked as trusted, so the owner role, which has CREATE on the database, can install it without being a superuser:

CREATE EXTENSION btree_gist;
ALTER TABLE campaigns ADD CONSTRAINT ex_campaigns_category_period
    EXCLUDE USING gist (category WITH =, period WITH &&);
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)');
INSERT INTO campaigns (name, category, discount, period)
VALUES ('Cyber Monday', 'SciFi', 0.30, '[2026-11-30,2026-12-02)');

CREATE EXTENSION
ALTER TABLE
INSERT 0 3
ERROR:  conflicting key value violates exclusion constraint "ex_campaigns_category_period"
DETAIL:  Key (category, period)=(SciFi, [2026-11-30,2026-12-02)) conflicts with existing key (category, period)=(SciFi, [2026-11-27,2026-12-01)).

Black Friday's canonical form extends to 1 December, so Cyber Monday starting on 30 November was rejected. Writing this rule with a trigger or in application code isn't reliable, because two concurrent inserts don't see each other; the constraint works at the index level and stays correct under concurrency. The same structure fits every "no overlapping intervals" rule: hotel rooms, appointments, warehouse loading docks.

PostgreSQL 18: Temporal Keys, NOT NULL NOT VALID and Virtual Generated Columns

PostgreSQL 18 moved the most common use of exclusion constraints into standard syntax: temporal primary keys with WITHOUT OVERLAPS and temporal foreign keys with PERIOD. In Bookstore a book's price changes by period, and a book included in a campaign must have a valid price for the whole campaign:

CREATE TABLE book_prices
(
    book_id      int NOT NULL REFERENCES books (book_id),
    valid_during daterange NOT NULL,
    price        numeric(10,2) NOT NULL,
    PRIMARY KEY (book_id, valid_during WITHOUT OVERLAPS)
);
INSERT INTO book_prices VALUES
    (1, '[2026-01-01,2026-07-01)', 32.77),
    (1, '[2026-07-01,2027-01-01)', 34.90);
INSERT INTO book_prices VALUES (1, '[2026-12-01,2027-02-01)', 36.00);

CREATE TABLE campaign_items
(
    campaign_id  int NOT NULL REFERENCES campaigns (campaign_id),
    book_id      int NOT NULL,
    valid_during daterange NOT NULL,
    PRIMARY KEY (campaign_id, book_id),
    FOREIGN KEY (book_id, PERIOD valid_during) REFERENCES book_prices (book_id, PERIOD valid_during)
);
INSERT INTO campaign_items VALUES (1, 1, '[2026-06-15,2026-07-15)');
INSERT INTO campaign_items VALUES (3, 1, '[2026-12-20,2027-01-10)');

INSERT 0 2
ERROR:  conflicting key value violates exclusion constraint "book_prices_pkey"
DETAIL:  Key (book_id, valid_during)=(1, [2026-12-01,2027-02-01)) conflicts with existing key (book_id, valid_during)=(1, [2026-07-01,2027-01-01)).
CREATE TABLE
INSERT 0 1
ERROR:  insert or update on table "campaign_items" violates foreign key constraint "campaign_items_book_id_valid_during_fkey"
DETAIL:  Key (book_id, valid_during)=(1, [2026-12-20,2027-01-10)) is not present in table "book_prices".

The primary key rejected two overlapping price periods for the same book; the error message saying "exclusion constraint" is no coincidence, since pg_indexes shows the primary key is a GiST index (which is why btree_gist is needed here as well). The foreign key side is more interesting: the 15 June to 15 July interval spans two separate price periods and was accepted, because the check asks whether the interval is covered by the union of the price periods. The interval running into 2027 was rejected; there is no price after 1 January 2027.

The second new feature in 18 is adding a NOT NULL constraint as NOT VALID. When you add a mandatory column to a large table and can't fill the old rows right away, you can start enforcing the rule for new rows immediately and validate the old ones later. We added the column inside a transaction and rolled it back at the end:

ALTER TABLE customers ADD COLUMN phone text;
UPDATE customers SET phone = '+90 555 000 ' || lpad(customer_id::text, 4, '0') WHERE customer_id <= 10;
ALTER TABLE customers ADD CONSTRAINT customers_phone_not_null NOT NULL phone NOT VALID;

INSERT INTO customers (first_name, last_name, email, country, city, created_at)
VALUES ('Ada', 'Lovelace', 'new-ada@example.com', 'GB', 'London', now());
SELECT count(*) FILTER (WHERE phone IS NULL) AS old_rows_without_phone FROM customers;
ALTER TABLE customers VALIDATE CONSTRAINT customers_phone_not_null;

ERROR:  null value in column "phone" of relation "customers" violates not-null constraint
 old_rows_without_phone
------------------------
                  99990

ERROR:  column "phone" of relation "customers" contains null values

The new row couldn't be inserted without a phone number, but the 99,990 old rows stayed as they were and the constraint is in the convalidated = f state. Once the old rows are filled, VALIDATE CONSTRAINT scans the table; this scan doesn't take a heavy lock that blocks writes. It is the counterpart of a SQL Server constraint added WITH NOCHECK and later validated with WITH CHECK CHECK CONSTRAINT.

The third change concerns generated columns. Since PostgreSQL 12 there have been stored generated columns, which write the computed value to disk; 18 brought virtual generated columns, computed at read time, and made them the default. On a copy of 250 thousand order lines we added both and watched the table's physical file:

CREATE TABLE gen_demo AS SELECT order_item_id, order_id, quantity, unit_price FROM order_items WHERE order_id <= 100000;
SELECT pg_relation_filenode('gen_demo') AS filenode;
ALTER TABLE gen_demo ADD COLUMN line_total numeric(12,2) GENERATED ALWAYS AS (quantity * unit_price);
SELECT pg_relation_filenode('gen_demo') AS filenode_after_virtual;
ALTER TABLE gen_demo ADD COLUMN line_total_stored numeric(12,2) GENERATED ALWAYS AS (quantity * unit_price) STORED;
SELECT pg_relation_filenode('gen_demo') AS filenode_after_stored;
CREATE INDEX ix_gen_demo_line_total ON gen_demo (line_total);
CREATE INDEX ix_gen_demo_line_total_stored ON gen_demo (line_total_stored);

 filenode
----------
    19546

 filenode_after_virtual
------------------------
                  19546

 filenode_after_stored
-----------------------
                 19551

ERROR:  indexes on virtual generated columns are not supported
CREATE INDEX

Adding the virtual column only changed the catalog; the stored column rewrote the table into a new file. On the other hand, a virtual column can't be indexed; if you need an index to filter or sort on it, you have to write STORED. Mapped to SQL Server: a default (virtual) computed column versus a PERSISTED computed column. The difference is that SQL Server can also index a non-persisted computed column under certain conditions.

In Production

Exclusion constraints and temporal keys use GiST indexes, which are usually more expensive to insert into and larger than B-tree. Measure that cost on write-heavy tables such as reservations. ON CONFLICT uses up a number from the identity sequence even when a conflict happens: in an experiment where we upserted the same key three times and then added a new key, the new row got number 4. A frequently conflicting upsert means large gaps in ids and, on int columns, running out of numbers sooner than expected. Use bigint on counter-like tables or try the update first.

Writable CTEs and MERGE on large data sets mean one transaction and one long-held set of locks. Split bulk deletes and moves into small batches; otherwise WAL piles up, replicas fall behind and the long transaction stops autovacuum. Deferrable unique constraints can't be used as an ON CONFLICT target (the error is "ON CONFLICT does not support deferrable unique constraints/exclusion constraints as arbiters"), and the planner doesn't rely on them for optimizations that need a uniqueness guarantee; keep the default unless you need it.

Common Pitfalls

Not matching the ON CONFLICT target to a constraint: the conflict target must match exactly the columns of a unique index (and, for a partial index, its WHERE condition). Column order doesn't matter, but a missing or extra column is an error.

Leaving the ordering incomplete in DISTINCT ON: if ORDER BY doesn't fully determine the order within a group, which row comes back is undefined, and when the plan changes the result changes too.

Forgetting to qualify the existing value: with SET quantity = quantity + EXCLUDED.quantity PostgreSQL can't tell which quantity you mean and fails with column reference "quantity" is ambiguous. Qualify the existing value on the right-hand side with the table name (cart_items.quantity) or an alias.

Expecting index-backed performance from a virtual generated column: in PostgreSQL 18 a column is virtual unless you write STORED. Keep this change of default in mind for schemas ported from older versions.

A Small Experiment

★☆☆ Write an upsert for cart_items that, when the same book is added again, updates added_at instead of increasing the quantity and returns "inserted" or "updated" with RETURNING.

★★☆ Rewrite the DISTINCT ON query with the ROW_NUMBER() OVER (PARTITION BY category ...) we saw in SQL Temelleri and compare the EXPLAIN output of the two queries.

★★★ Design a reservation table for warehouse loading docks: no two overlapping time intervals on the same dock, but cancelled reservations must not count. Hint: an exclusion constraint can have a WHERE condition too.

In the next post we turn to what may be PostgreSQL's most widely used feature: JSONB. Operators, SQL/JSON functions, GIN indexes and when JSONB is the wrong choice.