Using reserved keywords in PostgreSQL/ISAM

I Love Xbase++ (ILX)
The portal for Xbase++ developers worldwide

Till Warweg

Member
Staff member
I am here to help you!
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: