CODEWITHSUNDEEP
google-cloud-platformgoogle-bigquery
Calle Engene
Calle Engene

Reputation: 886

How do I write a Bigquery federated query towards postgres tables?

I'm confused about the federated query syntax. I'm able to query for metadata with the default connection query in bq console:

SELECT * FROM EXTERNAL_QUERY("connection-id", "SELECT * FROM INFORMATION_SCHEMA.TABLES;");

But I want to query the tables. So I try:

SELECT * FROM EXTERNAL_QUERY("connection-id", "SELECT * FROM my_postgres_table;");

Then the console shows me this:

PostgreSQL type  in column type is not supported in BigQuery. You can cast it to a supported type by PostgreSQL CAST() function. at [1:15]

How do I write this query to get table results back?

Thanks! 🙏

Upvotes: 0

Views: 1108

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172994

If your external query contains a data type that is unsupported in BigQuery, the query will fail immediately. You can cast the unsupported data type to a different supported MySQL or PostgreSQL data type.

  • Unsupported PostgreSQL data types: money, time with time zone, inet, path, pg_lsn, point, polygon, tsquery, tsvector, txid_snapshot, uuid, box, cidr, circle, interval, jsonb, line, lseg, macaddr, macaddr8
  • Resolution: Cast the unsupported data type to STRING.
  • Example: SELECT CAST('12.34'::float8::numeric::money AS varchar(30)); This command casts the unsupported data type money to STRING.

See more for Limitations and Data type mappings

Upvotes: 2

Related Questions