sql_syntax_migration_postgres_to_bq.md

SQL Syntax Migration Guide - Postgres to BigQuery

I'm in the process of migrating a lot of SQL (in LookML) from PostgreSQL to BigQuery (Standard SQL). Documenting the quirks I find along the way here!

General

Casting

Postgres

x::int

BigQuery

cast(x as int64)

Regex

Postgres

substring('asdf' from '^(.*)')

BigQuery

regexp_extract('asdf', '^(.*)')

Concat

BigQuery's concat() args must be cast to strings first. Double-pipes are no bueno in BQ.

Postgres

concat('#', id)
'#' || id

BigQuery

Note - concatenating a string with a null string will return the entire concatenated string as null. This is different than in PostgreSQL, which will simply treat the null string as a blank string.

concat('#', coalesce(cast(id as string), ''))

Date functions

Standard SQL date function reference

Timezone Conversion

The following examples create a naive timestamp of whatever time it is in NYC.

Postgres

(current_timestamp::timestamptz at time zone 'America/New_York')::timestamp

BigQuery

datetime(current_timestamp, 'America/New_York')

Adding / subtracting dates

Postgres

d1 + '9 days'::interval
d1 - '9 days'::interval

BigQuery

date_add(d, interval 9 day)
date_sub(d, interval 9 day)

Diffing dates

Postgres

d2 - d1

BigQuery

date_diff(d1, d2, day)

Looker

Table references

SQL table names need backticks and schema name updates.

Postgres

sql_table_name: pg_schema.pg_table_name ;;

BigQuery

sql_table_name: `bq_schema.bq_table_name` ;;