How do I get datatype for a column in postgres data dictionaries?

Question

Is it possible in postgres to copy a column (just data type) to another table. I have a trigger that attempts to maintain a mirror/clone of a table along with some other information. If we add a column to the table it should also be added to the clone automatically via a ddl trigger. Its mainly used for a data audit process and the columns in the audit copy need to match the data type. The clone table does not need to (nor should it) maintain things like foreign key constraints, indexes, or any other constraints but it needs to have the same data types and columns as the original table.

I have something working but I am trying to clean up the approach. Essentially I have a trigger that listens for table create/update. Then it reads the information_schema.columns view to compare the columns in the table with the audit table. If there is a new column added I will add it to the cloned table:

alter table XYZ add column ABC [datatype]

The issue is in figuring out the data type since nothing in information_schema.columns tells you the exact type to use. For instance if its a numeric type you need to look at other columns to figure out precision and scale. If its a character type you need to look at at the character_maximum_length column. Etc etc for every data type. I have a function that maps information_schema.columns.udt_name to a data type but Im not convinced I got all the mappings 100% correct.

Is there some easier way to get the data type so I can clone a column from one table to another? Bellow is my stab but clearly Im going to miss some data type if I go this route.

create or replace view system.column_type_view as
select c.table_schema, c.table_name, c.column_name, c.ordinal_position,
case 
    when udt_name = 'bit' then 'bit' || calculated_precision || '' 
    when udt_name = '_bit' then 'bit' || calculated_precision || '[]' 
    when udt_name = 'varbit' then 'bit varying' || calculated_precision || '' 
    when udt_name = 'bpchar' then 'character' || calculated_precision || '' 
    when udt_name = '_bpchar' then 'character' || calculated_precision || '[]' 
    when udt_name = 'varchar' then 'character varying' || calculated_precision || ''
    when udt_name = 'interval' then 'interval' || calculated_precision || '' 
    when udt_name = '_interval' then 'interval' || calculated_precision || '' 
    when udt_name = 'numeric' then 'numeric' || calculated_precision || '' 
    when udt_name = '_numeric' then 'numeric' || calculated_precision || '[]' 
    when udt_name = 'timetz' then 'time' || calculated_precision || ' with time zone'
    when udt_name = '_timetz' then 'time' || calculated_precision || ' with time zone[]'
    when udt_name = 'time' then 'time' || calculated_precision || ' without time zone'
    when udt_name = '_time' then 'time' || calculated_precision || ' without time zone[]'
    when udt_name = 'timestamptz' then 'timestamp' || calculated_precision || ' with time zone'
    when udt_name = '_timestamptz' then 'timestamp' || calculated_precision || ' with time zone[]'
    when udt_name = 'timestamp' then 'timestamp' || calculated_precision || ' without time zone'
    when udt_name = '_timestamp' then 'timestamp' || calculated_precision || ' without time zone[]'
    else data_type end data_type
from system.column_precision_view c;



create or replace view system.column_precision_view as
select c.table_schema, c.table_name, c.column_name, 
c.ordinal_position, c.data_type, c.udt_name,
case 
    when udt_name in ('bit','_bit','varbit','bpchar','_bpchar','varchar' ) and c.character_maximum_length is not null then '(' || c.character_maximum_length || ')' 
    when udt_name in ('bit','_bit','varbit','bpchar','_bpchar','varchar' ) and c.character_maximum_length is     null then ''
    when udt_name in('numeric','_numeric') and c.numeric_precision is not null and c.numeric_scale is not null then '('||c.numeric_precision || ',' || c.numeric_scale || ')' 
    when udt_name in('numeric','_numeric') and c.numeric_precision is not null then '('||c.numeric_precision || ')' 
    when udt_name in('numeric','_numeric') and c.numeric_precision is     null then '''
    when udt_name in ('interval','_interval','timetz','_timetz','time','_time','timestamptz','_timestamptz','timestamp','_timestamp') and c.datetime_precision is not null then '(' || c.datetime_precision || ')' 
    when udt_name in ('interval','_interval','timetz','_timetz','time','_time','timestamptz','_timestamptz','timestamp','_timestamp') and c.datetime_precision is     null then ''
    else '' end calculated_precision,
case when left(udt_name,1) = '_' then true else false end is_array
from information_schema.columns c;

Answer 1

Hah! Finally, a perfect use case for PostgreSQL table inheritance:

CREATE TABLE tab (col1 integer, col2 text);

CREATE TABLE tab_mirror () INHERITS (tab);

\d tab_mirror
            Table "laurenz.tab_mirror"
 Column │  Type   │ Collation │ Nullable │ Default 
════════╪═════════╪═══════════╪══════════╪═════════
 col1   │ integer │           │          │ 
 col2   │ text    │           │          │ 
Inherits: tab

ALTER TABLE tab ADD col3 polygon;

\d tab_mirror
            Table "laurenz.tab_mirror"
 Column │  Type   │ Collation │ Nullable │ Default 
════════╪═════════╪═══════════╪══════════╪═════════
 col1   │ integer │           │          │ 
 col2   │ text    │           │          │ 
 col3   │ polygon │           │          │ 
Inherits: tab

tab_mirror will always have all columns from tab (though you could add additional ones), but otherwise it is an independent table. It need not have the same indexes and constraints as tab.

There is only one drawback (although that may be a severe one): if you SELECT from tab, the result will include all rows of tab_mirror! To avoid that, you have to use

SELECT ... FROM ONLY tab

If that is an option for you, table inheritance should do all you need. A feasible workaround for that problem might be to use a view on ONLY tab.