PostgreSQL Data Types

Introduction To PostgreSQL Data Types

PostgreSQL is a relational database management system. It is an open-source software tool, used for managing database systems. It has the capability to recover quickly from difficulties and has good uprightness. Be it on web or analytics applications, or mobile applications, with PostgreSQL, it became very easy to store data and maintaining databases and data warehouses. Below we discuss the PostgreSQL Data Types.

Some of the data types are Numeric Types, Monetary Types, Character Types, Binary Types, Boolean Type, Enumerated Types, Date/Time Types, Geometric Types, Network Address Types, Bit String Types, Text Search Types, UUID Type, Pseudo-Types, Object Identifier Types, pg_lsn Type, Range Types, Composite Types, JSON Types, Arrays, and XML Type. There are some deprecated types; those are for internal use, like UUID, tsquery, polygon, pg_lsn, macaddr, interval [fields] [(p)], bit varying [(n)], bigint, etc.

Numeric Data Types

There are four parts to this. Those are Serial Types, Floating-Point Types, Arbitrary Precision Numbers, and Integer Types.

1. Serial Types

Whenever a new row is getting inserted into the database, we need to have a default sequence type value for every column. With the use of serial data types, we can start the value from 1. This is very useful while creating or altering a table.

2. Floating-Point Types

It’s a part of a binary point or decimal point. There can be single or double floating-point numbers. To represent floating-point, if we use 32 bits, then it would be called a single precision. Wherein if we use 64 bits, then it will be double precision. Respective to the significant digits, it can be placed anywhere, thus it is called a ‘float’.

3. Arbitrary Precision Numbers

In the available memory of the host system, some precisions are holding limited digits. Now if we want to implement those particular numbers, we can do so by deploying Arbitrary Precision.

4. Integer Types

If we want to set a range, to store an integer type of value, then we can implement it with this data type. Like for ‘smallint’ with the storage of 2 bytes, we can have a value between -32768 to 32767. Similarly, for bigint, we can have a range between -9223372036854775808 to 9223372036854775807.

5. Monetary Data Types

If we want to store currency amount, a kind of fractional precision that is fixed, then we use this data type. Within a database, with the help of the lc_monetary setting, fractional precision can be determined. Like a currency format such as ‘$5,000.00′.

6. Character Data Types

When we need to store numerical digits, symbols, upper and lower case letters, or common punctuation marks, then we need to use this data type. We will use character varying(n), varchar(n) if we want to limit the variable length, we can also use the character(n), char(n) if we want to blank padded or need to have a fixed length. In case if we want unlimited length for the variable then we can use ‘text’.

7. Binary Data Types

If we want to represent some opposed data, like ‘yes’ or ‘no’ then we use this data type. There are two formats in this data type, one is bytea Hexa and another is bytea Escape.

• bytea Escape: If we want to represent a binary string like a sequence of ASCII characters then we need this data type.
• bytea Hexa: If we have binary data, which we need to encode as 2 hexadecimal digits for every single byte then we need to use this data type. To make a difference from the escape format, the string is lead up to by the sequence ‘x’.

8. Boolean Data Type

If we want to set a value as ‘yes’ or ‘no’ or ‘not known’ then we use this data type. When we use some combination of comparison operators, like ‘>=’, ‘=’, ‘<’, ’<>’ or logical operators then we need this data type.

9. Data/Time Data Types

It stores the date format of a calendar and timestamp. There are five parts, Time Zones, Interval Input, Interval Output, Date/Time Input, and Date/Time Output.

• Time Zones: In the real world, time zones have too small meaning, till it is associated with the date as well as with time, because the offset can differ through the year, for time boundaries in daylight-saving. For a session, if we use the SET TIME ZONE command in SQL it will set the time zone for that. While connecting to the server, an environment variable PGTZ is also used, if we want to send a SET TIME ZONE command to the server.
• Interval Input: When we want to represent millennium, year, century, decade, month, day, week, hour, minute, microsecond, second, millisecond, or abbreviations or it can be plurals of these units, then we need to use this data type.
• Interval Output: In this, the interval type can be set to one of the four styles, iso_8601, postgres_verbose, Postgres, or sql_standard. We need to use the ‘SET interval-style’ command for the same.
• Date/Time Input: This input is accepted in nearly any reasonable format, traditional POSTGRES, SQL-compatible, ISO 8601, and others.
• Date/Time Output: This type can be set into four styles, it should be anyone of them. It can be German, Unix date format, ISO 8601, or SQL Ingres.

10. Enumerated Data Types

Also known as enum, is a kind of ordered set of values or rather comprises a static value. They are equivalent to the enum data types. There are four parts in this data type; those are Implementation Details, Type Safety, Ordering, and Declaration of Enumerated.

• Implementation Details: Levels of enum are case sensitive. So the ‘true’ is not the same as ‘TRUE’. Enum types are conscious of static sets of value.
• Type Safety: The enum data type cannot be compared with other enum types; it is separate from each other.
• Ordering: After type was created, if we want the value to be listed in a specific order, and then we need this.
• Declaration of Enumerated Types: To create enum type, we need to use the command CREATE TYPE. After creating, it can also be used in function definitions.

Conclusion

Apart from this, there are many more data types used in PostgreSQL. Each data types are important in their segments. It is very important to understand the relations between various data types because many of them are correlated with each other. The best way would be to implement data types by using syntax.

Recommended Articles

This is a guide to PostgreSQL Data Types. Here we discuss the introduction and numeric data types for better understanding. You can also go through our other suggested articles to learn more –

1. PostgreSQL Administration
2. PostgreSQL Architecture
3. PostgreSQL Features
4. What is PostgreSQL?