Skip site navigation (1) Skip section navigation (2)

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

From: " Jaromír Kamler" <kamler(at)centrum(dot)cz>
To: <mike(at)fuhr(dot)org>
Cc: <pgsql-novice(at)postgresql(dot)org>
Subject: Re: stupid SQL question, how reach different rows of two almost same tables
Date: 2005-10-09 21:28:06
Message-ID: 200510092328.3070@centrum.cz (view raw or flat)
Thread:
Lists: pgsql-novice
Hallo, thank you very much for your help. Your advice is realy full-range. 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?


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(at)fuhr(dot)org
> Komu: kamler(at)centrum(dot)cz
> CC: systemguards(at)gmail(dot)com, pgsql-novice(at)postgresql(dot)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
>


Responses

pgsql-novice by date

Next:From: Michael FuhrDate: 2005-10-10 02:32:26
Subject: Re: stupid SQL question, how reach different rows of two almost same tables
Previous:From: PéterDate: 2005-10-09 20:58:05
Subject: delphi+adodb+postgresql+bytea

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group