Skip to main content

Search

Welcome to Tibero GTS!

Error in Query Due to Case Sensitivity of Table Names When Using Tibero to Postgresql DBLink

Document Type | Troubleshooting

Category | Interface/Integration

Applicable Product Version | 7FS02PS

Document Number | TIITS009

 

Issue

When using Tibero to Postgresql DBLink, a TBR-12126 error occurs stating that the table does not exist when querying a table that actually exists.
 
As shown in the example code below, after creating a table in Postgresql, querying that table from Tibero results in an error code.
 
Create Table in Postgresql
create table test (c1 int);
insert into test values(1);

 

Error Code Displayed When Querying the Table in Tibero
SQL> select * from test@pgnew;
TBR-12126: Remote database client API error - Explicit prepare failed. - ERROR: relation "TEST" does not exist
 Position: 37

TBR-130097:Explicit prepare failed. - ERROR: relation "TEST" does not exist
 Position: 37
 
 

Cause

By default, Tibero converts table and column names to `uppercase`, whereas Postgresql converts all table and column names to `lowercase`, causing this error.
 
 

Solutions

When Creating Table Names in Lowercase in Postgresql

Tables created in Postgresql are in lowercase by default, so when querying these tables from Tibero, you must enclose the table name in double quotes ("") to query successfully.
prosyncmanager=> create table test (c1 int);
CREATE TABLE
prosyncmanager=> insert into test values(1);
INSERT 0 1

Query in Tibero

SQL> select * from "test"@pgnew;

       c1
----------
        1

1 row selected.

 

When Creating Table Names in Uppercase in Postgresql

If the table created in Postgresql is uppercase, you do not need to enclose the table name in double quotes ("") when querying from Tibero.
prosyncmanager=> create table "TEST3" (c1 int);
CREATE TABLE
prosyncmanager=> insert into "TEST3" values (3);
INSERT 0 1

prosyncmanager=> select * from "TEST3";
c1 
----
 3
(1 row)

Query in Tibero
SQL> select * from test3@pgnew;

       c1
----------
        3
 

Comparison of Output Based on Table Name Case Sensitivity

1) When Table Name is Lowercase, Output Comparison
Postgresql
prosyncmanager=> select * from test; 
c1 
----
 1
(1 row)


Tibero

SQL> select * from "test"@pgnew;

       c1
----------
        1

1 row selected.

 

2) When Table Name Contains Mixed Case, Output Comparison
Postgresql
prosyncmanager=> select * from "Test2";
c1 
----
 2
(1 row)


Tibero

SQL> select * from "Test2"@pgnew;

       c1
----------
        2

1 row selected.

3) When Table Name is Uppercase, Output Comparison 
Postgresql
prosyncmanager=> select * from "TEST3";
c1 
----
 3
(1 row)


Tibero

SQL> select * from test3@pgnew;

       c1
----------
        3

1 row selected.

 

 

 

Related to