"Column ’x’ does not exist" error for string literal ’x’ in PostgreSQL

Question

Possible Duplicate:
Column ‘mary’ does not exist

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’));

Here is the error I get:

 ERROR:  column "’verkehrsunfall’" does not exist

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.

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?

Here is the example from the lecture:

CREATE TABLE Professoren 
(PersNr INTEGER PRIMARYKEY,
 Name VARCHAR( 3 0 ) NOT NULL ,
 Rang CHAR(2) CHECK (Rang in ('C2' ,'C3' ,'C4')) , 
 Raum INTEGER UNIQUE) ;

Is this (http://stackoverflow.com/q/13196572/398670) from the same course material perhaps? The timing makes it seem like it could be some lecturer who edited SQL in a word processor and handed it out as PDF or web page notes that students have copied and pasted... — Craig Ringer, Nov 04 '12 at 14:08
The syntax is correct if you use the correct single quotes: http://www.sqlfiddle.com/#!12/fb4ec — , Nov 04 '12 at 14:14
Just a FYI your naming convention is not good. Either use under scores such as first_name or if you want to go what the less ideal route you can do "FirstName". Don't mix and match capitals and underscores. This is general to naming, but specific to postgres I would stick with under scores to separate words and don't use capital letters. — Kuberchaun, Nov 04 '12 at 17:30

Craig Ringer · Accepted Answer · 2012-11-04T22:44:53.867

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.

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.

Corrected example:

CREATE TABLE Professoren 
(PersNr INTEGER PRIMARYKEY,
 Name VARCHAR(30) NOT NULL,
 Rang CHAR(2) CHECK (Rang in ('C2' ,'C3' ,'C4')), 
 Raum INTEGER UNIQUE);

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)

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.

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';

... 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)

You also have an unwanted space in the varchar typmod; varchar( 3 0 ) is invalid:

regress=> SELECT 'x'::varchar( 3 0 );
ERROR:  syntax error at or near "0"
LINE 1: SELECT 'x'::varchar( 3 0 );

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).

Thanks a lot, such a small detail I wouldn't realize that they correspond to 2 different ASCII codes. — Lucius Rutilius Lupus, Nov 04 '12 at 15:24
@LuciusRutiliusLupus Just a nit-pick: they aren't both actually ASCII. A single quote (apostrophe, really) is, but `’` is `\u2019` which is not in the ASCII range. They're different character codes or unicode code-points, for sure. — Craig Ringer, Nov 04 '12 at 22:47

"Column ’x’ does not exist" error for string literal ’x’ in PostgreSQL

1 Answers1

Linked