In this article we will look into the various data types available in PostgreSQL. The following data types are supported by PostgreSQL:
- Character Types [ such as
- Numeric Types [ such as
- Temporal Types [ such as
- UUID [ for storing
UUID(Universally Unique Identifiers) ]
- Array [ for storing array
- JSON [ stores
- hstore [ stores
- Special Types [ such as
Now let’s get an overview of the above-mentioned data types.
A Boolean data type can hold one of three values namely, true, false or null. The
boolean keyword is used to create a column with Boolean data type. Insertation of data into a Boolean column converts it into the following:
- 1, yes, y, t, true values are converted to true
- 0, no, false, f values are converted to false
Selection of data from a Boolean column converts value back as below:
- t to true
- f to false
- space to null
PostgreSQL has three character data types namely,
- CHAR(n) is the fixed-length character with padded spaces. If you insert or add a string that is shorter than the length of the column, PostgreSQL pads the remaining spaces. If you insert a string that is longer than the length of the column, PostgreSQL raises an error.
- VARCHAR(n) is the variable-length character string. With VARCHAR(n), you can store up to n characters. No padding of spaces is done when the stored string is shorter than the length of the column.
- TEXT is the variable-length character string. Theoretically, text data is a character string with unlimited length.
PostgreSQL has 2 types of numbers namely, integers and floating point numbers.
- Small integer ( SMALLINT) is 2-byte signed integer that has a range from -32, 768 to 32, 767.
- Integer ( INT) is a 4-byte integer that has a range from -2, 147, 483, 648 to 2, 147, 483, 647.
- Serial is the same as integer except that PostgreSQL will automatically generate and populate values into the SERIAL column.
- Floating-point number:
- float(n) is a floating-point number whose precision, at least, n, up to a maximum of 8 bytes.
- realor float8is a 4-byte floating-point number.
- numeric or numeric(p, s) is a real number with p digits with s number after the decimal point. The numeric(p, s) is the exact number.
Temporal data type –
This data type allows user to store date and time data. PostgreSQL has 5 temporal data type:
- DATE stores the dates only.
- TIME stores the time of day values.
- TIMESTAMP stores both date and time values.
- TIMESTAMPTZ is a timezone-aware timestamp data type.
- INTERVAL stores periods of time.
In the PostgreSQL an array column can be used to store an array of strings or an array of integers etc. It can be handy when storing data likes storing days of months, a year, or even a week, etc.
PostgreSQL supports 2 type of JSON types namely JSON and JSONB(Binary JSON).The JSON data type stores plain JSON data that requires reparsing for each processing, while JSONB data type stores JSON data in a binary format which is faster to process but slower to insert. In addition, JSONB supports indexing, which can be an advantage.
The UUID data type allows you to store Universal Unique Identifiers defined by RFC 4122. The UUID values guarantee a better uniqueness than SERIAL and can be used to hide sensitive data exposed to the public such as values of id in URL.
Special data types –
In addition to the primitive data types, PostgreSQL also supports some special data types that are related to network or geometric. These special data types are listed below:
- box– a rectangular box.
- point– a geometric pair of numbers.
- lseg– a line segment.
- point– a geometric pair of numbers.
- polygon– a closed geometric.
- inet– an IP4 address.
- macaddr– a MAC address.
- PostgreSQL - NUMERIC Data Type
- PostgreSQL - VARCHAR Data Type
- PostgreSQL - TEXT Data Type
- PostgreSQL - hstore Data Type
- PostgreSQL - Date Data Type
- PostgreSQL - Array Data Type
- PostgreSQL - Interval Data Type
- PostgreSQL - TIME Data Type
- PostgreSQL - UUID Data Type
- PostgreSQL - JSON Data Type
- PostgreSQL - Timestamp Data Type
- PostgreSQL - CHAR Data Type
- PostgreSQL - INTEGER Data Type
- PostgreSQL - Boolean Data Type
- PostgreSQL - User-defined Data Type
- PostgreSQL - BIGINT Integer Data Type
- PostgreSQL - SMALLINT Integer Data Type
- PostgreSQL - CTE
- PostgreSQL - INSERT
- PostgreSQL - EXCEPT Operator
If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to email@example.com. See your article appearing on the GeeksforGeeks main page and help other Geeks.
Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.