CODEWITHSUNDEEP
sqlpostgresqlupsert
user9408779
user9408779

Reputation:

Postgres Insert where not exists multiple values

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.

Upvotes: 2

Views: 1664

Answers (1)

Jim Jones
Jim Jones

Reputation: 19653

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)

Upvotes: 2

Related Questions