How to use redundant alias in Postgres

Question

I am running a query with join from 2 tables having similarly named index "time". I want to return the result with the same named index "time".

SELECT "tbl1"."a"                       "a", 
       "tbl1"."b"                       "b", 
       "tbl2"."c"                       "c", 
       COALESCE("tbl"."time", "tb2"."time") "time" 
FROM   "tbl1" 
       FULL OUTER JOIN "tbl2" 
                    ON "tbl1"."timestamp" = 
                       "tbl2"."timestamp" 
ORDER BY "time"

This query returns an error that "time" is redundant (in ORDER BY).

If I change alias "time" to something else - it will work. But I do want that query will return "time"

If I change ORDER BY to

ORDER BY COALESCE("tbl"."time", "tb2"."time")

It will also work, but as far as I understand it will do the search for Nones twice and extend execution time (isn't it?).

You can use that function call in your ORDER BY clause, it will not be executed second time. — Łukasz Kamiński
– Łukasz Kamiński, Commented Nov 28, 2018 at 12:43
What is the error you're getting. There is nothing wrong with your query. — Salman Arshad
– Salman Arshad, Commented Nov 28, 2018 at 12:44

wildplasser · Accepted Answer · 2018-11-28 13:46:22Z

3

You can use positional column numbers instead of names:

SELECT "tbl1"."a"                       "a", 
       "tbl1"."b"                       "b", 
       "tbl2"."c"                       "c", 
       COALESCE("tbl"."time", "tb2"."time") "time" 
FROM   "tbl1" 
       FULL OUTER JOIN "tbl2" 
                    ON "tbl1"."timestamp" = 
                       "tbl2"."timestamp" 
ORDER BY 4;

This will also work forGROUP BY.

edited Nov 28, 2018 at 13:46

answered Nov 28, 2018 at 12:45

wildplasser

44.5k9 gold badges72 silver badges116 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Meir Tseitlin Over a year ago

Wow! I didn't even knew it!

Gordon Linoff · Accepted Answer · 2018-11-28 12:27:43Z

2

You could use a lateral join:

SELECT "tbl1"."a"                       "a", 
       "tbl1"."b"                       "b", 
       "tbl2"."c"                       "c", 
       v.time
FROM "tbl1" FULL OUTER JOIN
     "tbl2" 
     ON "tbl1"."timestamp" = "tbl2"."timestamp" CROSS JOIN LATERAL
     (VALUES (COALESCE("tbl"."time", "tb2"."time")) as v(time)
ORDER BY v."time";

I would suggest that you write your queries so all the double quotes are not needed.

answered Nov 28, 2018 at 12:27

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

1 Comment

Meir Tseitlin Over a year ago

Very interesting approach also !

Collectives™ on Stack Overflow

How to use redundant alias in Postgres

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related