title: union query across different similar schemas but columns ordered differently
body: <p>I have a postgresql database for a multitenanted application, each tenant has its own schema within the database, so all subschemas of the database have the same tables and the same columns.</p>
  <p>I need to create a view on the public database (no schemas) for some of the schema tables (one view for each table), where I summarise and consolidate the data.</p>
  <p>I have a database function that creates the views:</p>
  <pre><code>DECLARE
      schema RECORD;
      result RECORD;
      sql TEXT := '';
  BEGIN
      FOR schema IN EXECUTE
          format('SELECT schema_name FROM information_schema.schemata WHERE left(schema_name, 8) = %L', 'tenant_')
  
          LOOP
              sql := sql || format('SELECT *, ''%s'' AS schema_name FROM %I.%I UNION ALL ', schema.schema_name, schema.schema_name, table_name);
          END LOOP;
  
      EXECUTE
              format('CREATE OR REPLACE VIEW %I AS ', 'all_' || table_name) || left(sql, -11);
  END
  </code></pre>
  <p>which I call passing the table name as argument, and generates the view for me.</p>
  <p>After a bunch of migrations it happened that while the tables are exactly the same, the order of the columns is now different, so I get various errors, like:</p>
  <pre><code>ERROR:  UNION types timestamp without time zone and character varying cannot be matched
  LINE 1: ...ma_name FROM tenant_&lt;tenant_name&gt;.&lt;table_name? UNION ALL SELECT *, 'tenant...
  </code></pre>
  <p>which I believe are caused by the fact that columns are in a different order and the union query complains that (in this example, but I got also different errors for other tables) a column is a timestamp on one schema and it is a string on another.</p>
  <p>I ran the following other query:</p>
  <pre><code>select table_schema, column_name, ordinal_position
  from information_schema.columns
  where table_name = '&lt;table_name' and (table_schema in ('tenant_&lt;tenant_name&gt;', 'tenant_&lt;tenant_name&gt;', 'tenant_&lt;tenant_name&gt;', 'tenant_&lt;tenant_name&gt;', 'tenant_&lt;tenant_name&gt;'));
  </code></pre>
  <p>to find that indeed some of the columns had different <code>ordinal_position</code> numbers.</p>
  <p>I'm not sure how to go about it.</p>
  <ul>
  <li>It's probably worth re-ordering the columns in each schema, this would get the schemas to be more consistent, although I can't think of any possible errors caused by such difference in the order of the columns, other than the one I'm facing currently</li>
  <li>or maybe change the function to match columns by their names, rather by their position. this would be less invasive on the DB, and finally safer.</li>
  </ul>
  <p>worth mentioning:</p>
  <pre><code>% SELECT version();
  PostgreSQL 10.14 on x86_64-pc-linux-gnu, compiled by x86_64-unknown-linux-gnu-gcc (GCC) 4.9.4, 64-bit
  </code></pre>