PostgreSQL data types

PostgreSQL data types – PostgreSQL supports a rich palette of different data types, among which can be conventionally divided into subgroups: numeric, character, logical, date and time, binary and a number of others.

Numerical data types

• Serial: represents an auto-incrementing numeric value that takes 4 bytes and can store numbers from 1 to 2147483647. The value of this type is formed by auto-incrementing the value of the previous string. Therefore, as a rule, this type is used to define string identifiers.
• It represents an autoincremented numeric value, which takes 2 bytes and can store numbers from 1 to 32767. It is an analog of the serial type for small numbers.
• Smallserial: represents an auto-incrementing numeric value that takes 8 bytes and can store numbers from 1 to 9223372036854775807. Analog of the serial type for large numbers.
• Smallint: stores numbers from -32768 to +32767. Occupies 2 bytes. Has an int2 pseudonym.
• Integer: stores numbers from -2147483648 to +2147483647. Occupies 4 bytes. Has nicknames int and int4.
• Stores numbers from -9223372036854775808 to +9223372036854775807. Occupies 8 bytes. Has an alias of int8.
• Numeric: stores numbers with fixed precision, which can have up to 131072 characters in whole and 16383 decimal places.

This type can accept two precision and scale parameters: numeric(precision, scale).

The precision parameter indicates the maximum number of digits that a number can store.

The scale parameter represents the maximum number of digits that a number can contain after the decimal point. This value must range from 0 to the precision value. By default, it is 0.

For example, for number 23.5141 precision is 6, and scale is 4.

• Decimal: stores fixed precision numbers, which can have up to 131072 characters in the whole part and up to 16383 characters in the fractional part. Same as numeric.
• Real: stores floating point numbers from 1E-37 to 1E+37. Occupies 4 bytes. Has an alias of float4.
• Double precision: stores floating-point numbers from 1E-307 to 1E+308. Occupies 8 bytes. Has a nickname of float8.

Examples of usage:

Id SERIAL,
TotalWeight NUMERIC(9,2),
Age INTEGER,
Surplus REAL

Types for working with currency (monetary units)

To work with monetary units, there is a type of money that can take values in the range from -92233720368547758.08 to +92233720368547758.07 and takes 8 bytes.

Symbolic types

• character(n): represents a string of fixed characters. The parameter is used to specify the number of characters in the string. It has an alias char(n).
• character varying(n): represents a string of a fixed number of characters. This parameter is used to specify the number of characters in a string. Has the nickname varchar(n).
• text: represents text of any length.
• Binary data

The bytea type has been defined for storing binary data. It stores data as binary strings that represent a sequence of octets or bytes.

Types for working with dates and times

• timestamp: stores the date and time. Occupies 8 bytes. For dates, the lowest value is 4713 BC, the highest value is 294276 AD.
• timestamp with time zone: Same as timestamp, only adds time zone data.
• date: represents the date from 4713 BC to 5874897 AD. Occupies 4 bytes.
• time: stores the time to within 1 microsecond without specifying a time zone. Accepts values from 00:00:00 to 24:00:00. Occupies 8 bytes.
• time with time zone: Stores the time to within 1 microsecond with time zone. Accepts values from 00:00:00+1459 to 24:00:00:00-1459. Occupies 12 bytes.
• represents the time interval. Occupies 16 bytes.

Common date formats:

• yyyy-mm-dd – 1999-01-08
• Month dd, yyyy – January 8, 1999
• mm/dd/yyy – 1/8/1999

Common time formats:

• hh:mi – 13:21
• hh:mi am/pm – 1:21 pm
• hh:mi:ss – 1:21:34

Logical type

The boolean type can store one of two values: true or false.

You can specify the following values instead of true: TRUE, ‘t’, ‘true’, ‘y’, ‘yes’, ‘on’, ‘1’.

The following values can be specified instead of false: FALSE, ‘f’, ‘false’, ‘n’, ‘no’, ‘off’, ‘0’.

Types for presenting Internet addresses

• cidr: Internet address in IPv4 and IPv6 format. For example, 192.168.0.1. Occupies from 7 to 19 bytes.
• inet: Internet address in cidr/y format, where cidr is an address in IPv4 or IPv6 format, and /y is the number of bits in the address (if this parameter is not specified, it is used 34 for IPv4, 128 for IPv6). For example, 192.168.0.1/24 or 2001:4f8:3:ba:2e0:81ff:fe22:d1f1/128. Occupies 7 to 19 bytes.
• macaddr: stores the MAC address. Occupies 6 bytes.
• macaddr8: stores the MAC address in EUI-64 format. Occupies 8 bytes.

Geometric types

• point: represents a point on a plane in (x,y) format. Occupies 16 bytes.
• line: represents a line of undefined length in the format {A,B,C}. Occupies 32 bytes.
• lseg: represents the segment in the format ((x1,y1),(x2,y2)). Occupies 32 bytes.
• box: represents a rectangle in the format ((x1,y1), (x2,y2)). Occupies 32 bytes.
• path: represents a set of connected points. In ((x1,y1),…) format the path is closed (the first and the last point are connected by a line) and actually represents a polygon. In [(x1,y1),…] format, the path is an open path Occupies 16+16n bytes.
• polygon: represents a polygon in the format ((x1,y1),…). Occupies 40+16n bytes.
• circle: represents the circle in the format <(x,y),r>. Occupies 24 bytes.

Other data types

• json: stores json data in text form.
• jsonb: stores json data in binary format.
• uuid: stores a universally unique identifier (UUID) such as a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11. Occupies 32 bytes.
• xml: stores data in XML format.

There is one more obstacle before us, which we haven’t dealt with yet, – generation of unique keys of such fields as customer_id and item_id.

You will of course remember that each row of the table must be uniquely identifiable, and that where there is no obvious set of columns that could be used to achieve this goal, an additional column is added, a column with a unique identifier.

An integer or character field comes to mind, and by adding each new row, a new unique value for the column must be generated.

Since the need to introduce an additional unique column is encountered in database planning everywhere, there is also an integrated solution, the new SERIAL data type.

This special type is an integer that automatically increases when adding a row to a table and sets a new unique value when adding each row. If a new row is entered into a table having a column of the SERIAL type without specifying any value for this column, the DBMS will automatically assign the next value itself.

Most databases do not take the deleted rows into account when assigning consecutive values. The number being assigned is simply increased for each new row. The project planning tool with which the above diagrams were executed shows the fields of the SERIAL type as INTEGER because this type is the basic one.

Next we’ll look at other PostgreSQL data types and find that some of them are more suitable for storing monetary values than NUMERIC, because while NUMERIC is very convenient, it is not the most efficient way to store floating point numbers in PostgreSQL.

NULL value

Beginners in database theory may be confused by such a concept as NULL. In terms of databases NULL usually means that the value is not known (although there are one or two other types of definition with subtle differences).

Let’s look at the orderinfo table, it contains a column of order dates, as well as a column of shipping dates, both of which are of DATE type. What should I do if an order has already been received but not yet shipped? What should I save in the shipping date column? You can save a special date, a sentinel value, which would indicate that the order has not yet been shipped.

On UNIX systems, you can use January 1, 1970 as this is the date from which UNIX systems count down. In any case, this date should precede the date the database was created, then it would be obvious that the date is special, in our case it would make sense “not yet sent”.

It is clear that this solution is far from ideal. The presence of special values scattered across tables indicates that a project has failed and often leads to errors. For example, if a new programmer who does not know about the presence of special dates is connected to work on the project, he or she can calculate how much time on the average passes between placing an order and its shipment, and get very unexpected results (if in several cases the dates of shipment were set as the previous date of placing the order).

Fortunately, all relational databases have a special value called NULL, which usually means “currently unknown”. Please note that this is not zero, not an empty string or something that could be represented in a field of some type. “Unknown” is very different from 0 and an empty string.

It is extremely important to keep an eye on these values, because they can appear in arbitrary places, causing inconvenience. In the orderinfo table, you can set the date of shipment to NULL (before the order was shipped), the value “currently unknown” is fully in line with our situation.

There is also a slightly different value of NULL (it is not as common as the first one) – “not relevant” to this line. Suppose, for example, that a group of people is being polled, and one of the questions concerns the color of the glasses. But for those who do not wear glasses, this question naturally does not make any sense. In this case, you can use NULL in the appropriate column to show that the information is not relevant to this particular row.

Checking for NULL

One of the properties of NULL is the following: when comparing two NULL values, the result will always be “not equal”. This may be surprising, but think about it, because NULL is an unknown, so it is absolutely logical that when comparing two unknown, it turns out that they are not equal.

In SQL has a special opportunity to check values for the value of NULL, if you need to find them and check, then the question is ‘IS NULL’.

The behavior of values NULL may have its own characteristics, so when designing a table, you can specify that in some columns can not be stored NULL. Usually it is convenient to say that the columns will meet the condition of NOT NULL’ – to make sure that NULL does not appear there (for example, in the column representing the primary key).

Some database designers promote almost complete abolition of the use of values NULL, but still they have a certain benefit. Therefore, as a rule, supported the use of NULL values in individual columns when there is a real need for values that are not defined.

Understanding Advanced Datatypes in PostgreSQL