Re: Using a different column name in a foreign table

On 1/21/22 10:04 AM, Alanoly Andrews wrote:
> Following up on my previous message, there was a related problem with FT 
> definitions for which I have a workaround. It seems that the new 
> column_name after "options" in an FT definition cannot be in upper case. 
> See below:
> When I define a foreign table like below, I get an error in the 
> subsequent select statement:
> create foreign table scvmgl_rec (
> MGL_CLNT_HOST_NM char(15) options (column_name 'MSG_CLNT_HOST_NM'))
> server pglpolaris12018
> options (schema_name 'informix', table_name 'sctmsg_rec');
> ai01pr00=# select * from scvmgl_rec;
> ERROR:  column "MSG_CLNT_HOST_NM" does not exist
> CONTEXT:  Remote SQL command: SELECT "MSG_CLNT_HOST_NM" FROM 
> informix.sctmsg_rec
> But when I define the FT as below, there is no error in the subsequent 
> "select".
> create foreign table scvmgl_rec (
> mgl_clnt_host_nm char(15) options (column_name 'msg_clnt_host_nm'))
> server pglpolaris12018
> options (schema_name 'informix', table_name 'sctmsg_rec');
> So, apparently postgres transmits the remote query with double quotes 
> around the upper-case column name; and such a query comes back with 
> "column does not exist". But a double quote around a lower-case column 
> name does not produce the error.
> Is this a bug that needs to be fixed? I always thought that table names 
> and column names were case-insensitive with regard to SQL's.

You thought wrong:

https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS

'Quoting an identifier also makes it case-sensitive, whereas unquoted 
names are always folded to lower case. For example, the identifiers FOO, 
foo, and "foo" are considered the same by PostgreSQL, but "Foo" and 
"FOO" are different from these three and each other. (The folding of 
unquoted names to lower case in PostgreSQL is incompatible with the SQL 
standard, which says that unquoted names should be folded to upper case. 
Thus, foo should be equivalent to "FOO" not "foo" according to the 
standard. If you want to write portable applications you are advised to 
always quote a particular name or never quote it.)'

> A.A.
> 
-- 
Adrian Klaver
adrian.klaver@aklaver.com