To INSERT INTO a database uniquely by PostgreSQL

Question

My table

  question_id   |              title              |      user_id   |
+---------------+---------------------------------+----------------+

Its types are

                Column                 |            Type             |                            Modifiers                            
---------------------------------------+-----------------------------+-----------------------------------------------------------------
 question_id                           | integer                     | not null default nextval('questions_question_id_seq'::regclass)
 title                                 | character varying(256)      | 
 user_id                               | integer                     | 

I run

INSERT INTO questions VALUES (SERIAL, 'question title', 123);

I get an error about the use of SERIAL.

How can you add the question to the table automatically taking an unique question_id by PostgreSQL?

Answer 1

DEFAULT VALUE you have will auto generate a (serial) number from a sequence that's on your DataBase. So you can just do a simple:

insert into questions (title, user_id) values ('question_title', 123);

It will insert an AUTO INCREMENTED number in question_id field because of ** questions_question_id_seq** sequence.

Alternatively, instead of using SERIAL, on your insert clause, you could also use the sequence. But I would rather use the first sentence I suggested:

insert into questions values (nextval('questions_question_id_seq'), 'question_title', 123);

Answer 2

Normally, you don't need to specify the unique id, as this is done server-side. You should use following:

INSERT INTO questions (title, user_id) VALUES ('question title', 123);

Automatic id handling is done by 'questions_question_id_seq' sequence as you may see in modifier section.