views:

350

answers:

3

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?

+5  A: 

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);
Pablo Santa Cruz
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?**
Masi
@Masi: set the was_sent_at_time column type to the following: timestamp not null default current_timestamp
maksymko
@Masi: listen to maksymko. Use the CURRENT_TIMESTAMP value to populate you was_sent_at_time column. :-)
Pablo Santa Cruz
Thank you guys! - Your last tip simplifies my SQL queries a lot :)
Masi
+2  A: 

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.

Andrejs Cainikovs
A: 

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

asb