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 2010-07-22 21:51, gargoyle60 wrote:
[...]
Try order by databaseName. Also, the order by clause applies to the
whole query (your indentation indicates otherwise)


/Lennart

On Fri, 23 Jul 2010 06:11:33 +0200, Lennart Jonsson <erik.lennart.jonsson (AT) gmail (DOT) com> wrote:

Yes, that worked, thanks.
From my old Oracle days I don't recall ORDER BY working on aliases, unless my memory is blurred.

(BTW. the indentation was just for formatting to aid clarity in my question)

On 2010-07-23 09:41, gargoyle60 wrote:
In this case:

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_%'

is a derived table so the only thing that's visible in the order by
clause is the renamed attributes

I thought so, it was just to be sure. In recent versions of the standard
one is actually allowed to order a sub-select (whether we like it or not :-)

On Fri, 23 Jul 2010 09:54:52 +0200, Lennart Jonsson <erik.lennart.jonsson (AT) gmail (DOT) com> wrote:

Yes, that makes sense. Thanks