postgresql insert into from select

Jerry Smith Published at Dev

Jerry Smith

I have two tables table1 and test_table1 which have the same schema.

Both tables have rows/data and pk id's starting from 1.

I would like to do:

insert into test_table1 select * from table1;

but this fails due to the pk values from table1 existing in test_table1.

Way around it would be to specify columns and leave the pk column out, but for some reason thats not working either:

e.g. NOTE - no pk columns in query below

insert into test_table1 (col1, col2,..., coln) select col1,col2,...,coln from table1;

returns

ERROR: duplicate key value violates unique constraint "test_table1_pkey" DETAIL: Key (id)=(1) already exists.

I know this works in MySql, is this just due to Postgresql? Anyway around it?

EDIT:

Both tables have primary keys and sequence set.

Since it wasn't clear - tables don't have the same data. I would just like to add rows from table1 to test_table1.

For answers telling me to exclude primary key from the query - I did as I said before.

Taleh Ibrahimli

Just remove pk column from columns of query

insert into test_table1 (col2,..., coln) select col2,...,coln from table1;

If it still fails maybe you have not sequence on pk columns. Create sequence on already existing pk column

create sequence test_table1_seq;
ALTER TABLE test_table1 
    ALTER COLUMN col1 SET DEFAULT nextval('test_table1_seq'::regclass);

And update sequence value to current

SELECT setval('test_table1_seq', (SELECT MAX(col1) FROM test_table1));

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at2021-03-3

Comments

0 comments

From Dev

Insert into … values ( SELECT … FROM … ) in postgresql?

From Dev

PostgreSql INSERT FROM SELECT RETURNING ID

From Dev

Insert from Select Postgresql with primary key constraint

From Dev

postgresql insert values from a select query

From Dev

PostgreSQL INSERT FROM SELECT with additional column

From Dev

INSERT INTO MySQL table SELECT FROM PostgreSQL table

From Dev

PostgreSQL INSERT FROM SELECT with additional column

From Dev

Postgresql: INSERT INTO using SELECT and values

From Dev

postgresql: INSERT INTO … (SELECT * …,"fixed value")

From Dev

Postgresql Insert select with multiple rows

From Java

Insert into ... values ( SELECT ... FROM ... )

From Dev

SELECT FROM and INSERT INTO

From Dev

Slick insert from select

From Dev

sqlalchemy INSERT from SELECT

From Dev

Insert into select from

From Dev

Postgresql select from where

From Dev

postgresql cannot insert from table Cannot insert

From Dev

PostgreSQL - insert rows based on select from another table, and update an FK in that table with the newly inserted rows

From Dev

PostgreSQL - Multiple select primary key from three tables and insert as foreign key into one table

From Dev

insert into table from query in Postgresql

From Dev

INSERT based on a SELECT not inserting correctly with PostGreSQL

From Dev

PostgreSQL insert select multiple column values

From Dev

How to include the values of a select statement in an insert? (PostgreSQL)

From Dev

PostgreSQL INSERT based on SELECT results using Python

From Dev

PostgreSQL; Using a SELECT sub-query in an INSERT

From Dev

PostgreSQL: How to insert, select, and update values with underscores

From Dev

Postgresql - Insert when select return something

From Dev

select from insert into not working with sqlalchemy

From Dev

MySQL insert with value from select

Related Related

Article