Home / Guides
Migration guide

PostgreSQL to Snowflake: Type Mapping and Replication

By · October 9, 2026 · 14 min read

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:

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

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:

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:

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