Maintained
5.0 (upcoming) 4.5 (upcoming) 4.4.3 (current) 3.10.5 (upcoming)
Unmaintained
4.3 4.2 4.1 4.0 3.9 3.8 3.7 3.6 3.5 3.4 3.3 3.2 3.1 3.0

Migration to identity columns on PostgreSQL

As of version 4, the DBAL uses identity columns to implement the autoincrement behavior on PostgreSQL instead of SERIAL* column types.

If you have a database with autoincrement columns created using DBAL 3 or earlier, you will need to perform the following schema migration before being able to continue managing the schema with the DBAL:

  1. Identify all autoincrement columns and their tables.

  2. Create the upgrade_serial_to_identity() function in the database as described in PostgreSQL 10 identity columns explained:

    CREATE OR REPLACE FUNCTION upgrade_serial_to_identity(tbl regclass, col name)RETURNS voidLANGUAGE plpgsqlAS $$DECLARE  colnum smallint;  seqid oid;  count int;BEGIN  -- find column number  SELECT attnum INTO colnum FROM pg_attribute WHERE attrelid = tbl AND attname = col;  IF NOT FOUND THEN    RAISE EXCEPTION 'column does not exist';  END IF;  -- find sequence     SELECT INTO seqid objid    FROM pg_depend    WHERE (refclassid, refobjid, refobjsubid) = ('pg_class'::regclass, tbl, colnum)      AND classid = 'pg_class'::regclass AND objsubid = 0      AND deptype = 'a';  GET DIAGNOSTICS count = ROW_COUNT;  IF count < 1 THEN    RAISE EXCEPTION 'no linked sequence found';  ELSIF count > 1 THEN    RAISE EXCEPTION 'more than one linked sequence found';  END IF;  -- drop the default  EXECUTE 'ALTER TABLE ' || tbl || ' ALTER COLUMN ' || quote_ident(col) || ' DROP DEFAULT';  -- change the dependency between column and sequence to internal  UPDATE pg_depend    SET deptype = 'i'    WHERE (classid, objid, objsubid) = ('pg_class'::regclass, seqid, 0)      AND deptype = 'a';  -- mark the column as identity column  UPDATE pg_attribute    SET attidentity = 'd'    WHERE attrelid = tbl      AND attname = col;END;$$;

  3. Run the function for each table like this:

    SELECT upgrade_serial_to_identity('article', 'id');

Without this migration, next time when DBAL 4 is used to manage the schema, it will perform a similar migration but instead of reusing the existing sequence, it will drop it and create a new one. As a result, all new sequence numbers will be generated from 1, which is most likely undesired.

Migrating on managed PostgreSQL services (AWS RDS, Cloud SQL, Azure, Supabase, Neon)

The function above writes directly to pg_depend and pg_attribute. These catalog updates require PostgreSQL superuser privileges, which managed services typically do not grant to the database's master / admin user. On those services the function fails with:

ERROR:  permission denied for table pg_depend

Use the variant below instead. It produces the same end state - the column is GENERATED BY DEFAULT AS IDENTITY, an attached sequence positioned at the next value the original sequence would have emitted - but uses only standard DDL (DROP SEQUENCE + ALTER TABLE … ADD GENERATED BY DEFAULT AS IDENTITY). The trade-off is that the original sequence object is dropped and PostgreSQL creates a fresh internal sequence; only ownership of the table and sequence is required, no superuser.

CREATE OR REPLACE FUNCTION upgrade_serial_to_identity(tbl regclass, col name)RETURNS voidLANGUAGE plpgsqlAS $$DECLARE  seq_name regclass;  next_val bigint;BEGIN  -- already identity? nothing to do  IF EXISTS (    SELECT 1 FROM pg_attribute    WHERE attrelid = tbl AND attname = col AND attidentity IN ('a', 'd')  ) THEN    RETURN;  END IF;  -- find the sequence linked via the column's DEFAULT  SELECT pg_get_serial_sequence(tbl::text, col)::regclass INTO seq_name;  IF seq_name IS NULL THEN    RAISE EXCEPTION 'no linked sequence found for %.%', tbl, col;  END IF;  -- capture the next value the sequence would emit  EXECUTE format('SELECT CASE WHEN is_called THEN last_value + 1 ELSE last_value END FROM %s', seq_name)    INTO next_val;  -- swap SERIAL for IDENTITY via standard DDL  EXECUTE format('ALTER TABLE %s ALTER COLUMN %I DROP DEFAULT', tbl, col);  EXECUTE format('DROP SEQUENCE %s', seq_name);  EXECUTE format(    'ALTER TABLE %s ALTER COLUMN %I ADD GENERATED BY DEFAULT AS IDENTITY (START WITH %s)',    tbl, col, next_val  );END;$$;