Keywords

Updated at:

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

CategoryCan be used asCannot be used asFix when conflicting
Reserved keywordAny identifier, if enclosed in double quotation marks (")Table, column, or function name (unquoted)Enclose in "
Column name keywordColumn or table nameCommon data type or function nameNo fix needed for column/table use
Function name keywordFunction nameTable or column nameNo 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

ALLANALYSEANALYZEANDANYARRAYASASCASYMMETRIC
BOTHCASECHECKCOLLATECOLUMNCONNECT_BY_ROOTCONSTRAINTCREATECURRENT_CATALOG
CURRENT_ROLECURRENT_TIMECURRENT_TIMESTAMPCURRENT_USERDEFAULTDEFERRABLEDESCDISTINCTDO
ELSEENDEXCEPTFALSEFETCHFORFOREIGNFROMGRANT
GROUPHAVINGININITIALLYINTERSECTINTOLATERALLEADINGLEVEL
LIMITLOCALTIMELOCALTIMESTAMPLOOPMINUSNOTNULLOFFSETON
ONLYORORDERPLACINGPOLAR_SYS_ROWID_ATTRPRIORREFERENCESRETURNINGROWNUM
SELECTSESSION_USERSOMESYMMETRICTABLETHENTOTRAILINGTRUE
UNIONUNIQUEUSERUSINGVARIADICWHENWHEREWINDOWWITH

Column name keywords

BETWEENBIGINTBINARY_DOUBLEBINARY_FLOATBINARY_INTEGERBITBLOBBOOLEANCHAR
CHARACTERCLOBCOALESCEDATETIMEDECDECIMALEXISTSFLOATGREATEST
GROUPINGGROUPING_IDGROUP_IDINOUTINTINTEGERINTERVALLEASTLONG
LONGTEXTMEDIUMTEXTNATIONALNCHARNCLOBNONENORMALIZENULLIFNUMBER
NUMERICNVARCHARNVARCHAR2OUTOVERLAYPLS_INTEGERPRECISIONRAWREAL
ROWSETOFSMALLINTSUBSTRINGTIMETIMESTAMPTIMESTAMPLTZTREATTRIM
UROWIDVALUESVARCHARVARCHAR2XMLATTRIBUTESXMLCONCATXMLELEMENTXMLEXISTSXMLFOREST
XMLNAMESPACESXMLPARSEXMLPIXMLROOTXMLSERIALIZEXMLTABLE

Function name keywords

AUTHORIZATIONBINARYCOLLATIONCONCURRENTLYCROSSCURRENT_SCHEMAFREEZEFULLILIKE
INNERISISNULLJOINLEFTLIKENATURALNOTNULLOVERLAPS
RIGHTSIMILARTABLESAMPLEVERBOSE

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 TABLE

Incorrect 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;
                   ^