Re: [SQL] a wierd query
sad wrote: select distinct a as F from table union select distinct b as F from table; Note that UNION only returns the unique values of the union You can get repeated values by using UNION ALL. -- Edmund Bacon [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] a wierd query
hi
i have a wierd problem and i require an equally weird query.
1) backgound
Table test:
CREATE TABLE main_table (
string_A varchar( 20),
string_B varchar( 20),
);
-- both columns are identical in nature and usage
INSERT INTO main_table VALUES('abcd','qrst');
INSERT INTO main_table VALUES('efgh','efgh');
INSERT INTO main_table VALUES('ijkl','abcd');
INSERT INTO main_table VALUES('abcd','ijkl');
INSERT INTO main_table VALUES('qrst','uvwx');
2) problem:
i require a query that gives me a result set of the form
'abcd'
'efgh'
'ijkl'
'qrst'
'uvwx'
that is i require the dictinct values from (visualizing each column
result as a set) the union of the two columns
3) questions
a) is a query like this possible that can give me the desired result
b) if so what would it be.
4) remarks
i can get the solution using a temporary table and with repeated
insert into temporary select $column from main_table
thanks in advance
ashok
mail2web - Check your email from the web at
http://mail2web.com/ .
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] a wierd query
[EMAIL PROTECTED] schrieb:
hi
i have a wierd problem and i require an equally weird query.
1) backgound
Table test:
CREATE TABLE main_table (
string_A varchar( 20),
string_B varchar( 20),
);
-- both columns are identical in nature and usage
INSERT INTO main_table VALUES('abcd','qrst');
INSERT INTO main_table VALUES('efgh','efgh');
INSERT INTO main_table VALUES('ijkl','abcd');
INSERT INTO main_table VALUES('abcd','ijkl');
INSERT INTO main_table VALUES('qrst','uvwx');
2) problem:
i require a query that gives me a result set of the form
'abcd'
'efgh'
'ijkl'
'qrst'
'uvwx'
that is i require the dictinct values from (visualizing each column
result as a set) the union of the two columns
3) questions
a) is a query like this possible that can give me the desired result
b) if so what would it be.
Yes, the follwoing
select t1.string_a from main_table t1
union
select t2.string_b from main_table t2;
4) remarks
i can get the solution using a temporary table and with repeated
insert into temporary select $column from main_table
Why, SQL does the trick!
thanks in advance
ashok
Silke
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] a wierd query
i require the dictinct values from (visualizing each column result as a set) the union of the two columns select distinct a as F from table union select distinct b as F from table; ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] a wierd query
On Thu, 13 May 2004, [EMAIL PROTECTED] wrote:
hi
i have a wierd problem and i require an equally weird query.
1) backgound
Table test:
CREATE TABLE main_table (
string_A varchar( 20),
string_B varchar( 20),
);
-- both columns are identical in nature and usage
INSERT INTO main_table VALUES('abcd','qrst');
INSERT INTO main_table VALUES('efgh','efgh');
INSERT INTO main_table VALUES('ijkl','abcd');
INSERT INTO main_table VALUES('abcd','ijkl');
INSERT INTO main_table VALUES('qrst','uvwx');
2) problem:
i require a query that gives me a result set of the form
'abcd'
'efgh'
'ijkl'
'qrst'
'uvwx'
that is i require the dictinct values from (visualizing each column
result as a set) the union of the two columns
3) questions
a) is a query like this possible that can give me the desired result
b) if so what would it be.
4) remarks
i can get the solution using a temporary table and with repeated
insert into temporary select $column from main_table
select distinct t from (select string_A as t from main_table union
select string_B as t from main_table);
or
select f from (select A as t from main_table union select B as t from
main_table) group by t order by t;
hope that helps
Peter Childs
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [SQL] a wierd query
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Yes you can. Try this:
(select string_A from main_table) union (select string_B from main_table)
Yasir
On Thu, 13 May 2004, [EMAIL PROTECTED] wrote:
Date: Thu, 13 May 2004 04:07:08 -0400
From: [EMAIL PROTECTED] [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Subject: [SQL] a wierd query
hi
i have a wierd problem and i require an equally weird query.
1) backgound
Table test:
CREATE TABLE main_table (
string_A varchar( 20),
string_B varchar( 20),
);
-- both columns are identical in nature and usage
INSERT INTO main_table VALUES('abcd','qrst');
INSERT INTO main_table VALUES('efgh','efgh');
INSERT INTO main_table VALUES('ijkl','abcd');
INSERT INTO main_table VALUES('abcd','ijkl');
INSERT INTO main_table VALUES('qrst','uvwx');
2) problem:
i require a query that gives me a result set of the form
'abcd'
'efgh'
'ijkl'
'qrst'
'uvwx'
that is i require the dictinct values from (visualizing each column
result as a set) the union of the two columns
3) questions
a) is a query like this possible that can give me the desired result
b) if so what would it be.
4) remarks
i can get the solution using a temporary table and with repeated
insert into temporary select $column from main_table
thanks in advance
ashok
mail2web - Check your email from the web at
http://mail2web.com/ .
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.2.3 (NetBSD)
iQEVAwUBQKN2/+J7vYSSIbWdAQKklQf+JPhyMpbhEVX/4t70r1m6RFPXkm2VgbOz
Dyxkjhbko07c+YcnVbHmk/8D0d+1L0Qx23vytCfvqRS29O5tzwDFrSfHCZQ8WE4C
H7P0377jfa/LxgAeaUNnDfhhGj+qUI649i2QDSzdalVVwKtUl/aKdw0+evveuUXZ
QBYvVeoFU9KrnqBbQNW6AQOM8vfnYG3cxcb87krRy/b2EgZE462o2O3jGhqvlmrU
8eKJCrEnv4t53IOI3J2WECKbuSomTrUAqfUWbpL6g7zrOpkuCTqzTuOrx+7ISMTR
zyY36zUDeOB/A7u3PEh+wQz/Yqdq1Gu9GQ3kIsgao1WA+K3tj1ceKA==
=zMMM
-END PGP SIGNATURE-
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] Exceptions when 0 rows affected.
Andrei Bintintan mentioned : = Your problem depends on what interface/programming language you're using. Yep, I tried to do it using rules or triggers, but I can't get it to do what I want exactly, and it's not a good idea to put any rules or triggers on my database. I'm sticking to psql though, and managed to create the query with a nested nullif and coalesce to make my query fail if there are 0 rows matching for an update or delete. Cheers Stef ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] isolation level
On Thu, May 13, 2004 at 18:13:23 +, Jaime Casanova [EMAIL PROTECTED] wrote: Hi all, is there a way to set the isolation level to something like the sql standard dirty read. No. There will be a way to use the standard name in a SET command, but you will actaully get READ COMMITTED isolation (which is the next step up). ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] isolation level
Hi all, is there a way to set the isolation level to something like the sql standard dirty read. Thanx in advance, Jaime Casanova _ STOP MORE SPAM with the new MSN 8 and get 2 months FREE* http://join.msn.com/?page=features/junkmail ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[SQL] \d in 7.4.2
Hi all, maybe i'm wrong (because i'm writing this without 7.3 and 7.2 at hand) but i think when i do a \d my_table in pgsql 7.3 i get some fk info foreign key references etc. but in 7.4 i get Triggers: RI_ConstraintTrigger_46753, RI_ConstraintTrigger_46768, RI_ConstraintTrigger_46769 that is the same i got from 7.2. I think 7.3 version is better and much informative. why the hackers go back in this? thanx in advance, Jaime Casanova _ MSN 8 helps eliminate e-mail viruses. Get 2 months FREE*. http://join.msn.com/?page=features/virus ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Ignore my last message
Sorry it was my mistake, i was using psql from 7.2.2 _ Protect your PC - get McAfee.com VirusScan Online http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[SQL] \d in 7.4
Sorry it was my mistake, i was using psql from 7.2.2 _ STOP MORE SPAM with the new MSN 8 and get 2 months FREE* http://join.msn.com/?page=features/junkmail ---(end of broadcast)--- TIP 8: explain analyze is your friend
[SQL] Multiple outer join on same table
Hi! I'm searching a better (quicker) way to retrieve data as I used to do using the following query... == SELECT main.codice, other.value AS value_one, other.value AS value_two FROM main LEFT OUTER JOIN otherON main.id = other.id_main LEFT OUTER JOIN other AS other2 ON main.id = other2.id_main WHERE other.type = 'type_one' AND other2.type = 'type_two'; == Thanks, Marco ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] a wierd query
On Thursday 13 May 2004 19:27, you wrote: sad wrote: select distinct a as F from table union select distinct b as F from table; Note that UNION only returns the unique values of the union You can get repeated values by using UNION ALL. read the original problem look at the DISTINCT clause in my query and think again ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] a wierd query
On Fri, 14 May 2004, sad wrote: On Thursday 13 May 2004 19:27, you wrote: sad wrote: select distinct a as F from table union select distinct b as F from table; Note that UNION only returns the unique values of the union You can get repeated values by using UNION ALL. read the original problem look at the DISTINCT clause in my query and think again What about the fact that union already removes duplicates? ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] isolation level
Bruno Wolff III [EMAIL PROTECTED] writes: On Thu, May 13, 2004 at 18:13:23 +, Jaime Casanova [EMAIL PROTECTED] wrote: Hi all, is there a way to set the isolation level to something like the sql standard dirty read. No. There will be a way to use the standard name in a SET command, but you will actaully get READ COMMITTED isolation (which is the next step up). I wonder how hard this would be to implement. It doesn't seem like it should be very hard. It would be very convenient for debugging and for checking on the progress of large batch updates or loads. -- greg ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Multiple outer join on same table
Marco Lazzeri [EMAIL PROTECTED] writes: Hi! I'm searching a better (quicker) way to retrieve data as I used to do using the following query... == SELECT main.codice, other.value AS value_one, other.value AS value_two FROM main LEFT OUTER JOIN other ON (main.id = other.id_main) LEFT OUTER JOIN other AS other2 ON (main.id = other2.id_main) WHERE other.type = 'type_one' AND other2.type = 'type_two' ; a) you're better off sending the actual query rather than retyping it. I assume you made a typo in the select column list and it should be other2.value AS value_two? Also the parentheses are required on the ON clause. b) The WHERE clause will effectively make this a plain inner join, not an outer join at all. Since any values that aren't found would have a NULL type column and cause the row to not be selected. I think the query you meant to write would be SELECT codice, other1.value AS value_one, other2.value AS value_two FROM main LEFT OUTER JOIN other as other1 ON (main.id = other1.id_main AND type = 'type_one') LEFT OUTER JOIN other as other2 ON (main.id = other2.id_main AND type = 'type_two) Another way to write this query that might be faster or might not depending would be: SELECT codice, (SELECT value FROM other WHERE id_main = id AND type = 'type_one') AS value_one, (SELECT value FROM other WHERE id_main = id AND type = 'type_two') AS value_two FROM codice In theory the two queries really ought to always result in the same plan because they're equivalent. However the Postgres optimizer as clever as it is is incapable of seeing this. The first form with the outer join leaves the optimizer with a lot more flexibility though, including at least one plan that is effectively identical to what the optimizer is forced to do for the second query. So really the first one should be no worse than the second. If you find the second faster (or if they're both still slow) you might consider posting explain analyze output for both queries. It may be that you have other issues preventing the optimizer from finding a good plan. You have run analyze on these tables recently? And you vacuum regularly? And for the second query you would really want an index on other.id_main too. For the first one it would depend on the data in the two tables. -- greg ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
