sqlpostgresqlpostgresql-9.5

PostgreSQL copy row minus a few columns


How can I duplicate a row, excluding a couple columns, without listing out every column I want to copy? My concern is doing it as an include vs. an exclude is that if I add a new column or remove a column from the table, I have to remember to update this stored procedure that does the clone to list the new columns.


Solution

  • You have to explicitly list columns or use *. No shortcut for * except short_list. You can try hacking this with dynamic sql, preparing query from existing columns from eg information_schema.columns:

    t=# select column_name,ordinal_position from information_schema.columns where table_name ='s160' order by ordinal_position;
     column_name | ordinal_position
    -------------+------------------
     id          |                1
     a           |                2
     b           |                3
     c           |                4
     d           |                5
    (5 rows)
    

    So saving previous column list ang comparing it against current would give you new columns and you can adapt changes in execute format(..., but honestly - it leads to more problems then specifying explicit list of columns every time.