| From: | gargoyle60 <gargoyle60(at)example(dot)invalid> |
|---|---|
| To: | pgsql-novice(at)postgresql(dot)org |
| Subject: | ORDER BY with UNION |
| Date: | 2010-07-22 16:26:28 |
| Message-ID: | n4sg46556pf6ol1l3igr41o7jrim8u2lav@4ax.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | 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...
| From | Date | Subject | |
|---|---|---|---|
| Next Message | viju | 2010-07-22 18:31:06 | could not change directory to "/root" |
| Previous Message | Dimitri Fontaine | 2010-07-22 09:58:28 | Re: C Postgresql CGI |