Data Types

Casting values

You can cast values in postgres with ::.

It will enforce the target cast type constraints, and raise errors if the casting value does not comply.

-- This way postgres will treat it as `integer` instead of `numeric`
SELECT (2.0::integer)

Data Types

Array

Every data type has its own companion array type e.g. text[], integer[][].

Use GIN index types on arrays for fast searching.

Type	Description
type[]	One-dimensional or multi-dimensional arrays

How values are inserted

INSERT INTO table_name (col_array)
VALUES (ARRAY ['a', 'b', 'c'])
;

Inside the table, their are stored between brackets like {a,b,c}.

Selecting values

Indexes start from 1 and not 0.

If specfic index don't exists NULL is returned.

-- To select the hole array
SELECT col_array FROM table_name;

-- To select first value from array
SELECT col_array[1] FROM table_name;

Comparing arrays

Using comparison operators like =, !=, <, > between array values, will run them in the array's values and the length.

SELECT
    ARRAY[1,2,3] = ARRAY[1,2,3] -- true
    ARRAY[1,2,3] != ARRAY[1,2,3] -- false
    ARRAY[1,2,3] != ARRAY[1,3,3] -- true
    ARRAY[1,2,3] < ARRAY[1,2,3] -- false
    ARRAY[0,2,3] < ARRAY[1,2,3] -- true
    ARRAY[1,2,3] >= ARRAY[1,2,3] -- true
    ARRAY[0,2] < ARRAY[1,2,3] -- true

Use ALL or ANY/SOME apply a condition method to all or some of the array values.

SELECT
    25 = ANY(ARRAY(1,5,25)) -- true
    25 = ANY(ARRAY(1,5,27)) -- false
    25 = ALL(ARRAY(1,5,25)) -- false
    25 = ALL(ARRAY(25,25,25)) -- true

Combining arrays

Use || to combine arrays.

SELECT ARRAY[1,2] || ARRAY[3,4] -- {1,2,3,4}

Adding values to array

Also use || to add values to arrays.

Prepend or Append values depending on where the value is in the query.

-- Prepend
SELECT 1 || ARRAY[3,4] -- {1,3,4}

-- Append
SELECT ARRAY[3,4] || 1 -- {3,4,1}

Remove values from array

-- ARRAY_REMOVE(array, element)
SELECT ARRAY_REMOVE(ARRAY[3,4], 3) -- {4}

Get arrays metadata

-- Dimensions of an array
SELECT ARRAY_NDIMS(ARRAY[1,2]) -- 1 dimension
SELECT ARRAY_NDIMS(ARRAY[[1],[2]]) -- 2 dimensions

-- Length of array
-- Empty arrays will have a 'NULL' length
SELECT ARRAY_LENGTH(ARRAY[1,2], 1) -- 2

Search values in array

-- ARRAY_POSITION(array, element, start_position) (Must be one-dimension only)
SELECT ARRAY_POSITION(ARRAY[3,4], 4) -- 2

-- ARRAY_POSITIONS(array, element)
-- Will return all the positions the element is found
SELECT ARRAY_POSITIONS(ARRAY[3,4,4], 4) -- {2,3}

Unesting array values

unnest will split the array data into separate rows when selecting.

-- 1  ['a', 'b']
SELECT id, unnest(names);

-- id unnest(names)
-- 1  'a'
-- 1  'b'

Bit String

Type	Description
bit(n)	Fixed-length bit string
bit varying(n)	Variable-length bit string

Boolean

Some values are valid literals for booleans, but must be enclosed in single quotes.

• 'true', 'false'

• 't', 'f'

• 'y', 'n'

• 'yes', 'no'

• '1', '0'

Type	Description
boolean	Stores TRUE, FALSE, or NULL

Composite (Objects)

These are user defined data types that can hold multiple types of value.

Type	Description
row	Custom record-like structures

CREATE TYPE full_name AS (
  first text,
  last text
);

How to insert with this data type

Use the ROW() to insert values for a composite type.

INSERT INTO table_name (full_name, phone)
VALUES
    (ROW('First', 'Name'), '9999-9999')

How to select the data

SELECT full_name FROM table_name;

-- (First,Name)

SELECT (table.full_name).first FROM table_name;

-- First

Character

There is no performance difference between them (in terms of memory layout, storage size, index size or access speed). Internally postgres treats them the same, unlike MySQL.

Even a varchar(254) vs varchar(500) behaves the same at storage level. The only difference is enforced length checks on input.

When using a varchar(500) for instance, postgres doesn't allocate 500 bytes, only the actual content length of the data.

Postgres B-tree indexes work the same way on text and varchar, since the index is built from the actual string values.

• A 50-character string indexed on VARCHAR(254) or TEXT will produce the same index size and access path.

• Limiting the max length does not reduce index size unless actual values are smaller.

Type	Description
char(n)	Fixed-length string (Spaces are added to pad at the end)
varchar(n)	Variable-length string, with limit
text	Variable unlimited-length string

Convert function (to_char)

Convert an expression that can be multiple types of data to string. (Check format options in the docs)

SELECT to_char(expression, format);

SELECT to_char(120.105, '$99999D99') -- $ 120.10
SELECT to_char(CURRENT_DATE, 'DD/MM/YYYY') -- 01/01/2025

Date/Time

Type	Description
timestamp	Date and time (no timezone)
timestamptz	Timestamp with timezone (default to system's TimeZone parameter)
date	Calendar date (year, month, day)
time	Time of day (no date, no timezone)
timetz	Time with timezone
interval	Time span (e.g., 3 days, 2 hours)

With timezone dates like timestamptz the internally stored value is always in UTC.

When they are outputed, their values are then converted from UTC to their timezones.

So for timezone sensitive applications, prioritize using timestamptz.

Get the current date with the keyword CURRENT_DATE.

Get the current timestamp with NOW() or CURRENT_TIMESTAMP. (NOW also brings the timezone)

Get the current time with CURRENT_TIME.

Get the local time with LOCALTIME.

Operations with dates and times

interval accept values like interval 'n type' where:

• n is a desired number.

• type is value type like "second", "minute", "hour", "day", "month", "year", ...

SELECT CURRENT_TIME + interval '1 day';

Operations with timezones

Using timezone() to convert time, based on a timezone.

SELECT timezone('Asia/Singapore', '2025-01-01 00:00:00');

Showing Postgres timezone

-- Show all timezone names
SELECT * FROM pg_timezone_names;

-- Show the current postgres timezone
SELECT TIME ZONE;

-- Change the postgres timezone
SET TIME ZONE 'America/New_York';

Handling timezones

Always use date and time when using timezones.

Avoid numeric offsets with timezones.

CREATE TABLE table_name (
    created_at TIMESTAMP WITH TIME ZONE
)

INSERT INTO table_name (created_at)
VALUES ('2025-01-01 00:00:00 America/New_York')
;

Convert function (to_date) (to_timestamp)

Converts a string to date. (Check format options in the docs)

SELECT to_date(string, format);

SELECT to_date('2025-01-01', 'DD/MM/YYYY') -- 01/01/2025

Convert dates to formats

With Convert function (to_char).

Extracting data from a date value

Check more types of fields on the docs.

SELECT EXTRACT(field from source);

SELECT EXTRACT(EPOCH from '2025-01-01'::timestamp); -- 1735689600

Check for date ranges overlap

Will check if date-range2 overlaps with date-range1.

date-range2 doesn't have to be totally inside date-range1.

-- [date-range1] OVERLAPS [date-range2]

SELECT 
    ('2025-01-01'::date, interval '100 days')
    OVERLAPS
    ('2025-02-01'::date, '2025-02-05'::date)
; -- true

Showing Age between two dates

AGE returns the number of years, months and days between two dates.

-- date1 - date2
SELECT AGE(date1, date2);

SELECT AGE(NOW(), '1991-11-14'::date); -- 33 years 2 months...

-- Assumes NOW if first parameter not specified
SELECT AGE('1991-11-14'::timestamp)

Using infinity as date value

Use infinity or -infinity as values in dates or timestamps to indicate the highest or lowest value possible.

Enumerated

Type	Description
enum	Custom, user-defined list of valid string values

CREATE TYPE currency AS ENUM ('USD', 'EUR', 'GBP');

Add more values to the Enum

ALTER TYPE currency ADD VALUE 'CHF' AFTER 'EUR';

How to insert values with Enum

-- Considering `stocks_currency` if of type `currency`
INSERT INTO stocks (stock_currency) VALUES ('USD');

Drop Enum types

DROP TYPE currency;

Geometric

Type	Description
point	(x, y) coordinate
line	Infinite line
lseg	Line segment
box	Rectangular box
path	Closed/open path
polygon	Polygon
circle	Center and radius

Json & Jsonb

The json datatype is actually text under the hood, with a verification that the format is valid json. Being a text, it stores presentation exactly as it is sent to Postgres, including whitespaces and identation, and also multiple-keys when present. No processing at all is done on the content, only validation.

json does not support full-text-search indexing and also does not support a wide range of functions and operators.

It should be faster in inserts, since it does not process the input data.

Useful if you only want to store json content, and will not process it in postgres.

The jsonb is an advanced binary storage format with full processing, indexing and searching capabilities, and as such pre-processes the json data to an internal format, which does include a single values per key; and also isn't sensible to extra whitespace or identation.

jsonb will be faster with functions and operators, sinde it process the input data.

If indexing columns with jsonb use the GIN index instead of the default B-Tree.

It makes it possible to even index over specific keys from the json object.

Type	Description
json	Text-based JSON (slower, preserves formatting)
jsonb	Binary JSON (faster, no formatting preserved)

How values are inserted

INSERT INTO table_name (json_col)
VALUES
-- Inserting as [key, value, key, value, ...]
('[1,2,3,4,5,6]')
-- Inserting as objects
('{"key": "value"}')

INSERT INTO table_name (jsonb_col)
VALUES
('[1,2,3,4,5,6]')
('{"key": "value"}')

Selecting values

With json types, the returned value is always text. So you cannot extract data from it from inside Postgres.

To handle this use jsonb.

Use -> and ->> operators to select the json fields.

-> returns the json object field as a field in quotes, while ->> returns the object field as text.

-- { "id": 1, "name": "some value" }

SELECT
    jsonb_col->'name' -- "some value"
    json_col->>'name' -- some value
;

-- Find rows with keys or values that contains '2' in it
SELECT * FROM table_name
WHERE jsonb_col @> '2';

Update existing object values

UPDATE table_name
SET jsonb_col = jsonb_col || '{"name": "updated value"}'
WHERE jsonb_col->>'id' = 1;

Adding new object values

UPDATE table_name 
SET jsonb_col = jsonb_col || '{"description": "desc value"}';

Add arrays also like:

UPDATE table_name 
SET jsonb_col = jsonb_col || '{"phones": ["11", "22"]}';

Delete object values

UPDATE table_name 
SET jsonb_col = jsonb_col - 'description'
WHERE jsonb_col->>'id' = 1;

If the object values is an array, delete specific array's index values with #-:

-- #- '{object_key, array_index}'

UPDATE table_name 
SET jsonb_col = jsonb_col #- '{phones,1}'
WHERE jsonb_col->>'id' = 1;

Output a table contents in json

With row_to_json function, output an entire table in json, where each row of the select will be a json object with the columns being the key.

SELECT row_to_json(table_name) FROM table_name;

Network Address

It is better to use these types instead of plain text to store network addresses, because these types offer input error checking and specialized operators and function.

When sorting inet or cidr types, ipv4 addresses will always sort before ipv6, including ipv4 addresses encapsulated or mapped to ipv6.

Type	Description
cidr	IP network
inet	IP address
macaddr	MAC address
macaddr8	MAC address (EUI-64)

Numeric

Type	Subtype	Description
smallint	Integer	2-byte, range: -32,768 to +32,767
integer	Integer	4-byte, range: -2B to +2B
bigint	Integer	8-byte, large range
decimal / numeric	Fixed-Precision	Arbitrary precision exact numbers
real / float	Floating-Point	4-byte, approx precision ~6 decimal digits
double precision	Floating-Point	8-byte, approx precision ~15 digits
serial, bigserial, smallserial	Auto-increment	Integer auto sequences

For decimal or numeric you define two parameters:

decimal(precision, scale) where:

• precision is the maximum number of digits to the left and right of the decimal point;

• scale is the number of digits allowable on the right of the decimal point.

Convert function (to_number)

Convert a string to number. (Check formats in the docs)

SELECT to_number(string, format);

Range

Use GiST indexes on range columns for fast searching.

Ranges are not arrays, you will not access a specific range's value.

They are useful for instance, to reduce two date columns that should indicate a start and end date, to a single column that will have this same begin and end range.

Range Type	Description
int4range	Range of integers (4-byte)
int8range	Range of integers (8-byte)
tsrange	Range of timestamps (no timezone)
tstzrange	Range of timestamps (with timezone)
daterange	Range of dates

open_close parameter is defined as:

• []: Both upper and lower bounds is closed.

• [): Lower bound closed and upper bound open. (Default)

• (]: Lower bound open and upper bound closed.

• (): Both bounds opened.

Where opened means that the value is not included in the range, and closed means that it is part of the range.

If not specified the default open_close is [).

-- SELECT range_type(lower bound, upper bound, open_close);

SELECT int4range(1, 6, '[]'); -- 1 2 3 4 5 6
SELECT int4range(1, 6); -- 1 2 3 4 5

Checking for date range overlap

Use && to check if two ranges overlap.

-- SELECT range_type(lower bound, upper bound, open_close);

SELECT *
FROM table_name
WHERE
    -- && checks if two ranges overlap
    col_daterange && '[2025-01-01,2025-01-05)'
;

UUID

Type	Description
uuid	Universally Unique Identifier (By default uses uuidv4)