PostgreSQL | PostgreSQL Basic Syntax | Identifiers and Keywords

Tags:

This page explains identifiers and keywords in PostgreSQL.

Identifiers and Keywords

Consider the following SQL command as an example.

SELECT * FROM MYTBL;

Words that have meaning in SQL, such as SELECT and FROM, are called keywords, and MYTBL, which is used as a table name, is called an identifier.

Keywords cannot be used as identifiers unless they are quoted. For example, if you try to create a table using the keyword SELECT as the table name, an error occurs.

postgres=# create table select (id integer);
ERROR:  syntax error at or near "select"
LINE 1: create table select (id integer);
                     ^
postgres=#

Identifiers and keywords are case-insensitive except for quoted identifiers. Therefore, the following two SQL commands have the same meaning.

SELECT * FROM MYTBL;
select * from mytbl;

Identifiers and keywords must begin with a letter or underscore (_) as the first character. From the second character onward, letters, underscores, digits, and dollar signs ($) can be used. However, some databases do not allow dollar signs in identifiers, so it is better not to use them often.

Quoted Identifiers

Words that have meaning in SQL, such as SELECT and UPDATE, are registered in PostgreSQL as keywords. Keywords basically cannot be used as identifiers. However, by quoting keywords and values that include spaces, which normally cannot be used, you can use them as identifiers for database names and table names.

To use a quoted identifier, enclose the string in double quotes ("). For example, to create a table using the keyword SELECT as the table name, use a quoted identifier as follows.

postgres=# create table "select"(id integer);
CREATE TABLE
postgres=#

This time, no error occurred and the table was created. The difference from the previous example is that the table name was written as "select" instead of select.

A quoted identifier can include any character. To use a double quote itself as a character, write two double quotes ("").

List of Keywords Registered in PostgreSQL

The keywords registered in PostgreSQL are as follows.

ALL
ANALYSE
ANALYZE
AND
ANY
ARRAY
AS
ASC
ASYMMETRIC
AUTHORIZATION (can be used as a function or type)
BINARY (can be used as a function or type)
BOTH
CASE
CAST
CHECK
COLLATE
COLLATION (can be used as a function or type)
COLUMN
CONCURRENTLY (can be used as a function or type)
CONSTRAINT
CREATE
CROSS (can be used as a function or type)
CURRENT_CATALOG
CURRENT_DATE
CURRENT_ROLE
CURRENT_SCHEMA (can be used as a function or type)
CURRENT_TIME
CURRENT_TIMESTAMP
CURRENT_USER
DEFAULT
DEFERRABLE
DESC
DISTINCT
DO
ELSE
END
EXCEPT
FALSE
FETCH
FOR
FOREIGN
FREEZE (can be used as a function or type)
FROM
FULL (can be used as a function or type)
GLOBAL
GRANT
GROUP
HAVING
ILIKE (can be used as a function or type)
IN
INLINE
INNER (can be used as a function or type)
INTERSECT
INTO
IS (can be used as a function or type)
ISNULL (can be used as a function or type)
LATERAL
LEFT (can be used as a function or type)
LIKE (can be used as a function or type)
LIMIT
LOCALTIME
LOCALTIMESTAMP
NATURAL (can be used as a function or type)
NOT
NOTNULL (can be used as a function or type)
NULL
OFFSET
ON
ONLY
OR
ORDER
OUTER (can be used as a function or type)
OVERLAPS (can be used as a function or type)
PLACING
PRIMARY
REFERENCES
RETURNING
RIGHT (can be used as a function or type)
SELECT
SESSION_USER
SIMILAR (can be used as a function or type)
SOME
SYMMETRIC
TABLE
TABLESAMPLE (can be used as a function or type)
THEN
TO
TRAILING
TRUE
UNION
UNIQUE
USER
USING
VARIADIC
VERBOSE (can be used as a function or type)
WHEN
WHERE
WINDOW
WITH

–

This page explained identifiers and keywords in PostgreSQL.