In this tutorial I will show main PostgreSQL data types such as
Temporal. Also I will describe when and how we can use them.
By the way, Postgres is not limited by the mentioned data types. It also supports such types like
Network Addresses, etc. The full list of supported data types could be found here: Data Types
Boolean Data Type
boolean type (could be shortened to
bool) can store only 2 possible values
NULLin case when value is unknown. But Postgres is not limited by storing only
false values into boolean columns. In boolean columns we can insert values like 1, 0, t or f and they will be interpreted as
Values interpreted as
'1' 'yes' 'y' true 'true' 't'
All these values must be enclosed in single quotes, except
Here is how we can insert
true values into a table with single boolean column:
INSERT INTO example_table VALUES ('1'); INSERT INTO example_table VALUES ('yes'); INSERT INTO example_table VALUES ('y'); INSERT INTO example_table VALUES (true); INSERT INTO example_table VALUES ('true'); INSERT INTO example_table VALUES ('t');
#####Values interpreted as
'0' 'no' 'n' false 'false' 'f'
Here’s example of how we can insert
false values into a table:
INSERT INTO example_table VALUES ('0'); INSERT INTO example_table VALUES ('no'); INSERT INTO example_table VALUES ('n'); INSERT INTO example_table VALUES (false); INSERT INTO example_table VALUES ('false'); INSERT INTO example_table VALUES ('f');
An attempt to store any other values except mentioned above will result in an error:
ERROR: column "val_1" is of type boolean but expression is of type integer LINE 1: INSERT INTO example_table VALUES (1); ^ HINT: You will need to rewrite or cast the expression.
I would like to pay attention to how boolean values are retrieved in different tools. E.g. into select results
psql display boolean values as
f for true and false respectively:
select * from example_table; val_1 ------- t t f (3 rows)
Other tools may display chackboxes for boolean values into select results.
Character Data Types
There are 3 character data types in Postgres:
char - stores single character string char(n) - stores fixed-length character strings varchar(n) - stores variable-length strings text - stores strings of any length(unlimited length character string)
text is not in SQL Standard.
Let’s take a look a little bit closer to these types.
char - stores a single (exactly 1) character, like
char(n) - stores exactly
n characters in length. Padded with spaces. An attempt to store a too long string values will result in error. It’s better to use this type when the length of the string to be stored is fixed.
Let’s assume we have a table
person with a single column
title which is type of
CREATE TABLE person ( title CHAR(5) );
Now let’s try to insert the following titles into this table:
INSERT INTO person(title) VALUES ('Madam'); INSERT INTO person(title) VALUES ('Mr'); INSERT INTO person(title) VALUES ('Master');
The first row will insert and will store the value as is. But, the second command will store the specified value with 3 spaces added to the end of the value, namely
Mr--- (Three dashes
--- here represent 3 spaces). This variables will be stored and displayed that way.
The third row will result the error:
ERROR: value too long for type character(3)
Long characters can be truncated and inserted into the following way:
INSERT INTO person(title) VALUES ('Master'::varchar(5));
In that case the value will be truncated and only
Maste will be inserted.
varchar(n) - stores strings up to
n characters in length without padding. Without specified length it stores strings of any size.
Postgres Tip: > There is no performance difference among these three types, apart from increased storage space when using the blank-padded type, and a few extra CPU cycles to check the length when storing into a length-constrained column. While character(n) has performance advantages in some other database systems, there is no such advantage in PostgreSQL; in fact character(n) is usually the slowest of the three because of its additional storage costs. In most situations text or varchar should be used instead.
Numeric Data Type
Postgres supports 2 types of numbers:
floating-point numbers. Each of these type has several subtypes:
Integer types store whole numbers without fractional components. Attempts to store values outside of the allowed range will result in an error.
smallint - a 2-byte signed integer. Stores numbers from –32768 to 32767. integer (or int) - a 4-byte integer. Stores numbers from –2147483648 to 2147483647. bigint - a 8 bytes. Stores number from -9223372036854775808 to +9223372036854775807. serial - An auto incrementing integer which is automatically entered by PostgreSQL and is used mostly for columns with primary keys.
Floating-Point Number subtypes
Floating-point subtypes provide general-purpose floating-point values and fixed-precision numbers. Such subtypes can store numbers with a very large number of digits and are good candidates for storing monetary amounts and quantities with high precision.
float(p) - stores floating-point (8 bytes) numbers with precision p. real - stores double precision (8 bytes) floating-point numbers. numeric(n,s) - stores real numbers (exact numbers) with 'n' digits and 's' digits after the decimal point.
real numbers in case when
p specified from 1 to 24. In case when
p specified from 25 to 53 float stores numbers with double precision. In case when
p is not specified the
float stores numbers with double precision.
numeric(n,s) type is preferable for storing exact numbers, such as monetary amounts.
Also it’s worth mentioning that notation like
numeric(6,2) stores only 6 digits with two of them after the decimal point. As a result the value like
1234,56 is valid and
123456,23 is not.
Also Postgres supports
money type with 8 bytes storage size. It’s an equivalent to
Temporal Data Types
Temporal data types store time-related information
date - stores date only time - stores time only without time zone timestamp - stores a date and time without time zone interval - stores a difference in timestamps timestamptz -stores a timestamp with time zone
Let’s take a look at small example in which we’re creating a table with mentioned above temporal data types:
create table temporal_data_types ( date_col date, time_col time, time_stamp_col timestamp, time_stamp_tz_col timestamptz, interval_col interval ); INSERT INTO temporal_data_types VALUES (current_date, current_time, current_timestamp, current_timestamp, '5 days');
We also may insert the data into the following way:
INSERT INTO temporal_data_types VALUES ('now', 'now', 'now', 'now', '5 days 3 hours');
now is equivalent to date, time, timestamp, timestamptz
When we select the data from the table like this
SELECT * FROM temporal_data_types;
we’ll get the following output:
"2016-11-06";"17:46:16.";"2016-11-06 17:46:16";"2016-11-06 17:46:16+03";"5 days"