insert unique values to postgresql

Question

iam create a table using below command in postgresql.

CREATE TABLE someTable (
    id serial primary key,
    col1 int NOT NULL,
    col2 int NOT NULL,
    unique (col1, col2)
);

then am execute 2 insert statements.

insert into someTable (col1,col2) values(1,11),(1,12);

its working
insert into someTable (col1,col2) values(1,13),(1,14),(1,11);

got error (key(col1,col2)=(1,11) is already exist.

But i need to avoid only duplicate pairs. How it will be possible ?

iam try this with

PostgreSQL 9.5.0 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 4.8.2-19ubuntu1) 4.8.2, 64-bit and PostgreSQL 9.3 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 4.8.2-19ubuntu1) 4.8.2, 64-bit

but i got error

i need ot put like this after executing two statements.

(1,11),(1,12),(1,13),(1,14)

The second insert tries to insert the values (1,11) which you have already inserted with the first statement. And as you have defined col1, col2 to be unique, you can't insert the same tuple twice. — user330315
– user330315, Commented Feb 1, 2016 at 12:19
is it possible to insert all other values( avoid duplicate pairs).? — Abdul Manaf
– Abdul Manaf, Commented Feb 1, 2016 at 12:21
See here: stackoverflow.com/q/1009584/330315 and here: stackoverflow.com/q/1109061/330315 for possible solutions with older Postgres versions. Note that 9.1 is going to be out of support in 8 months so you should plan an upgrade anyway — user330315
– user330315, Commented Feb 1, 2016 at 12:23
Possible duplicate of Insert multiple rows where not exists PostgresQL — user330315
– user330315, Commented Feb 1, 2016 at 12:28

Gordon Linoff · Accepted Answer · 2016-02-02 00:46:19Z

4

You can do this using insert . . . select:

insert into someTable(col1, col2) 
    select col1, col2
    from (select 1 as col1, 13 as col2 union all
          select 1, 14 union all
          select 1, 11
         ) t
    where not exists (select 1
                      from someTable st
                      where st.col1 = t.col1 and st.col2 = t.col2
                     );

That is, filter the values out before the insert.

EDIT:

As a-horse-with-no-name points out, you can also write this as:

insert into someTable(col1, col2) 
    select col1, col2
    from (values (1, 13), (1, 14), (1, 11)
         ) as t(col1, col2)
    where not exists (select 1
                      from someTable st
                      where st.col1 = t.col1 and st.col2 = t.col2
                     );

I tend to use the union all approach because not all databases support this use of the values() statement.

edited Feb 2, 2016 at 0:46

answered Feb 1, 2016 at 12:27

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

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

3 Comments

user330315 Over a year ago

No need for the verbose select in the derived table. You can use values (1,13), (1,14) directly there

Abdul Manaf Over a year ago

i got error from above syntax ERROR: syntax error at or near "from" LINE 2: from (values (1, 13), (1, 14), (1, 11)

Gordon Linoff Over a year ago

@AbdulManaf . . . Somehow the select was missing before the from.

Abdul Manaf · Accepted Answer · 2016-02-01 14:13:16Z

3

using postgresql 9.5 (latest release)

use query like this

insert into someTable (col1,col2) values(1,13),(1,14),(1,11) ON CONFLICT DO NOTHING;

it will avoid duplication without any extra line of code.

answered Feb 1, 2016 at 14:13

Abdul Manaf

5,01311 gold badges57 silver badges99 bronze badges

Collectives™ on Stack Overflow

insert unique values to postgresql

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related