Insert command without id postgreSQL

Question

In mySQL i am able to do

INSERT INTO table_name (column_name1, column_name2) VALUES('John', 'Doe);

As you can see I do not have to mention the ID, how would I do this in postgreSQL.

Thank you

Assuming ID is [serial](http://www.postgresql.org/docs/9.2/static/datatype-numeric.html#DATATYPE-SERIAL) (i.e. PostgreSQL equivalent of auto-increment), you can return the just-generated value using the [RETURNING](http://www.postgresql.org/docs/9.2/static/sql-insert.html) statement. — Branko Dimitrijevic, Jun 04 '13 at 18:20

Anuj Patel · Accepted Answer · 2013-06-04T17:49:48.183

8

Approach #1: You can declare your ID column as Serial In this case it will create an implicit sequence for your column.

Example :

CREATE TABLE MyTable
(
   ID     serial   NOT NULL,
   column1 type,
   column2 type
}

Approach #2: Or you can manually define a sequence and then assign its next value as Default value for the column.

CREATE SEQUENCE my_sequence START 1;

CREATE TABLE MyTable
       (
       ID     integer   DEFAULT nextval('my_sequence'::regclass) NOT NULL,
       column1 type,
       column2 type
       }

edited Jun 04 '13 at 17:49

answered Jun 04 '13 at 17:43

Anuj Patel

17,261
3
30
57

those approaches don't work me and try to insert duplicate value :( – Lukas Salich Dec 28 '20 at 13:01

score 3 · Answer 2 · answered Jun 04 '13 at 17:46

This is not because of mysql that happens. You can make such this kind of query because you have set id as an auto_increment column

You can actually do the same thing in postgreSQL by using the serial pseudo data type instead

Example of primary column serial

id   serial PRIMARY KEY,

Insert command without id postgreSQL

2 Answers2