The following bug has been logged on the website:
Bug reference: 17759
Logged by: Kyro
Email address: adminnz@gmail.com
PostgreSQL version: 15.1
Operating system: Debian (Docker)
Description:
I'm running the docker image postgis/postgis:15-master which is reporting as
version "PostgreSQL 15.1 (Debian 15.1-1.pgdg110+1) on x86_64-pc-linux-gnu,
compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit".
A table with "GENERATED ALWAYS" columns do not appear to get updated when
the update is done via a MERGE statement.
Below is some SQL that can reproduce this issue.
-- create table with generated column using data-type postgis, and also
another without postgis to show its not limited to postgis datatype
columns
create table latest_position
(
id varchar(12) not null
,latitude numeric(11,8) not null
,longitude numeric(11,8) not null
,point geography(POINT) GENERATED ALWAYS AS (ST_POINT(longitude, latitude,
4326)::geography) STORED
,point_text text GENERATED ALWAYS AS (latitude::text || ' + ' ||
longitude::text) STORED
);
--insert some data to test if generated columns update
insert into latest_position VALUES ('1', -31.3848, 173.84848);
select *, ST_Y(point::geometry), ST_X(point::geometry) from
latest_position;
-- ST_Y & ST_X result in -31.3848, 173.84848 respectively which is correct.
-- and point_text column should be "-31.3848 & 173.84848" which it is.
-- update some data to test if generated columns update
update latest_position set latitude = -41.3848, longitude = 143.332211 where
id = '1';
select *, ST_Y(point::geometry), ST_X(point::geometry) from
latest_position;
-- ST_Y & ST_X result in -41.3848, 143.332211 respectively which is correct.
-- and point_text column should be "-41.3848 & 143.332211" which it is.
-- update some data via merge to test if generated columns update
MERGE INTO latest_position as trg
USING
(VALUES('1'::varchar(12),-51.3312684::numeric(11,8),173.2041482::numeric(11,8)))
as src(id,latitude,longitude)
ON trg.id = src.id
WHEN MATCHED THEN
UPDATE SET latitude = src.latitude, longitude = src.longitude;
select *, ST_Y(point::geometry), ST_X(point::geometry) from
latest_position;
-- ST_Y & ST_X do not result in 51.3312684, 173.2041482 respectively which
is incorrect.
-- and point_text column should be "-41.3848 & 143.332211" but it isnt.
