0

So I have the following PostgreSQL query:

INSERT INTO "Teams" ("name","createdAt","updatedAt") 
VALUES ('SOMETHING','2021-01-19 12:33:20.323 +00:00','2021-01-19 12:33:20.323 +00:00') 
ON CONFLICT DO NOTHING RETURNING *;

The table is as following:

CREATE TABLE "Teams" (
    id integer NOT NULL,
    name character varying(255),
    "createdAt" timestamp with time zone NOT NULL,
    "updatedAt" timestamp with time zone NOT NULL,
    abbreviation character varying(255)
);

But If I run this +2 times every time a new record is inserted.

Any idea on why it is doing this, and mostly how to prevent it from creating new records?

Kiwi
  • 2,713
  • 7
  • 44
  • 82
  • 3
    I am surprised you don't get an error message. Your table has absolutely no primary or unique constraint. –  Jan 19 '21 at 12:51
  • Btw: there is no magic performance improvement to defining a varchar column with a length of 255 compare to e.g. 260 or 240. –  Jan 19 '21 at 12:51
  • 2
    Your table definition does not seem to include the unique constraints (that you could conflict with) nor the default expression for the `id` column. – Bergi Jan 19 '21 at 12:51
  • Why did you think a conflict could happen? – Frank Heikens Jan 19 '21 at 13:12

1 Answers1

2

You are missing primary key or unique constraints on the table.

If there are no such constraints, there can also be no conflicts, so all rows will be inserted.

Perhaps you want a primary key on id and a unique constraint on name:

ALTER TABLE "Teams" ADD PRIMARY KEY (id);
ALTER TABLE "Teams" ADD UNIQUE (name);
Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • Apparently the primary key was the next line in the create table, but the unique is indeed the solution for fixing the duplicate values – Kiwi Jan 19 '21 at 13:22