Exploring SQL command changes in PG-14

Enterprise PostgreSQL Solutions

1 comment

Exploring SQL command changes in PG-14

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.

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.

One Response

  1. […] post Exploring SQL command changes in PG-14 appeared first on Highgo Software […]

Leave a Reply

Your email address will not be published. Required fields are marked *