Postgresql Data Types

PostgreSQL: Data Types

The following is a list of datatypes available in PostgreSQL, which includes string, numeric, and date/time datatypes.

String Datatypes

The following are the String Datatypes in PostgreSQL:

Data Type Syntax Explanation
char(size) Where size is the number of characters to store. Fixed-length strings. Space padded on right to equal size characters.
character(size) Where size is the number of characters to store. Fixed-length strings. Space padded on right to equal size characters.
varchar(size) Where size is the number of characters to store. Variable-length string.
character varying(size) Where size is the number of characters to store. Variable-length string.
text Variable-length string.

Numeric Datatypes

The following are the Numeric Datatypes in PostgreSQL:

Data Type Syntax Explanation
bit(size) Where size is the length of the bit string.
varbit(size) bit varying(size) Where size is the length of the bit string.
smallint Equivalent to int2. 2-byte signed integer.
int Equivalent to int4. 4-byte signed integer.
integer Equivalent to int4. 4-byte signed integer.
bigint 8-byte signed integer.
smallserial Small auto-incrementing integer value which is equivalent to serial2. 2-byte signed integer that is auto-incrementing.
serial Auto-incrementing integer value which is equivalent to serial4. 4-byte signed integer that is auto-incrementing.
bigserial . 8-byte signed integer that is auto-incrementing.
numeric(m,d) Where m is the total digits and d is the number of digits after the decimal.
double precision 8 byte, double precision, floating-point number
real 4-byte, single precision, floating-point number
money Currency value.
bool Logical boolean data type - true or false
boolean Logical boolean data type - true or false

Date/Time Datatypes

The following are the Date/Time Datatypes in PostgreSQL:

Data Type Syntax Explanation
date Displayed as 'YYYY-MM-DD'.
timestamp Displayed as 'YYYY-MM-DD HH:MM:SS'.
timestamp without time zone Displayed as 'YYYY-MM-DD HH:MM:SS'.
timestamp with time zone Displayed as 'YYYY-MM-DD HH:MM:SS-TZ'. Equivalent to timestamptz.
time Displayed as 'HH:MM:SS' with no time zone.
time without time zone Displayed as 'HH:MM:SS' with no time zone.
time with time zone Displayed as 'HH:MM:SS-TZ' with time zone. Equivalent to timetz.