Author Avatar



Share post:

PostgreSQL supports many data types some of which have been mentioned below. The full list of data types in Postgres is here.

General usage data types

bigintint8signed eight-byte integer
bigserialserial8autoincrementing eight-byte integer
bit [ (n) ] fixed-length bit string
bit varying [ (n) ]varbit [ (n) ]variable-length bit string
booleanboollogical Boolean (true/false)
box rectangular box on a plane
bytea binary data (“byte array”)
character [ (n) ]char [ (n) ]fixed-length character string
character varying [ (n) ]varchar [ (n) ]variable-length character string
cidr IPv4 or IPv6 network address
circle circle on a plane
date calendar date (year, month, day)
double precisionfloat8double precision floating-point number (8 bytes)
inet IPv4 or IPv6 host address
integerintint4signed four-byte integer
interval [ fields ] [ (p) ] time span
json textual JSON data
jsonb binary JSON data, decomposed
line infinite line on a plane
lseg line segment on a plane
macaddr MAC (Media Access Control) address
macaddr8 MAC (Media Access Control) address (EUI-64 format)
money currency amount
numeric [ (ps) ]decimal [ (ps) ]exact numeric of selectable precision
path geometric path on a plane
pg_lsn PostgreSQL Log Sequence Number
pg_snapshot user-level transaction ID snapshot
point geometric point on a plane
polygon closed geometric path on a plane
realfloat4single precision floating-point number (4 bytes)
smallintint2signed two-byte integer
smallserialserial2autoincrementing two-byte integer
serialserial4autoincrementing four-byte integer
text variable-length character string
time [ (p) ] [ without time zone ] time of day (no time zone)
time [ (p) ] with time zonetimetztime of day, including time zone
timestamp [ (p) ] [ without time zone ] date and time (no time zone)
timestamp [ (p) ] with time zonetimestamptzdate and time, including time zone
tsquery text search query
tsvector text search document
txid_snapshot user-level transaction ID snapshot (deprecated; see pg_snapshot)
uuid universally unique identifier
xml XML data

One can determine the type of the data using the in-built function ‘pg_typeof’.

select pg_typeof(10);
select pg_typeof(10.0);
select pg_typeof(10.34);
select pg_typeof(now());
select pg_typeof(True);
select pg_typeof(point(1.2, 123.1));
select pg_typeof(line(point(1.2, 123.1), point(-5, -123)));
select pg_typeof(circle(point(1.2, 123.1), 10));

In-depth with psql
Casting of Data Types

Leave a Comment

Your email address will not be published. Required fields are marked *