Postgres Keywords

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 function pg_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.