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
| Name | Aliases | Description |
|---|
bigint | int8 | signed eight-byte integer |
bigserial | serial8 | autoincrementing eight-byte integer |
bit [ (n) ] | | fixed-length bit string |
bit varying [ (n) ] | varbit [ (n) ] | variable-length bit string |
boolean | bool | logical 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 precision | float8 | double precision floating-point number (8 bytes) |
inet | | IPv4 or IPv6 host address |
integer | int, int4 | signed 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 [ (p, s) ] | decimal [ (p, s) ] | 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 |
real | float4 | single precision floating-point number (4 bytes) |
smallint | int2 | signed two-byte integer |
smallserial | serial2 | autoincrementing two-byte integer |
serial | serial4 | autoincrementing four-byte integer |
text | | variable-length character string |
time [ (p) ] [ without time zone ] | | time of day (no time zone) |
time [ (p) ] with time zone | timetz | time of day, including time zone |
timestamp [ (p) ] [ without time zone ] | | date and time (no time zone) |
timestamp [ (p) ] with time zone | timestamptz | date 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));
admin
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
bigintint8bigserialserial8bit [ (n) ]bit varying [ (n) ]varbit [ (n) ]booleanboolboxbyteacharacter [ (n) ]char [ (n) ]character varying [ (n) ]varchar [ (n) ]cidrcircledatedouble precisionfloat8inetintegerint,int4interval [fields] [ (p) ]jsonjsonblinelsegmacaddrmacaddr8moneynumeric [ (p,s) ]decimal [ (p,s) ]pathpg_lsnpg_snapshotpointpolygonrealfloat4smallintint2smallserialserial2serialserial4texttime [ (p) ] [ without time zone ]time [ (p) ] with time zonetimetztimestamp [ (p) ] [ without time zone ]timestamp [ (p) ] with time zonetimestamptztsquerytsvectortxid_snapshotpg_snapshot)uuidxmlOne 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));