Skip to content
IntoSQL home
Convert a file

Data types

PostgreSQL data types between versions

Every PostgreSQL data type an older or newer PostgreSQL stores differently, what the converter writes for it, and the report line.

View as Markdown

Each row is a type from PostgreSQL that an older or newer PostgreSQL stores differently: what the script gets, why, and the line the report writes about it. The rows come from the converter's own rules, so this page matches what it does.

Convert in the browser: PostgreSQL to PostgreSQL opens the converter set up for this pair. The PostgreSQL to PostgreSQL migration guide covers dumping, loading and the rules for keys, indexes and names.

A report line is a fix when the converter changed something and lists it, or a problem when a value or object is worth a look.

Numbers

In the dump In the script Why Report line
numeric(p,s), numeric without a precision, NaN and Infinity the same types and values The same type: every digit is copied as the dump wrote it. None
money numeric(19,2) How money is written depends on the server's lc_monetary, so the amount is kept as a number without the currency symbol, and it loads whatever the target's locale. type_replaced (fix)

Text

In the dump In the script Why Report line
varchar(n), char(n), text the same types The same types, with the same lengths. None
text that isn't valid in its encoding the bad bytes as the replacement character PostgreSQL text must be valid UTF-8. invalid_utf8_replaced (problem)

Enums and sets

In the dump In the script Why Report line
enum types the same enum types, created before the tables The labels keep their order. None
enum columns holding values outside their labels text without a check The type would reject those values, so the column is text and keeps all of them. enum_check_skipped (problem)

Dates and times

In the dump In the script Why Report line
timestamp with time zone timestamptz, the same instants Each value keeps the offset the dump wrote, so the instant is the same in any time zone. None
time, time with time zone the same types The same types, 24:00:00 included. None
infinity, -infinity and BC dates kept PostgreSQL holds them as they are. None

JSON, spatial and other types

In the dump In the script Why Report line
composite and range types the dump defines, extension types (citext, hstore, ltree, PostGIS) and multiranges text with PostgreSQL's text The script creates neither extensions nor composite types, so each value keeps the text PostgreSQL wrote; create the type and alter the column after loading. type_replaced (fix)
json, jsonb the same types json keeps the text as written; jsonb keeps the values as the server normalized them. None
NaN and Infinity in numeric, real and double precision kept PostgreSQL holds them as they are. None
types IntoSQL doesn't know text Text holds any value; change the type after loading. unknown_type (problem)

Other pairs