PostgreSQL - INSERT INTO statement

Question

What I'm trying to do is select various rows from a certain table and insert them right back into the same table. My problem is that I keep running into the whole "duplicate PK" error - is there a way to skip the PK field when executing an INSERT INTO statement in PostgreSQL?

For example:

INSERT INTO reviews SELECT * FROM reviews WHERE rev_id=14;

the rev_id in the preceding SQL is the PK key, which I somehow need to skip. (To clarify: I am using * in the SELECT statement because the number of table columns can increase dynamically).

So finally, is there any way to skip the PK field?

Thanks in advance.

Are you attempting to duplicate a row with a new ID assigned to it? This seems like a really weird thing to want to do. — Craig Ringer
– Craig Ringer, Commented Jun 3, 2013 at 6:46

Jonathan Kortleven · Accepted Answer · 2013-06-03 06:48:08Z

3

You can insert only the values you want so your PK will get auto-incremented

insert into reviews (col1, col2, col3) select col1, col2, col3 from reviews where rev_id=14

Please do not retrieve/insert the id-column

answered Jun 3, 2013 at 6:48

Jonathan Kortleven

6151 gold badge5 silver badges16 bronze badges

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

Comments

buritos · Accepted Answer · 2013-06-03 06:45:33Z

2

insert into reviews (col0, col1, ...) select col0, col1, ... from reviews where rev_id=14;

answered Jun 3, 2013 at 6:45

buritos

5962 silver badges9 bronze badges

2 Comments

user1023242 Over a year ago

This is ok, but the reviews columns are increasing dynamically in certain times, in that I cannot exactly say the column name

Craig Ringer Over a year ago

@user1023242 Dynamic column names? This is sounding more and more like a data model design problem, not a query problem.

Collectives™ on Stack Overflow

PostgreSQL - INSERT INTO statement

2 Answers 2

Comments

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Related