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.
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.
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 WHEREThe following are Postgres reserved words which are also SQL92 or SQL3 reserved words:
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(translated to TIMESTAMP and INTERVAL, respectively). This feature is intended to help with transitioning to v7.0, and will be removed in the next full release (likely v7.1).
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
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