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

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

From: Michael Fuhr <mike(at)fuhr(dot)org>
To: Jaromír Kamler <kamler(at)centrum(dot)cz>
Cc: systemguards(at)gmail(dot)com, pgsql-novice(at)postgresql(dot)org
Subject: Re: stupid SQL question, how reach different rows of two almost same tables
Date: 2005-10-06 14:58:55
Message-ID: 20051006145855.GA2281@winnie.fuhr.org (view raw or flat)
Thread:
Lists: pgsql-novice
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

In response to

pgsql-novice by date

Next:From: Terrance LoudenDate: 2005-10-06 15:51:53
Subject: Table Inheritance, Analyze and Seq Scans
Previous:From: Tom LaneDate: 2005-10-06 13:47:51
Subject: Re: Tranactions and viewing updated data

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