Postgres 13

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
DROP TABLE IF EXISTS tmpOrigDat;

DROP TABLE


CREATE TEMP TABLE tmpOrigDat AS
SELECT
*
FROM
( VALUES
 ( 'a' , 1 ) ,( 'b' , 2 ) ,( 'c' , 3 ) ,( 'd' , 4 ) ,( 'e' , 5 )
,( 'f' , 6 ) ,( 'g' , 7 ) ,( 'h' , 8 ) ,( 'j' , 9 ) ,( 'k' , 10)
,( 'l' , 11) ,( 'm' , 12) ,( 'n' , 13) ,( 'o' , 14) ,( 'p' , 15)
) a ( col1oldname , col2oldname );
SELECT * FROM tmpOrigDat LIMIT 5 ; 

SELECT 15




col1oldname
col2oldname


a
1

b
2

c
3

d
4

e
5



SELECT 5


/*
 create new but compatible rowtype
 (could be table-driven from a data dictionary)
 */
DROP TABLE IF EXISTS tmpNewRowType;
CREATE TEMP TABLE tmpNewRowType AS SELECT NULL::CHAR(1) AS zCol1newname2  ,NULL::INT AS zCol2newname1 LIMIT 0;
SELECT * FROM tmpNewRowType ; 

DROP TABLE



SELECT 0




zcol1newname2
zcol2newname1



SELECT 0


/**
   we can save it however if we just create the anon row
   but re-expand it right away saving the fields as generic f1, f2 ....
   every column is renamed & saved automatically 
*/
DROP TABLE IF EXISTS tmpPseudoSave;
CREATE TEMP TABLE tmpPseudoSave AS
SELECT (rowrow).* -- expand the anon table struct as f1, f2 .... 
FROM
 ( -- anon row type that we can save w/out the error: " [42P16] ERROR: column "rowrow" has pseudo-type record " 
   SELECT  ROW( (zz).* ) rowrow FROM (SELECT * FROM  tmpOrigDat  ) zz
 ) s1;
-- note the f1, f2 "pseudo" column names
SELECT * FROM tmpPseudoSave LIMIT 5;

DROP TABLE



SELECT 15




f1
f2


a
1

b
2

c
3

d
4

e
5



SELECT 5


/** 
 we can also retrieve data from the pseudo-save table
  but give them new names 
 */
SELECT  ((r)::tmpNewRowType).* 
FROM ( SELECT * FROM tmpPseudoSave ) r -- needs to be encapsulated as a CTE subselect (r)
LIMIT 5 
;


zcol1newname2
zcol2newname1


a
1

b
2

c
3

d
4

e
5



SELECT 5


/*
 we can also rename the columns from the orig. table
*/ 
SELECT  ((r)::tmpNewRowType).* 
FROM ( SELECT * FROM tmpOrigDat  ) r -- needs to be encapsulated as a CTE subselect (r)
LIMIT 5 
;


zcol1newname2
zcol2newname1


a
1

b
2

c
3

d
4

e
5



SELECT 5