Column names with snake notation don't work

[Timrael]

I have a mysql table:

CREATE TABLE `redirect` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `searchId` char(36) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `searchId` (`searchId`)
)

Note that 'I' in 'searchId' is capital.

Than I create FDW in postgres with such options:

IMPORT FOREIGN SCHEMA db limit to (redirect) from server mysql_server INTO public;

                   Foreign table "public.redirect"
   Column    |            Type             | Modifiers | FDW Options
-------------+-----------------------------+-----------+-------------
 id          | integer                     | not null  |
 searchId    | character(36)               |           |

When I try to reach 'searchId' field from FDW I get this error:

db=# select redirect.searchid from redirect where id < 7350;
ERROR:  column redirect.searchid does not exist
LINE 1: select redirect.searchid from redirect where id < 7350;
               ^
HINT:  Perhaps you meant to reference the column "redirect.searchId".

I can fix it by creating lowercase column names in FDW:

CREATE FOREIGN TABLE redirect(id int, searchid int) SERVER mysql_server options(dbname 'db', table_name 'redirect');

[ahsanhadi]

It seems that import foreign schema might have a bug in dealing with quoted
column names. We will look into this and get back to you.

On Fri, Mar 11, 2016 at 5:49 PM, Tim notifications@github.com wrote:

I have a mysql table:

CREATE TABLE redirect (
id int(11) NOT NULL AUTO_INCREMENT,
searchId char(36) DEFAULT NULL,
PRIMARY KEY (id),
KEY searchId (searchId)
)

Note that 'I' in 'searchId' is capital.

Than I create FDW in postgres with such options:

IMPORT FOREIGN SCHEMA db limit to (redirect) from server mysql_server INTO public;

               Foreign table "public.redirect"

Column | Type | Modifiers | FDW Options-------------+-----------------------------+-----------+-------------
id | integer | not null |
searchId | character(36) | |

When I try to reach 'searchId' field from FDW I get this error:

db=# select redirect.searchid from redirect where id < 7350;
ERROR: column redirect.searchid does not existLINE 1: select redirect.searchid from redirect where id < 7350;
^
HINT: Perhaps you meant to reference the column "redirect.searchId".

I can fix it by creating lowercase column names in FDW:

CREATE FOREIGN TABLE redirect(id int, searchid int) SERVER mysql_server options(dbname 'db', table_name 'redirect');

—
Reply to this email directly or view it on GitHub
#93.

Ahsan Hadi
Snr Director Product Development
EnterpriseDB Corporation
The Enterprise Postgres Company

Phone: +92-51-8358874
Mobile: +92-333-5162114

Website: www.enterprisedb.com
EnterpriseDB Blog: http://blogs.enterprisedb.com/
Follow us on Twitter: http://www.twitter.com/enterprisedb

This e-mail message (and any attachment) is intended for the use of the
individual or entity to whom it is addressed. This message contains
information from EnterpriseDB Corporation that may be privileged,
confidential, or exempt from disclosure under applicable law. If you are
not the intended recipient or authorized to receive this for the intended
recipient, any use, dissemination, distribution, retention, archiving, or
copying of this communication is strictly prohibited. If you have received
this e-mail in error, please notify the sender immediately by reply e-mail
and delete this message.

[jjthiessen]

Please note that I haven't tried to reproduce this myself. However, I believe this is due to mysql_fdw's preservation of case.

In PostgreSQL, unquoted identifiers are coerced/folded into lower case. However, IMPORT FOREIGN SCHEMA (at least for mysql_fdw) preserves the source case of foreign table identifiers when creating foreign tables. This, together with PostgreSQL's case sensitivity, means that you need to quote any identifiers that contain upper-case characters. This is different from the default MySQL behaviour of case preservation and case insensitivity for column names (whether they are quoted or not, IIRC).

For your example, db=# select redirect."searchId" from redirect where id < 7350; should work.

[Timrael]

@jjthiessen your example works fine, thanks

[nurikk]

But how handle select * from queries?

[nurikk]

Oh, it's works :)