Re: ORDER BY with UNION

> A way I work around this problem is to reference the result of the union as a table. This will allow the ORDER BY.
>
>    SELECT    T1.*
>    FROM
>     (
>    SELECT
>          table_catalog AS "databaseName",
>          table_schema AS "schemaName",
>          table_name AS "tableName",
>          '' AS "primaryKeyName",
>          column_name AS "columnMappings"
>     FROM information_schema.columns
>     WHERE table_schema NOT IN ('information_schema','pg_catalog')
>  UNION ALL
>     SELECT
>          table_catalog AS "databaseName",
>          table_schema AS "schemaName",
>          table_name AS "tableName",
>          constraint_name AS "primaryKeyName",
>          column_name AS "columnMappings"
>     FROM information_schema.key_column_usage
>     WHERE constraint_name LIKE 'pk_%'
>    ) AS T1
>     ORDER BY
>             table_catalog,
>             table_schema,
>             table_name,
>             constraint_name,
>             ordinal_position,
>             column_name

On 22 July 2010 18:26, gargoyle60 <gargoyle60(at)example(dot)invalid> wrote:
                          ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

It's a little difficult to reply to you if you use an invalid e-mail address...

> Having trouble with the following union query...
>
>     SELECT
>          table_catalog AS "databaseName",
>          table_schema AS "schemaName",
>          table_name AS "tableName",
>          '' AS "primaryKeyName",
>          column_name AS "columnMappings"
>     FROM information_schema.columns
>     WHERE table_schema NOT IN ('information_schema','pg_catalog')
>  UNION ALL
>     SELECT
>          table_catalog AS "databaseName",
>          table_schema AS "schemaName",
>          table_name AS "tableName",
>          constraint_name AS "primaryKeyName",
>          column_name AS "columnMappings"
>     FROM information_schema.key_column_usage
>     WHERE constraint_name LIKE 'pk_%'
>     -- ORDER BY
>     --      table_catalog,
>     --      table_schema,
>     --      table_name,
>     --      constraint_name,
>     --      ordinal_position,
>     --      column_name
> ;
>
> This works fine as above but as soon as I reintroduce the ORDER BY clause I get the syntax error...
>     ERROR:  column "table_catalog" does not exist
>     LINE 19:            table_catalog,
>                    ^
>     ********** Error **********
>     ERROR: column "table_catalog" does not exist
>     SQL state: 42703
>     Character: 667
>
> From the documentation I infer that ORDER BY should work with UNION, so where am I going wrong?
> Any help please...

I haven't tried it myself, but I think you need to put the whole
"SELECT ... UNION ALL SELECT ..." in parentheses and then have the
"ORDER BY ..." after that.  Also, try 'ORDER BY "databaseName", ...'
since you've basically renamed the columns.

--
Michael Wood <esiotrot(at)gmail(dot)com>