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. — , Feb 01 '16 at 12:19
is it possible to insert all other values( avoid duplicate pairs).? — Abdul Manaf, Feb 01 '16 at 12:21
See here: http://stackoverflow.com/q/1009584/330315 and here: http://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 — , Feb 01 '16 at 12:23
iam using PostgreSQL 9.3.10 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu 4.8.2-19ubuntu1) 4.8.2, 64-bit. thanks for valluable comments — Abdul Manaf, Feb 01 '16 at 12:24
Possible duplicate of [Insert multiple rows where not exists PostgresQL](http://stackoverflow.com/questions/24769157/insert-multiple-rows-where-not-exists-postgresql) — , Feb 01 '16 at 12:28
am upgrade my postgresql version to 9.5 . but still got error.. — Abdul Manaf, Feb 01 '16 at 13:53
Well, with Postgres 9.5 you need to use the new `insert ... on conflict ()` syntax — , Feb 01 '16 at 13:59

Gordon Linoff · Accepted Answer · 2016-02-02T00:46:19.633

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 02 '16 at 00:46

answered Feb 01 '16 at 12:27

Gordon Linoff

1,242,037
58
646
786

1

No need for the verbose `select` in the derived table. You can use `values (1,13), (1,14)` directly there – Feb 01 '16 at 12:28
i got error from above syntax ERROR: syntax error at or near "from" LINE 2: from (values (1, 13), (1, 14), (1, 11) – Abdul Manaf Feb 01 '16 at 12:58
@AbdulManaf . . . Somehow the `select` was missing before the `from`. – Gordon Linoff Feb 02 '16 at 00:46

score 3 · Answer 2 · answered Feb 01 '16 at 14:13

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 01 '16 at 14:13

Abdul Manaf

4,933
8
51
95

insert unique values to postgresql

2 Answers2