CODEWITHSUNDEEP
sqlpostgresqlunion
alekscooper
alekscooper

Reputation: 831

Can't perform union in Postgres

For some table t I'd like to do the following query:

select 'X' as "Indicator" from t limit 1
union 
select 'Y' as "Indicator" from t limit 1

What I expect:

Indicator
X
Y

What I get: ERROR: syntax error at or near "union"

Why is it so? The column name is the same, in both cases there's only one column. What is wrong?

Upvotes: 0

Views: 145

Answers (2)

Jim Jones
Jim Jones

Reputation: 19613

The LIMIT goes only in the very end of a statement. You may try to use subqueries ..

SELECT * FROM (SELECT 'X' AS indicator FROM t LIMIT 1) j
UNION
SELECT * FROM (SELECT 'y' AS indicator FROM t LIMIT 1) j

Or with FETCH FIRST ROW ONLY

SELECT * FROM (SELECT 'X' AS indicator FROM t FETCH FIRST ROW ONLY) j
UNION
SELECT * FROM (SELECT 'Y' AS indicator FROM t FETCH FIRST ROW ONLY) j

.. even a subquery would do (with a proper ORDER BY):

SELECT DISTINCT ON (indicator) * FROM (
  SELECT 'X' AS indicator FROM t
  UNION 
  SELECT 'Y' AS indicator FROM t) j

Upvotes: 1

Philippe
Philippe

Reputation: 1827

Like this if you want both results

(select 'X' as "Indicator" from t limit 1)
union 
(select 'Y' as "Indicator" from t limit 1)

Upvotes: 2

Related Questions