'What is the difference between " and ' in PostgreSQL?

When should I use "? And when should I use ' in PostgreSQL?

CREATE TABLE "user" (
  id SERIAL NOT NULL,
  first_name varchar(50) NOT NULL,
  last_name varchar(50) NOT NULL,
  email varchar(50) NOT NULL
) ;



INSERT INTO "user" (id, first_name, last_name, email) VALUES
(1, 'website', 'admin', '[email protected]')

Why the values need to use ' , the table name should use " and columns don't have to use any quotation? I don't get it.

sqlpostgresql


Solution 1:[1]

In SQL, the double quotes are used to quote Identifiers and Keywords that are otherwise illegal identifiers. user is a keyword and a built-in function. So you need to use double quotes to escape that. Quoted identifiers are case sensitive (as defined by the SQL standard). So "user" and "User" are two different names.

In general it's better to avoid identifiers that need quoting, thus you never have the need to use double quotes with your identifiers. You could e.g. use user_account instead of user.

Single quotes are used in SQL to enclose string constants

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 a_horse_with_no_name

Related Questions