- Feb 1, 2021
- 38
- 11
- 8
- Customer Identifier
- E074775
Reserved keywords in PostgreSQL
The PostgreSQL server defines a number of keywords which cannot be used as identifiers. If such a keyword is used as a table or column name in an ISAM-emulated table, problems may result either when upsizing or accessing the table. The Upsizer utility detects many of these conflicts and issues a warning during the upsize process. For example, upsizing a table named order.dbf will yield a warning like### WARNING: column name (ORDER) is a reserved keyword of PostgreSQL
The following lists contains exemplary reserved keywords for illustrational purposes. The full list of keywords is referenced below.
Reserved Keywords in PostgreSQL |
---|
ALL |
ANALYZE |
ASYMMETRIC |
BINARY |
CASE |
COLLATION |
COLUMN |
CREATE |
CROSS |
DEFAULT |
EXCEPT |
FULL |
GRANT |
LIMIT |
LOCALTIME |
NOTIFY |
OFFSET |
ORDER |
OVERLAPS |
REFERENCES |
SELECT |
UNIQUE |
USER |
VERBOSE |
Note: The PGDBE contains dedicated logic for handling naming conflicts. However, a generic solution for handling these is not always possible. Therefore, using reserved keywords as table or column identifiers is generally discouraged.
Using tables with reserved names
Using reserved keywords as table names causes syntax errors on the server-side, regardless of whether the tables are upsized or created dynamically in program code. In order to deal with this, the table needs to be renamed. This can be done very easily via the Upsizer tool by using the "as" <upsize> attribute in the .upsize file. The procedure is outlined below.1. Use the "as" attribute to implicitly rename the table during the upsize process:
<table name = "order" dbe = "foxcdx" dbf = "order.dbf"/>
<upsize table="order" as="_order" mode="isam" connection="..."/>
2. Use an alias when opening the table in your program:
USE _order ALIAS order
SELECT order
This way, program code using commands like REPLACE are unaffected by the change.
Using columns with reserved names
Column names may conflict with reserved keywords in PostgreSQL in the same way tables names do. Affected columns must be renamed in order to resolve potential conflicts. This can also be done using the Upsizer tool by specifying a suitable <rename> element in the .upsize file.1. Use a <rename> element for renaming a column during the upsize process:
<table name = "order" dbe = "foxcdx" dbf = "order.dbf"/>
<upsize table="order" as="_order" mode="isam" connection="...">
<deferred>
<rename column="all" name="_all" />
</deferred>
</upsize>
2. Change your program logic to use the renamed instead of the original column:
// REPLACE all WITH "tables;chairs;rugs"
REPLACE _all WITH "tables;chairs;rugs"
References:
- Keywords in PostgreSQL: https://www.postgresql.org/docs/current/sql-keywords-appendix.html