Postgres 生成的列靜默失敗

我正在將一個曾經由觸發器更新的表重新創建為新生成的列類型。

我遇到了一個問題，如果生成的列可以NULL，它們會默默地失敗並插入為NULL. 如果列是NOT NULL插入失敗，則為NOT NULL constraint.

兩種情況都失敗並且不會生成預期的單元格值。

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
);

理想情況下，插入應該只發生在值為coord_array

insert into test_table (coord_array) values ('{25.0 ,-26.0}')

您的程式碼的直接問題是，在 SQL（和 Postgres）中，數組從索引 1 開始，而不是 0。因此coord_array[0]將始終返回null，因此生成列的表達式始終返回 null，即使提供了兩個非 null 數組值.

但是，not null對數組本身的約束是不夠的。這並不能確保數組包含（至少）兩個非空值。要驗證這一點，您需要一個檢查約束。

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)
);

這將允許具有兩個以上元素的數組 - 但您生成的列不會在意，因為表達式從不使用超過前兩個元素。

引用自：https://dba.stackexchange.com/questions/306543