I have a table named “liverpool_players” and I’m trying to create a new one with an additional column “years_at_club”, which I mean for to be autogenerated as at date 31 Dec 2024. I run the following sql statements in PostgreSQL:
create table liverpool_players_new
(
name varchar(30),
age int,
position varchar(10),
club_debut date,
years_at_club decimal(4,2) GENERATED ALWAYS as (('2024-12-31' - club_debut)/365.25) STORED,
country varchar(20)
);
insert into liverpool_players_new
(select name, age, position, club_debut, [WHAT GOES HERE?], country
from liverpool_players);
Thing is I have no idea what to put in the “years_at_club” field when copying values from the old table. When I put a value (whether calculated or just any dummy decimal number) I get an error that the column is autogenerated and thus should not be populated. When I omit the field altogether I get an error that the dimensions don’t match (as expected). Weird enough, when I put the “years_at_club” column at the end in the CREATE TABLE statement and then omit its value in the INSERT INTO statement it works just fine!
Like so:
create table liverpool_players_new
(
name varchar(30),
age int,
position varchar(10),
club_debut date,
country varchar(20),
years_at_club decimal(4,2) GENERATED ALWAYS as (('2024-12-31' - club_debut)/365.25) STORED
);
insert into liverpool_players_new
(select name, age, position, club_debut, country
from liverpool_players);
But I’m stubborn and I want to be able to choose freely the exact position of my years_at_club column! 🙂
Any help would be appreciated please. Thank you in advance
2
-
The
insert..valueslist lets you specifydefault. demo at db<>fiddleinsert into liverpool_players_new values('name1', 20, 'position1', current_date, default, 'country1');Unfortunately, that’s not allowed in an
insert..select, as underlined under @JGH’s example. -
Skip the column if you told your
insertwhat’s what in the input, listing target columns:insert into liverpool_players_new(name, age, position, club_debut, country) select name, age, position, club_debut, country from liverpool_players;…as immediately pointed out by @Andrew and expanded on by @SQLpro.
-
Put the table behind an updatable
viewthat skips the column, target that view instead:create view v_liverpool_players_new as select name, age, position, club_debut, country from liverpool_players_new; insert into v_liverpool_players_new select name, age, position, club_debut, country from liverpool_players; -
Look up the default or generated expression in
pg_attrdefsystem catalog and reconstruct it withpg_get_expr(adbin, adrelid)if you need this for dynamic SQL statements.select pg_get_expr(adbin, adrelid) from pg_attrdef as ad join pg_attribute as a on a.attrelid=ad.adrelid where ad.adrelid='liverpool_players_new'::regclass and a.attname='years_at_club';pg_get_expr (((‘2024-12-31’::date – club_debut))::numeric / 365.25)
The Backus Naur form of SQL query command’s syntax is as:
INSERT [ INTO ] <table> [ ( <list_of_target_columns> )
{ VALUES ( <value_list> )
| <SELECT_query> }
Yous mixed query and value list. Also if any column is auto generated, yous have the choice to avoid this column in the <list_of_target_columns>
In your case you must use the optional <list_of_target_columns> and avoid the “years_at_club” columns name in the list or replace the value of the “years_at_club” column by the key word DEFAULT if you use a value_list
So the correct query must be:
INSERT INTO liverpool_players_new
(name, age, position, club_debut, country)
SELECT name, age, position, club_debut, country
FROM liverpool_players);
In addition to specifying the column names, you can also use the default value:
insert into liverpool_players_new (name, age, position, club_debut, years_at_club, country)
values ('test',1,'test', current_date, default, 'test');
1