PostgreSQL Table Field exists in Type Field;

Question

How do I check if fields of a table exist in a user definend Type in PostgeSQL? The Thing is that i want to compare the attributes and create a new Table only with the existing fields of the Type (test_type).

CREATE TYPE test_type AS (v1 int, v2 int, v3 int); CREATE TABLE test_table (v1 int, v2 int, v3 int, v4 int); DO $$ DECLARE r record; BEGIN FOR r IN SELECT COLUMN_NAME as colname, udt_name AS dtype FROM information_schema.columns WHERE table_name = 'test_table' and table_schema = current_schema() LOOP RAISE NOTICE '%', r; IF r.colname in test_type THEN RAISE NOTICE 'FOUND'; END IF; END LOOP; END; $$ LANGUAGE 'plpgsql'; 

Answer 1

The main problem you face is that you are comparing apples to oranges. Well actually meta data to tuple (record) data values. What you need is to get the mete data for test_type as well as you have from test table. The following retrieves the both from pg_catalog.pg_attribute after identifying the oid for each.

with the_oids( oid, namespace, relname) as -- get oid for both TYPE and TABLE ( select c.oid, n.nspname, c.relname from pg_catalog.pg_class c join pg_catalog.pg_namespace n on n.oid = c.relnamespace where 1=1 and c.relname in ('test_type', 'test_table') and pg_catalog.pg_table_is_visible(c.oid) and n.nspname = current_schema() ) -- select * from the_oids , tbl_cols( col_name, col_type) as -- get column name and type for TABLE ( SELECT a.attname , pg_catalog.format_type(a.atttypid, a.atttypmod) FROM pg_catalog.pg_attribute a WHERE a.attrelid = (select oid from the_oids where relname='test_table') AND a.attnum > 0 AND NOT a.attisdropped ) -- select * from tbl_cols; , typ_cols ( col_name, col_type) as -- get column name and type from TYPE ( SELECT a.attname , pg_catalog.format_type(a.atttypid, a.atttypmod) FROM pg_catalog.pg_attribute a WHERE a.attrelid = (select oid from the_oids where relname='test_type') AND a.attnum > 0 AND NOT a.attisdropped ) -- select * from typ_cols; -- join TABLE and TYPE and add indicator meaages select tb.col_name , case when tv.col_name is not null then 'Found' else 'Not Found' end "Found?" , case when tv.col_name is not null then case when tb.col_type = tv.col_type then 'Match' else 'Different' end end "Columns Compatable?" from tbl_cols tb left join typ_cols tv on tv.col_name = tb.col_name; 

This query can be run as is in a do block or, with only parameter modification, within a function and as such take parameters rather than hard coded type and table names. (See here for each.)

NOTE: I changed the Table column definitions to demonstrate the handling of the column definition check. Just because a type definition and a table definition contain the same column name does not mean they are compatable.