Range, Multiranges and composite data types
The RANGE data type represents a contiguous intervals of values; it is defined by its lower and upper bound.
//It's compatible with previous postgreSQL data types
create table lista(
id serial primary key,
user_id int,
raggio int4range //int8range, numrange, daterange, tsrange, tstzrange
)
insert into lista(raggio) values ('[12, 44]');
insert into lista(raggio8) values ('[12, 44]');
insert into lista(raggionum) values ('[12.12 , 44]'); //num for decimals
insert into lista(raggiodata) values ('[2021-01-12, 2021-01-15]');
insert into lista(raggiots) values ('[2021-01-12 12:12:12, 2021-01-15 15:15:15]');
insert into lista(raggiotstz)values('[2021-01-12 12:12:12+02,2021-01-15 15:15:15+02]')By default, PostgreSQL ranges follow the [inclusive, exclusive) bounds format.
//Depending on the data type it increases the upper bound for consistency
//Necessary to optimize range operations
select int4range('[1, 3]'); -- [1,4)
select int4range(2, 15, ()); -- [3,15) //It overrides range constructors
select daterange('[2001-05-12, 2001-05-14]'); -- [2001-05-12,2001-05-15)
select tsrange('[2001-05-12 12:12:10, 2001-05-14 12:12:30]');
-- ["2001-05-12 12:12:10","2001-05-14 12:12:30"]The lower() and upper() range functions retrieve their respective bounds. We can define infinite bounds using the 'infinity' string (compatible with tsrange, tstzrange, daterange, and numrange in both constructors and literals), NULL (in range constructors), or an empty bound (in range literals).
//Both the NULL and empty bound are compatible with ts/tzrange
//No space fo the empty bound
select int4range(5, NULL) @> 250; //true
SELECT tsrange('-infinity', '2023-06-30 10:00:00', '[]');
SELECT '["-infinity", "33.44"]'::numrange;
select '[, 450]'::int4range @> 250;Arrays and ranges are both collections of data types, but they use different operators. Array operators like ANY or EXISTS operate on a series of values, while a range represents a single contiguous interval.
//The @> and && operators are compatible with ranges.
//Some values like numrange require explicit casting or specific values
select 12 <@ '[10, 45]'::int8range; //teue
select 12 <@ '[10, 45.45]'::numrange; //not compatible data types integer <@ numrange
select 12::numeric <@ '[10, 45.45]'::numrange; //true
select 12.12 <@ '[12, 45]'::numrange; //true
//The && is only between ranges
select '[10, 45]'::int4range && '[30, 40]'; //trueRange operations include:
The not-extend-to-the-left/right operators (&> and &<) return true depending on the starting and ending bound between two ranges.
//&> returns true if the first lower() is greater than the second.
//&< returns true if the first upper() is lower than the second.
SELECT int4range(7, 20) &> int4range(5, 10); -- true, 7>5 no extend
SELECT int8range(3, 10) &> int8range(5, 12); -- false,
SELECT int8range(5, 10) &> int8range(5, 8); -- true
SELECT int4range(7, 20) &< int4range(5, 10); -- false, 20> 10
SELECT int8range(3, 10) &< int8range(5, 12); -- true 10 < 12
SELECT int8range(5, 10) &< int8range(5, 10); -- trueWe can process math operations using:
Multirange data type syntax and operations
The multirange data type, introduced in PostgreSQL 14, allows for the representation of multiple non-contiguous ranges in a single value. It normalizes any overlapping or adjacent ranges within it and orders them by their lower bound.
//We add "multi" to previous range data types
create table doppio(
id serial primary key,
tanti int4multirange -- int8range, numrange, tsrange, tstzrange, daterange
)
//Compatible with any range data type casting
select int4multirange(int4range(12, 35), int4range(30, 48), int4range(50, 55));
-- {[12,48),[50,55)}
select int4multirange('[12, 48]'::int4range,'[50, 55]'::int4range);
-- {[12,49),[50,56)}
select int4multirange('[21, 43]'::int4range, int4range(40, 48)); -- {[21,48)}
select '{(12, 45), (30, 48), (50, 55)}'::int4multirange; -- {[13,48),[51,55)}We can apply range opration on multiranges:
The containment operator (@>) returns true if every value within the second element is completely contained by at least one range in the first multirange.
//It's compatible with single values
select '{[2,4), (10, 11)}'::int4multirange @> '[2,3)'::int4range; --true
select '{[1,4.5), (10.5, 11.5)}'::nummultirange @> 3.5::numeric; --true
select '{[2,4), (10, 11)}'::int4multirange @> '10'::int4; --f, exclusive bound
select int4multirange('[2, 4)', '(10, 11)') @> '3'::int4; @> '3'::int4; -- t
select'{[2,10), (15, 25)}'::int4multirange @>'{[2,3), (5,11)}'::int4multirange
-- false, (5, 11) isn't completely contained in [2, 10)
select int4multirange('[2, 10]','(15, 25)') @>'{[2,3), (5,8)}'::int4multirange
-- true, both ranges are contained within one of the rangesThe addition operator (+) returns a new normalized multirange value.
SELECT '{
[3, 15], (45, 66)}'::int4multirange +
int4multirange('[29, 32]'::int4range , int4range(50, 60)
); -- {[3,16),[29,33),[46,66)}The subtraction operation (-) returns a multirange containing parts of the first multirange that do not overlap with any ranges in the second.
The intersection operation (*) returns a multirange containing parts of the first multirange that do overlap with any ranges in the second.
//They are not compatible with single ranges, but with single value multiranges
SELECT '{[2, 10)}'::int4multirange - '{[5, 7)}'::int4multirange; -- {[2,5),[7,10)}
SELECT '{[2, 10), (15, 24)}'::int4multirange - '{[5, 12), (20, 30)}'::int4multirange;
-- {[2,5),[16,21)}
SELECT '{[2, 10)}'::int4multirange * '{[5, 7)}'::int4multirange; -- {[5,7)}
SELECT '{[2, 10), (15, 24)}'::int4multirange * '{[5, 12), (20, 30)}'::int4multirange;
-- {[5,10),[21,24)}Multirange on table column
The upper()/lower() function provides the upper/lower bound among all ranges within a multirange value.
select lower( int4multirange('[1, 5)', '(33, 43)') ); -- 1
select upper( int4multirange('[1, 5)', '(33, 43)') ); -- 43
//It returns a column of bounds from the multirange rows values.
create table multi1(
inizio int4multirange, fine int4multirange
);
insert into multi1(inizio, fine) values
(int4multirange('[3, 6]', '(10, 15]') , '{(10, 15], [3, 6]}'::int4multirange ),
(int4multirange('[1, 5]', '(33, 43]') , '{(4, 17], [27, 31]}'::int4multirange );
select lower(fine) from multi1; -- lower| 3| 5|
select lower(inizio) from multi1; -- lower| 3| 1|The range_agg() function aggregates individual range values from multiple rows into a single multirange.
//Single values merged into range and then aggregated into a multirange
create table multi(
id SERIAL PRIMARY key,
inizio int4, fine int4
);
SELECT range_agg(int4range(inizio, fine)) FROM multi; -- {[1,30), ...}
//It returns a normalized and ordered multirange
create table raggio(
serie int4range
);
insert into raggio(serie) values (('(14, 22]'), ('(1, 12]'), ('(24, 30]');
select range_agg(serie) from raggio where id < 2; -- {[2,23)}
select * from raggio;PostgreSQL composite custom data types
PostgreSQL data types include composite, enum, range and base.
The composite data type defines an object-like structure with multiple user-defined fields and their paired data type. Type casting during INSERT validates and adapts the data to the composite fields. If casting is not used, PostgreSQL validates each field individually and produces an error if the data does not match the field definitions.
//The composite fields values are based on the INSERT order
//Type casting is necessary during arrays INSERT.
//PostgreSQL truncates the string so to adapt it ot the field
CREATE TYPE tag_info AS (
name VARCHAR(5), importance integer
);
CREATE TABLE customtipo (
id SERIAL PRIMARY KEY,
tags tag_info[], -- Define tags as an array of the custom type tag_info
uno tag_info
);
--We only inserted oe value
insert into customtipo (tags) values (
array[ ('mono', 1)::tag_info, ('database', 2)::tag_info]
-- |tags |{"(mono,1)","(datab,2)"}|
);
insert into customtipo (uno) values
(('allungato', 33)::tag_info), -- (allun,33)
(('allungato', 33)); -- Error
select * from customtipo;
//Use the text output format for a more clear structure of the composite columns
id|tags |uno |
--+------------------------+----------+
1|{"(mono,1)","(datab,2)"}| |
2|NULL |(allun,33)|We can SELECT a single composite value column by specifying its name within parentheses (). By default, SQL interprets dot notation (.) as extracting columns; the parentheses force SQL to evaluate the value as a composite, enabling direct access to its individual fields in your SELECT query.
//uno.name would try to extract "name" from a uno sub-table, not a composite
//We can also reference the FROM table c variable to access it.
select
(uno).name AS tag_name,
(c.uno).importance AS tag_importance
FROM
customtipo1 c;
-- tag_name|tag_importance|
-- --------+--------------+
-- | | -- First row contains tags, not the SELECT uno
-- allun | 33|We update a composite data type column by returning a new composite value that includes the appended element.
//Like with tables, it allows for dot notation(.) to access composite fields values
//We don't use a method to modify the composite, we return a new one with a function
UPDATE customtipo SET tags = array_append(tags, ('web', 3)::tag_info) WHERE id = 1;
-- {"(mono,1)","(datab,2)","(web,3)"}
select (tags[2]).name from customtipo;
name |-----+ datab| NULL|Composite data type arguments on PostgreSQL functions
A SQL function can accept a custom data type as its argument, allowing us to access and manipulate its fields during each invocation.
//We use them as reusable data types that are independent of table storage.
//RAISE NOTICE uses the printf syntax, while SQL string output relies on concatenation
CREATE TYPE user_info AS (
id INT, name TEXT, email TEXT
);
CREATE OR REPLACE FUNCTION composite_user(p_user user_info)
RETURNS TEXT LANGUAGE plpgsql
AS $$
BEGIN
RAISE NOTICE 'user ID: %, name: %, email: %', p_user.id, p_user.name,p_user.email;
RETURN 'User " ' || p_user.name || ' " (ID: ' || p_user.id || ') processed.';
END;
$$;
SELECT composite_user((101, 'Alice', 'alice@example.com')::user_info);
-- user ID: 101, name: Alice, email: alice@example.com
-- User " Alice " (ID: 101) processed successfully.A composite data type shares its structure with a table, as both are ordered collections of named attributes with assigned data types.
Every CREATE TABLE statement implicitly defines a corresponding composite data type, which serves as the row structure for the instances stored in the table. The table provides persistent storage for this data, managed by its composite row type.
We can use a table's implicit composite data type as a custom argument in SQL functions, passing specific instances of users%ROWTYPE directly from a SELECT query.
//INSERT and SELECT add and retrieve row instances from the table, respectively.
//We reference the exact field names from the table.
CREATE OR REPLACE FUNCTION table_user(p_user_data users)
RETURNS TEXT LANGUAGE plpgsql
AS $$
BEGIN
RAISE NOTICE 'user ID: %, name: %, email: %',
p_user_data.id, p_user_data.name, p_user_data.email;
RETURN 'User "' || p_user_data.name || '" (ID: ' ||p_user_data.id|| ') processed';
END;
$$;
CREATE TABLE users (
id INT PRIMARY KEY, name TEXT, email TEXT
);
INSERT INTO users VALUES (104, 'Charlie', 'charlie@example.com');
SELECT table_user(u) FROM users u WHERE u.id = 104; -- u users (data type)
-- user ID: 104, name: Charlie, email: charlie@example.com
-- User "Charlie" (ID: 104) processed successfully.
//We can convert the table ROW into a different composite type argument
SELECT composite_user(ROW(u.id,u.name,u.email)::user_info) FROM users u WHERE u.id=103
-- row user_info (data type)Instead of passing a query result as a SQL function argument, we can process the query within a PL/pgSQL DO block.
The declared variable data type %ROWTYPE will hold the row structure of the instances returned by the SELECT ... INTO query. It contains the implicit composite value of the table row, allowing us to directly access and manipulate its fields.
//An anonymous DO clock doesnt need SQL arguments.
//We can UPDATE the original table within the DO block.
CREATE TABLE page (
id SERIAL PRIMARY KEY,
title TEXT, content TEXT
);
INSERT INTO page (title, content) VALUES
('My First Blog Post', 'This is the first.'),
('About Us', 'Learn more about our second.'),
('Contact Information', 'Reach out to third.');
//We specify the table name in page%ROWTYPE
DO $$
DECLARE
my_page_row page%ROWTYPE;
BEGIN
SELECT * INTO my_page_row FROM page WHERE id = 2;
RAISE NOTICE 'Page ID: %,Title: %',my_page_row.id, my_page_row.title || ' concat';
//The %ROWTYPE fields can be used for internal operations
IF LENGTH(my_page_row.content) < 50 THEN
RAISE NOTICE 'This page has short content.';
ELSE
RAISE NOTICE 'This page has substantial content.';
END IF;
//We UPDATE the table with the variable contaning a new %ROWTYPE value
my_page_row.title := my_page_row.title || ' (UPDATED)';
UPDATE page SET title = my_page_row.title WHERE id = my_page_row.id;
END;
$$ LANGUAGE plpgsql;
-- Page ID: 2, Title: About Us concat
-- This page has short content.
select * from page where id = 2;
-- 2|About Us (UPDATED) |Learn more about our second.|Last updated
Was this helpful?