PostgreSQL data types cheat sheet

2016-11-06 6 min read Development Alexey Bogdanov

In this tutorial I will show main PostgreSQL data types such as Boolean, Character, Number, 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 JSON, Monetary, Geometric , Network Addresses, etc. The full list of supported data types could be found here: Data Types

Boolean Data Type

The boolean type (could be shortened to bool) can store only 2 possible values true or false and NULLin case when value is unknown. But Postgres is not limited by storing only true and false values into boolean columns. In boolean columns we can insert values like 1, 0, t or f and they will be interpreted as true or false.

Values interpreted as `true`:

'1'
'yes'
'y'
true
'true'
't'

All these values must be enclosed in single quotes, except true.

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 `false`:

'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 pgAdmin and psql display boolean values as t or 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)

Type text is not in SQL Standard.

Let’s take a look a little bit closer to these types.

Char type

char - stores a single (exactly 1) character, like a, y, n, etc.

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 char(3):

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 type

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: integers and floating-point numbers. Each of these type has several subtypes:

Integer 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.

In Postgres float(p) stores 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 numeric(5,2).

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"