Chapter 2. SQL Syntax

A description of the general syntax of SQL.

SQL manipulates sets of data. The language is composed of various key words. Arithmetic and procedural expressions are allowed. We will cover these topics in this chapter; subsequent chapters will include details on data types, functions, and operators.

Key Words

SQL92 defines key words for the language which have specific meaning. Some key words are reserved, which indicates that they are restricted to appear in only certain contexts. Other key words are not restricted, which indicates that in certain contexts they have a specific meaning but are not otherwise constrained.

Postgres implements an extended subset of the SQL92 and SQL3 languages. Some language elements are not as restricted in this implementation as is called for in the language standards, in part due to the extensibility features of Postgres.

Information on SQL92 and SQL3 key words is derived from Date and Darwen, 1997.

Reserved Key Words

SQL92 and SQL3 have reserved key words which are not allowed as identifiers and not allowed in any usage other than as fundamental tokens in SQL statements. Postgres has additional key words which have similar restrictions. In particular, these key words are not allowed as column or table names, though in some cases they are allowed to be column labels (i.e. in AS clauses).

Tip: Any string can be specified as an identifier if surrounded by double quotes (“like this!”). Some care is required since such an identifier will be case sensitive and will retain embedded whitespace and most other special characters.

The following are Postgres reserved words which are neither SQL92 nor SQL3 reserved words. These are allowed to be present as column labels, but not as identifiers:

ABORT ANALYZE 
BINARY 
CLUSTER CONSTRAINT COPY
DO 
EXPLAIN EXTEND
LISTEN LOAD LOCK 
MOVE 
NEW NONE NOTIFY 
OFFSET 
RESET 
SETOF SHOW
UNLISTEN UNTIL 
VACUUM VERBOSE
     

The following are Postgres reserved words which are also SQL92 or SQL3 reserved words, and which are allowed to be present as column labels, but not as identifiers:

ALL ANY ASC BETWEEN BIT BOTH 
CASE CAST CHAR CHARACTER CHECK COALESCE COLLATE COLUMN
 CONSTRAINT CROSS CURRENT CURRENT_DATE CURRENT_TIME
 CURRENT_TIMESTAMP CURRENT_USER 
DEC DECIMAL DEFAULT DESC DISTINCT 
ELSE END EXCEPT EXISTS EXTRACT 
FALSE FLOAT FOR FOREIGN FROM FULL 
GLOBAL GROUP 
HAVING 
IN INNER INTERSECT INTO IS 
JOIN 
LEADING LEFT LIKE LOCAL
NATURAL NCHAR NOT NULL NULLIF NUMERIC 
ON OR ORDER OUTER OVERLAPS 
POSITION PRECISION PRIMARY PUBLIC
REFERENCES RIGHT 
SELECT SESSION_USER SOME SUBSTRING 
TABLE THEN TO TRANSACTION TRIM TRUE
UNION UNIQUE USER 
VARCHAR 
WHEN WHERE 
     

ADD ALTER AND AS 
BEGIN BY
CASCADE CLOSE COMMIT CREATE CURSOR
DECLARE DEFAULT DELETE DESC DISTINCT DROP
EXECUTE EXISTS EXTRACT
FETCH FLOAT FOR FROM FULL
GRANT 
HAVING
IN INNER INSERT INTERVAL INTO IS
JOIN 
LEADING LEFT LIKE LOCAL
NAMES NATIONAL NATURAL NCHAR NO NOT NULL 
ON OR OUTER
PARTIAL PRIMARY PRIVILEGES PROCEDURE PUBLIC
REFERENCES REVOKE RIGHT ROLLBACK
SELECT SET SUBSTRING
TO TRAILING TRIM 
UNION UNIQUE UPDATE USING
VALUES VARCHAR VARYING VIEW
WHERE WITH WORK 
     

The following are SQL92 reserved key words which are not Postgres reserved key words, but which if used as function names are always translated into the function CHAR_LENGTH:

CHARACTER_LENGTH
     

The following are SQL92 or SQL3 reserved key words which are not Postgres reserved key words, but if used as type names are always translated into an alternate, native type:

BOOLEAN DOUBLE FLOAT INT INTEGER INTERVAL REAL SMALLINT
     

The following are not keywords of any kind, but when used in the context of a type name are translated into a native Postgres type, and when used in the context of a function name are translated into a native function:

DATETIME TIMESPAN
     

The following are either SQL92 or SQL3 reserved key words which are not key words in Postgres. These have no proscribed usage in Postgres at the time of writing (v7.0) but may become reserved key words in the future:

Note: Some of these key words represent functions in SQL92. These functions are defined in Postgres, but the parser does not consider the names to be key words and they are allowed in other contexts.

ALLOCATE ARE ASSERTION AT AUTHORIZATION AVG 
BIT_LENGTH
CASCADED CATALOG CHAR_LENGTH CHARACTER_LENGTH COLLATION
 CONNECT CONNECTION CONTINUE CONVERT CORRESPONDING COUNT
 CURRENT_SESSION
DATE DEALLOCATE DEC DESCRIBE DESCRIPTOR
 DIAGNOSTICS DISCONNECT DOMAIN 
ESCAPE EXCEPT EXCEPTION EXEC EXTERNAL 
FIRST FOUND
GET GO GOTO 
IDENTITY INDICATOR INPUT INTERSECT
LAST LOWER 
MAX MIN MODULE 
OCTET_LENGTH OPEN OUTPUT OVERLAPS 
PREPARE PRESERVE 
ROWS
SCHEMA SECTION SESSION SIZE SOME 
 SQL SQLCODE SQLERROR SQLSTATE SUM SYSTEM_USER
TEMPORARY TRANSLATE TRANSLATION 
UNKNOWN UPPER USAGE
VALUE 
WHENEVER WRITE
     

Non-reserved Keywords

SQL92 and SQL3 have non-reserved keywords which have a prescribed meaning in the language but which are also allowed as identifiers. Postgres has additional keywords which allow similar unrestricted usage. In particular, these keywords are allowed as column or table names.

The following are Postgres non-reserved key words which are neither SQL92 nor SQL3 non-reserved key words:

ACCESS AFTER AGGREGATE 
BACKWARD BEFORE 
CACHE COMMENT CREATEDB CREATEUSER CYCLE
DATABASE DELIMITERS 
EACH ENCODING EXCLUSIVE
FORCE FORWARD FUNCTION 
HANDLER
INCREMENT INDEX INHERITS INSENSITIVE INSTEAD ISNULL
LANCOMPILER LOCATION 
MAXVALUE MINVALUE MODE
NOCREATEDB NOCREATEUSER NOTHING NOTIFY NOTNULL 
OIDS OPERATOR 
PASSWORD PROCEDURAL
RECIPE REINDEX RENAME RETURNS ROW RULE
SEQUENCE SERIAL SHARE START STATEMENT STDIN STDOUT 
TEMP TRUSTED 
UNLISTEN UNTIL 
VALID VERSION
     

The following are Postgres non-reserved key words which are SQL92 or SQL3 reserved key words:

ABSOLUTE ACTION
CONSTRAINTS
DAY DEFERRABLE DEFERRED 
HOUR
IMMEDIATE INITIALLY INSENSITIVE ISOLATION 
KEY 
LANGUAGE LEVEL 
MATCH MINUTE MONTH
NEXT 
OF ONLY OPTION 
PENDANT PRIOR PRIVILEGES 
READ RELATIVE RESTRICT 
SCROLL SECOND
TIME TIMESTAMP TIMEZONE_HOUR TIMEZONE_MINUTE TRIGGER 
YEAR
ZONE
     

The following are Postgres non-reserved key words which are also either SQL92 or SQL3 non-reserved key words:

COMMITTED SERIALIZABLE TYPE
     

The following are either SQL92 or SQL3 non-reserved key words which are not key words of any kind in Postgres:

ADA
C CATALOG_NAME CHARACTER_SET_CATALOG CHARACTER_SET_NAME 
 CHARACTER_SET_SCHEMA CLASS_ORIGIN COBOL COLLATION_CATALOG
 COLLATION_NAME COLLATION_SCHEMA COLUMN_NAME
 COMMAND_FUNCTION CONDITION_NUMBER
 CONNECTION_NAME CONSTRAINT_CATALOG CONSTRAINT_NAME
 CONSTRAINT_SCHEMA CURSOR_NAME
DATA DATE_TIME_INTERVAL_CODE DATE_TIME_INTERVAL_PRECISION
 DYNAMIC_FUNCTION
FORTRAN
LENGTH
MESSAGE_LENGTH MESSAGE_OCTET_LENGTH MORE MUMPS
NAME NULLABLE NUMBER
PAD PASCAL PLI
REPEATABLE RETURNED_LENGTH RETURNED_OCTET_LENGTH
 RETURNED_SQLSTATE ROW_COUNT
SCALE SCHEMA_NAME SERVER_NAME SPACE SUBCLASS_ORIGIN
TABLE_NAME
UNCOMMITTED UNNAMED