Postgres Insert where not exists multiple values

Question

I want to insert multiple values in a Postgres table (value, guest_name, guest_room_number, created, employee_id, guest_group_id, table_numbers, show) if multiple values (value, guestName, guestRoomNumber) do not already exist. How can I achieve that?

Thanks for the help.

My try:

 await this.db.query(
 `INSERT INTO app_trace(value, guest_name, guest_room_number, created, employee_id, guest_group_id, table_numbers, show)
  SELECT $1, $2, $3, $4, $5, $6, $7, $8
  WHERE NOT EXISTS (SELECT value FROM app_trace WHERE value=$1 AND guest_name=$2 AND guest_room_number=$3)`,
 [value, guestName, guestRoomNumber, created, employeeId, guestGroupId, tableNumbers, show]);`

The try is still saving the data, even though the values do exist in the DB.

Example Data in the DB:

INSERT INTO "public"."app_trace"("id","value","guest_name","guest_room_number","created","employee_id","guest_group_id","table_numbers","show")
VALUES
(88,E'test',E'Maierei',E'123',E'2019-08-05 15:15:45.984+00',1,65866,E'90',NULL);

This try still inserts the data, but I do not want that.

 await this.db.query(
 `INSERT INTO app_trace(value, guest_name, guest_room_number, created, employee_id, guest_group_id, table_numbers, show)
  SELECT $1, $2, $3, $4, $5, $6, $7, $8
  WHERE NOT EXISTS (SELECT value FROM app_trace WHERE value=$1 AND guest_name=$2 AND guest_room_number=$3)`,
 ['test', 'Maierei', '123', '2019-08-06 15:15:45.984+00', 1, 123445, '23', true]);`

I want that the statement is not inserting the data, because the values (value, guestName, guestRoomNumber) already exist in the table.

What is failing in this query? Could you provide the error message and also a few sample data? — Jim Jones
– Jim Jones, Commented Dec 31, 2019 at 11:00
What exactly is the problem? Are you getting an error? The wrong result? — Mureinik
– Mureinik, Commented Dec 31, 2019 at 11:08
Do you have a unique constraint on (value, guest_name, guest_room_number)? — user330315
– user330315, Commented Dec 31, 2019 at 11:09
No I do not have a unique constraint on (value, guest_name, guest_room_number). Should I add one? Is there a possibility to avoid that? — user9408779
– user9408779, Commented Dec 31, 2019 at 11:12
If you want to make sure you never have duplicates than yes, you should create a unique constraint (or index) on those columns (regardless of the problem at hand) — user330315
– user330315, Commented Dec 31, 2019 at 11:15

Jim Jones · Accepted Answer · 2019-12-31 11:25:17Z

2

If you don't have an unique constraint in the columns value, guest_name, guest_room_number you should definitely create one. After that just create an upsert to ignore the insert in case of a conflict, e.g.

INSERT INTO app_trace 
  (value, guest_name, guest_room_number, created, 
   employee_id, guest_group_id, table_numbers, show)
VALUES ($1, $2, $3, $4, $5, $6, $7, $8)
ON CONFLICT (value,guest_name,guest_room_number) DO NOTHING

Example:

CREATE TEMPORARY TABLE t (foo INT, bar INT, bar2 INT, PRIMARY KEY (foo,bar));
INSERT INTO t (foo, bar, bar2) VALUES(1, 2, 1) ON CONFLICT (foo,bar) DO NOTHING;
INSERT INTO t (foo, bar, bar2) VALUES(1, 2, 42) ON CONFLICT (foo,bar) DO NOTHING;

SELECT * FROM t;

 foo | bar | bar2 
-----+-----+------
   1 |   2 |    1
(1 Zeile)

edited Dec 31, 2019 at 11:25

answered Dec 31, 2019 at 11:13

Jim Jones

19.9k3 gold badges44 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgres Insert where not exists multiple values

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related