In PostgreSQL how do you insert into a table with only one identity column?

sql postgresql identity
Charagh Jethnani picture Charagh Jethnani · Sep 9, 2012 · Viewed 20k times · Source

For instance:

{create table Participant ( id serial, primary key(id) );}

How do you insert into table in this case?

Answer

Akash KC picture Akash KC · Sep 9, 2012

If you create the table like above,

You can use default in following way to insert:

INSERT INTO Participant values(default);

Check out SQLFIDDLE.

Another way to insert is:

INSERT INTO Participant values(NEXTVAL('Participant_id_seq'));

CREATE TABLE will create implicit sequence "Participant_id_seq" for serial column "Participant.id".

You can get the sequence for the table using pg_get_serial_sequence function in following way:

pg_get_serial_sequence('Participant', 'id')

It will take new value from sequence using NEXTVAL().

Check out SQLFIDDLE

Related questions

Select first row in each GROUP BY group?

As the title suggests, I'd like to select the first row of each set of rows grouped with a GROUP BY. Specifically, if I've got a purchases table that looks like this: SELECT * FROM purchases; My Output: id | customer | total …

Read More
How do I (or can I) SELECT DISTINCT on multiple columns?

I need to retrieve all rows from a table where 2 columns combined are all different. So I want all the sales that do not have any other sales that happened on the same day for the same price. The sales …

Read More
How to select the nth row in a SQL database table?

I'm interested in learning some (ideally) database agnostic ways of selecting the nth row from a database table. It would also be interesting to see how this can be achieved using the native functionality of the following databases: SQL Server …

Read More