drop table customers;
CREATE TABLE customers (
name VARCHAR not null,
email VARCHAR NOT NULL,
active bool NOT NULL DEFAULT TRUE,
constraint pk_customer primary key(name, email)
);
INSERT INTO customers (NAME, email)
VALUES
('IBM', 'ibm@postgresdba.com'),
(
'Microsoft',
'ms@postgresdba.com'
),
(
'Intel',
'intel@postgresdba.com'
);
INSERT INTO customers (name, email)
VALUES
(
'Microsoft',
'ms@postgresdba.com'
)
ON CONFLICT (name,email) -- 컬럼명 말고 pk constraint 명시도 가능
DO
UPDATE
SET email = EXCLUDED.email || '==> new_' || customers.email;
SQL> select * From customers;
+-----------+----------------------------------------------+--------+
| name | email | active |
+-----------+----------------------------------------------+--------+
| IBM | ibm@postgresdba.com | t |
| Intel | intel@postgresdba.com | t |
| Microsoft | ms@postgresdba.com==> new_ms@postgresdba.com | t |
+-----------+----------------------------------------------+--------+
(3 rows)
