[DATABASE/POSTGRESQL] INSERT ON CONFLICT 명령 : 제약 조건 위반시 무시하기

icodebroker POSTGRESQL 2024-06-18

■ INSERT ON CONFLICT 명령을 사용해 제약 조건 위반시 예외 발생을 무시하는 방법을 보여준다.

▶ 예제 코드 (SQL)


DROP TABLE IF EXISTS TEST_CUSTOMER;

CREATE TABLE TEST_CUSTOMER
(
    CUSTOMER_ID SERIAL PRIMARY KEY
   ,NAME        VARCHAR UNIQUE
   ,EMAIL       VARCHAR NOT NULL
   ,ACTIVE      BOOL NOT NULL DEFAULT TRUE
);

INSERT INTO TEST_CUSTOMER (NAME, EMAIL) VALUES
    ('IBM'      , 'contact@ibm.com'      ),
    ('Microsoft', 'contact@microsoft.com'),
    ('Intel'    , 'contact@intel.com'    );

INSERT INTO TEST_CUSTOMER (NAME, EMAIL) VALUES ('Microsoft', 'hotline@microsoft.com')
ON CONFLICT ON CONSTRAINT TEST_CUSTOMER_NAME_KEY
DO NOTHING;

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

DROP TABLE IF EXISTS TEST_CUSTOMER;

CREATE TABLE TEST_CUSTOMER

(

CUSTOMER_ID SERIAL PRIMARY KEY

,NAME VARCHAR UNIQUE

,EMAIL VARCHAR NOT NULL

,ACTIVE BOOL NOT NULL DEFAULT TRUE

);

INSERT INTO TEST_CUSTOMER (NAME, EMAIL) VALUES

('IBM' , 'contact@ibm.com' ),

('Microsoft', 'contact@microsoft.com'),

('Intel' , 'contact@intel.com' );

INSERT INTO TEST_CUSTOMER (NAME, EMAIL) VALUES ('Microsoft', 'hotline@microsoft.com')

ON CONFLICT ON CONSTRAINT TEST_CUSTOMER_NAME_KEY

DO NOTHING;