英文:
How do I keep auto-generated columns in PostgreSQL even after removing the source columns?
问题
我有一个包含自动生成列的表：
-- 创建GEOGRAPHY点的函数，使用给定的输入create or replacefunction create_point_geography (long DOUBLE PRECISION, lat DOUBLE PRECISION)returns GEOGRAPHYlanguage sqlimmutableas $$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 GEOGRAPHYGENERATED ALWAYS AS(create_point_geography(longitude_from, latitude_from))stored,-- 添加自动生成的 'location_to' 列location_to GEOGRAPHYGENERATED 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 inputscreate or replacefunction create_point_geography (long DOUBLE PRECISION, lat DOUBLE PRECISION)returns GEOGRAPHYlanguage sqlimmutableas $$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 omitlatitude_from DOUBLE PRECISION NOT NULL, -- want to omitlongitude_to DOUBLE PRECISION NOT NULL, -- want to omitlatitude_to DOUBLE PRECISION NOT NULL, -- want to omit-- add autogenerated 'location_from' columnlocation_from GEOGRAPHYGENERATED ALWAYS AS(create_point_geography(longitude_from, latitude_from))stored,-- add autogenerated 'location_to' columnlocation_to GEOGRAPHYGENERATED 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), ...);
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论