Find min(created_at) for all tables in postgresql database

Question

So, I now know that I can't find the table creation time for postgresql tabes (PostgreSQL: Table creation time.) I would love to still be able to get a proxy of the tables that were created after a certain date. Most tables in my database have a created_at date. Is there any way to check across all tables for tables where the min(created_at) date is > x, without querying table by table?

This post seems to have the information that you're asking for. http://stackoverflow.com/questions/5350088/how-to-search-a-specific-value-in-all-tables-postgresql — Dylan Hamilton, Mar 03 '17 at 17:21

score 0 · Answer 1 · answered Mar 03 '17 at 18:19

Disclaimer: Actually it is not the answer to the whole question but only to

So, I now know that I can't find the table creation time for postgresql tabes

Yes, but you can collect such data by yourself using event triggers.

There is example:

/*
drop table if exists t1;
drop table if exists public.t2;
drop event trigger if exists tg_table_created_at;
drop function if exists fn_table_created_at(); 
drop table if exists table_created_at;
*/    

create table table_created_at(
  toid oid,
  tname text,
  created_at timestamptz default clock_timestamp());

create function fn_table_created_at()
  returns event_trigger
  language plpgsql
  as $$
begin
  insert into table_created_at(toid, tname)
    select objid, object_identity
    from pg_event_trigger_ddl_commands()
    where command_tag = 'CREATE TABLE';
end $$;

create event trigger tg_table_created_at on ddl_command_end
  execute procedure fn_table_created_at();

create temp table t1();
select pg_sleep(1);
create table public.t2();

select *, toid::regclass from table_created_at;

Result:

╔═══════╤════════════╤═══════════════════════════════╤══════════════╗
║ toid  │   tname    │          created_at           │     toid     ║
╠═══════╪════════════╪═══════════════════════════════╪══════════════╣
║ 87803 │ pg_temp.t1 │ 2017-03-03 20:05:44.339811+02 │ pg_temp_3.t1 ║
║ 87806 │ public.t2  │ 2017-03-03 20:05:45.344503+02 │ public.t2    ║
╚═══════╧════════════╧═══════════════════════════════╧══════════════╝

And several additional links: Event Trigger Functions, Event Trigger Firing Matrix, Object Identifier Types.

Find min(created_at) for all tables in postgresql database

1 Answers1