検索
検索

Postgres INSERT ON CONFLICT DO NOTHING vs SELECT + INSERT query

debugcn 投稿 Dev
4
Shuwn Yuan Tee

I have table stock_price_code as below. value column has a UNIQUE constraint.

create table stock_price_code (
    id serial primary key,
    value text not null,
    unique (value)
);

I want to INSERT into table, if no record found for value. I have these 2 queries:

-- query 1
INSERT INTO stock_price_code (value)
    SELECT 'MCD'
    WHERE NOT EXISTS (SELECT * FROM stock_price_code WHERE value = 'MCD')
    RETURNING id;


-- query 2
INSERT INTO stock_price_code (value) VALUES ('MCD')
    ON CONFLICT (value) DO NOTHING
    RETURNING id;

I was using query 1 before Postgres 9.5. Then Postgres 9.5 started introducing INSERT ... ON CONFLICT ... feature. If I substitude query 1 with query 2, is there any known side effect or perfomance issue? Thanks.

Vao Tsun

query 2 will work if a row gets inserted between SELECT 'MCD' WHERE NOT EXISTS (SELECT * FROM stock_price_code WHERE value = 'MCD') and INSERT INTO stock_price_code (value), while query 1 will fail with duplicate.

The overhead of ON CONFLICT I believe is smaller then WHERE NOT EXISTS, not sure though

この記事はインターネットから収集されたものであり、転載の際にはソースを示してください。

侵害の場合は、連絡してください[email protected]

編集
0

コメントを追加

0
サインイン

関連記事

分類Dev

Postgres INSERT ON CONFLICT DO NOTHING vs SELECT + INSERTクエリ

分類Dev

Postgres INSERT ON CONFLICT with JSONB

分類Dev

Postgres INSERT ON CONFLICT DO UPDATE vsINSERTまたはUPDATE

分類Dev

PostgreSQL duplicate key value violates unique constraint while using on conflict do nothing on insert

分類Dev

Is it ok to insert ascii decimal characters in postgres select query?

分類Dev

Postgres 9.5 ON CONFLICT DO SELECT

分類Dev

Improve INSERT INTO SELECT Query performance?

分類Dev

MYSQL INSERT INTO FROM SELECT QUERY

分類Dev

Postgres - UNION ALL vs INSERT INTO which is better?

分類Dev

Syntax error for insert query alias in Postgres

分類Dev

C Sharp MysqlBulkInsert vs Muplitple Insert Query

分類Dev

Mysql Insert query with values and select as subquery

分類Dev

Creating single query with subquery, essentially a insert query with select query

分類Dev

How Do create insert query using procedure

分類Dev

postgres insert using CTE and use the inersted id in next sub query

分類Dev

Insert POINT into postgres database

分類Dev

Insert JSON into Postgres 9.5

分類Dev

LINQ-to-SQL Insert ... Select ... Where ... in one query

分類Dev

これは、PostgresでINSERT ON CONFLICTを一括する正しい方法ですか？

分類Dev

postgresql：INSERT INTO ...（SELECT * ...）

分類Dev

Insert ... select with enums in jOOQ

分類Dev

Apache Camel select then insert

分類Dev

Select if exist else insert?

分類Dev

SQL CASE SELECT INSERT

分類Dev

SQL select and insert if not exists

分類Dev

converting insert into to select

分類Dev

MySql Insert Into Select Sum

分類Dev

INSERT INTO ... mysqlのSELECT

分類Dev

simple insert query not working

Related 関連記事

  1. 1

    Postgres INSERT ON CONFLICT DO NOTHING vs SELECT + INSERTクエリ

  2. 2

    Postgres INSERT ON CONFLICT with JSONB

  3. 3

    Postgres INSERT ON CONFLICT DO UPDATE vsINSERTまたはUPDATE

  4. 4

    PostgreSQL duplicate key value violates unique constraint while using on conflict do nothing on insert

  5. 5

    Is it ok to insert ascii decimal characters in postgres select query?

  6. 6

    Postgres 9.5 ON CONFLICT DO SELECT

  7. 7

    Improve INSERT INTO SELECT Query performance?

  8. 8

    MYSQL INSERT INTO FROM SELECT QUERY

  9. 9

    Postgres - UNION ALL vs INSERT INTO which is better?

  10. 10

    Syntax error for insert query alias in Postgres

  11. 11

    C Sharp MysqlBulkInsert vs Muplitple Insert Query

  12. 12

    Mysql Insert query with values and select as subquery

  13. 13

    Creating single query with subquery, essentially a insert query with select query

  14. 14

    How Do create insert query using procedure

  15. 15

    postgres insert using CTE and use the inersted id in next sub query

  16. 16

    Insert POINT into postgres database

  17. 17

    Insert JSON into Postgres 9.5

  18. 18

    LINQ-to-SQL Insert ... Select ... Where ... in one query

  19. 19

    これは、PostgresでINSERT ON CONFLICTを一括する正しい方法ですか？

  20. 20

    postgresql：INSERT INTO ...（SELECT * ...）

  21. 21

    Insert ... select with enums in jOOQ

  22. 22

    Apache Camel select then insert

  23. 23

    Select if exist else insert?

  24. 24

    SQL CASE SELECT INSERT

  25. 25

    SQL select and insert if not exists

  26. 26

    converting insert into to select

  27. 27

    MySql Insert Into Select Sum

  28. 28

    INSERT INTO ... mysqlのSELECT

  29. 29

    simple insert query not working

ホットタグ

アーカイブ