Querying Postgres through Babelfish

Question

I am connected over TDS (1433) to a Postgres/Aurora (babelfish-enabled) database.

I can run the following three queries from my application and I receive confusing responses:

SELECT current_database()

SELECT * FROM information_schema.tables WHERE table_name = 'PERSON'

SELECT COUNT(1) FROM "PERSON"

The responses are:

current_database":"babelfish_db"

"table_catalog":"babelfish_db","table_schema":"public","table_name":"PERSON","table_type":"BASE TABLE"...}

relation "person" does not exist

I simply cannot query the PERSON table. I have tried:

"PERSON"
"person"
PERSON
person
public.PERSON
public.person
public."PERSON"

I have ensured the user I am connecting as has access to the database, schema and tables:

GRANT CONNECT ON DATABASE babelfish_db TO popweb;
GRANT USAGE ON SCHEMA public TO popweb;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO popweb;

Still, I cannot access the table. I feel like such a boob/noob

For anyone who has connected to Postgres via Babelfish, what am I doing wrong?

Answer 1

The GA release of Babelfish didn't make any modifications to the PostgreSQL implementation of the information schema. So, what you see is the physical database of babelfish_db and the public schema. It looks like you created the table using the PostgreSQL endpoint.

To work with tables in Babelfish at this time, you need to create a T-SQL virtual database and your tables inside of that database using the T-SQL endpoint - just like you did before.

For example using SSMS and a new query connected to your Babelfish endpoint. You should notice in the SSMS database drop down and in the status bar that your context shows you are in the master database.

    CREATE DATABASE ford_prefect;   
    GO   
    USE ford_prefect;   
    GO   
    CREATE SCHEMA school;   
    GO   
    CREATE TABLE [School].[Person](   
        [PersonID] [int] NOT NULL,   
        [LastName] [nvarchar](50) NOT NULL,   
        [FirstName] [nvarchar](50) NOT NULL,   
        [HireDate] [datetime] NULL,   
        [EnrollmentDate] [datetime] NULL,   
        [Discriminator] [nvarchar](50) NOT NULL,   
    CONSTRAINT [PK_School.Student] PRIMARY KEY CLUSTERED    
    (   
        [PersonID] ASC   
    )   
    GO   

At this point you can add records via INSERT and select the table without issues. Cheers,
Bill Ramos
Aurora PostgreSQL Babelfish PM, Amazon