Syntax of creating sequence and auto increment fields in PostgreSQL for the given table

Question

I am new in PostgreSQL. I'm trying to figure out the syntax for creating the following table.
I'm having difficulties in creating the sequence and the auto increment fields.

    Column    |         Type          |                      Modifiers
--------------+-----------------------+-----------------------------------------------------
 id_numuser   | integer               | not null default nextval('id_numuser_seq'::regclass)
 username     | character varying(70) |
 completename | character varying(70) |
 id_cat       | integer               |
 email        | character varying(70) |
 password     | character varying(30) |
 active       | boolean               |
Indexes:
    "users_pkey" PRIMARY KEY, btree (id_numuser)
    "taskuser_uniq" UNIQUE, btree (username)
Foreign-key constraints:
    "users_id_cat_fkey" FOREIGN KEY (id_cat) REFERENCES usercategories(id_numcat)

score 2 · Answer 1 · edited May 23 '17 at 12:32

2

Use a serial column. Details here:
Auto increment SQL function

The complete Script:

CREATE TABLE users (
   id_numuser   serial PRIMARY KEY
  ,username     character varying(70) UNIQUE
  ,completename character varying(70)
  ,id_cat       integer REFERENCES usercategories(id_numcat)
  ,email        character varying(70) 
  ,password     character varying(30) 
  ,active       boolean
);

You can use pgAdmin to get complete reverse-engineered SQL scripts for all objects.

Aside: I'd suggest to use just text instead of varchar(n).

edited May 23 '17 at 12:32

Community

1
1

answered Dec 31 '13 at 16:14

Erwin Brandstetter

605,456
145
1,078
1,228

Thanks,,,,But I would like to know the full syntax for crating this table. This will help in creating other tables. Thanks again.... – user3001418 Dec 31 '13 at 16:40
@user3001418: Added a full script. – Erwin Brandstetter Dec 31 '13 at 16:49

Craig Ringer · Answer 2 · 2013-12-31T20:43:38.747

If you're ever in doubt about how to define something, pg_dump will help.

pg_dump -t 'users' --schema-only

will print a dump that shows the command(s) to create your users table.

It won't use shorthand like SERIAL, so it'll create the sequence then assign the sequence ownership and set the column default. So sometimes there's a shorter and simpler way than how pg_dump does it. The way pg_dump does it will always work, though.

In this case it produces (trimmed):

CREATE TABLE users (
    id_numuser integer NOT NULL,
    username character varying(70),
    completename character varying(70),
    id_cat integer,
    email character varying(70),
    password character varying(30),
    active boolean
);

CREATE SEQUENCE users_id_numuser_seq
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;

ALTER SEQUENCE users_id_numuser_seq OWNED BY users.id_numuser;

ALTER TABLE ONLY users ALTER COLUMN id_numuser 
SET DEFAULT nextval('users_id_numuser_seq'::regclass);

ALTER TABLE ONLY users
ADD CONSTRAINT users_pkey PRIMARY KEY (id_numuser);

ALTER TABLE ONLY users
ADD CONSTRAINT users_username_key UNIQUE (username);

ALTER TABLE ONLY users
ADD CONSTRAINT users_id_cat_fkey FOREIGN KEY (id_cat) REFERENCES usercategories(id_numcat);

So it's defining the sequence and all the constraints after creating the base table, not as part of it, and it's specifying a bunch of stuff that would usually be set by defaults.

The effect is the same and you can do things this way if you want. Stuff like SERIAL PRIMARY KEY is basically just convenient shorthand. All that is covered well in the documentation for CREATE TABLE, so once you know what you want you can generally figure out how to define it pretty easily. Most of the time, anything you can write in ALTER TABLE ... ADD ... can be written the same in CREATE TABLE (...), eg:

ALTER TABLE ONLY users
ADD CONSTRAINT users_id_cat_fkey FOREIGN KEY (id_cat) REFERENCES usercategories(id_numcat);

can be done at create time with:

CREATE TABLE users (
    ....,
    CONSTRAINT users_id_cat_fkey FOREIGN KEY (id_cat) REFERENCES usercategories(id_numcat)
);

Additionally, for any column-specific CONSTRAINT there's usually a way to tack it on to the end of the column definition. In this case, you omit the CONSTRAINT constraint_name (it's generated) and the FOREIGN KEY (id_cat) (because the column is implied, you don't need to specify it), and write:

CREATE TABLE users (
     ....
     id_cat integer REFERENCES usercategories(id_numcat),
     ....
);

Once you know what to look for in the CREATE TABLE docs it's usually easy to find how to write what you want.

Syntax of creating sequence and auto increment fields in PostgreSQL for the given table

2 Answers2