Keywords
PolarDB for PostgreSQL (Compatible with Oracle) classifies SQL keywords into three categories — reserved, column name, and function name — each with distinct usage rules. Misidentifying a keyword category is a common source of SQL syntax errors during Oracle migration or application development.
Keyword categories
| Category | Can be used as | Cannot be used as | Fix when conflicting |
|---|---|---|---|
| Reserved keyword | Any identifier, if enclosed in double quotation marks (") | Table, column, or function name (unquoted) | Enclose in " |
| Column name keyword | Column or table name | Common data type or function name | No fix needed for column/table use |
| Function name keyword | Function name | Table or column name | No fix needed for function use |
To use a reserved keyword as an identifier, always enclose it in double quotation marks ("). Unquoted reserved keywords cause a syntax error.Keyword 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 |
Use a keyword as an identifier
The following examples show how to handle identifiers that share a name with a reserved keyword.
Create a table with a keyword as a column name
To create a column named order (a reserved keyword), enclose it in double quotation marks.
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 TABLEIncorrect Usage
Using the keyword without quotes 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)
^Query a column whose name is a keyword
When a column is defined with a quoted name, use the same double quotation marks in every reference to it — including INSERT and SELECT statements.
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
Omitting the double quotation marks causes the database to interpret the column name as a keyword.
-- 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;
^