Alias a POSTGRES select statement

Question

I want to create new tables in the query and use them at the end for a final query. I don't remember what the syntax is and can't find it anywhere. (here is a basic example for illustration only).

phones as 
    (
        SELECT phone_number from customers
    )


emails as 
    (
        SELECT emails from customers
    )

// do something with both

postgresql.org/docs/current/static/queries-with.html

user330315
– user330315

2017-01-28 14:31:48 +00:00
Commented Jan 28, 2017 at 14:31 — user330315
– user330315, Commented Jan 28, 2017 at 14:31

David דודו Markovitz · Accepted Answer · 2017-01-28 14:17:14Z

9

You are looking for CTE's

With phones as 
    (
        SELECT phone_number from customers
    )
,emails as 
    (
        SELECT emails from customers
    )
select * from phones--/emails

edited Jan 28, 2017 at 14:17

David דודו Markovitz

45.2k7 gold badges75 silver badges97 bronze badges

answered Jan 28, 2017 at 14:15

Pரதீப்

94.1k21 gold badges144 silver badges179 bronze badges

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

4 Comments

William Falcon Over a year ago

cool thanks. Will accept in 10 mins. Do i need to join now to use data from all tables? or can i say select phones.phone, emails.email

Pரதீப் Over a year ago

@WilliamFalcon - It depends on the requirement. You current sample query makes not sense. Add sample data and expected result in next question.

William Falcon Over a year ago

yeah, it's a very simplified example for illustration. The as queries are fairly complex involving a bunch of joins. It's to do some calculations (group by users per month, see rate of growth, etc...)

Pரதீப் Over a year ago

@WilliamFalcon - You can Join,union, etc.. between phones and emails in your current example

Gordon Linoff · Accepted Answer · 2017-01-28 14:15:30Z

3

You are looking for a Common Table Expression (CTE). These are introduced using with:

with phones as (
      SELECT phone_number from customers
     ),
     emails as (
      SELECT emails from customers
    )
select . . .;

Your example selects don't seem particularly useful, but I assume they are just for illustration.

answered Jan 28, 2017 at 14:15

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

Alias a POSTGRES select statement

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related