PostgreSQL ERROR 列不存在是指一个列值

问题描述

我在 postgreSQL 中有一个表 projects，如下所示

I have a table projects in postgreSQL that looks like below

id.    project_name.   col3.  col4
1111.   test.          ...    ...

仅当 id 不存在时，我才想为新的 id 插入新行.所以我写了下面的查询

I want to insert a new row for a new id only if the id does not already exist. So I wrote below query

INSERT INTO projects(id, project_name)
VALUES("1234", "Test_Project")
ON CONFLICT (id)
DO NOTHING

但它给我一个错误

Query 1 ERROR: ERROR:  column "1234" does not exist
LINE 2: VALUES("1234", "Test_Project_...

请提出建议.

** 编辑**

表中的 id 列是一个 uuid 并且不是唯一的.可以有多个具有相同 ID 的行.根据 GMB 的建议，我尝试了以下

The id column in the table is a uuid and not unique. There can be multiple rows with the same id. Based on GMBs suggestion, I tried below

INSERT INTO projects(id, project_name)
    VALUES('1234', 'Test_Project')
    ON CONFLICT (create unique index on projects(id))
    DO NOTHING

我收到以下错误

Query 1 ERROR: ERROR:  syntax error at or near "create"
LINE 3: ON CONFLICT (create unique index on projects(id...

我是新手，所以我肯定会遗漏一些明显的东西.

I am new to this so I am sure missing something obvious.

推荐答案

对文字字符串使用单引号.双引号代表标识符(例如列名或表名)——因此你会得到错误:

Use single quotes for literal strings. Double quotes stand for identifiers (such as column names or table names) - hence the error that you are getting:

INSERT INTO projects(id, project_name)
VALUES('1234', 'Test_Project')
ON CONFLICT (id)
DO NOTHING

也就是说，我怀疑 id 是 integer 数据类型.如果是这样，根本不要引用它:

That said, I would suspect that id is of integer datatype. If so, don't quote it at all:

INSERT INTO projects(id, project_name)
VALUES(1234, 'Test_Project')
ON CONFLICT (id)
DO NOTHING

这篇关于PostgreSQL ERROR 列不存在是指一个列值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！