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

I am sorry, I know that this is an SQL problem ... . I need reach different rows in 2 almost same tables. I need use it
inPL/pgSQL. Thanks for your advidce. 

On 10/4/05, Jaromír Kamler <kamler@centrum.cz> wrote:
> I am sorry, I know that this is an SQL problem ... . I need reach different
> rows in 2 almost same tables. I need use it in PL/pgSQL. Thanks for your
> advidce.
>
>

this works?...

create temp table t1 as
select * from (select * from foo1 intersect select * from foo2) as tmp_foo;

select * from (select * from foo1 except select * from t1) as tmp_foo1
union
select * from (select * from foo2 except select * from t1) as tmp_foo2;


--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator ;)

Thanks for your reply, I tryed it and modified it for my needs, but this is result:

--error message
SQL error:

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;

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);


Thank you for your help
Jaromir Kamler
______________________________________________________________
> Od: systemguards@gmail.com
> Komu: kamler@centrum.cz
> CC: pgsql-novice@postgresql.org
> Datum: 04.10.2005 23:51
> Předmět: Re: stupid SQL question, how reach different rows of two almost same tables
>
> On 10/4/05, Jaromír Kamler <kamler @centrum.cz=""> wrote:
> > I am sorry, I know that this is an SQL problem ... . I need reach
> different
> > rows in 2 almost same tables. I need use it in PL/pgSQL. Thanks for your
> > advidce.
> >
> >
>
> this works?...
>
> create temp table t1 as
> select * from (select * from foo1 intersect select * from foo2) as
> tmp_foo;
>
> select * from (select * from foo1 except select * from t1) as tmp_foo1
> union
> select * from (select * from foo2 except select * from t1) as tmp_foo2;
>
>
> --
> regards,
> Jaime Casanova
> (DBA: DataBase Aniquilator ;)
> </kamler>

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

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
>

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