Postgres generated column silently failing



  • 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}')
    


  • 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.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2