constraint c for table t does not exist on PostgreSQL even though it’s there

I’m trying to run an INSERT query on TablePlus.

INSERT INTO minutes_clone (date, ticker, "lastTime", "openTime", date_time, group_type, "totalVolume", "totalPrice", "totalTrades")
        VALUES('2021-07-02', 'YELP', '00:15:00', '00:00:00', '2021-07-02 00:00:00', 15,  0,  0,  0) 
        ON CONFLICT ON CONSTRAINT minutes_clone_stick_tickers_unique 
        DO UPDATE SET "lastTime" = '00:15:00', "openTime" = '00:00:00', date_time = '2021-07-02 00:00:00', "totalVolume" = 0, "totalPrice" = 0, "totalTrades" = 0
        RETURNING id;

Instead of the query sending a success message, I’m getting an ERROR: constraint “minutes_clone_stick_tickers_unique” for table “minutes_clone” does not exist.

Here is an image of my table structure.

to replicate:

CREATE TABLE "public"."minutes_clone" (
    "ticker" varchar NOT NULL,
    "totalTrades" int4 NOT NULL,
    "totalPrice" numeric NOT NULL,
    "totalVolume" int4,
    "lastTime" time NOT NULL,
    "openTime" time NOT NULL,
    "date" date NOT NULL,
    "group_type" int4 NOT NULL DEFAULT 1,
    "date_time" timestamp,
    "parent_id" int4,
    "id" int4 NOT NULL DEFAULT nextval('id_seq'::regclass),
    PRIMARY KEY ("id")
);

CREATE INDEX "minutes_clone_ticker_group_date_index" ON "public"."minutes_clone" USING BTREE ("ticker","group_type","date_time");

CREATE UNIQUE INDEX "minutes_clone_stick_tickers_unique" ON "public"."minutes_clone" USING BTREE ("date","ticker","openTime","group_type");

CREATE INDEX "minutes_clone_date_time_index" ON "public"."minutes_clone" USING BTREE ("date_time");

I’ve tried many things, like removing ON CONSTRAINT and dropping and re-adding the constraing but haven’t been able to solve this issue. Any solutions?

Answer

You are mixing up indexes and constraints. That is understandable, because a unique constraint is always implemented by a unique index, but they are still not the same.

To make your statement work, you need a unique constraint on top of the index you currently have. You can create that with:

ALTER TABLE public.minutes_clone
   ADD UNIQUE USING INDEX minutes_clone_stick_tickers_unique;

Advertisement

Answer