In my postgres db there is a table called testing_thing, which I can see (by running \d testing_thing in my psql prompt) it is defined as

                                        Table "public.testing_thing"

    Column    |       Type        | Collation | Nullable |                       Default                       

-------------- ------------------- ----------- ---------- -----------------------------------------------------

 thing_id   | integer           |           | not null | nextval('testing_thing_thing_id_seq'::regclass)

 thing_num  | smallint          |           | not null | 0

 thing_desc | character varying |           | not null | 

Indexes:

    "testing_thing_pk" PRIMARY KEY, btree (thing_num)

I want to drop it and re-create it exactly as it is, but I don't know how to reproduce the

nextval('testing_thing_thing_id_seq'::regclass)

part for column thing_id.

This is the query I put together to create the table:

CREATE TABLE testing_thing(
   thing_id integer NOT NULL, --what else should I put here?
   thing_num smallint NOT NULL PRIMARY KEY DEFAULT 0,
   thing_desc varchar(100) NOT NULL
);

what is it missing?

CodePudding user response：

Add a DEFAULT to the column you want to increment and call nextval():

CREATE SEQUENCE testing_thing_thing_id_seq START WITH 1;

CREATE TABLE testing_thing(
   thing_id integer NOT NULL DEFAULT nextval('testing_thing_thing_id_seq'),
   thing_num smallint NOT NULL PRIMARY KEY DEFAULT 0,
   thing_desc varchar(100) NOT NULL
);

Side note: Keep in mind that attaching a sequence to a column does not prevent users to manually fill it with random data, which can create really nasty problems with primary keys. If you want to overcome it and do not necessarily need to have a sequence, consider creating an identity column, e.g.

CREATE TABLE testing_thing(
   thing_id integer NOT NULL GENERATED ALWAYS AS IDENTITY,
   thing_num smallint NOT NULL PRIMARY KEY DEFAULT 0,
   thing_desc varchar(100) NOT NULL
);

Demo: db<>fiddle