Column ’x’ does not exist error for string literal ’x’ in PostgreSQL
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)
.
postgres column X does not exist
Use single quotes
for string constants
INSERT INTO config_change_log(last_config_version, is_done, change_description )
VALUES('5837-2016-08-24_09-12-22', false, '{ ''key'':''value''}');
Also you can escape single quotes
in data by doubling them
- SQL FIDDLE DEMO
Getting column does not exist error in postgresql sql table
You need to use single quote
instead of double quote
SELECT * FROM test where source = 'aaamt'
PSQL: Error: column x does not exist
Try phrasing this with single quotes:
INSERT INTO movies (title, year, description, posterUrl, screenshotUrl)
VALUES ('Black Panther', 2018, 'After the death of his father, T''Challa returns home to...', 'https://ia.media-imdb.com/images/M/MV5BMTg1MTY2MjYzNV5BMl5BanBnXkFtZTgwMTc4NTMwNDI@._V1_UX182_CR0,0,182,268_AL_.jpg', 'http://cdn.wegotthiscovered.com/wp-content/uploads/2017/10/black-panther-movie-image-9.jpg');
To escape a single quote in SQL, use two single quotes in a row.
PostgreSQL Column does not exist but it actually does
Try to take it into double quotes - like "Continent"
in the query:
SELECT "Continent"
FROM network.countries
...
PostgreSQL column exists does not exist
The explanation for the confusing error can be found in the manual for pl/pgsql Expressions, which explains:
When you write a PL/pgSQL statement
IF expression THEN ...
PL/pgSQL will evaluate the expression by feeding a query likeSELECT expression
to the main SQL engine
So in your case, the expression is being translated to SELECT EXISTS _colvar
, which looks to the query parser like a column named "EXISTS" being given an alias _colvar".
To fix it, you need something that would be valid in a select list. For instance:
IF _colvar IS NOT NULL ...
PostgreSQL column 'foo' does not exist
You accidentally created the column name with a trailing space and presumably phpPGadmin created the column name with double quotes around it:
create table your_table (
"foo " -- ...
)
That would give you a column that looked like it was called foo
everywhere but you'd have to double quote it and include the space whenever you use it:
select ... from your_table where "foo " is not null
The best practice is to use lower case unquoted column names with PostgreSQL. There should be a setting in phpPGadmin somewhere that will tell it to not quote identifiers (such as table and column names) but alas, I don't use phpPGadmin so I don't where that setting is (or even if it exists).
Related Topics
Log Record Changes in SQL Server in an Audit Table
Linq Version of SQL "In" Statement
Jpql in Clause: Java-Arrays (Or Lists, Sets...)
How to Use Structural Annotations to Set SQL Type to Date in Model First Approach
How to Delete from Select in MySQL
Basic Recursive Query on SQLite3
SQL Grouping by Month and Year
What Is the Null Character Literal in Tsql
Partition Function Count() Over Possible Using Distinct
Finding Similar Strings with Postgresql Quickly
How to Prevent a Database Trigger from Recursing
Does Ms SQL Server's "Between" Include the Range Boundaries