CODEWITHSUNDEEP
postgresqlstring-constant
Steven Kristian
Steven Kristian

Reputation: 122

Basic DELETE commands in PostgreSQL detecting value as column name

I'm trying to delete a row at PostgreSQL using pgAdmin4. Here is my command:

DELETE FROM commissions_user 
WHERE first_name = "Steven";

For some reason, the error states that

ERROR:  column "Steven" does not exist
LINE 2: WHERE first_name = "Steven";
                           ^
SQL state: 42703
Character: 50

It's weird, why is "Steven" detected as a column name, shouldn't the column name be first_name?

Upvotes: 0

Views: 87

Answers (2)

jian
jian

Reputation: 4824

https://www.postgresql.org/docs/current/sql-syntax-lexical.html
Double quote:

A convention often used is to write key words in upper case and names in lower case, e.g.:

UPDATE my_table SET a = 5;

There is a second kind of identifier: the delimited identifier or quoted identifier. It is formed by enclosing an arbitrary sequence of characters in double-quotes ("). A delimited identifier is always an identifier, never a key word. So "select" could be used to refer to a column or table named “select”, whereas an unquoted select would be taken as a key word and would therefore provoke a parse error when used where a table or column name is expected. The example can be written with quoted identifiers like this:
UPDATE "my_table" SET "a" = 5;

Single Quote:
https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-STRINGS

A string constant in SQL is an arbitrary sequence of characters bounded by single quotes ('), for example 'This is a string'. To include a single-quote character within a string constant, write two adjacent single quotes, e.g., 'Dianne''s horse'. Note that this is not the same as a double-quote character (")

Upvotes: 1

Noob Life
Noob Life

Reputation: 580

Use single quotes instead

DELETE FROM commissions_user 
WHERE first_name = 'Steven';

Double quotes can be used table and column, and single quotes can be used for strings.

ex.

DELETE FROM "commissions_user"
WHERE "first_name" = 'Steven';

Upvotes: 3

Related Questions