如何在删除源列后保留PostgreSQL中自动生成的列?

w8biq8rn  于 2023-06-05  发布在  PostgreSQL
关注(0)|答案(1)|浏览(259)

我有一个自动生成列的表:

--  function that creates GEOGRAPHY point from given inputs
create or replace 
function create_point_geography (long DOUBLE PRECISION, lat DOUBLE PRECISION)
  returns GEOGRAPHY
  language sql 
  immutable 
as $$
  select ST_GeogFromText(CONCAT('SRID=4326;POINT(', long::text, ' ', lat::text, ')'))
$$;

CREATE TABLE driver_orders (
  id SERIAL PRIMARY KEY,
  driver_id INTEGER NOT NULL,
  address_from VARCHAR NOT NULL,
  address_to VARCHAR NOT NULL,
  active BOOLEAN DEFAULT true,
  date DATE NOT NULL,
  longitude_from DOUBLE PRECISION NOT NULL, -- want to omit
  latitude_from DOUBLE PRECISION NOT NULL,  -- want to omit
  longitude_to DOUBLE PRECISION NOT NULL,   -- want to omit
  latitude_to DOUBLE PRECISION NOT NULL,    -- want to omit
  -- add autogenerated 'location_from' column
  location_from GEOGRAPHY
    GENERATED ALWAYS AS
    (create_point_geography(longitude_from, latitude_from))
      stored,
  -- add autogenerated 'location_to' column
  location_to GEOGRAPHY
    GENERATED ALWAYS AS
    (create_point_geography(longitude_to, latitude_to))
      stored
);

正如您所看到的,2列是基于其他列的值自动生成的。我想删除名为longitude_from, latitude_from, longitude_to, latitude_to的列,其值在生成器函数中使用。但我想保留自动生成的列。所以,基本上我希望列location_from, location_to是用给定的值自动生成的,而不是基于其他列。
这可能吗?

sczxawaw

sczxawaw1#

不!你不能做你想做的事--至少不能直接做。documentation清楚地表明:
生成的列是一个特殊的列,它总是从其他列计算得出。
但是,您可以通过在insert期间调用相同的函数将其转换为GEOGRAPHY列来获得结果。

create table driver_orders (
  id            serial primary key,
  driver_id     integer not null,
  address_from  varchar not null,
  address_to    varchar not null,
  active        boolean default true,
  date          date    not null,
  location_from geography not null,
  location_to   geography not null
);

insert into driver_orders(location_from, location_to , ...) 
     select create_point_geography(longitude_from, latitude_from)
          , create_point_geography(longitude_to, latitude_to)
          , ... 
          );

相关问题