Convert Postgres data types to Snowflake: the full mapping table, the verification query for every risky type, and the two Snowflake documents that disagree about your columns

How does Postgres to Snowflake data type mapping work?

Sort the columns into three piles. Pile one converts cleanly and needs no thought: boolean, date, timestamp without time zone, double precision, varchar, text and jsonb all have honest equivalents, and jsonb arguably improves in the move. Pile two has no target at all and both Snowflake documents will tell you so: interval is the big one, because Snowflake states plainly that "INTERVAL is not a data type (that is, you can't define a table column to be of data type INTERVAL)". Pile three is the dangerous one. Those columns convert, load and behave differently, and nothing in the migration mentions it.

Pile three is invisible to the tooling for a structural reason. A connector can tell you that a type has no target, because that is a fact about its own mapping table. It cannot tell you that a column which converted perfectly well was carrying a range the target will not enforce, or that the comparison semantics of a padded code column just changed. Those are facts about your data and your application, and the only place the evidence still exists is the Postgres database you are about to demote to a source.

Why do two Snowflake documents disagree about the same column?

Because they were written for different jobs. The SnowConvert PostgreSQL reference exists to translate DDL and SQL, so it favors keeping the source type name where a same-named Snowflake type exists, which is why it maps SMALLINT to SMALLINT. The Openflow connector documentation describes what a running pipeline actually writes, so it collapses the integer family into INT and says so. Neither is lying. They are answering different questions, and if you read only one you will get a different schema than a colleague who read the other.

The practical consequence is that "we follow Snowflake's documented mapping" is not an answer, and it is worth asking any vendor on this route which of the two they followed. On timestamptz the connector is right and the translation reference is not. On the integer family the connector is more honest, though both end at the same 38 digit number. On money, only the connector has an opinion, and it is the wrong one.

Postgres to Snowflake data type mapping table

The target column is what the two Snowflake documents produce between them, checked against the PostgreSQL manual and the Snowflake SQL reference on 3 September 2026. Rows marked "see below" are the ones that look equivalent and are not.

Eight risky mappings, and the query that proves each one is safe

This is the part no mapping table carries. For each risky conversion below there is a query you can run against Postgres today, before you load anything, that tells you whether the risk is theoretical or whether it is already sitting in your data. All eight run in an afternoon and they are the cheapest work in the entire migration. If you need them expanded per column across a few hundred tables, describing the check in plain English and letting something turn the question into SQL is a faster route than writing three hundred variants by hand.

SELECT table_schema, table_name, column_name
FROM information_schema.columns
WHERE data_type = 'money'
ORDER BY 1, 2;

SELECT table_name, column_name
FROM information_schema.columns
WHERE data_type = 'numeric'
  AND numeric_precision IS NULL;

SELECT table_name, column_name, data_type
FROM information_schema.columns
WHERE data_type IN ('smallint','integer','bigint')
  AND table_schema = 'public'
ORDER BY data_type, table_name;

SELECT table_name, column_name, data_type
FROM information_schema.columns
WHERE data_type LIKE 'timestamp%'
ORDER BY data_type;

SHOW timezone;

SELECT table_name, column_name
FROM information_schema.columns
WHERE data_type = 'time with time zone';

SELECT table_name, column_name, character_maximum_length
FROM information_schema.columns
WHERE data_type = 'character'
ORDER BY table_name;

SELECT table_name, column_name, udt_name
FROM information_schema.columns
WHERE data_type = 'ARRAY'
ORDER BY table_name;

SELECT slot_name, active, slot_type,
       pg_size_pretty(
         pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)
       ) AS retained_wal
FROM pg_replication_slots
ORDER BY retained_wal DESC;

Does Snowflake enforce primary keys and unique constraints?

No, not on standard tables. Snowflake accepts PRIMARY KEY, UNIQUE and FOREIGN KEY definitions, records them as metadata the query optimizer can use, and does not enforce any of them. Its constraints page is explicit that it does not enforce them "except for NOT NULL and CHECK constraints, which are always enforced". Hybrid tables do enforce the full set, which is one reason to consider them for the handful of tables where integrity genuinely matters.

This lands harder coming from Postgres than from anywhere else, because Postgres enforces the lot and most teams have leaned on it for years without thinking about it. The CHECK clause is the useful half of the sentence. It is what lets you rebuild the integer ranges the type collapse just removed, and it is enforced on ordinary tables with no special table type required.

Should I use NUMBER or FLOAT for money in Snowflake?

NUMBER, always. This is not a stylistic preference, it is what the source database tells you: PostgreSQL's money documentation states that "floating point numbers should not be used to handle money due to the potential for rounding errors". Snowflake's own Openflow connector nonetheless maps a Postgres money column to FLOAT by default, so unless somebody overrides it, currency arrives in your warehouse as a binary floating point approximation.

Use NUMBER(38,2) for ordinary currency, and cast on the source side with amount::numeric rather than letting the value pass through a float on its way out. While you are there, check the lc_monetary setting of the database you are reading from, because Postgres decides the fractional precision of the money type from it, and a dump restored into a differently configured database is a documented way to get this wrong.

What happens to my Postgres interval columns in Snowflake?

They become text. Snowflake states that "INTERVAL is not a data type (that is, you can't define a table column to be of data type INTERVAL). Intervals can only be used in date, time, and timestamp arithmetic." Both Snowflake mapping documents agree on this and both send the column to VARCHAR or TEXT, which is one of the few places on this route where they do not contradict each other.

The mapping is correct and the consequence is still yours to handle. Every query that added an interval to a timestamp, compared two durations, or summed elapsed time now operates on strings. The usable target is a NUMBER holding seconds plus a separate unit column, decided during the migration rather than discovered afterwards by an analyst whose report started returning nothing.

Why does my Snowflake join return fewer rows than Postgres?

Two causes account for nearly all of it on this route. The first is character(n) columns. Postgres treats trailing spaces on a character(n) value as semantically insignificant when comparing, and treats them as significant on varchar and text. Both Snowflake mappings send character(n) to a varchar-like type, so a padded product code that matched its unpadded twin in Postgres stops matching in Snowflake.

The second is case. Postgres collation is usually configured to be case sensitive already, so this route suffers less than the SQL Server one does, but any database created with a case insensitive ICU collation will behave differently in Snowflake. Normalise the join keys during the load, with RTRIM and where appropriate lower(), rather than wrapping every predicate in a function at query time and losing the pruning that makes Snowflake fast.

Do I even need to convert, now that Snowflake runs Postgres?

Worth asking, and almost nothing written about this route has caught up with it. Snowflake Postgres reached general availability on 24 February 2026 and lets you create and manage Postgres instances directly from Snowflake, each running on a dedicated virtual machine you connect to with any ordinary Postgres client. It is not a fork of Postgres, and it supports a curated extension set including PostGIS, pgvector, pg_cron and pg_stat_statements.

That does not make this page redundant, because the two products do different jobs. If you want columnar analytics over history, you still need the data in Snowflake tables and you still need every mapping decision above. If what you actually wanted was to stop operating Postgres yourself and to have one vendor, lift and shift is now a real option and it skips the type system entirely. Price both before committing to a conversion project, because the answer changed in February and most comparison content still assumes it did not.

Do it in this order

Inventory the types first, with a single query against information_schema.columns. Run the eight checks above while the source is still live and still authoritative. Write the target DDL by hand for the columns those checks flagged, which is usually under twenty columns out of several hundred, and let the tool generate the rest. Decide explicitly what happens to every unique index you are about to stop enforcing. Then load once, reconcile on values rather than counts, and only after that turn on continuous replication.

The full tool comparison for this route, including what each vendor bills by and the ten places the two Snowflake documents disagree, is on our Postgres to Snowflake migration tools page, and the connector itself is documented on the Postgres to Snowflake connector page. The mechanism underneath the ongoing sync is covered in Postgres logical replication. The equivalent treatment for the neighboring routes is in convert SQL Server data types to Snowflake and convert MySQL data types to PostgreSQL. Snowflake publishes a second pair of conflicting mappings for the Oracle route, taken apart the same way in Oracle to Snowflake data type mapping. If Postgres is staying alive alongside the warehouse, which it usually does, the ongoing case is in Snowflake ETL tools, and the budget model behind either program is in what a data migration really costs.