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?

Pablo Santa Cruz · Accepted Answer · 2009-08-07 11:46:16Z

16

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);

edited Aug 7, 2009 at 11:46

answered Aug 7, 2009 at 11:40

Pablo Santa Cruz

182k33 gold badges250 silver badges300 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Léo Léopold Hertz 준영 Over a year ago

If there is a column was_sent_at_time in the table, I would like know how you can add automatically the time to the new column. My unsuccessful attempt uses the method now() and explicit column name in the brackets: INSERT INTO questions(title, user_id, wast_sent_at_time) VALUES ('Lorem Ipsun.', 123, now()). How can you add the time also to the table?

Maxim Sloyko Over a year ago

@Masi: set the was_sent_at_time column type to the following: timestamp not null default current_timestamp

Pablo Santa Cruz Over a year ago

@Masi: listen to maksymko. Use the CURRENT_TIMESTAMP value to populate you was_sent_at_time column. :-)

Léo Léopold Hertz 준영 Over a year ago

Thank you guys! - Your last tip simplifies my SQL queries a lot :)

Andrejs Cainikovs · Accepted Answer · 2009-08-07 11:42:50Z

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.

answered Aug 7, 2009 at 11:42

Andrejs Cainikovs

28.7k2 gold badges81 silver badges99 bronze badges

Comments

Léo Léopold Hertz 준영 · Accepted Answer · 2009-08-07 13:57:04Z

2

You can also create table using

Create Table tbl_1 ( id SERIAL , name varchar(30));

You can go on inserting data like

insert into tbl_1(name) values('xyz');

This will create the id automatically. Using Serial is equivalent to the sequence only. You can check link text

edited Aug 7, 2009 at 13:57

Léo Léopold Hertz 준영

143k191 gold badges467 silver badges721 bronze badges

answered Aug 7, 2009 at 13:17

asb

9283 gold badges10 silver badges19 bronze badges

Collectives™ on Stack Overflow

To INSERT INTO a database uniquely by PostgreSQL

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related