What to use in Snowflake for tstzrange and tsrange types from Postgres

0
votes

I am replicating tables in Postgres to Snowflake and run into these types of tstzrange and tsrange in Postgres which I am not sure whether Snowflake supports.

I see something like TIMESTAMP_LTZ, TIMESTAMP_NTZ and TIMESTAMP_TZ in Snowflake documentation (https://docs.snowflake.net/manuals/sql-reference/data-types-datetime.html#timestamp-ltz-timestamp-ntz-timestamp-tz). But these are not range types.

What would be the best way to support these range timestamp types from Postgres in Snowflake?

1
postgresqlreplicationsqldatatypessnowflake-cloud-data-platform

1 Answers

1
votes

In Postgres

  • tsrange contains timestamp without time zone
  • tstzrange contains timestamp with time zone

The Snowflake alias for timestamp_ntz and timestamp_tz matches exactly to the Postgres type.

Now the Postgres ranges actually contain 3 items: lower range, upper range, and boundary inclusion/exclusion. It's the boundary that may cause issues - you may have to manually alter queries to accommodate them. Also watch out for Infinity (+-) in the ranges. (See sections 8.17.3 and 8.17.4 in above referenced documentation.)