1

I have a database full of tables. I'm using pgAdmin to access it. One table's name is timecycle and the others are called event_run_x where 'x' is a number for each different table.

I would like to list all the tables with it's size but those who where created only on 2018.

So far I was able to list all the databases with it's size with:

SELECT relname AS table_name, pg_size_pretty(table_size) AS size, table_size
    FROM (
        SELECT pg_catalog.pg_namespace.nspname AS schema_name, relname, pg_relation_size(pg_catalog.pg_class.oid) AS table_size
        FROM pg_catalog.pg_class JOIN pg_catalog.pg_namespace ON relnamespace = pg_catalog.pg_namespace.oid
    ) t
    WHERE schema_name NOT LIKE 'pg_%';

Each table event_run_x has many elements with different id's. From the table timecycle I have the same id's and a column named date. I am able to get all the id's from the year 2018 with:

SELECT * FROM timecycle WHERE extract(year from date) = 2018

Now what I would need is to change the first code to list only the tables that has an id that is listed in the last query. (from timecycle year 2018)

id's from event_run_x does not repeat within another event_run_y (x != y ofc). Each event_run_x id is supposed to have the same year. So if there is an id from say event_run_8 in the timecycle query, then all the id's from event_run_8 will be in said query and vice versa.

Example:

Event_run_1:
id  |
----|
1   |
2   |
3   |
4   |
-----
Event_run_2:
id  |
----|
5   |
6   |
7   |
----|

timecycle:
id  | date
-----------
1   | 2018-01-01 00:00:00
2   | 2018-01-01 00:00:00
2   | 2018-01-01 00:00:00
3   | 2018-01-01 00:00:00
4   | 2018-01-01 00:00:00
5   | 2017-01-01 00:00:00
6   | 2017-01-01 00:00:00
7   | 2017-01-01 00:00:00
--------------------------

I am currently getting:

table_name    | size     | table_size
--------------------------------------
"event_run_1" | "23 MB"  | "24272896"
"event_run_1" | "167 MB" | "175046656"

What I want is event_run_1 to be listed but not event_run_2.

PS: If I am over-complicating things and one can do it without sql queries or whatever it can also be a solution. I think no other options where possible because of: PostgreSQL: Table creation time

I tried the answer from Automatically Drop Tables and Indexes Older than 90 Days that I got from this answer PostgreSQL: Creation date of a table but got:

ERROR: must be superuser to get directory listings 
SQL state: 42501
J Agustin Barrachina
  • 3,501
  • 1
  • 32
  • 52
  • I don't get it. What are the "elements" a table has and whose ids you find in `timecycle`? Rows? – Laurenz Albe Jan 11 '19 at 15:23
  • @LaurenzAlbe, `timecycle` has only 2 columns. Id and date. And for the sake of clarity, `event_run_x` has only one column that is id. I'll add an example in the intent to clarify right now. – J Agustin Barrachina Jan 11 '19 at 16:00

1 Answers1

1

I suggest that you leverage the sys tables for this. You can easily get the create_date of a table out of sys.tables.

Select * from sys.tables where create_date between '1/1/2018' and '12/31/2018'

You can join that output to whatever query you prefer that has the table sizes display the data the way you want. The only caveat to something like this would be if you are dealing with a database that has established processes that drop and create tables. This is not unheard of and could show you some results you don't like. You would have to further filter for any known tables that fall into that scenario.

All in all this shouldn't be very hard to put together.

S. J.
  • 76
  • 8