Re: stupid SQL question, how reach different rows of two almost same tables - Mailing list pgsql-novice

Hallo, thank you very much for your help. Your advice is realy full-range. I made second table (work name "tables")
withnames like in geometry_columns, but in table "tables" one name of table is missing. I need know that name. All this
Ido, becouse I want know time when was some table with spatial data created, but I do not want give this time to the
tablegeometry_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? 


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?
Thanks
Jaromir Kamler

P.S.: I apologize for my bad english :-(

______________________________________________________________
> Od: mike@fuhr.org
> Komu: kamler@centrum.cz
> CC: systemguards@gmail.com, pgsql-novice@postgresql.org
> Datum: 06.10.2005 17:04
> Předmět: Re: [NOVICE] stupid SQL question, how reach different rows of two almost same tables
>
> On Thu, Oct 06, 2005 at 12:53:14PM +0200, Jaromír Kamler wrote:
> > ERROR: subquery in FROM must have an alias
> > HINT: For example, FROM (SELECT ...) [AS] foo.
> >
> > In statement:
> > create temp table t1 as
> > select * from (select f_table_name from geometry_columns intersect
> select jmeno from tables) as tmp_foo;
> >
> > select * from (select f_table_name from geometry_columns except select
> jmeno from tables)) as tmp_foo1
> > union
> > select * from (select jmeno from tables except select f_table_name from
> geomety_columns) as tmp_foo2;
>
> The first query in the union has an extra right parenthesis; the
> second query has a misspelled table name ("geomety_columns" instead
> of "geometry_columns").
>
> > I was traying somethin like this, but it looks like wrong way:
> > SELECT f_table_name FROM geometry_columns WHERE (SELECT jmeno FROM
> tables) NOT IN (SELECT f_table_name FROM geometry_columns);
>
> The name geometry_columns suggests that you're using PostGIS. What
> are you trying to do? Are you looking for values of tables.jmeno
> that aren't in geometry_columns.f_table_name, or vice versa, or
> both? Maybe this example will help:
>
> CREATE TABLE geometry_columns (f_table_name text);
> CREATE TABLE tables (jmeno text);
>
> INSERT INTO geometry_columns (f_table_name) VALUES ('only in
> geometry_columns');
> INSERT INTO geometry_columns (f_table_name) VALUES ('in both');
>
> INSERT INTO tables (jmeno) VALUES ('only in tables');
> INSERT INTO tables (jmeno) VALUES ('in both');
>
> SELECT f_table_name FROM geometry_columns EXCEPT SELECT jmeno FROM tables;
> f_table_name
> --------------------------
> only in geometry_columns
> (1 row)
>
> SELECT jmeno FROM tables EXCEPT SELECT f_table_name FROM geometry_columns;
> jmeno
> ----------------
> only in tables
> (1 row)
>
> (SELECT f_table_name FROM geometry_columns EXCEPT SELECT jmeno FROM
> tables)
> UNION
> (SELECT jmeno FROM tables EXCEPT SELECT f_table_name FROM
> geometry_columns);
> f_table_name
> --------------------------
> only in geometry_columns
> only in tables
> (2 rows)
>
> SELECT f_table_name FROM geometry_columns INTERSECT SELECT jmeno FROM
> tables;
> f_table_name
> --------------
> in both
> (1 row)
>
> --
> Michael Fuhr
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org
>