BigQuery EXTERNAL_QUERY to Postgres failing when accessing tables with or without schema name

[scottmcq]

We are receiving the following error when trying to query any of our tables in the postgres db.

Query:

SELECT * FROM EXTERNAL_QUERY("xxxx.us-east4.xxxxxxxx", "SELECT * FROM public.User;");

Error:

Invalid table-valued function EXTERNAL_QUERY Failed to get query schema from PostgreSQL server, prepare statement failed. Error: ERROR: relation "public.user" does not exist LINE 1: SELECT * FROM public.User; /* Federated query from BigQuery.... ^ at [1:15]

However, the following query does work:

Query public tables:

SELECT * FROM EXTERNAL_QUERY("xxxx.us-east4.xxxxxxxx", "SELECT * FROM pg_tables where schemaname = 'public';");

Results:

Any hints, tips, tricks to try? 

Things we have done:

• tried adding schema name, removing schema name, single quotes, double quotes,... all the formats
• verified user has permission
• verified schema is in search path
• Query Insights is disabled
• verified user can successfully query in DBeaver.

[brubom]

Hi mate, any luck with this? Got same problem.

[hvgd]

same issue here

[VishalBulbule]

Hi,

I would like to confirm a few things:

1. What database name and username combination did you use while creating the external connection - "xxxx.us-east4.xxxxxxxx ?

2. Does the same user have privileges to read tables? If not, you can grant the necessary privileges using the following command:

GRANT SELECT ON ALL TABLES IN SCHEMA schema_name TO username;

Just let me know on above.

[mclaughlinbrad]

I had the same issue, but the solution is to add escaped quotes around the table name like this:
SELECT * FROM EXTERNAL_QUERY("xxxx.us-east4.xxxxxxxx", "SELECT * FROM public.\"User\";");