Skip to content
Search

PostgreSQL:InsertInto

lastmod 2021-05-01created 2021-03-029 edits

Insert into if not exists

How to INSERT when a query returns no results: 

INSERT INTO users (full_name, login, password) 
  SELECT 'Mahbub Tito','tito',SHA1('12345') FROM DUAL
WHERE NOT EXISTS 
  (SELECT login FROM users WHERE login='tito');

UPSERT

Updates a record if it already exists, or inserts a new record if it doesn't. The form is as follows: 

INSERT
    INTO
    TABLE_NAME(COLUMN_1)
VALUES(VALUE_1) ON
CONFLICT TARGET ACTION;

Sample example: 

INSERT
    INTO
    CUSTOMERS(NAME,
    EMAIL)
VALUES ('MICROSOFT',
'[email protected]') ON
CONFLICT (NAME) DO
UPDATE
SET
    EMAIL = EXCLUDED.EMAIL || ';' || CUSTOMERS.EMAIL;

COMMIT;

See also

Favorite site

author Zer0 sitemap