PostgreSQL Data Types + Examples

In this PostgreSQL tutorial, we are going to learn about PostgreSQL Data Types. Here we will learn about different data types available in PostgreSQL, and we will also cover the following list of topics.

  • Postgresql data types
  • Postgresql data types and sizes
  • Postgresql data types numeric
  • Postgresql data types with example
  • Postgresql data types for double
  • Postgresql data types datetime
  • Postgresql data types text vs varchar

PostgreSQL Data Types with Examples

PostgreSQL upholds a vast range of Data Types. It contains several data types that include Boolean, Numeric, Character, Temporal, Array, JSON, UUID, and special data types. While generating a table, for every column, we explain a data type expressly whatever type of data we want to store in the table fields.

This empowers a few advantages −

  1. Consistency: Operations against columns of the constant data types give reliable outcomes and are usually the fastest.
  2. Performance: Correct utilization of data types offers the foremost productive reposting of knowledge. The values hold on are often handled speedily, which upgrades the exhibition.
  3. Validation: Correct use of data types implies format validation of data and rejection of data outside the scope of data type.
  4. Compactness: As a column will store one type of value, it is kept in a compressed method.
Review of PostgreSQL data types. PostgreSQL has the following data types:
  • Numeric data types like integer and floating-point numbers.
  • Temporal types like date, time, timestamp, and interval
  • UUID for storing globally distinctive Identifiers
  • ARRAY for storing array strings, numbers, etc.
  • JSON will store JSON data
  • Special types like network address and geometric data.
  • Boolean types like true, false, or null.
  • Character types therefore as text, char, and varchar

Numeric data types in PostgreSQL

PostgreSQL allocates two distinct kinds of numbers:

  • integers
  • floating-point numbers

Temporal data types in PostgreSQL

The temporal data types permit us to keep date and time data. PostgreSQL has five foremost temporal data types:

  • The DATE will store the dates only.
  • The TIME will store the time of day values.
  • TIMESTAMP stores both date and time values.
  • TIMESTAMPTZ is a timezone-aware timestamp data type which is the abbreviation for timestamp with the time zone.
  • The INTERVAL will store the periods of time.

The TIMESTAMPTZ is PostgreSQL’s expansion to the SQL standard’s temporal data types. Here’s an example of a DATE data type that uses the CURRENT_DATE() function:

CREATE TABLE Student(
id INT NOT NULL,
name TEXT,
join_date DATE DEFAULT CURRENT_DATE
);
This statement will return results that look like the following:
 SELECT * FROM Student;

UUID data type in PostgreSQL

A UUID which is also named as Universally Unique Identifiers is drafted as an order of lower-case hexadecimal digits, in various classifications distinguished by hyphens, particularly a grouping of eight digits, after that three groups of four digits, afterward a group of 12 digits, for an entire total of 32 digits producing the 128 bits.

For Example: 660e8500-e25b-40d4-a496-336633990000.

ARRAY in PostgreSQL

PostgreSQL permits a column of tables as a variable-length and multidimensional array. We will build any user-defined base type, built-in, composite, and enumerated type arrays. We will implement many operations on arrays as well as declare, insert, accessing, modifying, and searching in PostgreSQL.
PostgreSQL reserves an array of strings, an array of integers, etc., in array columns. An array that seems in sure circumstances like storing days of the week, months of the year.

In the below example, we will create a table named Students with the contact column defined as a text array:

CREATE TABLE Students (
   id int PRIMARY KEY,
   name VARCHAR (100),
   contact TEXT []
);

The command will run successfully:

postgreSQL datatype array example

Now, we will insert the array values:

INSERT INTO Students
VALUES
   (
      1,
      'Richard Jener',
      ARRAY [ '(408)-643-9245',
      '(408)-667-8920' ]
   );
The insertion will run successfully:
postgreSQL datatype array example output

JSON in PostgreSQL

PostgreSQL supports two JSON data types: JSON and JSONB for putting away JSON data. The JSON data type stores plain JSON data which needs reparsing for every processing although JSONB data type stores JSON data in a binary format and that’s quicker to measure however more slow to insert. Also, JSONB supports indexing, which can be a benefit. Let’s understand with the help of an example by creating a table:

CREATE TABLE goods (
	id serial NOT NULL PRIMARY KEY,
	info json NOT NULL
);

The products the table comprises of two columns:

  1. The id column is the primary key column that identifies the order.
  2. The info column stores the data in the form of JSON.

Insert JSON data

Let’s insert the data into a JSON column, we will ensure that data is in a valid JSON format. The below statement inserts a new row into the table.

INSERT INTO goods (info)
VALUES('{ "customer": "David Geller", "items": {"product": "Vodka","qty": 4}}');

It means "David Geller" bought 4 bottles of vodka. The below statement inserts multiple rows at the same time.

INSERT INTO goods (info)
VALUES('{ "customer": "Christina Applegate", "items": {"product": "Diaper","qty": 24}}'),
      ('{ "customer": "Chandler Bing", "items": {"product": "Toy Car","qty": 1}}'),
      ('{ "customer": "Phoebe Buffay", "items": {"product": "Toy Train","qty": 2}}');

We will use a SELECT statement to query JSON data, which is similar to querying other native data types which will generate the following output:

SELECT info FROM goods;
postgresql datatype JSON example
PostgreSQL datatype JSON example

Special Data Types in PostgreSQL

Apart from primary data types, PostgreSQL also gives various data types linked to geometric and network.

  • box: the representation of a box is a rectangular box. The storage size of box is 32 bytes.
  • macaddr: The representation of macaddr is a MAC address whose storage size is 6 bytes.
  • line: the representation of a line is an Infinite line that is not fully implemented. The storage size of a line is 32 bytes.
  • polygon: the representation of a polygon is a polygon that is similar to closed path. The storage size of a polygon is 40+ 16n.
  • point: the representation of point is a point on a plane. The storage size of a point is 16 bytes.
  • lseg: the representation of a lseg is a line segment. The storage size of lseg is 32 bytes.
  • inet: an IP4 and IPV6 address.

Read PostgreSQL Date Difference

Boolean Data Type in PostgreSQL

PostgreSQL gives the standard SQL type Boolean. A Boolean data type carries one of three potential values: true, false, and a third state, null, which is illustrated by the SQL null value. A boolean or bool is a keyword that declares a column with the Boolean data type.

When we insert data into a Boolean column, PostgreSQL transforms it into a Boolean value

  • 1, yes, y, t, true values turn to true
  • 0, no, false, f values turns to false.

When we select data from a Boolean column, PostgreSQL transforms the values back e.g., t to true, f to false, and space to null. The storage size of a boolean is 1 byte. Let’s understand with the help of an example. First, create a table  item_availability to log which products are available:

CREATE TABLE item_availability (
   item_id INT PRIMARY KEY,
   available BOOLEAN NOT NULL
);
Now insert some data into the item_avaliability table. We will use various literal value for the boolean values.
INSERT INTO item_availability (item_id, available)
VALUES
   (100, TRUE),
   (200, FALSE),
   (300, 't'),
   (400, '1'),
   (500, 'y'),
   (600, 'yes'),
   (700, 'no'),
   (800, '0');
Now, use the following statement to check for the item’s availability:
SELECT *
 FROM item_availability
WHERE available = 'yes';
postgresql datatype boolean image
PostgreSQL datatype boolean output

Character Data types in PostgreSQL

PostgreSQL has three-character data types: CHAR(n), VARCHAR(n), and TEXT

  • CHAR(n) is a certain sized character with space padded. If we insert a string that is smaller than the length of the column, PostgreSQL pads spaces. If we insert a string that is longer than the length of the column, PostgreSQL will matter a glitch.
  • VARCHAR(n) is the variable-length character string. With VARCHAR(n), we store up to n characters. PostgreSQL doesn’t pad spaces when the stored string is smaller than the length of the column.
  • TEXT is the variable-length character string. Hypothetically text data is a character string with unlimited length.

Read: PostgreSQL WHERE

PostgreSQL Numeric

In this tutorial, we are going to learn about the PostgreSQL NUMERIC type which stores numeric data.

The NUMERIC type stores numbers with a lot of digits. Generally, we use the NUMERIC type for numbers that insist on correctness includes financial amounts or quantities.
Following represents the syntax of the NUMERIC type:

NUMERIC(precision, scale)

The accuracy within the above than syntax is the total number of digits and also the scale is that number of digits within the fraction part. For example, the number 1234.567 has a precision of 7 and a scale of 3.
The NUMERIC type will hold a value up to 131,072 digits before the decimal point 16,383 digits afterward the decimal point.
The scale of the NUMERIC type is zero or positive. The related shows the syntax of NUMERIC type with scale zero:

NUMERIC(precision)

If we exclude both precision and scale, we can store any precision and scale up to the limit of the precision and scale mentioned above.

NUMERIC

In PostgreSQL, the NUMERIC and DECIMAL types are identical along two of them are likewise a piece of the SQL standard.
If in the case of accuracy isn’t necessary, we ought not to use the NUMERIC type since estimations on NUMERIC values are generally slower than integers, floats, and double precisions.

Example: Firstly we will create a new table named products for the demonstration

DROP TABLE IF EXISTS items;

CREATE TABLE items (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    cost NUMERIC(6,3)
);
Secondly, we will insert some products with cost whose scales exceed the scale declared in the price column:
INSERT INTO items (name, cost)
VALUES ('Speakers',400.214), 
       ('Earphones',700.21);

Because the scale of the cost column is 2, PostgreSQL rounds the value 400.150 up to 400.22 and rounds the value 400.214 down to 400.21

The following query returns all rows of the products table:

SELECT * FROM items;
postgreSQL Numeric Example
PostgreSQL Numeric Example

If we store a value whose accuracy overtakes the declared precision, PostgreSQL raises an error which is shown in the example:

INSERT INTO items (name, price)
VALUES('Speakers',123456.21);
PostgreSQL will generate the following error:
PostgreSQL Numeric error Example
PostgreSQL Numeric error Example

Read: PostgreSQL CASE

Postgresql datatype text vs varchar

In this tutorial, we will understand the PostgreSQL character data types as well as CHAR, VARCHAR, and TEXT. Now, we will see the differences between TEXT and VARCHAR.

Both TEXT and VARCHAR have a higher limit at 1 Gb, and there is no performance difference between them. The main variation between TEXT and VARCHAR(n) is that we can limit the maximum length of a VARCHAR column such as VARCHAR(255) does not admit inserting a string more than 255 characters long.

In a worth embedded to a TEXT column has trailing spaces, PostgreSQL doesn’t shorten them, and they are expository in analysis:

CREATE TABLE globe (a1 TEXT);

INSERT INTO globe VALUES ('A  ')

SELECT '''' || a1 || '''' FROM globe;

SELECT COUNT(a1) FROM globe WHERE a1 = 'A'

SELECT COUNT(a1) FROM globe WHERE a1 = 'A   ';

Here is the output of the query.

Postgresql datatype text vs varchar
Postgresql datatype text vs varchar
  • PostgreSQL supports CHAR, VARCHAR etc data types. The VARCHAR and TEXT are varying length character types.
  • We can use VARCHAR(n) if we want to approve the length of the string (n) before inserting it into or updating to a column.
  • VARCHAR when without the length specifier and TEXT are equivalent.

Read: PostgreSQL WHERE IN

PostgreSQL Datatypes sizes

Geometric Type: These data types will illustrate two-dimensional spatial objects. The basic type, the point, forms the basis for all of another type:

NAMEREPRESENTATIONSTORAGE SIZEDESCRIPTION
boxRectangular box32 bytes((x1,y1),(x2,y2))
lineInfinite line(not
fully implemented)
32 bytes ((x1,y1),(x2,y2))
pointthe point on a plane16 bytes(x,y)
circleCircle24 bytes<(x,y),r> (center point
and radius)
lsegFinite line segment32 bytes ((x1,y1),(x2,y2))
pathClosed path(similar
to polygon)
16+16n bytes((x1,y1),…)
pathOpen path16+16n bytes[(x1,y1)…]
polygonPolygon(similar to
closed path)
40+16n((x1,y1),…)
TABLE 1.1

Network Address Type: PostgreSQL offers data types to store MAC addresses, IPv4 and IPv6. It is higher to use these types rather than plain text types to store network addresses as a result these supply error checking and specialized operators and functions.

Name DescriptionStorage size
inetIPv4 and IPv6 hosts and networks7 or 19 bytes
cidrIPv4 and IPv6 networks7 or 19 bytes
macaddrMAC addresses6 bytes
TABLE 1.2

Boolean type: Boolean data type is given by PostgreSQL. The Boolean data type can have the states true, false, and a third state, unknown, which is addressed by the SQL null value.

NameDescriptionStorage Size
booleanstate true or false1 byte
TABLE 1.3

Date/Time types: PostgreSQL upholds a full set of SQL date and time types, as displayed in the table below. According to the Gregorian calendar dates are counted. Now, every type has a resolution of 1 microsecond / 14 digits except the date type, whose goal is the day.

NameDescriptionHigh ValueLow valueStorage size
TIMESTAMPTZboth date and time, with time zone294276 AD4713 BC8 bytes
timestamp [(p)] [without time zone ]both date and time(no time zone) 294276 AD 4713 BC 8 bytes
interval [fields ] [(p) ]time interval178000000 years– 178000000 years12 bytes
time [ (p)] [ without time zone ]time of day (no date)24:00:0000:00:008 bytes
time [ (p)] with time zone
times of day only, with time zone24:00:00-145900:00:00+145912 bytes
datedate(no time of day)5874897 AD4713 BC4 bytes
TABLE 1.4

Character Types: The below table lists the purpose of character types accessible in PostgreSQL.

NameDescription
textvariable-length with limit
character varying(n), varchar(n)variable- length with a limit
character(n), char(n)fixed-length, blank padded
TABLE 1.5

Numeric Type: It consists of two-byte, four-byte, and eight-byte integers, four-byte and eight-byte floating-point numbers, and selectable-precision decimals. Here is the list of obtainable data types.

NameDescriptionRangeStorage size
integera typical choice for integer-2147483648 to +21474836474 bytes
decimaluser-specified precision, exactup to 131072 digits before the decimal point; up to 16383 digits after the decimal pointvariable
numericuser-specified precision, exactup to 131072 digits before the decimal point; up to 16383 digits after the decimal pointvariable
realvariable-precision,inexact6 decimal digits precision6 bytes
serialautoincrementing integer1 to 21474836474 bytes
bigseriallarge autoincrementing integer1 to 92233720368547758078 bytes
smallserialsmall autoincrementing integer1 to 327672 bytes
smallintsmall-range integer-32768 to +327672 bytes
bigintlarge-range integer-9223372036854775808 to 92233720368547758078 bytes
double precisionvariable-precision,inexact15 decimal digits precision8 bytes
TABLE 1.6

Read: PostgreSQL TO_NUMBER() function

Postgresql data types for double

In PostgreSQL, a numeric data type is a DOUBLE PRECISION which is also called alternate name float8. Double-precision values are managed as floating-point values that means that any rounding will happen if we try to store a value with “too many” decimal digits like if we try to store the result of 2/3, there would be some rounding when the 15th digit was attained.

Sometimes there is no actual result of this estimation, but there are circumstances where absolute precision is essential with the NUMERIC data type is the greater option.

Data TypeRangeDescription
double-precision,
float, float8
15 decimal digits precisiondecimal, floating-point numeric data,
integer values
TABLE 1.7

Create a database and table in PostgreSQL: Now, we will create a table with a DOUBLE PRECISION data type. Firstly, we have to create a database in PostgreSQL in which we will use the command shown below:

CREATE DATABASE any_db;

Now we will create a table:

CREATE TABLE TABLE_NAME(
        COLUMN1 + DATA_TYPE + CONSTRAINT [OPTIONAL],
        COLUMN2 + DATA_TYPE + CONSTRAINT [OPTIONAL]);

Now, we will see the statement used to create our table:

CREATE TABLE abc(
    id SERIAL PRIMARY KEY,
    str_col VARCHAR(100),
    int_col INT NOT NULL,
    boolean_col BOOL,
    float_col DOUBLE PRECISION
);
Use the command \d abc; To view the table information, the output of this command will look like this:
postgreSQL datatype double
PostgreSQL datatype double

Insert some values inside the DOUBLE PRECISION column.

For inserting our data, we can use an INSERT statement in our DOUBLE PRECISION column and another column of the table:

INSERT INTO abc (str_col, int_col, boolean_col, float_col)
VALUES('Glass', 7896, TRUE, 0879654321),
    ('Images', 7768, FALSE, 09876544568),
    ('Tutor', 6754, TRUE, 09417689765);
To see the values in the float_column, use the simple SELECT statement shown below:
SELECT id, float_col FROM demo;
Now, here is the following output:
postgresql datatype double output
Postgresql datatype double output

Read: PostgreSQL TO_TIMESTAMP function

PostgreSQL Data Type Datetime

PostgreSQL has a wide range of date/time data types which is already shown above in TABLE 1.4. Now we will discuss the date/time inputs. Table 1.8 shows some possible inputs for the datetype.

ExampleDescription
January 8, 1999
unambiguous in any datestyle
 input mode
1999-Jan-08January 8 in any mode
99-Jan-08January 8 in YMD
 mode, else error
1/8/1999January 8 in MDY
 mode; August 1 in DMY
 mode
Table 1.8

Time Input: These types comprise a time of day followed by an optional time zone of valid input. If a time zone is described in the input for time without time zone which is mutely unnoticed.

We can also define a date but it is, however unnoticed, excluding when you use a time zone name that involves a daylight-savings rule, including America(New York).

In this case, specifying the date is required to determine whether standard or daylight-savings time applies in which the proper time zone offset is recorded in the time with the time zone value.

ExampleDescription
04:05:06ISO 8601
04:05 AMsame as 04:05; AM does not affect the value
04:05 PMsame as 16:05; input hour must be <= 12
04:05:06 PSTtime zone specified by the abbreviation
Table 1.9

Date/Time Output: In PostgreSQL, the ISO format is by default. The output format of the DateTime data type is commonly set to a minimum of one of the four styles ISO 8601, the German, SQL, traditional POSTGRES, which uses the command SET datestyle.

The SQL standard needs the usage of the ISO 8601 format. Below Table 1.10 will show examples of each output style. The output of the DateTime data type is absolutely the date or time part in accordance with the given examples.

ExampleStyle SpecificationDescription
17.12.1997 07:37:16.00 PSTGermanregional style
12/17/1997 07:37:16.00 PSTSQLtraditional style
1997-12-17 07:37:16-08ISOISO 8601/SQL standard
Wed Dec 17 07:37:16 1997 PSTPOSTGRESoriginal style
Table 1.10

You may also like to read the following PostgreSQL articles.

In this PostgreSQL tutorial, we have learned about PostgreSQL Data Types. Here we have covered different data types available in PostgreSQL, and we have also covered the following list of topics.

  • Postgresql data types
  • Postgresql data types and sizes
  • Postgresql data types numeric
  • Postgresql data types with example
  • Postgresql data types for double
  • Postgresql data types datetime
  • Postgresql data types text vs varchar

Leave a Comment