You might be aware that the sql standard reserves certain identifiers that cannot be used for naming user objects. Postgres follows that standard, with slight modifications. For example, you cannot create a table called all:
CREATE TABLE all (x INTEGER);
ERROR: syntax error at or near "all"
LINE 1: CREATE TABLE all (x INTEGER);
^
It is actually very easy to find what identifiers are reserved because they are listed in the documentation. More interestingly, they are also accessible via the sql functionpg_get_keywords():
SELECT *
FROM pg_get_keywords()
ORDER BY 1;
word | catcode | catdesc
-------------------+---------+----------------------------------------------
abort | U | unreserved
absolute | U | unreserved
access | U | unreserved
action | U | unreserved
…
You can also do statistical analysis on it:
SELECT catcode, COUNT(*)
FROM pg_get_keywords()
GROUP BY catcode
ORDER BY 2 DESC;
catcode | count
---------+-------
U | 276
R | 77
C | 48
T | 23
You can even join pg_get_keywords() to system tables to identify the use of system keywords by user objects, e.g. a check for column names:
SELECT nspname, relname, attname
FROM pg_namespace JOIN pg_class ON (pg_namespace.oid = relnamespace)
JOIN pg_attribute ON (attrelid = pg_class.oid)
JOIN pg_get_keywords() ON (word = attname)
WHERE nspname NOT IN ('pg_catalog', 'information_schema')
Read the documentation to understand the types of system keywords, i.e., using them is not always a problem.
Bruce Momjian is a Senior Database Architect at EnterpriseDB.
This post originally appeared on Bruce's personal blog.