postgres - where in (list) - column does not exist

Question

I'm coming from SQL Server and I was suprised to see that the following query does not work:

DELETE FROM user_job_titles WHERE id IN (
"c836d018-1d12-4507-a268-a4d80d6d3f54",
"d0961a90-7d31-4c4c-9c1b-671115e3d833",
"62dda420-6e62-4017-b41d-205c0aa82ead"
)

where user_job_titles has the following columns:

id
user_id
job_title_id

The error is:

ERROR:  column "c836d018-1d12-4507-a268-a4d80d6d3f54" does not exist
LINE 2: "c836d018-1d12-4507-a268-a4d80d6d3f54"

I'm using pgAdmin with latest postgresql version. Is there any other way to run this query?

SQL uses single quotes for string constants: postgresql.org/docs/current/static/… — user330315
– user330315, Commented Mar 5, 2016 at 13:32

Gordon Linoff · Accepted Answer · 2016-03-05 13:32:46Z

18

Use single quotes for string constants:

DELETE FROM user_job_titles
    WHERE id IN ('c836d018-1d12-4507-a268-a4d80d6d3f54',
                 'd0961a90-7d31-4c4c-9c1b-671115e3d833',
                 '62dda420-6e62-4017-b41d-205c0aa82ead'
                );

Double quotes are an escape character used with table and column names. Hence the error.

answered Mar 5, 2016 at 13:32

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

java-addict301 Over a year ago

wow, this was super annoying. Error is clear as mud.

Lukasz Szozda · Accepted Answer · 2016-03-05 13:37:43Z

7

You need to quote string literals with '

DELETE FROM user_job_titles 
WHERE id IN (
'c836d018-1d12-4507-a268-a4d80d6d3f54',
'd0961a90-7d31-4c4c-9c1b-671115e3d833',
'62dda420-6e62-4017-b41d-205c0aa82ead'
);

I'm coming from SQL Server and I was suprised to see that the following query does not work

Then you have SET QUOTED_IDENTIFIER AS OFF. By default it is ON.

When SET QUOTED_IDENTIFIER is ON, all strings delimited by double quotation marks are interpreted as object identifiers.

Check:

SET QUOTED_IDENTIFIER OFF;
SELECT "A"


-- The same behaviour as in Postgresql
SET QUOTED_IDENTIFIER ON;
SELECT "A"
-- Invalid column name 'A'.

LiveDemo

edited Mar 5, 2016 at 13:37

answered Mar 5, 2016 at 13:32

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

Collectives™ on Stack Overflow

postgres - where in (list) - column does not exist

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related