Thread: Using a different column name in a foreign table

On 1/21/22 08:24, Alanoly Andrews wrote:
> Hello,
> I see that the syntax for the creation of a foreign table allows you to 
> use a column name in the FT that is different from the one in the base 
> table. Such a "create foreign table" statement executes successfully and 
> creates the FT. But when I query the FT, I get an error wrt to the 
> column that had been renamed. See example below:
> create foreign table tab1_ft (
>    id int,
>      name char(10) options(column_name 'newname'))
> server xxxxxx
> options(schema_name 'public', table_name 'tab1');

What is the definition of the table tab1?

> select * from tab1_ft;
> ERROR:  column "newname" does not exist
> HINT:  Perhaps you meant to reference the column "tab1.name".
> CONTEXT:  Remote SQL command: SELECT id, newname FROM public.tab1
> So, it seems that the when the remote SQL command is composed, the 
> mapping of 'newname' to the 'name' in the base table does not take effect.
> Is there a resolution to this issue?
> Awaiting some input.
> Alanoly Andrews.
> 



-- 
Adrian Klaver
adrian.klaver@aklaver.com

Alanoly Andrews <alanolya@invera.com> writes:
> I see that the syntax for the creation of a foreign table allows you to use a column name in the FT that is different
fromthe one in the base table. Such a "create foreign table" statement executes successfully and creates the FT. But
whenI query the FT, I get an error wrt to the column that had been renamed. See example below: 

> create foreign table tab1_ft (
>     id int,
>     name char(10) options(column_name 'newname'))
> server xxxxxx
> options(schema_name 'public', table_name 'tab1');

> select * from tab1_ft;

> ERROR:  column "newname" does not exist
> HINT:  Perhaps you meant to reference the column "tab1.name".
> CONTEXT:  Remote SQL command: SELECT id, newname FROM public.tab1

> So, it seems that the when the remote SQL command is composed, the mapping of 'newname' to the 'name' in the base
tabledoes not take effect. 

Huh?  The CONTEXT line shows what was issued to the remote server,
and it's very obvious that we *are* asking for "newname", as indeed
is also implied by the error issued by the remote.  I think you
just didn't match the name correctly to the actual name on the remote.

            regards, tom lane

Thanks Tom, Guillaume and Adrian.
My mistake in reversing the order of name and newname in the definition.
After the switch, it works.

But....I'm investigating another environment where the definition appears to have been correct and yet there is an
errorin select. Maybe I'll post again later, if needed.
 

Thanks.

A.A.

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Friday, January 21, 2022 11:52 AM
To: Alanoly Andrews <alanolya@invera.com>
Cc: pgsql-general@lists.postgresql.org
Subject: Re: Using a different column name in a foreign table

[Email External/Externe] Caution opening links or attachments/attention lors de l'ouverture de liens ou de pièces
jointes.

Alanoly Andrews <alanolya@invera.com> writes:
> I see that the syntax for the creation of a foreign table allows you to use a column name in the FT that is different
fromthe one in the base table. Such a "create foreign table" statement executes successfully and creates the FT. But
whenI query the FT, I get an error wrt to the column that had been renamed. See example below:
 

> create foreign table tab1_ft (
>     id int,
>     name char(10) options(column_name 'newname')) server xxxxxx
> options(schema_name 'public', table_name 'tab1');

> select * from tab1_ft;

> ERROR:  column "newname" does not exist
> HINT:  Perhaps you meant to reference the column "tab1.name".
> CONTEXT:  Remote SQL command: SELECT id, newname FROM public.tab1

> So, it seems that the when the remote SQL command is composed, the mapping of 'newname' to the 'name' in the base
tabledoes not take effect.
 

Huh?  The CONTEXT line shows what was issued to the remote server, and it's very obvious that we *are* asking for
"newname",as indeed is also implied by the error issued by the remote.  I think you just didn't match the name
correctlyto the actual name on the remote.
 

                        regards, tom lane

This e-mail may be privileged and/or confidential, and the sender does not waive any related rights and obligations.
Anydistribution, use or copying of this e-mail or the information it contains by other than an intended recipient is
unauthorized.If you received this e-mail in error, please advise me (by return e-mail or otherwise) immediately.
 


Ce courriel est confidentiel et protégé. L'expéditeur ne renonce pas aux droits et obligations qui s'y rapportent.
Toutediffusion, utilisation ou copie de ce message ou des renseignements qu'il contient par une personne autre que le
(les)destinataire(s) désigné(s) est interdite. Si vous recevez ce courriel par erreur, veuillez m'en aviser
immédiatement,par retour de courriel ou par un autre moyen.'. If the disclaimer can't be applied, attach the message to
anew disclaimer message.
 

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

Thanks Adrian. I was aware of that.

But...in the case of FT definition, the quotes are mandatory after column_name in options. And it is a single quote.
The problem here may be is that the "create foreign table" statement accepts only lower case  after options. Remember
thatthe double quotes for the column name in the "select" statement is generated by postgres, not by the end user. If
thedouble quotes were not present, the query with the upper case would have worked.
 

A.A.

-----Original Message-----
From: Adrian Klaver [mailto:adrian.klaver@aklaver.com]
Sent: Friday, January 21, 2022 1:14 PM
To: Alanoly Andrews <alanolya@invera.com>; Tom Lane <tgl@sss.pgh.pa.us>; Guillaume Lelarge <guillaume@lelarge.info>
Cc: pgsql-general@lists.postgresql.org
Subject: Re: Using a different column name in a foreign table

[Email External/Externe] Caution opening links or attachments/attention lors de l'ouverture de liens ou de pièces
jointes.

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
fromthese 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
youare advised to always quote a particular name or never quote it.)'
 

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

This e-mail may be privileged and/or confidential, and the sender does not waive any related rights and obligations.
Anydistribution, use or copying of this e-mail or the information it contains by other than an intended recipient is
unauthorized.If you received this e-mail in error, please advise me (by return e-mail or otherwise) immediately.
 


Ce courriel est confidentiel et protégé. L'expéditeur ne renonce pas aux droits et obligations qui s'y rapportent.
Toutediffusion, utilisation ou copie de ce message ou des renseignements qu'il contient par une personne autre que le
(les)destinataire(s) désigné(s) est interdite. Si vous recevez ce courriel par erreur, veuillez m'en aviser
immédiatement,par retour de courriel ou par un autre moyen.'. If the disclaimer can't be applied, attach the message to
anew disclaimer message.
 

Alanoly Andrews <alanolya@invera.com> writes:
> But...in the case of FT definition, the quotes are mandatory after column_name in options. And it is a single quote.

Right.  That string literal is not case-folded, so it has to accurately
match the actual spelling of the remote column name.  Anything else we
might do there would either make things even more confusing, or prevent
you from accessing mixed-case remote names at all.

(Well, maybe we could ignore the SQL standard's mandate that column
option values be string literals, but that's just setting ourselves
up for future problems.)

            regards, tom lane

On 1/21/22 10:32 AM, Alanoly Andrews wrote:
> Thanks Adrian. I was aware of that.
> 
> But...in the case of FT definition, the quotes are mandatory after column_name in options. And it is a single quote.
> The problem here may be is that the "create foreign table" statement accepts only lower case  after options. Remember
thatthe double quotes for the column name in the "select" statement is generated by postgres, not by the end user. If
thedouble quotes were not present, the query with the upper case would have worked.
 

I have not looked at the source, but I'm betting it is using something like:

https://www.postgresql.org/docs/14/functions-string.html

quote_ident ( text ) → text

Returns the given string suitably quoted to be used as an identifier in 
an SQL statement string. Quotes are added only if necessary (i.e., if 
the string contains non-identifier characters or would be case-folded). 
Embedded quotes are properly doubled. See also Example 43.1.

quote_ident('Foo bar') → "Foo bar"


So:

select quote_ident('MSG_CLNT_HOST_NM');
     quote_ident
--------------------
  "MSG_CLNT_HOST_NM"


select quote_ident('msg_clnt_host_nm');
    quote_ident
------------------
  msg_clnt_host_nm


> 
> A.A.
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com