# Coming from Postgres 18? What to change for Postgres 17 Relyt One runs PostgreSQL 17. If your schema or your habits come from Postgres 18, here is what fails on 17, what to write instead, and how to load a dump taken on 18, with every snippet run on both versions. **Relyt One runs PostgreSQL 17. Postgres 18 shipped in September 2025, so plenty of new projects, tutorials and AI-generated schemas now lean on 18-only features: `uuidv7()`, virtual generated columns, `OLD` and `NEW` in `RETURNING`, temporal primary keys. On 17 each of these is an error. This post lists what fails, gives a replacement for each that runs on 17 today, and walks through loading a dump taken on 18. We ran every snippet on PostgreSQL 17.5 and 18.6; the outputs shown are what we got.** None of this is exotic. Most schemas use none of these features, and the ones that do usually use one or two. The point is to find them before a migration fails halfway, not after. ![Four Postgres 18 features and what to use on Postgres 17: uuidv7() becomes a SQL function, a virtual generated column becomes STORED or a view, RETURNING old/new becomes a CTE with FOR UPDATE, and WITHOUT OVERLAPS becomes an exclusion constraint with btree_gist](/blog/assets/4ab60f9f573c6be1.svg) ## First, check what you are talking to ```sql SELECT version(); SHOW server_version_num; -- 170005 means 17.5; anything starting with 17 is Postgres 17 ``` If your code needs to branch on the version at runtime, compare `server_version_num` numerically (`>= 180000`), not the `version()` string. ## 1. `uuidv7()` Postgres 18 added `uuidv7()`, which generates time-ordered UUIDs. They make better primary keys than random version 4 UUIDs because new rows land at the end of the index instead of all over it. On 17: ```text ERROR: function uuidv7() does not exist ``` You can generate the same format in SQL. A version 7 UUID is a 48-bit Unix timestamp in milliseconds followed by random bits, with the version and variant bits set. `gen_random_uuid()` (built in since Postgres 13, no extension needed) already provides the random bits and the variant, so the function overwrites the first six bytes with the timestamp and flips two bits to change the version from 4 to 7: ```sql CREATE FUNCTION public.uuidv7() RETURNS uuid LANGUAGE sql VOLATILE AS $$ -- 48-bit Unix time in ms, then version 7 over the random bits of a v4 UUID SELECT encode( set_bit(set_bit( overlay(uuid_send(gen_random_uuid()) PLACING substring(int8send(floor(extract(epoch FROM clock_timestamp()) * 1000)::bigint) FROM 3) FROM 1 FOR 6), 52, 1), 53, 1), 'hex')::uuid; $$; CREATE TABLE events ( id uuid PRIMARY KEY DEFAULT public.uuidv7(), body text ); ``` On Postgres 15 and later only the database owner can create objects in `public` by default, so run this as the owner (or put the function in a schema you own). We checked 10,000 generated values: every one has version 7 and the RFC 9562 variant, and the embedded timestamp matched the server clock. It uses `clock_timestamp()` rather than `now()`, so rows inserted later in the same transaction still get later timestamps. There is one real difference. The built-in function in 18 also fills in a sub-millisecond part, and in our test 1,000 values generated in one statement on 18 came out in strictly increasing order. The SQL version orders values across milliseconds but not within one: values generated in the same millisecond are in random order. For index locality that does not matter. If your code relies on IDs from one session being strictly increasing, use a sequence for ordering instead. A related trap: `uuid_extract_timestamp()` exists in 17, but there it only understands version 1 UUIDs and returns `NULL` for version 7. Postgres 18 added version 7 support. On 17, extract the timestamp yourself: ```sql CREATE FUNCTION public.uuidv7_timestamp(u uuid) RETURNS timestamptz LANGUAGE sql IMMUTABLE STRICT AS $$ SELECT to_timestamp(('x' || substr(replace(u::text, '-', ''), 1, 12))::bit(48)::bigint / 1000.0); $$; SELECT public.uuidv7_timestamp('019535d9-3df7-79fb-b466-fa907fa17f9e'); -- 2025-02-24 02:46:24.503+00, the same value 18's uuid_extract_timestamp() returns ``` Unlike the built-in, this helper doesn't check the version: give it a version 4 UUID and it returns a meaningless time instead of `NULL`. Only call it on IDs you know are version 7. ## 2. Virtual generated columns In 18, a generated column is computed when it is read unless you say otherwise. `VIRTUAL` is the new default, and `STORED` is the old behavior. 17 only has stored generated columns, and it requires the keyword. Both of these fail on 17: ```sql CREATE TABLE product (price_cents int, price numeric GENERATED ALWAYS AS (price_cents / 100.0) VIRTUAL); -- ERROR: syntax error at or near "VIRTUAL" CREATE TABLE product (price_cents int, price numeric GENERATED ALWAYS AS (price_cents / 100.0)); -- ERROR: syntax error at or near ")" ``` The second one matters more than it looks: on 18 it is valid and means virtual, so schemas written for 18 often have no keyword at all. Two replacements: ```sql -- Option A: store it. Same column, same queries; costs disk and write time. CREATE TABLE product ( price_cents int, price numeric GENERATED ALWAYS AS (price_cents / 100.0) STORED ); -- Option B: keep the table lean and compute in a view. CREATE TABLE product_base (price_cents int); CREATE VIEW product AS SELECT *, price_cents / 100.0 AS price FROM product_base; ``` A simple view like this one is automatically updatable, so `INSERT INTO product (price_cents) VALUES (1999)` still works through it. We tried that on 17. Pick A unless the expression is large or the table is write-heavy. ## 3. `OLD` and `NEW` in `RETURNING` 18 lets `RETURNING` name the row before and after a change: ```sql UPDATE acct SET balance = balance - 30 WHERE id = 1 RETURNING old.balance AS before, new.balance AS after; ``` On 17 that fails with `missing FROM-clause entry for table "old"`. To get the previous values, read and lock the row in a CTE, then join to it in the `UPDATE`: ```sql WITH old AS ( SELECT id, balance FROM acct WHERE id = 1 FOR UPDATE ) UPDATE acct a SET balance = a.balance - 30 FROM old WHERE a.id = old.id RETURNING old.balance AS before, a.balance AS after; ``` ```text before | after --------+------- 100 | 70 ``` `FOR UPDATE` is what makes this correct: it locks the row before the update reads it, so a concurrent change can't land between the two and make `before` a lie. Without it, under concurrency, you can return a "before" value that was never the row's state at the time of your update. For `DELETE`, plain `RETURNING *` already returns the old row. For `INSERT … ON CONFLICT DO UPDATE`, there is no clean 17 equivalent for the old values. If you need them, select them first with `FOR UPDATE`, or keep an audit trigger. Selecting first doesn't help when the row doesn't exist yet: there is nothing to lock, so a concurrent insert can still land in between. ## 4. Temporal constraints: `WITHOUT OVERLAPS` 18 can enforce "no two rows for the same room with overlapping time ranges" as a primary key: ```sql -- on 18 this also needs btree_gist, because room is a plain int CREATE TABLE booking (room int, during tstzrange, PRIMARY KEY (room, during WITHOUT OVERLAPS)); -- on 17: ERROR: syntax error at or near "WITHOUT" ``` The guarantee itself has been available for years as an exclusion constraint. Mixing a plain equality column (`room`) with a range in one GiST index needs the `btree_gist` extension, which is marked trusted, so any role with `CREATE` on the database (the owner has it) can install it without superuser rights: ```sql CREATE EXTENSION IF NOT EXISTS btree_gist; CREATE TABLE booking ( room int NOT NULL, during tstzrange NOT NULL CHECK (NOT isempty(during)), EXCLUDE USING gist (room WITH =, during WITH &&) ); INSERT INTO booking VALUES (101, '[2026-11-10 09:00Z, 2026-11-10 10:00Z)'); INSERT INTO booking VALUES (101, '[2026-11-10 10:00Z, 2026-11-10 11:00Z)'); -- ok: touching, not overlapping INSERT INTO booking VALUES (101, '[2026-11-10 09:30Z, 2026-11-10 10:30Z)'); -- ERROR: conflicting key value violates exclusion constraint "booking_room_during_excl" ``` The `CHECK` matters: 18 rejects empty ranges in a `WITHOUT OVERLAPS` key, but an empty range never overlaps anything, so without the check the exclusion constraint would accept any number of them. Check that the extension is available on your database with `SELECT * FROM pg_available_extensions WHERE name = 'btree_gist';`. We confirmed it ships, at version 1.7, in the Relyt One compute image we tested. What 17 can't do is the other half of the feature: temporal foreign keys (`FOREIGN KEY (…, PERIOD …)`). There is no declarative equivalent. Enforce it in a trigger or in the application, and test it, because it is easy to get wrong at the edges of a range. ## 5. What you don't need to change Several of the headline 18 features change performance or operations, not SQL. Your schema and queries don't mention them, so there is nothing to rewrite: - **Asynchronous I/O** speeds up sequential scans, bitmap heap scans and vacuum on 18. On 17 the same queries work and use synchronous I/O. - **Skip scan** lets 18 use a multicolumn B-tree index even when the query doesn't filter on the leading column. If a query on 17 filters only on the second column of `(tenant_id, created_at)`, it may not use that index. Look at the plan with `EXPLAIN`, and add an index that leads with the filtered column if it matters. - **OAuth client authentication** is new in 18. You connect to Relyt One with the role's password over TLS, using the connection string from the console with `sslmode=require`. - **MD5 password deprecation** in 18 has no effect on 17 schemas. ## Loading a dump taken on Postgres 18 The `pg_dump` documentation is explicit: its output is expected to load into *newer* servers, and loading into an older major version "is not guaranteed" and "may require manual editing of the dump file". Going from 18 to 17 is that case. Here is the procedure we used, and what broke along the way. **Use a psql of 17.6 or later to load it.** Dumps from current `pg_dump` start with a `\restrict` line, a psql meta-command added in 17.6 for a security fix (CVE-2025-8714) and included in every Postgres 18 release. psql 17.5 rejects it with `invalid command \restrict`. With `ON_ERROR_STOP=1` the load aborts right there; without it, psql carries on and silently skips the protection `\restrict` exists to provide. Use an 18.x client or 17.6+: psql 18 connects to a 17 server without trouble. **Dump schema and data separately**, without owners or grants, since the roles on the target will differ: ```bash pg_dump "$SOURCE_URL" --schema-only --no-owner --no-privileges --quote-all-identifiers > schema.sql pg_dump "$SOURCE_URL" --data-only --no-owner --no-privileges --quote-all-identifiers > data.sql ``` `--quote-all-identifiers` is what the `pg_dump` docs recommend for cross-version loads: it avoids surprises from words that are reserved in one version and not the other. Loading the data after the full schema means indexes, foreign keys and triggers already exist during the load. That is slower than a single dump, and your own triggers fire for every row. For a large database, split with `--section=pre-data`, `data` and `post-data` instead and edit only the pre-data file. **Find the 18-only syntax** before loading anything: ```bash grep -nE 'uuidv7\(|uuidv4\(|WITHOUT OVERLAPS|PERIOD ' schema.sql grep -nE 'GENERATED ALWAYS AS \(' schema.sql | grep -v STORED # virtual columns grep -niE 'RETURNING +(old|new)\.|RETURNING +WITH *\(' schema.sql # in functions and procedures ``` In our test schema this found two lines: ```text 56: id uuid DEFAULT uuidv7() NOT NULL, 58: price numeric GENERATED ALWAYS AS (((cents)::numeric / 100.0)) ``` Note line 58: `pg_dump` 18 writes a virtual column with **no keyword**, so searching for `VIRTUAL` misses it. Any `GENERATED ALWAYS AS (…)` without `STORED` after it is virtual, including one followed by `NOT NULL`. Add `STORED`. **Qualify the function you replaced.** A dump sets `search_path` to empty at the top, and on 18 `uuidv7()` is a built-in, so the dump calls it unqualified. Your `public.uuidv7()` won't be found during the load: ```text ERROR: function uuidv7() does not exist ``` Rewrite the default to `public.uuidv7()`. Then create your replacement functions first, and load everything in one transaction that stops at the first error: ```bash psql "$TARGET_URL" -v ON_ERROR_STOP=1 --single-transaction \ -f uuidv7.sql -f schema.sql -f data.sql ``` `$TARGET_URL` is your Relyt One connection string, ending in `?sslmode=require`. With those two edits, our test database loaded cleanly on 17. Identity columns continued from the right value, the generated column was recomputed on load, and new rows got version 7 UUIDs from the replacement function. Generated column values are not in the data dump at all, so switching a column from virtual to stored needs no data changes. ## A checklist before you move - [ ] `SHOW server_version_num` on the target; know which major version you are on - [ ] Load with psql 17.6+ or 18.x and `-v ON_ERROR_STOP=1` - [ ] `grep` the schema dump for `uuidv7(`, `uuidv4(`, `GENERATED ALWAYS AS` without `STORED`, `WITHOUT OVERLAPS`, `PERIOD`, and `RETURNING old.` / `new.` in any case - [ ] Create replacement functions before the schema, and qualify them (`public.uuidv7()`) - [ ] Replace `WITHOUT OVERLAPS` with an exclusion constraint; move temporal foreign keys into a trigger - [ ] Search application code too: ORMs and query builders generate `RETURNING`, and schema files in the repo may target 18 - [ ] Run the test suite against a branch before pointing production at it That last step is cheap on Relyt One: create a branch, load into it, run your tests against its connection string, and throw it away if something is off. [Try it on a branch](/auth/signin?ref=blog-postgres-18-features-on-17)