CREATE USER test_user WITH PASSWORD 'test_password'

Password value needs single quotes, username doesn't. I just wonder why this is so? Both look like input parameters (strings) but are treated differently.

CREATE USER test_user WITH PASSWORD 'test_password'

Password value needs single quotes, username doesn't. I just wonder why this is so? Both look like input parameters (strings) but are treated differently.

• sql
• postgresql

• 3 While the password is a string literal, the user name is an identifier and it doesn't necessarily need to be wrapped in quotes as mentioned here postgresql./docs/7.1/user-manag.html. – Ali Khan Commented Nov 17, 2024 at 13:22
• 1 From the docs: name follows the rules for SQL identifiers: either unadorned without special characters, or double-quoted. – Ali Khan Commented Nov 17, 2024 at 13:24
• 1 @AliKhan Please link to the "current" version, not one that has been EOL for almost 2 decades... postgresql./docs/current/user-manag.html – Frank Heikens Commented Nov 17, 2024 at 20:11

1 Answer 1

The username is an identifier, which is either an unquoted, simple identifier, or a quoted identifier a.k.a. delimited identifier (which are enclosed in double quotes "). In other words, a username is similar to a table name, column name, etc.

A password however, is a string, and must be provided as a string literal (a.k.a. string constants in PostgreSQL documentation), and as such must be enclosed by single quotes ' or dollar quotes $$.