1

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?

Jim Jones
  • 18,404
  • 3
  • 35
  • 44
Tms91
  • 3,456
  • 6
  • 40
  • 74

1 Answers1

2

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

Jim Jones
  • 18,404
  • 3
  • 35
  • 44
  • 1
    @Tms91 you have to create a sequence manually (I added it to my fiddle), e.g.: `CREATE SEQUENCE testing_thing_thing_id_seq START WITH 1;` – Jim Jones Oct 22 '21 at 16:13
  • 1
    The table was probably created with `thing_id serial not null primary key` rather than manually creating and attaching the sequence. And that would mean that the sequence is owned by `testing_thing`. – mu is too short Oct 22 '21 at 17:23
  • @mu is too short, but `thing_id` is not my table primary key, `thing_num` is. – Tms91 Oct 22 '21 at 21:24
  • 1
    @Tms91 Then probably `thing_id serial not null`, the main point was that it looks like what the `serial` pseudo-type would do. – mu is too short Oct 22 '21 at 22:22
  • 1
    @Tms91 `serial` isn't a standard data type and I wouldn't recommend its usage, although it seems pretty practical. If you can afford not having a sequence, consider an identity column instead - it has the same effect, it is a SQL standard and it is much safer. Cheers! . – Jim Jones Oct 23 '21 at 17:59
  • 1
    @mu is too short if you wish to type your advice about `serial` as answer I will be happy to upvote it – Tms91 Oct 26 '21 at 12:14