如何在 PostgreSQL 中保留自动生成的列,即使删除了源列?

huangapple go评论59阅读模式
英文:

How do I keep auto-generated columns in PostgreSQL even after removing the source columns?

问题

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

--  创建GEOGRAPHY点的函数,使用给定的输入
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, -- 想要省略
  latitude_from DOUBLE PRECISION NOT NULL,  -- 想要省略
  longitude_to DOUBLE PRECISION NOT NULL,   -- 想要省略
  latitude_to DOUBLE PRECISION NOT NULL,    -- 想要省略
  -- 添加自动生成的 'location_from' 列
  location_from GEOGRAPHY
    GENERATED ALWAYS AS
    (create_point_geography(longitude_from, latitude_from))
      stored,
  -- 添加自动生成的 'location_to' 列
  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 使用给定的值自动生成,而不是基于其他列。

这是否可能?

英文:

I have a table that has autogenerated columns:

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

As you can see 2 columns are autogenerated based on values of other columns. I want to remove the columns named longitude_from, latitude_from, longitude_to, latitude_to whose values are used in generator function. But I want to keep auto-nenerated columns. So, basically I want columns location_from, location_to be auto-generated with given values, not based on other columns.

Is it possible?

答案1

得分: 1

不可以!你不能直接做你想做的事情。文档 明确指出:

> 生成的列是一种特殊列,它始终从其他列计算得出

然而,你可以在 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)
          , ... 
          );  
英文:

NO! You cannot do what you want - at least directly. The documentation clearly indicates this:

> A generated column is a special column that is always computed from
> other columns
.

You can however get the results by making the conversion to a GEOGRAPHY column during the insert by calling the same function.

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

huangapple
  • 本文由 发表于 2023年5月30日 00:46:48
  • 转载请务必保留本文链接:https://go.coder-hub.com/76359035.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定