Thread: can I get a bit more attribute out of \dt directive?
Hi everybody,
Is there any quick way to list attributes of table?
As I type at psql prompt:
\dt+ foo.mytable*
It gives me 5 columns (schema, name, type, owner, and
description) of tables named mytable1, mytable2, etc.
in the schema foo.
What I want would be the last modification date of each
table. How can I get this out of psql?
Thank you in advance.
Regards,
Tena Sakai
tsakai@gallo.ucsf.edu
On Tue, Oct 20, 2009 at 10:11 AM, Tena Sakai <tsakai@gallo.ucsf.edu> wrote: > Hi everybody, > > Is there any quick way to list attributes of table? > As I type at psql prompt: > > \dt+ foo.mytable* > > It gives me 5 columns (schema, name, type, owner, and > description) of tables named mytable1, mytable2, etc. > in the schema foo. > > What I want would be the last modification date of each > table. How can I get this out of psql? That information is not stored by the db really, so there's no way to get it.
Hi Scott,
Thank you for your reply.
> That information is not stored by the db really,
> so there's no way to get it.
I think then I would build another column for
timestamp. But in object-oriented lingo, this
would be a class field, rather than instance
field, and I don't want to timestamp every row
of each table. The timestamp is strictly to
tell when it was last modified.
Is there any good way to do this?
Regards,
Tena Sakai
tsakai@gallo.ucsf.edu
-----Original Message-----
From: Scott Marlowe [mailto:scott.marlowe@gmail.com]
Sent: Tue 10/20/2009 12:24 PM
To: Tena Sakai
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] can I get a bit more attribute out of \dt directive?
On Tue, Oct 20, 2009 at 10:11 AM, Tena Sakai <tsakai@gallo.ucsf.edu> wrote:
> Hi everybody,
>
> Is there any quick way to list attributes of table?
> As I type at psql prompt:
>
> \dt+ foo.mytable*
>
> It gives me 5 columns (schema, name, type, owner, and
> description) of tables named mytable1, mytable2, etc.
> in the schema foo.
>
> What I want would be the last modification date of each
> table. How can I get this out of psql?
That information is not stored by the db really, so there's no way to get it.
On Tue, Oct 20, 2009 at 2:42 PM, Tena Sakai <tsakai@gallo.ucsf.edu> wrote: > Hi Scott, > > Thank you for your reply. > >> That information is not stored by the db really, >> so there's no way to get it. > > I think then I would build another column for > timestamp. But in object-oriented lingo, this > would be a class field, rather than instance > field, and I don't want to timestamp every row > of each table. The timestamp is strictly to > tell when it was last modified. > > Is there any good way to do this? Do you mean when the table was modified (i.e. alter table add column) or when the data in the table was changed? If it's when the table was changed, the easiest way is to store that in the comment for the table whenever you alter it. Otherwise, you'll want a trigger or something to store the latest update time in the latest row updated. There's probably other ways too, but would involve a separate table to store such things.
Hi Scott,
> Do you mean when the table was modified
> (i.e. alter table add column) or when the
> data in the table was changed?
I mean the latter, the data change.
> If it's when the table was changed, the
> easiest way is to store that in the comment
> for the table whenever you alter it.
Great idea! That's my ticket to solution.
Many thanks. I appreciate it.
Regards,
Tena Sakai
tsakai@gallo.ucsf.edu
-----Original Message-----
From: Scott Marlowe [mailto:scott.marlowe@gmail.com]
Sent: Tue 10/20/2009 1:44 PM
To: Tena Sakai
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] can I get a bit more attribute out of \dt directive?
On Tue, Oct 20, 2009 at 2:42 PM, Tena Sakai <tsakai@gallo.ucsf.edu> wrote:
> Hi Scott,
>
> Thank you for your reply.
>
>> That information is not stored by the db really,
>> so there's no way to get it.
>
> I think then I would build another column for
> timestamp. But in object-oriented lingo, this
> would be a class field, rather than instance
> field, and I don't want to timestamp every row
> of each table. The timestamp is strictly to
> tell when it was last modified.
>
> Is there any good way to do this?
Do you mean when the table was modified (i.e. alter table add column)
or when the data in the table was changed? If it's when the table was
changed, the easiest way is to store that in the comment for the table
whenever you alter it. Otherwise, you'll want a trigger or something
to store the latest update time in the latest row updated. There's
probably other ways too, but would involve a separate table to store
such things.
In article <dcc563d10910201344t6ec78e82hb67aaac471279d89@mail.gmail.com>, Scott Marlowe <scott.marlowe@gmail.com> writes: > Do you mean when the table was modified (i.e. alter table add column) > or when the data in the table was changed? If it's when the table was > changed, the easiest way is to store that in the comment for the table > whenever you alter it. Highly interesting. I put an "EXECUTE 'COMMENT ON TABLE ...'" into an AFTER INSERT OR UPDATE OR DELETE trigger, and this indeed works. Does anyone see a drawback in modifying a table comment very often?
Harald Fuchs <hari.fuchs@gmail.com> writes:
> Highly interesting. I put an "EXECUTE 'COMMENT ON TABLE ...'" into an
> AFTER INSERT OR UPDATE OR DELETE trigger, and this indeed works. Does
> anyone see a drawback in modifying a table comment very often?
As long as pg_description gets vacuumed or autovacuumed often enough,
it should be no worse than frequent updates in any other table.
regards, tom lane