Combined SELECT from unnested composite type array and regular column
sql,arrays,postgresql,rowtype,set-returning-functions
Your feeling is correct: a normalized schema with another table instead of the array of composite types would be the superior approach in many respects. While stuck with your unfortunate design: Test setup (You should have provided this.) CREATE TYPE card AS (year int, cardname text); CREATE TABLE my_friends_cards (id...
confused on how to properly use %rowtype for many tables
Unless I'm missing something by simplifying this, and assuming cd.date and cf.date are equal, this should work: INSERT INTO NEW_TABLE (ID, Name, date, population, urban_pop, birth_rate, gdp, gdp_per_capita) values (select c.id, c.name, cd.date, cd.population, cd.urban_pop, cd.birthrate, cf.gdp, cf.gdp_per_capita) from Countries c, country_demographics cd, country_financials cf where c.id = cd.id and...