CREATING TEMP TABLES USING VALUES METHOD

By: Saurav

2020-09-21 08:57:00 UTC

Hello.
Recently I faced a scenario where I had to generate a quick table on the fly without having to store it in the database. This had to be used just for a single query so it didn't make a lot of sense to create a separate table.

I tried a few different approaches and settled on using a cool method provided by PSQL. This is what I am showcasing in this blog.

Values (), (), () 

This method can be used to create rows of data provided as expressions to the brackets above

The method can be invoked by using it like:

select * from (values (), ()) as alias(label1, label2))

This method needs an alias and by default it uses column1, column2... and so on as column names unless specified as in the example above.

This method is very similar to the approach of doing UNION ALL for us but does it in very succinct way.

Let's say I have a product table which has following data

select * from products;

id, name, shipping_label, country_code

1,hat,AHGHJHAJGAJG12121782AA,US
2,hat,AHGHJHAJGAJG12121782AA,US
3,cat,AHGHYAUANJG12121782AA,US
4,rat,BJHGJJGAJG12121782AA,CA
5,mat,AYYAPAJGAJG12121782AA,CA

Let's say hypothetically, for temporary reasons, the acting manager on call for the US shipments is Andrea and for Canada is Matt.
The actual manager might be on leave. And those data are stored permanently but we don't wanna use it here.

In this case, we can use Values method to create a temporary table for us on which we can join products on and create a report on how many shipments each managers are handling. We can write something like:

With team_leads as
(select * from (values ('US', 'Aria'), ('CA', 'Sam')) as t(country_code, manager_name))
select manager_name, count(*) from products
inner join team_leads on products.country_code = team_leads.country_code
group by manager_name;

Screen shot 2020 09 21 at 2.29.04 am

Try them and let me know how it works out for you :)
Twitter



------------------------------------------------------------------------------------------------------------------

Promoted Products/Links


If you work with Excel sheets, check out these High Quality Spreadsheet Templates, Financial Calculators, Calendars And Other Productivity Tools For Private And Business Users on www.spreadsheet123.com

Owned & Maintained by Saurav Prakash

If you like what you see, you can help me cover server costs or buy me a cup of coffee though donation :)