Error when using `on conflict` on postgresql query-CodePudding

I've the following table on PostgreSQL:

-- Table: product_ohlcv_history

-- DROP TABLE product_ohlcv_history;

CREATE TABLE product_ohlcv_history
(
  broker_uuid uuid NOT NULL,
  product_id character varying(10) NOT NULL, -- ID of the product.
  "timestamp" character varying(50) NOT NULL, -- Timestamp when value was calculated.
  open double precision NOT NULL, -- Open price
  high double precision NOT NULL, -- High price
  low double precision NOT NULL, -- Low price
  close double precision NOT NULL, -- Close price
  volume double precision NOT NULL, -- Volume.
  timeframe integer NOT NULL, -- Timeframe of the OHLCV in seconds.
  CONSTRAINT "PK_product_ohlcv_history" PRIMARY KEY (product_id, "timestamp", broker_uuid)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE product_ohlcv_history
  OWNER TO postgres;
COMMENT ON COLUMN product_ohlcv_history.product_id IS 'ID of the product.';
COMMENT ON COLUMN product_ohlcv_history."timestamp" IS 'Timestamp when value was calculated.';
COMMENT ON COLUMN product_ohlcv_history.open IS 'Open price';
COMMENT ON COLUMN product_ohlcv_history.high IS 'High price';
COMMENT ON COLUMN product_ohlcv_history.low IS 'Low price';
COMMENT ON COLUMN product_ohlcv_history.close IS 'Close price';
COMMENT ON COLUMN product_ohlcv_history.volume IS 'Volume.';
COMMENT ON COLUMN product_ohlcv_history.timeframe IS 'Timeframe of the OHLCV in seconds.';

The following query fails, giving the error

ERROR:  syntax error near 'on'
LINE 4: ON CONFLICT ON CONSTRAINT PK_product_ohlcv_history do nothin...

This is the query:

insert into product_ohlcv_history (broker_uuid, product_id, timestamp, open, high, low, close, volume, timeframe) values 
('85dcfe70b81c44589a805dca647dad0c', '1INCH-BTC', '05/04/2022 04:40:00  00:00', 0.000041, 0.0000411, 0.000041, 0.0000411, 2.67, 60), 
('85dcfe70b81c44589a805dca647dad0c', '1INCH-BTC', '05/04/2022 03:01:00  00:00', 0.0000411, 0.0000411, 0.0000411, 0.0000411, 53.54, 60)
ON CONFLICT ON CONSTRAINT PK_product_ohlcv_history do nothing;

If I remove the last row, the normal insertion works. So what I'm doing wrong? I want to avoid errors if for some reason the row that I want to insert already exists.

I'm using PostgreSQL version 9.4.

CodePudding user response：

ON CONFLICT was introduced in PostgreSQL 9.5. But if you are running an old and unsupported version of PostgreSQL, that's the least of your problems.