Toad World Blog

Postgres Keywords

Jul 12, 2017 7:41:14 PM by Bruce Momjian

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.

Tags: Postgres

Bruce Momjian

Written by Bruce Momjian

Bruce Momjian is a co-founder of the PostgreSQL Global Development Group, and has worked on PostgreSQL since 1996 as a committer and community leader.

He is a frequent speaker and Postgres evangelist and travels worldwide appearing at conferences to help educate the community on the business value of Postgres advances and new technology enhancements.

He is the author of PostgreSQL: Introduction and Concepts, published by Addison-Wesley.