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 <https://www.enterprisedb.com/blog/postgresql-10-identity-columns-explained>_:

   .. code-block:: sql

   CREATE OR REPLACE FUNCTION upgrade_serial_to_identity(tbl regclass, col name) RETURNS void LANGUAGE plpgsql AS $$ 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;

   Copy

    -- 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:

   .. code-block:: sql

   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:

.. code-block:: text

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.

.. code-block:: sql

CREATE OR REPLACE FUNCTION upgrade_serial_to_identity(tbl regclass, col name) RETURNS void LANGUAGE plpgsql AS $$ 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; $$;