sw2_std=# CREATE TABLE q (id bigserial NOT NULL PRIMARY KEY);
NOTICE:  CREATE TABLE will CREATE implicit SEQUENCE "q_id_seq" FOR serial COLUMN "q.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will CREATE implicit INDEX "q_pkey" FOR TABLE "q"
CREATE TABLE
sw2_std=# \d q
                         TABLE "public.q"
 COLUMN |  TYPE  |                   Modifiers
--------+--------+------------------------------------------------
 id     | BIGINT | NOT NULL DEFAULT NEXTVAL('q_id_seq'::regclass)
Indexes:
    "q_pkey" PRIMARY KEY, btree (id)

sw2_std=# INSERT INTO q VALUES (2);
INSERT 0 1
sw2_std=# INSERT INTO q VALUES (DEFAULT);
INSERT 0 1
sw2_std=# INSERT INTO q VALUES (DEFAULT);
ERROR:  duplicate KEY VALUE violates UNIQUE CONSTRAINT "q_pkey"