RE: Using a different column name in a foreign table - Mailing list pgsql-general

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:

 

 

 

But when I define the FT as below, there is no error in the subsequent "select".

 

 

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.

 

A.A.

 

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

 

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 error in 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 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');

 

> 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.

 

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