“列'x'不存在”错误的字符串文字'x'在PostgreSQL [英] "Column ’x’ does not exist" error for string literal ’x’ in PostgreSQL
问题描述
可能重复：
列'mary'不存在
通过检查约束检查可接受到列的值。我需要使用检查约束，因为这是一个大学作业。
I need to check the values that can be accepted to a column through a check constraint. I need to use the check constraint, because this is for a college assignment.
我使用这个代码创建和添加约束到表。
I use this code to create and add the constraint to the table.
CREATE TABLE Ereignis(
E_Id Serial PRIMARY KEY,
Typ varchar(15),
Zeitpunkt timestamp,
Ort varchar(32),
Anzahl_Pers int
);
ALTER TABLE Ereignis ADD
CONSTRAINT typ_ch CHECK (Typ in (’Verkehrsunfall’, ’Hochwasser’, ’Sonstiges’));
这是我得到的错误：
ERROR: column "’verkehrsunfall’" does not exist
当我从这个错误，它试图比较列的列verkehrsunfall，其中我尝试检查列try可以获得的值是（'Verkehrsunfall'，'Hochwasser'，'Sonstiges'）字符串。
As I get from this error it tries to compare column typ with column verkehrsunfall, where as I try to check the values that column try can get is one of the (’Verkehrsunfall’, ’Hochwasser’, ’Sonstiges’) strings.
这跟我们讲师在讲座上讲的完全一样。我不知道如果可以比较varchars与检查？
This is exactly the same syntax what our lecturer showed us at the lecture. I am not sure if it is possible to compare varchars with check? Or what am I doing wrong?
以下是演讲中的示例：
CREATE TABLE Professoren
(PersNr INTEGER PRIMARYKEY,
Name VARCHAR( 3 0 ) NOT NULL ,
Rang CHAR(2) CHECK (Rang in ('C2' ,'C3' ,'C4')) ,
Raum INTEGER UNIQUE) ;
推荐答案
您的文本编辑器或文字处理器正在使用所谓智能引用，像'，而不是普通单引号，如'。对于标识符使用普通单引号（实际是撇号）'或对于标识符使用双引号逗号，这可能会导致语法错误。请参阅 PostgreSQL手册SQL语法，具体说明词法结构。
Your text editor or word processor is using so-called smart quotes, like ’, not ordinary single quotes, like '. Use ordinary single quotes (actually apostrophes) ' for literals, or double quotes " for identifiers. You also have some odd commas in there which may cause syntax errors. See the PostgreSQL manual on SQL syntax, specicifically lexical structure.
不要在字处理程序中编辑SQL（或任何其他源代码）像像记事本++
Don't edit SQL (or any other source code) in a word processor. A decent text editor like Notepad++, BBEdit, vim, etc, won't mangle your SQL like this.
更正的示例：
CREATE TABLE Professoren
(PersNr INTEGER PRIMARYKEY,
Name VARCHAR(30) NOT NULL,
Rang CHAR(2) CHECK (Rang in ('C2' ,'C3' ,'C4')),
Raum INTEGER UNIQUE);
因为它不会导致一个完全的语法错误 - 而是给你一个奇怪的错误消息关于列不存在 - 是因为PostgreSQL接受unicode列名称，并认为' character是标识符的完全有效的字符。观察：
The reason it doesn't cause an outright syntax error - and instead gives you an odd error message about the column not existing - is because PostgreSQL accepts unicode column names and considers the ’ character a perfectly valid character for an identifier. Observe:
regress=> SELECT 'dummy text' AS won’t, 'dummy2' as ’alias’;
won’t | ’alias’
------------+---------
dummy text | dummy2
(1 row)
因此，如果你有一个名为 test ，并且要求名为'test'的列，PostgreSQL将正确地告诉您没有名为'test'。在你的情况下，你想要使用文本字符串 Verkehrsunfall 时，要求一个名为'verkehrsunfall' ，因此会显示错误消息，指出'verkehrsunfall'不退出。
Thus, if you have a column named test and you ask for the column named ’test’, PostgreSQL will correctly tell you that there is no column named ’test’. In your case you're asking for a column named ’verkehrsunfall’ when you meant to use the literal string Verkehrsunfall instead, hence the error message saying that the column ’verkehrsunfall’ does not exit.
报价语法无效。第一个不会在psql中执行，因为它有一个未封闭的单引号;第二个会失败，例如：
If it were a real single quote that'd be invalid syntax. The 1st wouldn't execute in psql at all because it'd have an unclosed single quote; the 2nd would fail with something like:
regress=> SELECT 'dummy2' as 'alias';
ERROR: syntax error at or near "'alias'"
LINE 1: SELECT 'dummy2' as 'alias';
...因为在ANSI SQL中，试图使用文字作为标识符。正确的语法将使用标识符的双引号或根本没有引号：
... because in ANSI SQL, that's trying to use a literal as an identifier. The correct syntax would be with double-quotes for the identifier or no quotes at all:
regress=> SELECT 'dummy2' as "alias", 'dummy3' AS alias;
alias | alias
--------+--------
dummy2 | dummy3
(1 row)
也在varchar中有一个不需要的空间typmod;
varchar（3 0）无效：
regress=> SELECT 'x'::varchar( 3 0 );
ERROR: syntax error at or near "0"
LINE 1: SELECT 'x'::varchar( 3 0 );
BTW，在PostgreSQL中，通常最好使用文本 column而不是varchar。如果您想要应用程序或验证原因的长度约束，请在 length（colname）上添加检查约束。
BTW, in PostgreSQL it's usually better to use a text column instead of varchar. If you want a length constraint for application or validation reasons, add a check constraint on length(colname).
这篇关于“列'x'不存在”错误的字符串文字'x'在PostgreSQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
