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) | Fixed-length bit string Where size is the length of the bit string. |
| varbit(size) bit varying(size) | Variable-length bit string 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 | Big integer value which is equivalent to int8. 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 | Big auto-incrementing integer value which is equivalent to serial8. 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. |
