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.

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

   its working

2. 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)

Answer 1

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.

Answer 2

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.