主页 / dba / 问题 / 306543
Accepted
Kevin Potgieter
Asked: 2022-01-25 22:08:47 +0800 CST

Postgres 生成的列静默失败

  • 772

我正在将一个曾经由触发器更新的表重新创建为新生成的列类型。

我遇到了一个问题，如果生成的列可以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}')
postgresql postgis

1 个回答

  1. Best Answer

    您的代码的直接问题是，在 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)
);

    这将允许具有两个以上元素的数组 - 但您生成的列不会在意，因为表达式从不使用超过前两个元素。

    • 2

相关问题