select specific columns for each query in union in postgresql from python

Question

I have different tables of same columns across monthly time periods. I want to pull only specific columns from each table and then do a UNION as shown below.

col_list = ['income', 'urban2', 'marital_stat', 'ethnic_group']

data_sample = str(""" SELECT {} FROM dbo.gold_nov17 
                            where drform in ('NON')
                            """.format(', '.join(col_list)))

This query on a single table works just fine. However when I try to do a union as below:

data_sample = str(""" SELECT {} FROM dbo.gold_nov17  
                            where drform in ('NON')

                            -----------
                            union all
                            -----------
                          SELECT {} FROM dbo.gold_nov17  
                            where drform in ('NON')
                            """.format(', '.join(col_list)))

It throws error:

""".format(', '.join(col_list)))

IndexError: tuple index out of range

I am essentially wanting to select specific columns(based on col_list) for each of the tables for UNION.

Mureinik · Accepted Answer · 2018-06-12 05:52:29Z

2

By numbering your placeholders you should be able to replace multiple instance with the same value:

data_sample = str(""" SELECT {0} FROM dbo.gold_nov17  
                        where drform in ('NON')

                        -----------
                        union all
                        -----------
                      SELECT {0} FROM dbo.gold_nov17  
                        where drform in ('NON')
                        """.format(', '.join(col_list)))

answered Jun 12, 2018 at 5:52

Mureinik

316k54 gold badges400 silver badges405 bronze badges

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

1 Comment

Shuvayan Das Over a year ago

thanks a lot @Mureinik. This worked like a charm. :)

Collectives™ on Stack Overflow

select specific columns for each query in union in postgresql from python

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related