Re: How to figure out when was a table created

Can you add two columns to the table creation definition, one with a
default current timestamp, the second to be updated with current time in
an on update trigger. Then perhaps you could do something like

SELECT
min(new_insert_timestamp_column),
max(new_update_timestamp_column) FROM your_table;

to get the table-wide date of first entry and last update.

~Berend Tober

>
> Well, in certain filesystems you can have the birth time
> (like ufs2) stored in the inode struct.
>
> So you find the file name in your $PGDATA/base directory
> using the oid of your table (in pg_class),
> and then you open that file with stat (2) or utimes (2) (or
> from perl) to read creation data.
>
> All that apply for FreeBSD, see if creation time is supported
> in ext2/3.
>
> On Thu, 2 Oct 2003, David B wrote:
>
>> Hi folks,
>>
>> I posted this question a few days ago and got no response so I guess
>> it cannot be done (surprising!)
>> So that leaves me with my business problem.
>>
>> We create a table for each days activity.
>> After N days (typically 7 days) we can drop the table.
>> The table name is not known so cannot force business to make
>> tablename something like mydata_MMDDYY
>>
>> I'd like to be able to do something like:
>> SELECT tablename
>> FROM pg_???
>> WHERE to_char( ???, 'dd/mm/yy' ) = to_char( now() - interval '7
>> days',
>> 'dd/mm/yy' )
>>
>> Any suggestions?
>>
>> --- Prior msg was:
>>
>> Folks,
>>
>> I have a list of tables for which I want to get the date they were
>> created...and if possible the date last updateded.
>>
>> I suspect there is a pg_??? table that can answer this question but I
>> don't know what it is and I cannot find it mentioned in any docs.
>>
>> Any suggestions...tia