Обсуждение: ORDER BY with UNION

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...

On 22 July 2010 18:26, gargoyle60 <gargoyle60@example.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@gmail.com>

Michael Wood <esiotrot@gmail.com> writes:
> On 22 July 2010 18:26, gargoyle60 <gargoyle60@example.invalid> wrote:
>> This works fine as above but as soon as I reintroduce the ORDER BY clause I get the syntax error...

> 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.

ORDER BY effectively binds less tightly than UNION, so adding
parentheses that way just specifies what the parser will assume by
default.  You can force it to associate the ORDER BY with only the
second UNION arm by doing

    (SELECT ...) UNION (SELECT ... ORDER BY ...)

but in most cases this is quite pointless, since the UNION will feel free
to not preserve the row ordering of its inputs.  The default behavior
applies the ORDER BY after the UNION, which is usually what's wanted.

> Also, try 'ORDER BY "databaseName", ...'
> since you've basically renamed the columns.

Yes, that's the real problem: the output columns of the UNION are named
as specified by the AS clauses, so you need to use those names in an
ORDER BY that's meant to sort the UNION's output.

            regards, tom lane

> 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>