Serial Type Columns in PostgreSQL

PostgreSQL allows creating columns of types smallserial, serial, and bigserial. These types are not actual types, but more like "macros" for creating non-nullable integer columns with sequences attached.

We can see this in action by creating a table with 3 "serial columns":

CREATE TABLE serials(
    c1 smallserial,
    c2 serial,
    c3 bigserial
);

Serials Description

 Column |   Type   | Nullable |            Default
--------+----------+----------+-------------------------------
 c1     | smallint | not null | nextval('t_c1_seq'::regclass)
 c2     | integer  | not null | nextval('t_c2_seq'::regclass)
 c3     | bigint   | not null | nextval('t_c3_seq'::regclass)

As you can see, each serial column was created as non-nullable integer with a default value set to the next sequence value.

info

Note that nextval increments the sequence by 1 and returns its value. Thus, the first call to nextval('serials_c1_seq') returns 1, the second returns 2, etc.

ALTER COLUMN type to serial

Sometimes it is necessary to change the column type from integer type to serial. However, as mentioned above, the serial type is not a true type, and therefore, the following commands will fail:

CREATE TABLE t(
    c integer not null primary key
);

ALTER TABLE t ALTER COLUMN c TYPE serial;
ERROR: type "serial" does not exist

We can achieve this by manually creating a sequence owned by the column c, and setting the column DEFAULT value to the incremental counter of the sequence using the nextval function.

note

Note that it is recommended to follow the PostgreSQL naming format (i.e. <table>_<column>_seq) when creating the sequence as some database tools know to detect such columns as "serial columns".

-- Create the sequence.
CREATE SEQUENCE "public"."t_c_seq" OWNED BY "public"."t"."c";

-- Assign it to the table default value.
ALTER TABLE "public"."t" ALTER COLUMN "c" SET DEFAULT nextval('"public"."t_c_seq"');

Update the sequence value

When a sequence is created, its value starts from 0 and the first call to nextval returns 1. Thus, in case the column c from the example above already contains values, we may face a constraint error on insert when the sequence number will reach to the minimum value of c. Let's see an example:

SELECT "c" FROM "t";
 c
---
 2
 3

-- Works!
INSERT INTO "t" DEFAULT VALUES;
-- Fails!
INSERT INTO "t" DEFAULT VALUES;
ERROR:  duplicate key value violates unique constraint "t_pkey"
DETAIL:  Key (c)=(2) already exists.

We can work around this by setting the sequence current value to the maximum value of c, so the following call to nextval will return MAX(c)+1, the one after MAX(c)+2, and so on.

SELECT setval('"public"."t_c_seq"', (SELECT MAX("c") FROM "t"));
 setval
--------
   3

-- Works!
INSERT INTO "t" DEFAULT VALUES;
SELECT "c" FROM "t";
 c
---
 2
 3
 4

Managing Serial Columns with Atlas

Atlas makes it easier to define and manipulate columns of serial types. Let's use the atlas schema inspect command to get a representation of the table we created above in the Atlas HCL format :

atlas schema inspect -u "postgres://postgres:pass@:5432/test?sslmode=disable" > schema.hcl

schema.hcl

table "t" {
  schema = schema.public
  column "c" {
    null = false
    type = serial
  }
  primary_key {
    columns = [column.c]
  }
}
schema "public" {
}

After inspecting the schema, we can modify it to demonstrate Atlas's capabilities in migration planning:

Change a column type from serial to bigserial

schema.hcl

table "t" {
  schema = schema.public
  column "c" {
    null = false
    type = bigserial
  }
  primary_key {
    columns = [column.c]
  }
}
schema "public" {
}

Next, running schema apply will plan and execute the following changes:

atlas schema apply -u "postgres://postgres:pass@:5432/test?sslmode=disable" -f schema.hcl

-- Planned Changes:
-- Modify "t" table
ALTER TABLE "public"."t" ALTER COLUMN "c" TYPE bigint
✔ Apply

As you can see, Atlas detected that only the underlying integer type was changed as serial maps to integer and bigserial maps to bigint.

Change a column type from bigserial to bigint

schema.hcl

table "t" {
  schema = schema.public
  column "c" {
    null = false
    type = bigint
  }
  primary_key {
    columns = [column.c]
  }
}
schema "public" {
}

After changing column c to bigint, we can run schema apply and let Atlas plan and execute the new changes:

atlas schema apply -u "postgres://postgres:pass@:5432/test?sslmode=disable" -f schema.hcl

-- Planned Changes:
-- Modify "t" table
ALTER TABLE "public"."t" ALTER COLUMN "c" DROP DEFAULT
-- Drop sequence used by serial column "c"
DROP SEQUENCE IF EXISTS "public"."t_c_seq"
✔ Apply

As you can see, Atlas dropped the DEFAULT value that was created by the serial type, and in addition removed the sequence that was attached to it, as it is no longer used by the column.

Change a column type from bigint to serial

schema.hcl

table "t" {
  schema = schema.public
  column "c" {
    null = false
    type = serial
  }
  primary_key {
    columns = [column.c]
  }
}
schema "public" {
}

Changing a column type from bigint to serial requires 3 changes:

1. Create a sequence named t_c_seq owned by c.
2. Set the DEFAULT value of c to nextval('"public"."t_c_seq"').
3. Alter the column type, as serial maps to integer (!= bigint).

We call atlas schema apply to plan and execute this three step process with Atlas:

atlas schema apply -u "postgres://postgres:pass@:5432/test?sslmode=disable" -f schema.hcl

-- Planned Changes:
-- Create sequence for serial column "c"
CREATE SEQUENCE IF NOT EXISTS "public"."t_c_seq" OWNED BY "public"."t"."c"
-- Modify "t" table
ALTER TABLE "public"."t" ALTER COLUMN "c" SET DEFAULT nextval('"public"."t_c_seq"'), ALTER COLUMN "c" TYPE integer
✔ Apply

Need More Help?

Join the Ariga Discord Server for early access to features and the ability to provide exclusive feedback that improves your Database Management Tooling.