dynamic-col-ordering.sql

CREATE TABLE target (
  center text,
  forward text,
  point text,
  shooting text
);

CREATE TEMP TABLE players AS (
  SELECT 'kevin' as forward, 'steph' as point, 'klay' as shooting, 'draymond' as center
  UNION ALL
  SELECT 'scottie' as forward, 'ron' as point, 'michael' as shooting, 'luc' as center
);

INSERT INTO target SELECT * FROM players;
SELECT * FROM target; -- values in wrong columns
DELETE FROM target;

DO $$
  BEGIN
    EXECUTE FORMAT('INSERT INTO target SELECT %s FROM players', (
      SELECT '"' || string_agg(col, '","') || '"'
      FROM (
        SELECT attname AS col
        FROM pg_attribute
        JOIN pg_class
          ON pg_class.relfilenode = pg_attribute.attrelid
        WHERE pg_attribute.attnum > 0
        AND pg_class.relname = 'target'
        ORDER BY pg_attribute.attnum
      ) target_cols LIMIT 1));
  END;
$$;
SELECT * FROM target; -- values in correct columns!