How to insert values into a table that has an autogenerated column

Question

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

Answer 1

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