Thread: Subselects, the Oracle way
Hi, Is there any way to mimic the oracle way of subselect, especially constructs like select * from (select col1 as x, col2, col6 from t1 union select col2 as x, col6, col2 from t2) y order by y.x I am aware of the fact, that it is possible to accomplish through the use of tempoary tables, but would rather not use them. Thanks in advance Svenne -- svenne@krap.dk http://www.krap.dk ICQ 5434480 PGP-key http://keys.pgp.dk:11371/pks/lookup?op=get&search=0xDF484022 PGP @ http://www.pgp.com / http://www.phpi.com
> Is there any way to mimic the oracle way of subselect, especially > constructs like > > > select * from (select col1 as x, col2, col6 from t1 union select col2 > as x, col6, col2 from t2) y order by y.x > Works fine as written in version 7.1 and above. See http://www.postgresql.org/idocs/index.php?queries.html#QUERIES-FROM test=# select version(); version -------------------------------------------------------------------PostgreSQL 7.1 on i686-pc-linux-gnu, compiled by GCC egcs-2.91.66 (1 row) test=# select * from (select col1 as x, col2, col6 from t1 union select col2 as x, col6, col2 from t2) y order by y.x;x | col2 | col6 ---+------+------1 | 1 | 12 | 2 | 23 | 3 | 34 | 4 | 4 (4 rows) -- Joe
> That should work fine in 7.1 ... Ups.. forgot to tell that I was working on 7.0.3 .. upgrade is not an option right now.. but I guess you answered already (not possible in 7.0) :) Btw. any idea when cross-database queries will be implementet (you know select x.col1, y.col2 from localtable x, foreigndbtable y where x.col1=y.col2) ? A real needy feature, if you ask me :) Btw. a litte side question: having a table with quite an amount of data (all telephone/addresses in denmark :) which change only very rarely (once every 3 months). I our first setup it is contained in a database for its own (becase PGSQL 7.0 - not sure about 7.1) connects rather slowly, if giant tables are present. Is there any way to speed it up, or is a split database design the only way to go ? Again TIA Svenne -- svenne@krap.dk http://www.krap.dk ICQ 5434480 PGP-key http://keys.pgp.dk:11371/pks/lookup?op=get&search=0xDF484022 PGP @ http://www.pgp.com / http://www.phpi.com
hello again everyone,I seem to have hit what i -think- may be a bug (but i am not crying
wolf jst yet ;).
I have three tables. action, client and order_details. action has a primary
key 'order', client references action (along with adding the client_id as
part of its primary key), order_details references client (both parts of the
primary key there). all foreign keys (order in client and order+client_id in
order_details) are set to INITIALLY DEFERRED. so far so good i hope.
Now, i have a trigger that fires on insert, so i delete from the live
database and then insert the changes rather than doing an update. not
great, but shouldnt be a problem.
The problem comes when i do this:mms_post=# BEGIN;BEGINmms_post=# DELETE from client WHERE order_id = 22;DELETE
1mms_post=#INSERT INTO client mms_post-#(cli_business_name,cli_chain_id,cli_business_type,cli_short_name,cl
i_sic,order_id,client_id,cli_agent_bank_id,cli_operating_name,creation_id,cli_
web_page,cli_tcc,creation_date)mms_post-# VALUES ('STEFS','100-000000000333',1,'FHASDLKJH HFAKSDJ
HKALSDJ',2534,22,'100-00000000555',230,'FHASDLKJH HFAKSDJ
HKALSDJFH','jack','fgahsk@afsdhfkja.net','R','2001-06-18 13:46:45-04');INSERT 24211 1mms_post=# COMMIT;ERROR:
<unnamed>referential integrity violation - key in client still
referenced from order_details
Now. the way i understand it, shouldnt the integrity of any foreign keys
be checked at the -end- of the transaction, after all the commands have been
processed ? it seems that the DELETE is being processed and rejected, but
the foreign key would be 'okay' due to the following INSERT. I have tried
SET CONSTRAINTS as well with no difference :\
Does this make any sense or am i completely mad ? (more than likely)
regards,Stefs.
"Svenne Krap" <svenne@krap.dk> writes:
> Is there any way to mimic the oracle way of subselect, especially
> constructs like
> select * from (select col1 as x, col2, col6 from t1 union select col2
> as x, col6, col2 from t2) y order by y.x
That should work fine in 7.1 ...
regards, tom lane
Svenne Krap writes: > Is there any way to mimic the oracle way of subselect, especially > constructs like > > select * from (select col1 as x, col2, col6 from t1 union select col2 > as x, col6, col2 from t2) y order by y.x I don't know what's supposed to be the "Oracle" way about this, but this particular query should work in PostgreSQL 7.1. -- Peter Eisentraut peter_e@gmx.net http://funkturm.homeip.net/~peter
On Mon, 18 Jun 2001, Stef Telford wrote: > > I seem to have hit what i -think- may be a bug (but i am not crying > wolf jst yet ;). > > I have three tables. action, client and order_details. action has a primary > key 'order', client references action (along with adding the client_id as > part of its primary key), order_details references client (both parts of the > primary key there). all foreign keys (order in client and order+client_id in > order_details) are set to INITIALLY DEFERRED. so far so good i hope. > > Now. the way i understand it, shouldnt the integrity of any foreign keys > be checked at the -end- of the transaction, after all the commands have been > processed ? it seems that the DELETE is being processed and rejected, but > the foreign key would be 'okay' due to the following INSERT. I have tried > SET CONSTRAINTS as well with no difference :\ Yes. There's a problem where intermediate conditions are being checked in certain sequences of deferred constraints. It should be fixed for 7.2.