I have spent sometime exploring all the SQL command changes in PostgreSQL 14, this blog will provide a brief summary about the SQL changes along with some examples. There are exciting new features that in progress for PG-14, this blog will only focus on the SQL changes that are committed and will be released with PG-14. Most of then SQL command changes are minor enhancements, there are some SQL command patches that are still under consideration, have mentioned those briefly in the last section.
Without the “AS”
Using a Keywords for column alias in SELECT query is already supported, But unlike non-keyword alias we need to precede keyword alias with “AS” clause. Following is a simple example of a keyword as column alias using the AS clause :
create table dept( deptno numeric(2,0), dname varchar(14), loc varchar(13), constraint pk_dept primary key (deptno) ); postgres=# select loc as analyze from dept; analyze --------- (0 rows) If you run this without the AS clause in PG-13, it will throw an error postgres=# select loc analyze from dept; ERROR: syntax error at or near "analyze" LINE 1: select loc analyze from dept; In PG-14 Keywords are allowed as column alias without the “AS” prefix postgres=# select loc analyze from dept; analyze --------- (0 rows) postgres=# select loc as analyze from dept; analyze --------- (0 rows)
Their are some keywords that still need to be preceded by AS :
array, as, char, character, create, day, except, fetch, filter, for, from, grant, group, having, hour, intersect, into, isnull, limit, minute, month, notnull, offset, on, order, over, overlaps, precision, returning, second, to, union, varying, where, window, with, within, without, year
The list of keywords that don’t need the AS prefix is pretty long hence not included in this blog.
Add support for leading/trailing trim()ing
This is minor enhancement for trim(bytea) function for trimming the leading and trailing 0’s from a bytea value. Prior to this enhancement in PG-14, the bytea version of trim() function supported the BOTH clause. This allows both leading and trailing values to be trimmed from the bytea function.
postgres=# SELECT trim(both '\x00'::bytea FROM '\x0000beefbabe00'::bytea);
btrim
------------
\xbeefbabe
(1 row)
This enhancement will allow trimming leading or trailing zero’s from bytea value as shown below :
SELECT trim(TRAILING '\x00'::bytea FROM '\x0000beefbabe00'::bytea);
rtrim
----------------
\x0000beefbabe
(1 row)
SELECT trim(LEADING '\x00'::bytea FROM '\x0000beefbabe00'::bytea);
ltrim
--------------
\xbeefbabe00
(1 row)
Allow current_role in GRANTED by
This is another minor enhancement for SQL conformance, the grant role…privilege is supported in PG-13 as shown below :
GRANT role_name [, ...] TO role_specification [, ...]
[ WITH ADMIN OPTION ]
[ GRANTED BY role_specification ]
where role_specification can be:
[ GROUP ] role_name
| PUBLIC
| CURRENT_USER
| SESSION_USER
CURRENT_ROLE is equivalent to CURRENT_USER but you will get an error if you try to use it in GRANT role…
postgres=# grant foo to use1 granted by current_role;
ERROR: syntax error at or near "current_role"
LINE 1: grant foo to use1 granted by current_role;
This has been addressed in PG-14
postgres=# grant foo to use1 granted by current_role;
GRANT ROLE
Create or Replace trigger support
The create trigger command doesn’t support “or Replace” hence if trigger definition needs to be changed, the previous one needs to be dropped. PG-14 will support “create or replace trigger…”, this is also a important enhancement for Oracle to PostgreSQL migration.
postgres=# create table my_table (id integer); CREATE TABLE postgres=# postgres=# postgres=# create function funcA() returns trigger as $$ postgres$# begin postgres$# raise notice 'hello from funcA'; postgres$# return null; postgres$# end; $$ language plpgsql; CREATE FUNCTION postgres=# postgres=# postgres=# create function funcB() returns trigger as $$ postgres$# begin postgres$# raise notice 'hello from funcB'; postgres$# return null; postgres$# end; $$ language plpgsql; CREATE FUNCTION postgres=# postgres=# create trigger my_trig postgres-# after insert on my_table postgres-# for each row execute procedure funcA(); CREATE TRIGGER The following command will fail : postgres=# create trigger my_trig postgres-# after insert on my_table postgres-# for each row execute procedure funcB(); ERROR: trigger "my_trig" for relation "my_table" already exists In PG-14, we can use the “or Replace” to create the trigger.. ostgres=# create or replace trigger my_trig postgres-# after insert on my_table postgres-# for each row execute procedure funcB(); CREATE TRIGGER postgres=# postgres=# insert into my_table values (1); NOTICE: hello from funcB INSERT 0 1
Support REINDEX concurrently on Partitioned table
PostgreSQL 13 already support creating concurrent indexes on partitioned tables however the concurrent indexes can created on the child partitions not on the parent partition. Concurrent indexes will avoid locking the table for updates while indexes are being built on the table. The following will create a concurrent index on partitioned table.
postgres=# CREATE TABLE reind_conc_parent (id int) PARTITION BY RANGE (id); CREATE TABLE postgres=# CREATE TABLE reind_conc_0_10 PARTITION OF reind_conc_parent postgres-# FOR VALUES FROM (0) TO (10); CREATE TABLE postgres=# CREATE TABLE reind_conc_10_20 PARTITION OF reind_conc_parent postgres-# FOR VALUES FROM (10) TO (20); CREATE TABLE
The following command will throw an error as creating concurrent indexes on parent partition table is not supported.
postgres=# create index concurrently conc_id_idx on reind_conc_parent (id); ERROR: cannot create index on partitioned table "reind_conc_parent" concurrently Create concurrent indexes is allowed on the partitions : postgres=# create index concurrently conc_id_idx on reind_conc_0_10(id); CREATE INDEX postgres=# create index concurrently conc_id_idx_2 on reind_conc_10_20(id); CREATE INDEX PG-14 will support reindexes the partition table, the following will throw an error in PG-13 : postgres=# reindex table concurrently idxpart; WARNING: REINDEX of partitioned tables is not yet implemented, skipping "idxpart" NOTICE: table "idxpart" has no indexes that can be reindexed concurrently REINDEX Reindexing on partition table is supported in PG-14 : postgres=# reindex table concurrently reind_conc_parent; REINDEX
split_part function support negative index
The split_part() function is enhanced in PG-14 to support negative index value( third argument), currently in PG-13 specifying a negative index value produces an error.
postgres=# select split_part('','@',-1) AS "empty string";
ERROR: field position must be greater than zero
The split_part function accepts the following arguments :
split_part(string text, delimiter text, field int);
The split_part function splits a given string at a specific delimiter and return the given field (counting from one). The text representing the delimiter can be multiple characters, the third field is the part number which is the requested part of the string. With the inclusion of negative index, it counts from the right instead of the left.
— Negative part number, parts counted backward
postgres=# select split_part('one,two,three', ',', -1);
split_part
------------
three
(1 row)
postgres=# select split_part('one,two,three', ',', -2);
split_part
------------
two
(1 row)
postgres=# select split_part('one,two,three', ',', -3);
split_part
------------
one
(1 row)
postgres=# select split_part('one,two,three', ',', -4);
split_part
------------
(1 row)
The following SQL command changes are not committed to PG-14 yet, they are marked for ready for committer.
- Automatic hash and list partition creation https://commitfest.postgresql.org/32/2694/
- Extending the range operators so they can cope with range <op> element and element <op> range in addition to the existing range <op> range https://commitfest.postgresql.org/32/2747/
Conclusion
I have provided a preview of SQL command changes that will be part of next major release of PostgreSQL. As mentioned in the introduction, the SQL command changes are mostly trivial. I will be writing about more exciting new features in PostgreSQL 14 in my next blog of this series.
Ahsan Hadi
Recent Comments