Creating a key value pair table

I have a table with 32 columns whose primary key is play_id and my goal is to create a key value pair table where the output would look like this:

play_id, field_name, field_value
play_id_1, game_date, ‘2020-07-23’
play_id_1, possession, ‘home’
'       ', '        ', '    '
play_id_2, game_date, ‘2020-07-24’
play_id_2, possession, ‘away’

where all the entries in field_name are columns from the original table (besides play_id). So there would be 31 appearances of play_id_1 in this new table. I want the new primary key for this table to be (play_id, field_name). There’s a long way of doing it where I can query for play_id and each column and union everything together, but I want to find a more elegant solution.

Answer

You can use a lateral join for that. But you will need to cast all values to text.

select t.play_id, x.*
from the_table t
  cross join lateral (
    values 
       ('game_date', game_date::text),
       ('possession', possession::text),
       ('col_3', col_3::text),
       ...
  ) as x(field_name, field_value);

Another option is to convert the row to a JSON value and then unnest that json:

select t.play_id, x.*
from the_table t
  cross join lateral jsonb_each(to_jsonb(t) - 'play_id') as x(field_name, field_value);