Re: stupid SQL question, how reach different rows of two almost same tables

On Sun, Oct 09, 2005 at 11:28:06PM +0200, Jaromír Kamler wrote:
> I made second table (work name "tables") with names like in
> geometry_columns, but in table "tables" one name of table is missing.
> I need know that name. All this I do, becouse I want know time when
> was some table with spatial data created, but I do not want give
> this time to the table geometry_columns. I have table (tables) with
> tables names like in geometry_columns and on geometry_columns is
> trigger. When is something spatial imported into database, trigger
> write that new table to the table tables and there is column "time"
> with default value CURRENT_TIMESTAMP. Do you have some better way
> how to state time of create table?

If you just want to log tables' creation times somewhere then you
could use PostgreSQL's logging features.

http://www.postgresql.org/docs/8.0/interactive/runtime-config.html#RUNTIME-CONFIG-LOGGING

If you want the times stored in the database then your way sounds
reasonable. I don't know if adding a timestamp column to
geometry_columns would break PostGIS or other applications, so
storing times in a separate table via a trigger on geometry_columns
sounds like a safe way to do it. Note that the time you're storing
isn't when the table was created, but rather when AddGeometryColumn()
was called, although that distinction probably doesn't matter if
you add geometry columns immediately after creating a table.

> I made this query:
> SELECT jmeno FROM tables WHERE jmeno NOT IN (SELECT f_table_name FROM geometry_columns);
>
> It looks works good like yours too. Is this correct?

That should work, although you might want to check the schema-qualified
table name instead of just the table name.

--
Michael Fuhr