ORDER BY with UNION

Поиск
Список
Период
Сортировка
От gargoyle60
Тема ORDER BY with UNION
Дата
Msg-id n4sg46556pf6ol1l3igr41o7jrim8u2lav@4ax.com
обсуждение исходный текст
Ответы Re: ORDER BY with UNION  (Michael Wood <esiotrot@gmail.com>)
Список pgsql-novice
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...

В списке pgsql-novice по дате отправления:

Предыдущее
От: Josh Berkus
Дата:
Сообщение: Re: [pgsql-advocacy] C Postgresql CGI
Следующее
От: c b
Дата:
Сообщение: importing a data file without defining a table structure