Skip to content
IntoSQL home
Convert a file

Data types

PostgreSQL to SQLite data types

Every PostgreSQL data type that SQLite stores differently, what the converter writes for it, when, and the report line.

View as Markdown

Each row is a type from PostgreSQL that SQLite stores differently: what the SQLite 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 SQLite opens the converter set up for this pair. The PostgreSQL to SQLite 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 PostgreSQL In SQLite Why Report line
numeric(p,s) past 15 digits and numeric without a precision TEXT SQLite keeps about 15 significant digits in a number. Wider numbers are kept as text so every digit stays exact. decimal_as_text (fix)
money NUMERIC SQLite has no money type. The amount is kept as a number, without the currency symbol the server's locale added. type_replaced (fix)
oid, xid, cid INTEGER with a CHECK that it isn't negative SQLite's 64-bit integers hold every value. None

Booleans and bits

In PostgreSQL In SQLite Why Report line
boolean INTEGER 0 or 1 with a CHECK SQLite has no boolean type; its own true and false are 1 and 0. None
bit(n), bit varying INTEGER with the bits' value up to 63 bits; TEXT digits beyond A bit string is stored as the number its bits make while it fits a signed integer. None

Text

In PostgreSQL In SQLite Why Report line
varchar(n), char(n), text VARCHAR(n) and TEXT, not enforced SQLite keeps the declared type for tools to read but doesn't limit the length. None
text that isn't valid in its encoding the bad bytes as the replacement character SQLite text is UTF-8. invalid_utf8_replaced (problem)

Enums and sets

In PostgreSQL In SQLite Why Report line
enum types TEXT with a CHECK of its labels Text with a check keeps the same values. None
enum columns holding values outside their labels TEXT without a check Values the labels don't name would fail the check, so the check isn't created. enum_check_skipped (problem)

Dates and times

In PostgreSQL In SQLite Why Report line
interval TEXT with PostgreSQL's text SQLite has no interval type; the text keeps every part. type_replaced (fix)
timestamp DATETIME as ISO 8601 text SQLite stores dates as text its date functions read. None
timestamp with time zone TIMESTAMP as ISO 8601 text in UTC SQLite has no type that keeps an offset, so each moment is written in UTC: the same instant whatever offset the dump wrote. moments_as_utc (fix)
time TIME as text SQLite keeps times as text, 24:00:00 included. None
infinity, -infinity and BC dates NULL SQLite's date functions read years 0 to 9999 without infinity. Those values become NULL and the report counts them. date_out_of_range (problem)

Binary

In PostgreSQL In SQLite Why Report line
bytea BLOB Bytes stay bytes. None

JSON, spatial and other types

In PostgreSQL In SQLite Why Report line
arrays (integer[], text[]...) TEXT with a JSON array SQLite has no arrays. A JSON array keeps every item and NULL, and SQLite's JSON functions read it. type_replaced (fix)
ranges, geometric types, tsvector, xml, hstore, PostGIS, composite and extension types TEXT with PostgreSQL's text SQLite has no equivalent type, so each value keeps the text PostgreSQL wrote. type_replaced (fix)
json, jsonb TEXT SQLite's JSON functions read JSON from text. None
NaN and Infinity in numeric, real and double precision NULL SQLite stores NaN as NULL and its infinity doesn't survive every tool, so those values become NULL and the report counts them. invalid_value_null (problem)
uuid, inet, cidr, macaddr TEXT SQLite has no such types; the text keeps every value. None
types IntoSQL doesn't know TEXT Text holds any value; change the type after loading. unknown_type (problem)

Counters and identities

In PostgreSQL In SQLite Why Report line
serial, identity INTEGER PRIMARY KEY AUTOINCREMENT, continuing after the highest id SQLite counts a whole integer primary key, and AUTOINCREMENT keeps ids from being reused, as a sequence does. None
serial and identity columns that aren't the whole primary key INTEGER without a counter SQLite only counts a column that is the whole primary key, so the column keeps its values and new rows need theirs. counter_dropped (problem)

Other pairs