Keywords in PolarDB for PostgreSQL (Compatible with Oracle) - PolarDB

When you migrate from an Oracle database or develop applications that use Oracle syntax, misusing keywords can cause SQL syntax errors. To prevent this, PolarDB for PostgreSQL (Compatible with Oracle) classifies keywords into three categories: reserved, column name, and function name. Each category has specific usage rules. Following these rules helps you write compliant SQL and ensures code compatibility and standardization.

Core principles

To provide flexible SQL syntax compatibility, PolarDB for PostgreSQL (Compatible with Oracle) supports three categories of keywords. Understanding the differences between these categories is key to avoiding syntax errors:

Reserved keywords: These are words reserved by the system. Do not use them directly as table, column, or function names. To use a reserved keyword as an identifier, you must enclose it in double quotation marks (").
Column name keywords: These can be used as column or table names, but not as names for common data types or functions.
Function name keywords: These can be used as function names, but not as table or column names.

Correctly using these different keyword types helps you avoid syntax conflicts and improves SQL compatibility and code standardization.

Keyword quick reference

Reserved keywords

ALL	ANALYSE	ANALYZE	AND	ANY	ARRAY	AS	ASC	ASYMMETRIC
BOTH	CASE	CHECK	COLLATE	COLUMN	CONNECT_BY_ROOT	CONSTRAINT	CREATE	CURRENT_CATALOG
CURRENT_ROLE	CURRENT_TIME	CURRENT_TIMESTAMP	CURRENT_USER	DEFAULT	DEFERRABLE	DESC	DISTINCT	DO
ELSE	END	EXCEPT	FALSE	FETCH	FOR	FOREIGN	FROM	GRANT
GROUP	HAVING	IN	INITIALLY	INTERSECT	INTO	LATERAL	LEADING	LEVEL
LIMIT	LOCALTIME	LOCALTIMESTAMP	LOOP	MINUS	NOT	NULL	OFFSET	ON
ONLY	OR	ORDER	PLACING	POLAR_SYS_ROWID_ATTR	PRIOR	REFERENCES	RETURNING	ROWNUM
SELECT	SESSION_USER	SOME	SYMMETRIC	TABLE	THEN	TO	TRAILING	TRUE
UNION	UNIQUE	USER	USING	VARIADIC	WHEN	WHERE	WINDOW	WITH

Column name keywords

BETWEEN	BIGINT	BINARY_DOUBLE	BINARY_FLOAT	BINARY_INTEGER	BIT	BLOB	BOOLEAN	CHAR
CHARACTER	CLOB	COALESCE	DATETIME	DEC	DECIMAL	EXISTS	FLOAT	GREATEST
GROUPING	GROUPING_ID	GROUP_ID	INOUT	INT	INTEGER	INTERVAL	LEAST	LONG
LONGTEXT	MEDIUMTEXT	NATIONAL	NCHAR	NCLOB	NONE	NORMALIZE	NULLIF	NUMBER
NUMERIC	NVARCHAR	NVARCHAR2	OUT	OVERLAY	PLS_INTEGER	PRECISION	RAW	REAL
ROW	SETOF	SMALLINT	SUBSTRING	TIME	TIMESTAMP	TIMESTAMPLTZ	TREAT	TRIM
UROWID	VALUES	VARCHAR	VARCHAR2	XMLATTRIBUTES	XMLCONCAT	XMLELEMENT	XMLEXISTS	XMLFOREST
XMLNAMESPACES	XMLPARSE	XMLPI	XMLROOT	XMLSERIALIZE	XMLTABLE

Function name keywords

AUTHORIZATION	BINARY	COLLATION	CONCURRENTLY	CROSS	CURRENT_SCHEMA	FREEZE	FULL	ILIKE
INNER	IS	ISNULL	JOIN	LEFT	LIKE	NATURAL	NOTNULL	OVERLAPS
RIGHT	SIMILAR	TABLESAMPLE	VERBOSE

Examples: How to use identifiers correctly

The following examples show correct and incorrect ways to handle identifiers that share a name with a keyword.

Scenario 1: Use a keyword as a column name when you create a table

Create a table that contains a column named order.

Correct Usage

Enclose the keyword in double quotation marks to force it to be treated as an identifier.

CREATE TABLE t_order (
    id INT PRIMARY KEY,
    "order" VARCHAR(50) -- Use double quotation marks.
);

Result:

CREATE TABLE

Incorrect Usage

Using the keyword directly as a column name causes a syntax error.

CREATE TABLE t_order (
    id INT PRIMARY KEY,
    order VARCHAR(50) -- "order" is a reserved keyword.
);

Result:

ERROR:  syntax error at or near "order"
LINE 3:     order VARCHAR(50) 
            ^

Scenario 2: Query data from a column named with a keyword

Query the order column from the t_order table. The column was defined with its name in double quotation marks.

Correct Usage

You must also use double quotation marks when you query the column.

-- Insert sample data.
INSERT INTO t_order (id, "order") VALUES (1, 'PolarDB');

-- Correct query.
SELECT id, "ORDER" FROM t_order;

Result:

 id |  ORDER  
----+---------
  1 | PolarDB
(1 row)

Incorrect Usage

If you do not use double quotation marks, the database interprets the column name as a keyword and fails to find the column.

-- Incorrect example: Missing double quotation marks.
SELECT id, order FROM t_order;

Result:

ERROR:  syntax error at or near "order"
LINE 1: SELECT id, order FROM t_order;
                   ^