PostgreSQL Question posted in
The official documentation can be found here.

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

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 "INSER 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

Tags:

2

Answers


  1. 0

    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');
    Login or Signup to reply.
  2. 0

    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);
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search