1

I am recreating a table which used to be updated by triggers, to the new generated column types.

I'm running into a problem where if the generated column can be NULL, they silently fail and get inserted as NULL. If the columns are NOT NULL the insert fails as a NOT NULL constraint.

Both cases fail and don't generate the expected cell values.

create table test_table
(
    coord_array          double precision[]                                                                                                   not null,
    wkb_geometry         geometry generated always as (st_setsrid(st_point(coord_array[0], coord_array[1]), 4326)) stored                     not null,
    wkt_geometry         text generated always as (st_astext(st_setsrid(st_point(coord_array[0], coord_array[1]), 4326))) stored              not null,
    geojson_coord_string jsonb generated always as ((st_asgeojson(st_setsrid(st_point(coord_array[0], coord_array[1]), 4326)))::jsonb) stored not null
);

Ideally, the insert should only happen with values for coord_array

 insert into test_table (coord_array) values ('{25.0 ,-26.0}')
2
  • A strange data model. Perhaps you are looking for a check constraint that makes sure that coord_array has two non-NULL elements. Jan 25, 2022 at 7:14
  • very true, the coord_array can be less than 2 values, which should be rejected. A trigger could solve that Jan 25, 2022 at 7:25

1 Answer 1

3

The immediate problem with your code is, that in SQL (and Postgres) arrays start at index 1, not 0. So coord_array[0] will always return null and thus the expressions of your generated columns always return null, even if two non-null array values are provided.

However, the not null constraint on the array itself is not enough. This does not ensure that the array contains (at least) two not null values. To validate that, you need a check constraint.

create table test_table
(
    coord_array          double precision[]                                                                                                   not null,
    wkb_geometry         geometry generated always as (st_setsrid(st_point(coord_array[1], coord_array[2]), 4326)) stored                     not null,
    wkt_geometry         text generated always as (st_astext(st_setsrid(st_point(coord_array[1], coord_array[2]), 4326))) stored              not null,
    geojson_coord_string jsonb generated always as ((st_asgeojson(st_setsrid(st_point(coord_array[1], coord_array[0]), 4326)))::jsonb) stored not null,
    constraint check_coord 
       check (num_nulls(coord_array[1], coord_array[2]) = 0)
);

This would allow arrays with more than two elements - but your generated columns won't care as the expressions never use more than the first two elements.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.