英文:
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)
, ...
);
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论