Using keywords as table names

The database server issues an error in contexts where the unqualified identifier of a table object is also a valid keyword of SQL. You can disambiguate the table name by qualifying it with the authorization identifier of the owner of the table.

Examples that follow illustrate owner-name qualifiers as workarounds when the keyword STATISTICS, OUTER, or FROM has been declared as a table name or synonym. (These examples also apply if any of those keywords is the identifier of a view.)

Using statistics as a table identifier causes the following UPDATE statement example to fail. An exception occurs because the database server interprets statistics as a keyword in a syntactically incorrect UPDATE STATISTICS statement, rather than as the target table name in an UPDATE statement:

UPDATE statistics SET mycol = 10;  -- fails

The workaround in the following example qualifies the table name with the owner name, to avoid that ambiguity:

UPDATE josh.statistics SET mycol = 10;

Using outer as a table name causes the following example to fail, because the database server interprets outer as a keyword for performing a syntactically incorrect outer join:

SELECT mycol FROM outer; -- fails

The following successful example uses owner naming to avoid ambiguity:

SELECT mycol FROM josh.outer;

The following DELETE statement, whose target table was created with from as its identifier, returns a syntax error:

DELETE from;    -- fails

Because FROM is also an optional keyword that immediately precedes the name of the table whose records are to be destroyed, the database server expects a table name after FROM. Finding none, it issues an exception.

The following example, in contrast, deletes all the rows from what it correctly recognizes as the from table, because that table name is qualified by the name of its owner:

DELETE zelaine.from;

If the DELIMIDENT environment variable is set on the database server, an alternative workaround is to use double quotation marks ( " ) as delimiters.

DELETE "from";

This avoids the exception by indicating that from is an SQL identifier, rather than a string literal or an SQL keyword.

Despite the availability of these workarounds, your code will be easier for humans to read and to maintain if you avoid declaring SQL keywords as the identifiers of tables, views, or other database objects.