UPSERT Postgres 9.5 [英] UPSERT Postgres 9.5

问题描述

我在CentOS 6上使用Postgres 9.5。
我有下表（DDL）。

 创建表core_table.user_list（
 user_id SMALLSERIAL默认值nextval（'core_table.user_list_user_id_seq':: regclass）NOT NULL，
 user_name VARCHAR（50）NOT NULL，
 full_name VARCHAR（255），
 role_id INTEGER DEFAULT 1，
 is_accessible INTEGER DEFAULT 1，
备注VARCHAR（255），
密码VARCHAR（50），
 customer_schema VARCHAR（50），
 core_schema VARCHAR（50），
 CONSTRAINT user_list_pkey主键（user_id，user_name），
 CONSTRAINT user_list_user_id_key UNIQUE（user_id）
）
 WITH（oids = false）; 
  

具有4个现有数据行。

 行1：
用户名：1​​ 
用户名：kelly 
全名：kelly petz 
 role_id：1 
 is_accessible：1 
备注：< null> 
密码：test123 
 customer_schema：kelly_db1 
 core_schema：kelly_db1 
 
第2行：
 user_id：2 
用户名：kelly 
全名：kelly petz 
 role_id：1 
 is_accessible：1 
备注：< null> 
密码：test123 
 customer_schema：petz_db1 
 core_schema：kelly_db1 
 
第3行：
 user_id：3 
用户名：sam 
 full_name：sam howiz 
 role_id：1 
 is_accessible：1 
备注：< null> 
密码：test456 
 customer_schema：kelly_db1 
 core_schema：kelly_db1 
 
第4行：
 user_id：4 
用户名：jon 
 full_name：jon lam 
 role_id：1 
 is_accessible：1 
备注：< null> 
密码：test789 
 customer_schema：lam_db1 
 core_schema：lam_db1 
  

尝试执行以下UPSERT时遇到错误。

  INSERT INTO core_table.user_list 
（用户名，全名，备注，密码，customer_schema，core_schema，role_id，is_accessible）
值
（'kelly'，'kelly petz'，'The Boss'，'test123'，'snoppy_db1'，'snoppy_db1'，1 ，1）
冲突（用户名）
做更新集
密码='test123'，
备注='老板'; 
  

错误是错误：没有符合ON CONFLICT规范的唯一或排除约束。 / p>

有人可以为我指出我的错误以及如何解决吗？

解决方案

如果您对用户名列没有任何限制，则不会发生冲突，发生冲突（用户名）没有任何意义。

您需要在 user_name 列上使用某种唯一性约束。 / p>

例如

 创建唯一索引user_list上的user_list_name_index（用户名）； 
  

编辑以下评论：

就像您想要此表架构一样：

 创建表core_table.user_list（
 user_id SMALLSERIAL DEFAULT nextval（'core_table.user_list_user_id_seq ':: regclass）NOT NULL，
 user_name VARCHAR（50）NOT NULL，
 full_name VARCHAR（255），
 role_id INTEGER DEFAULT 1，
 is_accessible INTEGER DEFAULT 1，
备注VARCHAR（255），
密码VARCHAR（50），
 customer_schema VARCHAR（50），
 core_schema VARCHAR（50），
 CONSTRAINT user_list_pkey主键（user_id，user_name ），
 CONSTRAINT user_list_user_id_key UNIQUE（user_id），
 CONSTRAINT user_list_name_schema主键（user_name，customer_schema），
）
 WITH（oids = false）; 
  

，然后出现此发生冲突的情况子句：

 插入到core_table.user_list 
（用户名，全名，备注，密码，customer_schema，core_schema，role_id和is_accessible）
值
（'kelly'，'kelly petz'，'The Boss'，'test123'，'snoppy_db1'，'snoppy_db1'，1、1）
发生冲突（user_list_name_schema）
做更新集
 password ='test123'，
备注='The Boss'; 
  

I am using Postgres 9.5 on CentOS 6. I have the following table (DDL).

CREATE TABLE core_table.user_list(
  user_id SMALLSERIAL DEFAULT nextval('core_table.user_list_user_id_seq'::regclass) NOT NULL,
  user_name VARCHAR(50) NOT NULL,
  full_name VARCHAR(255),
  role_id INTEGER DEFAULT 1,
  is_accessible INTEGER DEFAULT 1,
  remarks VARCHAR(255),
  password VARCHAR(50),
  customer_schema VARCHAR(50),
  core_schema VARCHAR(50),
  CONSTRAINT user_list_pkey PRIMARY KEY(user_id, user_name),
  CONSTRAINT user_list_user_id_key UNIQUE(user_id)
) 
WITH (oids = false);

with 4 existing rows of data.

Row 1:
user_id: 1
user_name: kelly
full_name: kelly petz
role_id: 1
is_accessible: 1
remarks: <null>
password: test123
customer_schema: kelly_db1
core_schema: kelly_db1

Row 2:
user_id: 2
user_name: kelly
full_name: kelly petz
role_id: 1
is_accessible: 1
remarks: <null>
password: test123
customer_schema: petz_db1
core_schema: kelly_db1

Row 3:
user_id: 3
user_name: sam
full_name: sam howiz
role_id: 1
is_accessible: 1
remarks: <null>
password: test456
customer_schema: kelly_db1
core_schema: kelly_db1

Row 4:
user_id: 4
user_name: jon
full_name: jon lam
role_id: 1
is_accessible: 1
remarks: <null>
password: test789
customer_schema: lam_db1
core_schema: lam_db1

I hit an error when I tried to do the following UPSERT.

INSERT INTO core_table.user_list 
(user_name, full_name, remarks, password, customer_schema, core_schema, role_id, is_accessible) 
VALUES 
('kelly', 'kelly petz', 'The Boss', 'test123', 'snoppy_db1', 'snoppy_db1', 1, 1) 
ON CONFLICT (user_name) 
DO UPDATE SET 
    password = 'test123', 
    remarks = 'The Boss';

The error is "ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification".

Can someone please point out my error for me and how to resolve it?

解决方案

If you don't have any constraint on the user_name column, no conflict can happen, the on conflict (user_name) makes no sense.

You need some kind of unicity constraint here on the user_name column.

For example

CREATE UNIQUE INDEX user_list_name_index on  user_list (user_name);

EDIT following comment:

It looks like you want this table schema:

CREATE TABLE core_table.user_list(
  user_id SMALLSERIAL DEFAULT nextval('core_table.user_list_user_id_seq'::regclass) NOT NULL,
  user_name VARCHAR(50) NOT NULL,
  full_name VARCHAR(255),
  role_id INTEGER DEFAULT 1,
  is_accessible INTEGER DEFAULT 1,
  remarks VARCHAR(255),
  password VARCHAR(50),
  customer_schema VARCHAR(50),
  core_schema VARCHAR(50),
  CONSTRAINT user_list_pkey PRIMARY KEY(user_id, user_name),
  CONSTRAINT user_list_user_id_key UNIQUE(user_id),
  CONSTRAINT user_list_name_schema PRIMARY KEY(user_name, customer_schema),
) 
WITH (oids = false);

and then this on conflict clause:

INSERT INTO core_table.user_list 
(user_name, full_name, remarks, password, customer_schema, core_schema, role_id, is_accessible) 
VALUES 
('kelly', 'kelly petz', 'The Boss', 'test123', 'snoppy_db1', 'snoppy_db1', 1, 1) 
ON CONFLICT (user_list_name_schema) 
DO UPDATE SET 
    password = 'test123', 
    remarks = 'The Boss';

这篇关于UPSERT Postgres 9.5的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！