1

I have 20 schemas that follow an ending pattern depending on the office, that is the name of the office followed by '_finance', '_security' etc and it would something like this:

  • new_york_office_finance
  • boston_office_finance
  • new_york_office_security
  • boston_office_security
  • etc

In addition, all the tables inside the schemas have the same table names.

  • staff
  • logistics
  • Etc

How can I select only the schemas that end in '_finance' and the tables 'logistics' from all those schemas?

user18140022
  • 89
  • 8

1 Answers1

1

You can create sql's by querying information_schema.table like this:

select 'select <column_list> from '||table_name||' where ... <conditions> ;' from information_schema.tables where table_name='logistics' and table_schema like '%\_finance';

Example: To query a table tbl in a schema having name starting with demo

postgres=#  select table_schema, 'select <column_list> from '||table_name||' where ... <conditions> ;' from information_schema.tables where table_name ='tbl' and table_schema like 'demo\_%';
 table_schema |                        ?column?
--------------+--------------------------------------------------------
 demo_schema  | select <column_list> from tbl where ... <conditions> ;
(1 row)
rajorshi
  • 697
  • 4
  • 9
  • Thanks, this partially works... it lists the tables but does not show its content. How can I then get them into a selection list? I.e. rows. – user18140022 Feb 22 '22 at 08:35
  • If you are using PSQL command - you can spool the SQL's into a file [psql spool](https://stackoverflow.com/questions/7414723/can-we-export-query-result-in-postgres-using-spooling-command) and then run that file with \i psql switch. If you are using other libq adapter like pyscopg2 , you can again store the SQL's in a file and then execute the file [like this](https://stackoverflow.com/questions/17261061/execute-sql-schema-in-psycopg2-in-python). – rajorshi Feb 22 '22 at 18:34
  • Ok I see. I was planning to grab lots of data and bring it into a DataFrame, so there would be no point going on PSQL as I can do the task via Python. I thought there was a way to let SQL do all the heavy (retrieving) work before I move into Python. – user18140022 Feb 25 '22 at 09:28
  • If you are planning to use Pandas , then its actually better. Get the statements into a file and call the file like I showed it to the OP [here](https://stackoverflow.com/questions/71201138/python-flask-using-tables-created-in-separate-sql-file). – rajorshi Feb 25 '22 at 18:45
  • And pull the data into Pandas Dataframe like [this](https://stackoverflow.com/questions/71118024/convert-json-format-to-pandas-dataframe-source-postgresql/71122168#71122168) – rajorshi Feb 25 '22 at 19:12
  • 1
    Oh wow. This is cool. Thanks mate! – user18140022 Feb 28 '22 at 08:22