The short version
For Postgres to Snowflake data type mapping, most columns are easy: integers go to NUMBER, text and varchar to VARCHAR, jsonb to VARIANT, bytea to BINARY, timestamp to TIMESTAMP_NTZ and timestamptz to TIMESTAMP_LTZ. The trouble is in the values, not the column types: numeric with more than 38 digits, NaN and infinity, dates before 1582, and CSV that confuses NULL with an empty string. For replication, Snowflake documents the Openflow Connector for PostgreSQL (snapshot, then change data capture over logical replication). For a one-off load, export with COPY ... TO and load with PUT and COPY INTO.
The PostgreSQL side below I ran on PostgreSQL 16.14. The Snowflake statements come from Snowflake's documentation, not from a run. If you would rather hand the whole move over, see PostgreSQL to Snowflake migration.
Postgres to Snowflake data type mapping table
The right column is what Snowflake's Openflow connector maps each type to. The middle column is what I would pick when I write the Snowflake DDL by hand, where it differs.
| PostgreSQL | By hand | Openflow connector |
|---|---|---|
smallint, integer, bigint, serial |
NUMBER(38,0) (INT) |
INT |
numeric(p,s) with p up to 38 |
NUMBER(p,s) | NUMBER |
numeric with no precision |
NUMBER(38,s), pick s after measuring | NUMBER |
real, double precision |
FLOAT | FLOAT |
money |
NUMBER(19,2) | FLOAT |
boolean |
BOOLEAN | BOOLEAN |
char, varchar, text |
VARCHAR | TEXT |
bytea |
BINARY | BINARY |
date |
DATE | DATE |
time |
TIME | TIME |
timetz |
TIMESTAMP_TZ | TIMESTAMP_TZ |
timestamp |
TIMESTAMP_NTZ | TIMESTAMP_NTZ |
timestamptz |
TIMESTAMP_LTZ | TIMESTAMP_LTZ |
json, jsonb |
VARIANT | VARIANT |
uuid |
UUID or VARCHAR(36) | TEXT |
text[], int[] and other arrays |
ARRAY | not listed |
interval, inet, xml, geometric types |
VARCHAR | TEXT |
The limits behind that table:
- Snowflake NUMBER holds up to 38 digits, and the maximum scale is 37. A PostgreSQL
numericdeclared without precision has no such ceiling, so measure it before you choose a scale. - Snowflake FLOAT accepts
'NaN','inf'and'-inf'. NUMBER has no special values, while PostgreSQLnumericacceptsInfinity,-InfinityandNaNin an unconstrained column. - VARCHAR is limited to 134217728 bytes (128 MB), BINARY to 64 MB, and a VARIANT to 128 MB of uncompressed data. The Openflow connector is stricter: it replicates values up to 16 MB by default and marks the table as failed when it meets a bigger one.
- Snowflake now has a UUID data type that takes the same 36-character text form PostgreSQL prints, and a string can be cast with
::UUID. The connector still mapsuuidto TEXT. - Arrays are missing from the connector's table, and that page says any type not listed is mapped to TEXT. By hand I convert them to JSON and load them into an ARRAY.
- I would override the connector's
moneyto FLOAT; see Money Stored as Float.
timestamp vs timestamptz: TIMESTAMP_NTZ, TIMESTAMP_TZ or TIMESTAMP_LTZ?
PostgreSQL stores a timestamptz internally as UTC and does not keep the original zone; on output it converts to the session's TimeZone. Snowflake's TIMESTAMP_LTZ stores UTC values and works in the session time zone, which is the same idea. TIMESTAMP_TZ stores UTC plus an offset, and only the offset, not the named zone. TIMESTAMP_NTZ stores wall-clock time with no zone at all, so it is the match for plain timestamp.
One trap on the Snowflake side: a column declared as just TIMESTAMP follows the TIMESTAMP_TYPE_MAPPING parameter, and the default is TIMESTAMP_NTZ. Write the variant out in every DDL statement.
The other trap is on the PostgreSQL side, and I hit it in the run below: the text that COPY writes for a timestamptz depends on the session time zone of whoever runs the export.
What I ran: a table with the awkward types
A scratch database on the QA PostgreSQL 16 container, one table, three rows that cover the values that break loads:
CREATE TABLE orders (
id bigint PRIMARY KEY,
ext_id uuid,
amount numeric(12,2),
ratio numeric,
score double precision,
placed_at timestamptz,
local_at timestamp,
ship_date date,
payload jsonb,
tags text[],
photo bytea,
note text
);
INSERT INTO orders VALUES
(1, 'f353ca91-4fc5-49f2-9b9e-304f83d11914', 1234.50, 0.1234567890123456789012345678901234567890,
1.5, '2026-03-29 01:30:00+00', '2026-03-29 01:30:00', '2026-03-29',
'{"sku": "A-1", "qty": 2, "note": "say \"hi\", ok"}', ARRAY['red','big, heavy'], '\x0001ff', 'line one
line two'),
(2, NULL, -0.01, 'NaN', 'NaN', 'infinity', '-infinity', 'infinity',
'null', '{}', '', ''),
(3, NULL, NULL, 'Infinity', '-Infinity', '0044-03-15 12:00:00+00 BC', '1900-01-01 00:00:00', '0001-01-01',
NULL, ARRAY[NULL,'x'], NULL, NULL);
Then a plain export, the way most people start. The Docker CLI on my machine hung that day, so I sent COPY ... TO STDOUT through psycopg2's copy_expert against the container's port instead of docker exec psql; the server output is the same. First with TimeZone set to UTC:
id,ext_id,amount,ratio,score,placed_at,local_at,ship_date,payload,tags,photo,note
1,f353ca91-4fc5-49f2-9b9e-304f83d11914,1234.50,0.1234567890123456789012345678901234567890,1.5,2026-03-29 01:30:00+00,2026-03-29 01:30:00,2026-03-29,"{""qty"": 2, ""sku"": ""A-1"", ""note"": ""say \""hi\"", ok""}","{red,""big, heavy""}",\x0001ff,"line one
line two"
2,,-0.01,NaN,NaN,infinity,-infinity,infinity,null,{},\x,""
3,,,Infinity,-Infinity,0044-03-15 12:00:00+00 BC,1900-01-01 00:00:00,0001-01-01,,"{NULL,x}",,
The same COPY after SET TIME ZONE 'America/New_York':
1,...,2026-03-28 21:30:00-04,2026-03-29 01:30:00,...
...
3,,,Infinity,-Infinity,0044-03-15 07:03:58-04:56:02 BC,1900-01-01 00:00:00,0001-01-01,,"{NULL,x}",,
The problems that output shows
Counting them in the source before exporting:
SELECT
count(*) FILTER (WHERE ratio IN ('NaN','Infinity','-Infinity')) AS numeric_special,
count(*) FILTER (WHERE score IN ('NaN','Infinity','-Infinity')) AS float_special,
count(*) FILTER (WHERE NOT isfinite(placed_at) OR placed_at < '1582-01-01') AS ts_out_of_range,
count(*) FILTER (WHERE NOT isfinite(ship_date)) AS date_infinite,
max(scale(ratio)) AS max_scale
FROM orders;
-- numeric_special | float_special | ts_out_of_range | date_infinite | max_scale
-- 2 | 2 | 2 | 1 | 40
timestamptzmoved by four hours between the two exports, and the BC row came out with a-04:56:02offset, the kind of seconds-level offset PostgreSQL says appears only for timestamps that predate standardized time zones. Export in UTC, always.ratiohas a scale of 40. Snowflake NUMBER stops at 37, so this column needs a decision (I rounded to 18), and itsNaNandInfinityhave nowhere to go in NUMBER.infinityand-infinityintimestampanddateare special values in PostgreSQL with no Snowflake equivalent. Snowflake also recommends years between 1582 and 9999, and row 3 has a date in year 1 and a timestamp in 44 BC.jsonbcame out with its keys reordered (qtybeforesku). That is by design: jsonb does not preserve key order, white space or duplicate keys.- Row 2's
payloadis the JSON valuenull, and row 3's is SQL NULL. PostgreSQL treats these as different things, and so does Snowflake, where a VARIANT null is a real value, distinct from SQL NULL. Keep them apart. - Arrays come out in PostgreSQL's own
{red,"big, heavy"}literal syntax, which nothing in Snowflake parses. byteacomes out with a\xprefix, the default hex output format. Snowflake'sBINARY_FORMAT = HEXwants the hex digits without it.- NULL versus empty string: with default settings, PostgreSQL CSV writes NULL as an unquoted empty field and an empty string as
"", and Snowflake's CSV loader hasEMPTY_FIELD_AS_NULLset to TRUE by default. Too fragile to depend on.
Does Parquet avoid all this?
I tried it. DuckDB 1.5.5 with its postgres extension can attach the database and write Parquet in two lines, which is tempting:
ATTACH 'host=127.0.0.1 port=15432 user=postgres password=qa dbname=pg_to_snowflake' AS pg (TYPE postgres, READ_ONLY);
COPY (SELECT * FROM pg.public.orders ORDER BY id) TO 'orders.parquet' (FORMAT parquet);
DESCRIBE showed what it did with my columns: the unconstrained numeric became DOUBLE, jsonb became VARCHAR, the array became VARCHAR[]. Reading the file back:
┌───────┬─────────────────────┬────────┬─────────────────┬─────────────────────┬──────────────┐
│ id │ ratio │ score │ payload_is_null │ tags │ photo │
│ int64 │ double │ double │ boolean │ varchar[] │ blob │
├───────┼─────────────────────┼────────┼─────────────────┼─────────────────────┼──────────────┤
│ 1 │ 0.12345678901234569 │ 1.5 │ false │ [red, 'big, heavy'] │ \x00\x01\xFF │
│ 2 │ 0.0 │ nan │ false │ [] │ │
│ 3 │ 0.0 │ -inf │ true │ [NULL, x] │ NULL │
└───────┴─────────────────────┴────────┴─────────────────┴─────────────────────┴──────────────┘
The numeric NaN and Infinity became 0.0, with no warning. The precise value in row 1 lost digits to a double. The infinite timestamps were written as raw integers:
placed_at (int64 microseconds): [1774747800000000, 9223372036854775807, -63517780800000000]
ship_date (int32 days): [20541, 2147483647, -719162]
That is the largest int64 and int32 standing in for infinity. A loader will read those as real dates far in the future, or reject them. So Parquet keeps types better for the easy columns and hides the hard ones. Clean the special values in SQL first, whatever writes the files.
The export I would actually use
One query that settles each problem in PostgreSQL:
SET TIME ZONE 'UTC';
COPY (
SELECT id,
ext_id,
amount,
CASE WHEN ratio IN ('NaN','Infinity','-Infinity') THEN NULL
ELSE round(ratio, 18) END AS ratio,
CASE WHEN score = 'Infinity' THEN 'inf'
WHEN score = '-Infinity' THEN '-inf'
ELSE score::text END AS score,
CASE WHEN isfinite(placed_at) AND placed_at >= '1582-01-01'
THEN to_char(placed_at, 'YYYY-MM-DD"T"HH24:MI:SS.US"+00:00"') END AS placed_at,
CASE WHEN isfinite(local_at) THEN local_at END AS local_at,
CASE WHEN isfinite(ship_date) THEN ship_date END AS ship_date,
payload::text AS payload,
to_jsonb(tags)::text AS tags,
encode(photo, 'hex') AS photo,
note
FROM orders ORDER BY id
) TO STDOUT WITH (FORMAT csv, HEADER, NULL '\N', FORCE_QUOTE *);
Output:
id,ext_id,amount,ratio,score,placed_at,local_at,ship_date,payload,tags,photo,note
"1","f353ca91-4fc5-49f2-9b9e-304f83d11914","1234.50","0.123456789012345679","1.5","2026-03-29T01:30:00.000000+00:00","2026-03-29 01:30:00","2026-03-29","{""qty"": 2, ""sku"": ""A-1"", ""note"": ""say \""hi\"", ok""}","[""red"", ""big, heavy""]","0001ff","line one
line two"
"2",\N,"-0.01",\N,"NaN",\N,\N,\N,"null","[]","",""
"3",\N,\N,\N,"-inf",\N,"1900-01-01 00:00:00","0001-01-01",\N,"[null, ""x""]",\N,\N
Why each choice:
NULL '\N'matches Snowflake's defaultNULL_IFof\N. Now NULL is\Nand an empty string is"", which no loader can mix up.FORCE_QUOTE *quotes every non-NULL value. Snowflake'sESCAPE_UNENCLOSED_FIELDdefaults to a backslash and applies to unenclosed fields only, and the docs warn that a row ending in a backslash gets merged with the next row. With everything quoted, the only unenclosed value left is\N.- The timestamp is written as
2026-03-29T01:30:00.000000+00:00, one of the ISO forms Snowflake lists for automatic timestamp detection. A trailingZis not in that list. 'inf'and'-inf'are the spellings the Snowflake FLOAT docs give.to_jsonb(tags)turns the PostgreSQL array into a JSON array, which Snowflake can parse.- Row 3's
0001-01-01date is still there. It is finite, so my filter let it through, and it is outside Snowflake's recommended range. Decide what such dates mean in your data; the Openflow connector replaces out-of-range dates with placeholders such as 9999-12-31, which is one answer.
I dropped the scratch database afterwards.
Loading it: PUT and COPY INTO (from the Snowflake docs, not run)
Local files go up to a stage with PUT, which gzips them by default and cannot be run from a Snowsight worksheet; use the Snowflake CLI or a driver. Snowflake suggests files of roughly 100-250 MB compressed, so split big tables.
I load into an all-VARCHAR staging table first, because VALIDATION_MODE does not work with a COPY that transforms data, and I want to validate:
CREATE FILE FORMAT pg_csv
TYPE = CSV
SKIP_HEADER = 1
FIELD_OPTIONALLY_ENCLOSED_BY = '"'
EMPTY_FIELD_AS_NULL = FALSE;
CREATE STAGE pg_stage FILE_FORMAT = pg_csv;
PUT file:///tmp/orders.csv @pg_stage;
COPY INTO orders_raw FROM @pg_stage VALIDATION_MODE = RETURN_ERRORS;
COPY INTO orders_raw FROM @pg_stage;
INSERT INTO orders
SELECT id::NUMBER(38,0),
ext_id::UUID,
amount::NUMBER(12,2),
ratio::NUMBER(38,18),
score::FLOAT,
placed_at::TIMESTAMP_LTZ,
local_at::TIMESTAMP_NTZ,
ship_date::DATE,
PARSE_JSON(payload),
PARSE_JSON(tags)::ARRAY,
TO_BINARY(photo, 'HEX'),
note
FROM orders_raw;
BINARY_FORMAT defaults to HEX, which is why the export used encode(photo, 'hex'). If you skip the staging table, PARSE_JSON and TO_BINARY are both on the list of functions allowed in a COPY transformation.
PostgreSQL to Snowflake replication and CDC options
For ongoing replication rather than a one-time copy, Snowflake documents two connectors.
The Openflow Connector for PostgreSQL does a snapshot load of each table, then applies changes incrementally. Points from its docs that decide whether it fits:
- Supports PostgreSQL 11 through 18, including RDS, Aurora, Cloud SQL and Azure Database.
- UPDATE and DELETE need an identity key: a primary key, a unique index with
REPLICA IDENTITY USING INDEX, or a declared key withREPLICA IDENTITY FULL. Without one, only INSERTs replicate. - TRUNCATE on the source is ignored.
- Deleted rows are soft-deleted in Snowflake.
On the PostgreSQL side this is logical replication, which needs wal_level set to logical and a publication, with a replica identity on every table that gets UPDATEs or DELETEs. wal_level can only be set at server start, so if it is still replica, plan a restart.
The older Snowflake Connector for PostgreSQL still has docs, but Snowflake says general availability is not on its roadmap and points to Openflow. It also does not replicate tables with TOAST values.
The CDC pattern itself, and how to cut over without downtime, is the subject of Zero Downtime Database Migration with CDC. Once the data is in, the SQL differences you meet next are in PostgreSQL vs Snowflake SQL: What Changes, and building models on top is in dbt on Snowflake.
Sources
- Snowflake: Openflow Connector for PostgreSQL
- Snowflake: Openflow PostgreSQL data type mapping
- Snowflake: Snowflake Connector for PostgreSQL
- Snowflake: Numeric data types
- Snowflake: String and binary data types
- Snowflake: Date and time data types
- Snowflake: Semi-structured data types
- Snowflake: UUID data type
- Snowflake: Date and time input and output formats
- Snowflake: COPY INTO table
- Snowflake: CREATE FILE FORMAT
- Snowflake: Transforming data during a load
- Snowflake: PUT
- Snowflake: Preparing your data files
- PostgreSQL: Numeric types
- PostgreSQL: Date/time types
- PostgreSQL: JSON types
- PostgreSQL: Binary data types
- PostgreSQL: COPY
- PostgreSQL: Write ahead log settings
- PostgreSQL: Logical replication configuration
- PostgreSQL: Logical replication publications