I’m getting the following error when doing the following type of insert:
Query:
INSERT INTO accounts (type, person_id) VALUES ('PersonAccount', 1) ON
CONFLICT (type, person_id) WHERE type = 'PersonAccount' DO UPDATE SET
updated_at = EXCLUDED.updated_at RETURNING *
Error:
SQL execution failed (Reason: ERROR: there is no unique or exclusion
constraint matching the ON CONFLICT specification)
I also have an unique INDEX:
CREATE UNIQUE INDEX uniq_person_accounts ON accounts USING btree (type,
person_id) WHERE ((type)::text = 'PersonAccount'::text);
The thing is that sometimes it works, but not every time. I randomly get
that exception, which is really strange. It seems that it can’t access that
INDEX or it doesn’t know it exists.
Any suggestion?
I’m using PostgreSQL 9.5.5.
Example while executing the code that tries to find or create an account:
INSERT INTO accounts (type, person_id, created_at, updated_at) VALUES ('PersonAccount', 69559, '2017-02-03 12:09:27.259', '2017-02-03 12:09:27.259') ON CONFLICT (type, person_id) WHERE type = 'PersonAccount' DO UPDATE SET updated_at = EXCLUDED.updated_at RETURNING *
SQL execution failed (Reason: ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification)
In this case, I’m sure that the account does not exist. Furthermore, it never outputs the error when the person has already an account. The problem is that, in some cases, it also works if there is no account yet. The query is exactly the same.
asked Feb 3, 2017 at 10:43
Tiago BaboTiago Babo
1,0141 gold badge7 silver badges9 bronze badges
12
Per the docs,
All table_name unique indexes that, without regard to order, contain exactly the
conflict_target-specified columns/expressions are inferred (chosen) as arbiter
indexes. If an index_predicate is specified, it must, as a further requirement
for inference, satisfy arbiter indexes.
The docs go on to say,
[index_predicate are u]sed to allow inference of partial unique indexes
In an understated way, the docs are saying that when using a partial index and
upserting with ON CONFLICT, the index_predicate must be specified. It is not
inferred for you. I learned this
here, and the following example demonstrates this.
CREATE TABLE test.accounts (
id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
type text,
person_id int);
CREATE UNIQUE INDEX accounts_note_idx on accounts (type, person_id) WHERE ((type)::text = 'PersonAccount'::text);
INSERT INTO test.accounts (type, person_id) VALUES ('PersonAccount', 10);
so that we have:
unutbu=# select * from test.accounts;
+----+---------------+-----------+
| id | type | person_id |
+----+---------------+-----------+
| 1 | PersonAccount | 10 |
+----+---------------+-----------+
(1 row)
Without index_predicate
we get an error:
INSERT INTO test.accounts (type, person_id) VALUES ('PersonAccount', 10) ON CONFLICT (type, person_id) DO NOTHING;
-- ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification
But if instead you include the index_predicate, WHERE ((type)::text = 'PersonAccount'::text)
:
INSERT INTO test.accounts (type, person_id) VALUES ('PersonAccount', 10)
ON CONFLICT (type, person_id)
WHERE ((type)::text = 'PersonAccount'::text) DO NOTHING;
then there is no error and DO NOTHING is honored.
answered Jan 13, 2019 at 13:57
unutbuunutbu
836k182 gold badges1773 silver badges1663 bronze badges
1
A simple solution of this error
First of all let’s see the cause of error with a simple example. Here is the table mapping products to categories.
create table if not exists product_categories (
product_id uuid references products(product_id) not null,
category_id uuid references categories(category_id) not null,
whitelist boolean default false
);
If we use this query:
INSERT INTO product_categories (product_id, category_id, whitelist)
VALUES ('123...', '456...', TRUE)
ON CONFLICT (product_id, category_id)
DO UPDATE SET whitelist=EXCLUDED.whitelist;
This will give you error No unique or exclusion constraint matching the ON CONFLICT
because there is no unique constraint on product_id
and category_id
. There could be multiple rows having the same combination of product and category id (so there can never be a conflict on them).
Solution:
Use unique constraint on both product_id
and category_id
like this:
create table if not exists product_categories (
product_id uuid references products(product_id) not null,
category_id uuid references categories(category_id) not null,
whitelist boolean default false,
primary key(product_id, category_id) -- This will solve the problem
-- unique(product_id, category_id) -- OR this if you already have a primary key
);
Now you can use ON CONFLICT (product_id, category_id)
for both columns without any error.
In short: Whatever column(s) you use with on conflict
, they should have unique constraint.
answered Apr 6, 2021 at 21:56
Ali SajjadAli Sajjad
3,44126 silver badges38 bronze badges
The easy way to fix it is by setting the conflicting column as UNIQUE
answered Mar 3, 2021 at 14:58
idirall22idirall22
3162 silver badges11 bronze badges
I did not have a chance to play with UPSERT, but I think you have a case from
docs:
Note that this means a non-partial unique index (a unique index
without a predicate) will be inferred (and thus used by ON CONFLICT)
if such an index satisfying every other criteria is available. If an
attempt at inference is unsuccessful, an error is raised.
answered Feb 3, 2017 at 13:43
Vao TsunVao Tsun
46.7k12 gold badges98 silver badges131 bronze badges
1
I solved the same issue by creating one UNIQUE INDEX for ALL columns you want to include in the ON CONFLICT clause, not one UNIQUE INDEX for each of the columns.
CREATE TABLE table_name (
element_id UUID NOT NULL DEFAULT gen_random_uuid(),
timestamp TIMESTAMP NOT NULL DEFAULT now():::TIMESTAMP,
col1 UUID NOT NULL,
col2 STRING NOT NULL ,
col3 STRING NOT NULL ,
CONSTRAINT "primary" PRIMARY KEY (element_id ASC),
UNIQUE (col1 asc, col2 asc, col3 asc)
);
Which will allow to query like
INSERT INTO table_name (timestamp, col1, col2, col3) VALUES ('timestamp', 'uuid', 'string', 'string')
ON CONFLICT (col1, col2, col3)
DO UPDATE timestamp = EXCLUDED.timestamp, col1 = EXCLUDED.col1, col2 = excluded.col2, col3 = col3.excluded;
answered Jul 23, 2022 at 13:33
To use on conflict
you need to enforce a unique(userid, songd)
on your summary_songs
table:
SQL Fiddle
PostgreSQL 9.6 Schema Setup:
CREATE TABLE summary_songs (
date_time date NOT NULL,
userid integer NOT NULL,
songd integer NOT NULL,
countid integer NOT NULL,
unique(userid, songd)
);
CREATE TABLE daily_report(
date_time date NOT NULL,
userid integer NOT NULL,
songd integer NOT NULL,
countid integer NOT NULL
);
insert into daily_report (date_time, userid, songd, countid) values
(to_date('2017-12-31','YYYY-MM-DD'), 1 , 1 , 5),
(to_date('2017-12-31','YYYY-MM-DD'), 2 , 1 , 10),
(to_date('2017-12-31','YYYY-MM-DD'), 4 , 1 , 7);
insert into summary_songs (date_time, userid, songd, countid) values
(to_date('2017-12-30', 'YYYY-MM-DD'),1, 1, 80),
(to_date('2017-12-30', 'YYYY-MM-DD'),2, 1, 51),
(to_date('2017-12-30', 'YYYY-MM-DD'),3, 1, 66);
Query 1:
select * from daily_report
Results:
| date_time | userid | songd | countid |
|------------|--------|-------|---------|
| 2017-12-31 | 1 | 1 | 5 |
| 2017-12-31 | 2 | 1 | 10 |
| 2017-12-31 | 4 | 1 | 7 |
Query 2:
select * from summary_songs
Results:
| date_time | userid | songd | countid |
|------------|--------|-------|---------|
| 2017-12-30 | 1 | 1 | 80 |
| 2017-12-30 | 2 | 1 | 51 |
| 2017-12-30 | 3 | 1 | 66 |
Query 3:
insert into summary_songs (date_time, userid, songd, countid)
select date_time, userid, songd, countid from daily_report
on conflict (userid, songd)
do update set
countid = summary_songs.countid + excluded.countid ,
date_time = excluded.date_time
Query 4:
select * from summary_songs
Results:
| date_time | userid | songd | countid |
|------------|--------|-------|---------|
| 2017-12-30 | 3 | 1 | 66 |
| 2017-12-31 | 1 | 1 | 85 |
| 2017-12-31 | 2 | 1 | 61 |
| 2017-12-31 | 4 | 1 | 7 |
Добрый день.
У меня есть таблица в PostgreSQL с ключем id.
Как правильно вставить или заменить?
Если делаю так, то плдучаю ошибку :нет уникального ограничения или ограничения-исключения, соответствующего указанию ON CONFLICT
INSERT INTO public.drivers( name_driver, surname, middle_name ) VALUES ('name', 'surname', 'midlename2') ON CONFLICT (middle_name) --WHERE middle_name ='name_driver3' DO UPDATE SET name_driver = 'name_driver3', surname = 'surname3', middle_name = 'middle_name3'
Do you like it? Share on social networks!
I currently have a table which looks like this:
CREATE TABLE "PDPC".collection
(
col_no bigint NOT NULL DEFAULT nextval('"PDPC".collection_col_no_seq'::regclass),
q1 character varying(10000) COLLATE pg_catalog."default",
q2 character varying(10000) COLLATE pg_catalog."default",
q3 character varying(10000) COLLATE pg_catalog."default",
q4 character varying(10000) COLLATE pg_catalog."default",
dg_fkey bigint,
CONSTRAINT collection_pkey PRIMARY KEY (col_no),
CONSTRAINT collection_dg_fkey_fkey FOREIGN KEY (dg_fkey)
REFERENCES "PDPC".datagroup (dg_no) MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE NO ACTION
)
WITH (
OIDS = FALSE
)
TABLESPACE pg_default;
ALTER TABLE "PDPC".collection
OWNER to postgres;
I am trying to execute an UPSERT statement in PHP using postgresql, but i received
Fatal error: Uncaught PDOException: SQLSTATE[42P10]: Invalid column reference: 7 ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification in C:Apache24htdocsconsideration.php:77 Stack trace: #0 C:Apache24htdocsconsideration.php(77): PDOStatement->execute() #1 {main} thrown in C:Apache24htdocsconsideration.php on line 77
My web page currently has a form that takes in a user input of answers to four questions, and these questions will go into "PDPC".collection
table. I want to run INSERT or UPDATE according to the dm_fkey
, which is the foreign key I have set for this table.
This is the UPSERT statement that I used.
INSERT INTO "PDPC".collection (q1, q2, q3, q4, dg_fkey)
VALUES (:q1, :q2, :q3, :q4, :dg_no)
ON CONFLICT(dg_fkey) DO UPDATE
SET q1=:q1, q2=:q2, q3=:q3, q4=:q4
I’m getting the following error when doing the following type of insert:
Query:
INSERT INTO accounts (type, person_id) VALUES ('PersonAccount', 1) ON
CONFLICT (type, person_id) WHERE type = 'PersonAccount' DO UPDATE SET
updated_at = EXCLUDED.updated_at RETURNING *
Error:
SQL execution failed (Reason: ERROR: there is no unique or exclusion
constraint matching the ON CONFLICT specification)
I also have an unique INDEX:
CREATE UNIQUE INDEX uniq_person_accounts ON accounts USING btree (type,
person_id) WHERE ((type)::text = 'PersonAccount'::text);
The thing is that sometimes it works, but not every time. I randomly get
that exception, which is really strange. It seems that it can’t access that
INDEX or it doesn’t know it exists.
Any suggestion?
I’m using PostgreSQL 9.5.5.
Example while executing the code that tries to find or create an account:
INSERT INTO accounts (type, person_id, created_at, updated_at) VALUES ('PersonAccount', 69559, '2017-02-03 12:09:27.259', '2017-02-03 12:09:27.259') ON CONFLICT (type, person_id) WHERE type = 'PersonAccount' DO UPDATE SET updated_at = EXCLUDED.updated_at RETURNING *
SQL execution failed (Reason: ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification)
In this case, I’m sure that the account does not exist. Furthermore, it never outputs the error when the person has already an account. The problem is that, in some cases, it also works if there is no account yet. The query is exactly the same.
asked Feb 3, 2017 at 10:43
Tiago BaboTiago Babo
9941 gold badge6 silver badges9 bronze badges
12
Per the docs,
All table_name unique indexes that, without regard to order, contain exactly the
conflict_target-specified columns/expressions are inferred (chosen) as arbiter
indexes. If an index_predicate is specified, it must, as a further requirement
for inference, satisfy arbiter indexes.
The docs go on to say,
[index_predicate are u]sed to allow inference of partial unique indexes
In an understated way, the docs are saying that when using a partial index and
upserting with ON CONFLICT, the index_predicate must be specified. It is not
inferred for you. I learned this
here, and the following example demonstrates this.
CREATE TABLE test.accounts (
id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
type text,
person_id int);
CREATE UNIQUE INDEX accounts_note_idx on accounts (type, person_id) WHERE ((type)::text = 'PersonAccount'::text);
INSERT INTO test.accounts (type, person_id) VALUES ('PersonAccount', 10);
so that we have:
unutbu=# select * from test.accounts;
+----+---------------+-----------+
| id | type | person_id |
+----+---------------+-----------+
| 1 | PersonAccount | 10 |
+----+---------------+-----------+
(1 row)
Without index_predicate
we get an error:
INSERT INTO test.accounts (type, person_id) VALUES ('PersonAccount', 10) ON CONFLICT (type, person_id) DO NOTHING;
-- ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification
But if instead you include the index_predicate, WHERE ((type)::text = 'PersonAccount'::text)
:
INSERT INTO test.accounts (type, person_id) VALUES ('PersonAccount', 10)
ON CONFLICT (type, person_id)
WHERE ((type)::text = 'PersonAccount'::text) DO NOTHING;
then there is no error and DO NOTHING is honored.
answered Jan 13, 2019 at 13:57
unutbuunutbu
816k176 gold badges1753 silver badges1649 bronze badges
1
A simple solution of this error
First of all let’s see the cause of error with a simple example. Here is the table mapping products to categories.
create table if not exists product_categories (
product_id uuid references products(product_id) not null,
category_id uuid references categories(category_id) not null,
whitelist boolean default false
);
If we use this query:
INSERT INTO product_categories (product_id, category_id, whitelist)
VALUES ('123...', '456...', TRUE)
ON CONFLICT (product_id, category_id)
DO UPDATE SET whitelist=EXCLUDED.whitelist;
This will give you error No unique or exclusion constraint matching the ON CONFLICT
because there is no unique constraint on product_id
and category_id
. There could be multiple rows having the same combination of product and category id (so there can never be a conflict on them).
Solution:
Use unique constraint on both product_id
and category_id
like this:
create table if not exists product_categories (
product_id uuid references products(product_id) not null,
category_id uuid references categories(category_id) not null,
whitelist boolean default false,
primary key(product_id, category_id) -- This will solve the problem
-- unique(product_id, category_id) -- OR this if you already have a primary key
);
Now you can use ON CONFLICT (product_id, category_id)
for both columns without any error.
In short: Whatever column(s) you use with on conflict
, they should have unique constraint.
answered Apr 6, 2021 at 21:56
Ali SajjadAli Sajjad
2,91324 silver badges35 bronze badges
The easy way to fix it is by setting the conflicting column as UNIQUE
answered Mar 3, 2021 at 14:58
idirall22idirall22
1961 silver badge10 bronze badges
I did not have a chance to play with UPSERT, but I think you have a case from
docs:
Note that this means a non-partial unique index (a unique index
without a predicate) will be inferred (and thus used by ON CONFLICT)
if such an index satisfying every other criteria is available. If an
attempt at inference is unsuccessful, an error is raised.
answered Feb 3, 2017 at 13:43
Vao TsunVao Tsun
45.5k10 gold badges93 silver badges124 bronze badges
1
I solved the same issue by creating one UNIQUE INDEX for ALL columns you want to include in the ON CONFLICT clause, not one UNIQUE INDEX for each of the columns.
CREATE TABLE table_name (
element_id UUID NOT NULL DEFAULT gen_random_uuid(),
timestamp TIMESTAMP NOT NULL DEFAULT now():::TIMESTAMP,
col1 UUID NOT NULL,
col2 STRING NOT NULL ,
col3 STRING NOT NULL ,
CONSTRAINT "primary" PRIMARY KEY (element_id ASC),
UNIQUE (col1 asc, col2 asc, col3 asc)
);
Which will allow to query like
INSERT INTO table_name (timestamp, col1, col2, col3) VALUES ('timestamp', 'uuid', 'string', 'string')
ON CONFLICT (col1, col2, col3)
DO UPDATE timestamp = EXCLUDED.timestamp, col1 = EXCLUDED.col1, col2 = excluded.col2, col3 = col3.excluded;
answered Jul 23, 2022 at 13:33
#python #postgresql #sqlalchemy #amazon-rds #postgresql-9.3
Вопрос:
(извините, на SO есть много похожих вопросов, но ни один из них я не смог найти достаточно хорошо)
Попытка перейти в таблицу RDS Postgres через временную таблицу…
import sqlalchemy as sa
# assume db_engine is already set up
with db_engine.connect() as conn:
conn.execute(sa.text("DROP TABLE IF EXISTS temp_table"))
build_temp_table = f"""
CREATE TABLE temp_table (
unique_id VARCHAR(40) NOT NULL,
date TIMESTAMP,
amount NUMERIC,
UNIQUE (unique_id)
);
"""
conn.execute(sa.text(build_temp_table))
upsert_sql_string = """
INSERT INTO production_table(unique_id, date, amount)
SELECT unique_id, date, amount FROM temp_table
ON CONFLICT (unique_id)
DO UPDATE SET
date = excluded.date,
amount = excluded.amount
"""
conn.execute(sa.text(upsert_sql_string))
Примечание: production_table
настроен одинаково для temp_table
Другие методы, которые я пробовал, включают:
- Указание
unique_id
в качестве ПЕРВИЧНОГО КЛЮЧА илиUNIQUE
в определении таблицы - Запуск
ALTER TABLE temp_table ADD PRIMARY KEY (unique_id)
после созданияtemp_table
Независимо от того, что я делаю, я получаю ошибку:
psycopg2.errors.InvalidColumnReference: there is no unique or exclusion constraint matching the ON CONFLICT specification
Спасибо
Комментарии:
1.
PK
ОперационнаяUNIQUE
должна быть включенаproduction_table
, вот где будет конфликт.2. да, я забыл упомянуть, что
production_table
он настроен идентичноtemp_table
3. Либо это не так, либо есть
search_path
настройка, указывающая на другую версиюproduction_table
Часть 12 Можем ли мы объединить две таблицы без отношения первичного внешнего ключа
Я получаю следующую ошибку при вставке следующего типа:
Запрос:
INSERT INTO accounts (type, person_id) VALUES ('PersonAccount', 1) ON CONFLICT (type, person_id) WHERE type = 'PersonAccount' DO UPDATE SET updated_at = EXCLUDED.updated_at RETURNING *
Ошибка:
Ошибка выполнения SQL (Причина: ОШИБКА: нет ограничения уникальности или исключения, соответствующего спецификации ON CONFLICT)
Еще у меня есть уникальный ИНДЕКС:
CREATE UNIQUE INDEX uniq_person_accounts ON accounts USING btree (type, person_id) WHERE ((type)::text = 'PersonAccount'::text);
Дело в том, что иногда это срабатывает, но не всегда. я случайно получить это исключение, что действительно странно. Кажется, что он не может получить доступ к этому ИНДЕКСУ или не знает, что он существует.
Любое предложение?
Я использую PostgreSQL 9.5.5.
Пример при выполнении кода, который пытается найти или создать учетную запись:
INSERT INTO accounts (type, person_id, created_at, updated_at) VALUES ('PersonAccount', 69559, '2017-02-03 12:09:27.259', '2017-02-03 12:09:27.259') ON CONFLICT (type, person_id) WHERE type = 'PersonAccount' DO UPDATE SET updated_at = EXCLUDED.updated_at RETURNING * SQL execution failed (Reason: ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification)
В этом случае я уверен, что учетной записи не существует. Кроме того, он никогда не выводит ошибку, если у человека уже есть учетная запись. Проблема в том, что в некоторых случаях он также работает, если еще нет учетной записи. Запрос точно такой же.
- 1 может потому что это частичный индекс? ..
- Вы можете уточнить?
- Частичные индексы @VaoTsun поддерживаются
ON CONFLICT
- @pozs Спасибо. Разве ограничение Unique или PK не является обязательным для апсерта?
- 1 @TiagoBabo
type
внутри индекса кажется избыточным (из-за вашего предиката это может быть только'PersonAccount'
) — попробуйте с(person_id) WHERE type = 'PersonAccount'
как в индексе, так и вON CONFLICT
Технические характеристики
Согласно документам,
Все уникальные индексы table_name, которые, безотносительно порядка, содержат именно столбцы / выражения, указанные в параметре Conflict_target, выводятся (выбираются) как индексы-арбитры. Если указан index_predicate, он должен, в качестве дополнительного требования для вывода, удовлетворять индексам арбитра.
В документах говорится:
[index_predicate are u] sed, чтобы разрешить вывод частичных уникальных индексов
В заниженной форме документы говорят, что при использовании частичного индекса и обновлении с помощью ON CONFLICT, index_predicate должен быть указан. Это не предполагается для вас. Я узнал об этом здесь, и следующий пример демонстрирует это.
CREATE TABLE test.accounts (
id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
type text,
person_id int);
CREATE UNIQUE INDEX accounts_note_idx on accounts (type, person_id) WHERE ((type)::text = 'PersonAccount'::text);
INSERT INTO test.accounts (type, person_id) VALUES ('PersonAccount', 10);
так что у нас есть:
unutbu=# select * from test.accounts;
+----+---------------+-----------+
| id | type | person_id |
+----+---------------+-----------+
| 1 | PersonAccount | 10 |
+----+---------------+-----------+
(1 row)
Без index_predicate
получаем ошибку:
INSERT INTO test.accounts (type, person_id) VALUES ('PersonAccount', 10) ON CONFLICT (type, person_id) DO NOTHING;
-- ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification
Но если вместо этого вы включите index_predicate, WHERE ((type)::text = 'PersonAccount'::text)
:
INSERT INTO test.accounts (type, person_id) VALUES ('PersonAccount', 10)
ON CONFLICT (type, person_id)
WHERE ((type)::text = 'PersonAccount'::text) DO NOTHING;
тогда ошибки нет, и соблюдается НИЧЕГО.
- Ты потрясающий <3
У меня не было возможности поиграть с UPSERT, но думаю, у вас есть кейс из документации:
Обратите внимание, что это означает, что будет выведен неполный уникальный индекс (уникальный индекс без предиката) (и, следовательно, он будет использоваться ON CONFLICT), если такой индекс, удовлетворяющий всем остальным критериям, доступен. Если попытка вывода не удалась, возникает ошибка.
- 1 Я видел это и в документации, но, судя по моей интерпретации, если он не может вывести индекс, он всегда должен этого не делать, а не только иногда. Кроме того, это определяет поведение для неполных уникальных индексов, которые не создаются. В моем случае я использую частичный уникальный индекс.
Tweet
Share
Link
Plus
Send
Send
Pin
I я пытаюсь выполнить операцию upsert для таблицы с именем «message_payload», используя приведенный ниже запрос, но получаю сообщение об ошибке:
ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification
SQL state: 42P10
Запрос:
INSERT INTO public.error_message(
id, created_by, created_dt, modified_by, modified_dt, attempt, message_headers,
message_main_topic, message_payload, message_status, message_status_code)
VALUES (51, null, null, null, null, 1, '{
"jsonContent": {
"content-length": "1635",
"message_status_code": "417",
"cookie": "JSESSIONID=279AF4C174E6192BDAB11A067768BBD5",
"postman-token": "f0f33e86-498f-452a-aaf6-18eb84dc5907",
"kafka_timestampType": "CREATE_TIME",
"message_id": "21",
"kafka_receivedMessageKey": "null",
"kafka_receivedTopic": "error-topic",
"accept": "*/*",
"kafka_offset": "33",
"kafka_consumer": "org.apache.kafka.clients.consumer.KafkaConsumer@5091bb5f",
"host": "localhost:8082",
"content-type": "application/json",
"connection": "keep-alive",
"cache-control": "no-cache",
"kafka_receivedPartitionId": "0",
"kafka_receivedTimestamp": "1552305428711",
"accept-encoding": "gzip, deflate",
"message_main_topic": "ldarsQCustomStatistics.1",
"user-agent": "PostmanRuntime/7.6.1"
}
}', 'ldarsQCustomStatistics.1', '{
"jsonContent": {
"messageTime": 16772223422,
"messageRev": 9,
"businessId": "DB",
"messageId": "55"
}
}', 1, 201)
ON CONFLICT ((message_payload->'jsonContent'->>'message_id'))
DO UPDATE SET attempt = error_message.attempt + 1, message_headers = EXCLUDED.message_headers,
message_status_code = EXCLUDED.message_status_code, message_status = EXCLUDED.message_status,
created_by = EXCLUDED.created_by, created_dt = EXCLUDED.created_dt,
modified_by = EXCLUDED.modified_by, modified_dt = EXCLUDED.modified_dt,
message_main_topic = EXCLUDED.message_main_topic, message_payload = EXCLUDED.message_payload,
id = DEFAULT
Fedoor3 0 / 0 / 0 Регистрация: 08.10.2019 Сообщений: 13 |
||||||||
1 |
||||||||
03.09.2021, 12:51. Показов 1283. Ответов 1 Метки нет (Все метки)
Как можно сделать upsert по совпадению по нескольким столбцам?
что то вроде этого
так возникает ошибка «нет уникального ограничения или ограничения-исключения, соответствующего указанию ON CONFLICT»
__________________ 0 |
1184 / 914 / 367 Регистрация: 02.09.2012 Сообщений: 2,785 |
|
06.09.2021, 21:27 |
2 |
Но поля нельзя сделать уникальными, так как из них может быть несколько вариантов Как тогда определить конфликтные поля?? В этом суть. Обратите внимание, что в синтаксисе команде указано Код where conflict_target can be one of: ( { index_column_name | ( index_expression ) } [ COLLATE collation ] [ opclass ] [, ...] ) [ WHERE index_predicate ] ON CONSTRAINT constraint_name 0 |
IT_Exp Эксперт 87844 / 49110 / 22898 Регистрация: 17.06.2006 Сообщений: 92,604 |
06.09.2021, 21:27 |
Помогаю со студенческими работами здесь JS conflict Save Conflict 409 Conflict Merge Conflict Documents Conflict — PCI in slot ON CONFLICT sequence обновляется Столкнулся с проблемой, дело в том, что при вставке записи в таблицу бд… PostgreSQL: on conflict/on constraint Искать еще темы с ответами Или воспользуйтесь поиском по форуму: 2 |