0

Actually my question is basically the same as here but I need to insert values from a nested query into an existing table and I'am not that familiar with sql as to know how to incorporate the setval part into my query:

insert into table_a
select * 
from table_b 
where val_1 IN (select "val_1" from table_c where "val_2" is null)

returns

ERROR:  duplicate key value violates unique constraint "table_a_pkey"
DETAIL: Key (qid)=(470971) already exists.

Now I know I could do a workaround with drop column and autgenerate as described here but there must be a more elegant way. I am using Postgresql/Postgis 2.4 btw.

maxwhere
  • 125
  • 1
  • 9
  • 1
    What do you want to do in case of a duplicate? Update the existing row? –  Nov 21 '19 at 11:52
  • 1
    Please **[edit]** your question (by clicking on the [edit] link below it) and add the `create table` statement of all involved tables –  Nov 21 '19 at 11:53
  • I know there are no duplicates and the the qid fields of both tables were created with `add column qid serial primary key` independently from oneanother – maxwhere Nov 21 '19 at 11:55

1 Answers1

1

It he primary key is auto-generated, don't insert the PK column:

insert into table_a (some_column, other_column, third_column)
select some_column, other_column, third_column
from table_b 
where val_1 IN (select "val_1" from table_c where "val_2" is null)

(I had to guess the column names as you did not provide the real ones)

  • Well that's what i meant with a more elegant way because with 29+ columns that might turn out tedious and secondly I know that there is this workaround: [exclude a column using SELECT * except columnA FROM tableA?](https://stackoverflow.com/questions/729197/sql-exclude-a-column-using-select-except-columna-from-tablea) – maxwhere Nov 21 '19 at 12:08
  • Using `select *` and unqualified `insert` statements is bad coding style to begin with. And with a good SQL query tool, pasting those 29 columns is quite easy –  Nov 21 '19 at 12:28
  • 1
    Unfortunately, @a_horse_with_no_name is right. The only way an INSERT statement "matches" incoming values to the target table is by position. The first column of the incoming rows goes into the first column of the table. To accomplish anything else requires the target columns to be listed. – Chris Bandy Nov 22 '19 at 06:42