Postgres change column to auto increment primary key-CodePudding

When I create a table with a SERIAL primary key using this DDL:

CREATE TABLE public.test_change_column (
    id SERIAL PRIMARY KEY NOT NULL
)

I get a sequence called 'test_change_column_id_seq' and gives the column id a default value of nextval('test_change_column_id_seq'::regclass)

If I create a table without SERIAL and a primary key using this DDL:

CREATE TABLE public.test_change_column (
    id INTEGER NOT NULL
)

It would make life a lot simpler if you could write

ALTER TABLE public.test_change_column  ALTER COLUMN id TYPE serial;

but this results in the error "type "serial" does not exist".

The alternative is to change the table to add a sequence and a primary using this DDL:

ALTER TABLE public.test_change_column ALTER COLUMN id ADD GENERATED BY DEFAULT AS IDENTITY;

ALTER TABLE public.test_change_column ADD CONSTRAINT test_change_column_pkey PRIMARY KEY(id);

The first statement correctly creates the sequence and the second statement creates the primary key but the default value of id is not set to

nextval('test_change_column_id_seq'::regclass)

Is the only way to add this is by setting the default value to the nextval using SET DEFAULT?

The issue seems to be with how I am extracting the column default from the database.

This shows the default

\d public.test_change_column

Whereas this does not:

SELECT column_name, column_default FROM information_schema.columns WHERE table_name = 'test_change_column';

CodePudding user response：

Changing the column to an identity column (and adding the PK constraint) is enough and the correct thing to do (the use of the serial pseudo type is discouraged)

You can see that e.g. psql reports this correctly as an identity column:

arthur=> CREATE TABLE public.test_change_column (id INTEGER NOT NULL);
CREATE TABLE
arthur=> \d public.test_change_column
         Table "public.test_change_column"
 Column |  Type   | Collation | Nullable | Default
-------- --------- ----------- ---------- ---------
 id     | integer |           | not null |


arthur=> ALTER TABLE public.test_change_column ALTER COLUMN id ADD GENERATED BY DEFAULT AS IDENTITY;
ALTER TABLE
arthur=> ALTER TABLE public.test_change_column ADD CONSTRAINT test_change_column_pkey PRIMARY KEY(id);
ALTER TABLE
arthur=> \d public.test_change_column
                     Table "public.test_change_column"
 Column |  Type   | Collation | Nullable |             Default
-------- --------- ----------- ---------- ----------------------------------
 id     | integer |           | not null | generated by default as identity
Indexes:
    "test_change_column_pkey" PRIMARY KEY, btree (id)

You can also verify that the default is working by inserting a row:

insert into public.test_change_column default values;

Will create a new row and will increment the id value.

Such a column will be shown in information_schema.columns with is_identity = 'YES' instead of having a default value.

I recommend to use generated always as identity so that you can't accidentally bypass the generation through the sequence (with the effect that the sequence and the values in the table are no longer "in sync").

CodePudding user response：

Create table.

CREATE TABLE public.test_change_column (
    id INTEGER NOT NULL
);

Alter table in one line:

ALTER TABLE 
    public.test_change_column ALTER COLUMN id ADD GENERATED BY DEFAULT AS IDENTITY, 
    ADD CONSTRAINT test_change_column_pkey PRIMARY KEY(id);

\d test_change_column
                     Table "public.test_change_column"
 Column |  Type   | Collation | Nullable |             Default              
-------- --------- ----------- ---------- ----------------------------------
 id     | integer |           | not null | generated by default as identity
Indexes:
    "test_change_column_pkey" PRIMARY KEY, btree (id)

An IDENTITY column has a sequence associated with it that is used just like with a serial column. It is just not shown as the DEFAULT. To find underlying sequence:

select pg_get_serial_sequence ('public.test_change_column', 'id');
      pg_get_serial_sequence      
----------------------------------
 public.test_change_column_id_seq

select * from public.test_change_column_id_seq ;
 last_value | log_cnt | is_called 
------------ --------- -----------
          1 |       0 | f

You can manipulate the above with the IDENTITY commands here ALTER TABLE:

ALTER [ COLUMN ] column_name ADD GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( sequence_options ) ]

ALTER [ COLUMN ] column_name { SET GENERATED { ALWAYS | BY DEFAULT } | SET sequence_option | RESTART [ [ WITH ] restart ] } [...]

ALTER [ COLUMN ] column_name DROP IDENTITY [ IF EXISTS ]

UPDATE

From here serial type:

The data types smallserial, serial and bigserial are not true types, but merely a notational convenience for creating unique identifier columns (similar to the AUTO_INCREMENT property supported by some other databases). In the current implementation, specifying:

CREATE TABLE tablename ( colname SERIAL );

is equivalent to specifying:

CREATE SEQUENCE tablename_colname_seq AS integer;

CREATE TABLE tablename ( colname integer NOT NULL DEFAULT nextval('tablename_colname_seq') );

ALTER SEQUENCE tablename_colname_seq OWNED BY tablename.colname;

Thus, we have created an integer column and arranged for its default values to be assigned from a sequence generator. A NOT NULL constraint is applied to ensure that a null value cannot be inserted. (In most cases you would also want to attach a UNIQUE or PRIMARY KEY constraint to prevent duplicate values from being inserted by accident, but this is not automatic.) Lastly, the sequence is marked as “owned by” the column, so that it will be dropped if the column or table is dropped.

So if you want to ALTER a table to replicate a serial 'type' you will need to do the individual steps above:

CREATE TABLE public.test_change_column (id INTEGER NOT NULL);

create sequence test_change_column_seq;


alter 
    table test_change_column alter COLUMN id 
    set default nextval('test_change_column_seq'), 
ADD CONSTRAINT test_change_column_pkey PRIMARY KEY(id);
alter sequence test_change_column_seq owned by test_change_column.id;

\d test_change_column
                           Table "public.test_change_column"
 Column |  Type   | Collation | Nullable |                   Default                   
-------- --------- ----------- ---------- ---------------------------------------------
 id     | integer |           | not null | nextval('test_change_column_seq'::regclass)
Indexes:
    "test_change_column_pkey" PRIMARY KEY, btree (id)