[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 (NAME)
DO UPDATE SET EMAIL = EXCLUDED.EMAIL || ';' || TEST_CUSTOMER.EMAIL;

SELECT * FROM PUBLIC.TEST_CUSTOMER;

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

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 (NAME)

DO UPDATE SET EMAIL = EXCLUDED.EMAIL || ';' || TEST_CUSTOMER.EMAIL;

SELECT * FROM PUBLIC.TEST_CUSTOMER;