D
D
decenter2019-04-05 07:26:10
PostgreSQL
decenter, 2019-04-05 07:26:10

How to implement the if update else insert construct in postgresql?

Good day to all!
What is there?
there is a conditional table

CREATE TABLE public.company
(
    id integer DEFAULT nextval('company_id_seq'::regclass) NOT NULL,
   company_name varchar(125),
   inn varchar(32),
    kpp varchar(32),
    "check" varchar(65)  -- проверка уникальности, так как данные формируются из нескольких источников
);
CREATE UNIQUE INDEX company_check_uindex ON public.company ("check");

the task is to implement some analogue
INSERT INTO company ( company_name, inn, kpp, "check")  VALUES ('ООО рога и копыта', ''111, '222', '111-222')   ON CONFLICT (\"check\") DO UPDATE SET company_name = 'ООО рога и копыта',  inn='111', kpp='222',  RETURNING id;

In this case, it is necessary to bypass the "side effect" of increasing the id in case of updating the data.
Googling found several options, the most suitable of which, in my opinion, is
DO
    $do$
    BEGIN
UPDATE SET company_name = 'ООО рога и копыта',  inn='111', kpp='222',  WHERE "check"='111-222'    RETURNING id;
    IF NOT FOUND THEN
INSERT INTO company ( company_name, inn, kpp, "check")  VALUES ('ООО рога и копыта', ''111, '222', '111-222')  RETURNING id;
    END IF;
    END
    $do$;

However, this option throws an error.
ERROR: current transaction is aborted, commands ignored until end of transaction block [42601] ERROR: syntax error at or near "INTO

Prompt in what there can be a hitch, or alternative of the decision of a question with bypass of the above-stated "side effect".
Thanks in advance!

Answer the question

In order to leave comments, you need to log in

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question