[SQL] Offtopic: psql
Hi (B (BPostgres has a perfect tool - psql (Bwhat libraries did you (developers) use to develop psql console and (Bparticulary command-line editor. (B (Bthnx (B (B (B---(end of broadcast)--- (BTIP 6: Have you searched our list archives? (B (B http://archives.postgresql.org
Re: [SQL] Offtopic: psql
O sad έγραψε στις Oct 21, 2004 : > Hi > > Postgres has a perfect tool - psql > what libraries did you (developers) use to develop psql console and > particulary command-line editor. % ldd `which psql` maybe? > > thnx > > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > >http://archives.postgresql.org > -- -Achilleus ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Finding duplicated values
I have a few tables that have duplicated values from an import from a different database. I have two keys I tried to set as primary and got an error ERROR: could not create unique indexDETAIL: Table contains duplicated values. Is there some join I can use to compare the hmhmkey, wmwmkey pairs against the table to find duplicate values? Each pair key should be unique but the old database was less than normalized. I was trying to use the code below but it returned no rows. SELECT hmhmkey, wmwmkey FROM exceptionsEXCEPTSELECT hmhmkey, wmwmkey FROM exceptions; Any suggestions? Kent Anderson EZYield.com 407-629-0900 www.ezyield.com This electronic message transmission contains information from the Company that may be proprietary, confidential and/or privileged. The information is intended only for the use of the individual(s) or entity named above. If you are not the intended recipient, be aware that any disclosure, copying or distribution or use of the contents of this information is prohibited. If you have received this electronic transmission in error, please notify the sender immediately by replying to the address listed in the "From:" field.
Re: [SQL] Finding duplicated values
On Thu, 21 Oct 2004, Kent Anderson wrote: > I have a few tables that have duplicated values from an import from a > different database. I have two keys I tried to set as primary and got an > error > ERROR: could not create unique index > DETAIL: Table contains duplicated values. > > Is there some join I can use to compare the hmhmkey, wmwmkey pairs against > the table to find duplicate values? Each pair key should be unique but the > old database was less than normalized. Maybe SELECT hmhmkey, wmwmkey FROM exceptions GROUP BY hmhmkey, wmwmkey HAVING count(*)>1; ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] Trouble with explicit joins
Hi, everybody!
I am writing a fairly long query, that joins several (like 10-15)
tables. It is dynamically generated, and the tables in the join may be
different, depending on the criteria etc...
The problem is that I need to outer (left) join some of those tables.
The only way I know to do that is using explicit join sytax ("select
blah from foo left join bar using (x)").
BUT when I try to write my query with that syntax, postgres comes up
with a totally wrong query plan (e.g., seq scan on a table with 100
million rows), even when no outer joins are used at all
(as I said, the query is dynamic - sometimes it needs a left join,
sometimes it does not, but it is the same code that generates it).
The same exact query with implicit join syntax ("select blah from foo,
bar where foo.x=bar.x" works fine.
I suppose, this is because the planner takes the order, in which the
tables appear in the explicit joins as some kind of a hint to how I want
that query to be executed, and, if I changed the order o fthose joins, I
believe, I would be able to get the same query plan as without explicit
joins, but unfortunately this is not an option, since, as I said, the
query is dynamically generated the set of tables being joined is
different every time, the criteria varies too, there are just too many
possibilities.
So, my only hope is that, perhaps, I am missing something simple here,
and somebody would be able to tell me either how to do an outer join
with implicit syntax or how to make the planner behave the same way when
it reads explicit syntax.
I'd appreciate any ideas.
Thanks a lot!
Dima
---(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
[SQL] Functions return a select in a table, which data type I must use? (Brazilian User)
Hello, friends. If anybody can help, how can I do a FUNCTION return a result from a SELECT in a table in PostgreSQL? My Problem is the RETURN TYPE from a FUNCTION, I don´t know what I have to use to return the data as a select result. Example: DROP FUNCTION ACADEMICO.teste(int4); CREATE FUNCTION ACADEMICO.teste(int4) RETURNS ? AS ' select cod_aluno, nome, cpf from ACADEMICO.TB_alunos ' LANGUAGE 'SQL'; -- Thanks in Advance André Toscano (Brazilian user) ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Trouble with explicit joins
Dmitry Tkach <[EMAIL PROTECTED]> writes: > I suppose, this is because the planner takes the order, in which the > tables appear in the explicit joins as some kind of a hint to how I want > that query to be executed, It's not a "hint", it's a requirement. In general, changing the order in which outer joins are executed changes the results. There are some cases in which it is safe to rearrange the order, but determining this takes close analysis of the join conditions, and we don't (yet) have any code to do that. So the planner must be conservative and take your join order as gospel. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Trouble with explicit joins
Tom Lane wrote: Dmitry Tkach <[EMAIL PROTECTED]> writes: I suppose, this is because the planner takes the order, in which the tables appear in the explicit joins as some kind of a hint to how I want that query to be executed, It's not a "hint", it's a requirement. In general, changing the order in which outer joins are executed changes the results. There are some cases in which it is safe to rearrange the order, but determining this takes close analysis of the join conditions, and we don't (yet) have any code to do that. So the planner must be conservative and take your join order as gospel. regards, tom lane Yeah.. that's what I figured. Are you saying there is no way around it at all? Isn't there a syntax supported to write a left join with implicit joins? ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Finding duplicated values
Kent Anderson wrote: I have a few tables that have duplicated values from an import from a different database. I have two keys I tried to set as primary and got an error ERROR: could not create unique index DETAIL: Table contains duplicated values. Is there some join I can use to compare the hmhmkey, wmwmkey pairs against the table to find duplicate values? Each pair key should be unique but the old database was less than normalized. I was trying to use the code below but it returned no rows. SELECT hmhmkey, wmwmkey FROM exceptions EXCEPT SELECT hmhmkey, wmwmkey FROM exceptions; Any suggestions? Kent Anderson EZYield.com 407-629-0900 www.ezyield.com This electronic message transmission contains information from the Company that may be proprietary, confidential and/or privileged. The information is intended only for the use of the individual(s) or entity named above. If you are not the intended recipient, be aware that any disclosure, copying or distribution or use of the contents of this information is prohibited. If you have received this electronic transmission in error, please notify the sender immediately by replying to the address listed in the "From:" field. ** This might do it... If you do this on the table that had duplicates you wont need to join select count(hmhmkey),count(wmwmkey) from exceptions group by hmhmkey,wmwmkey having count(hmhmkey) >1 or having count(wmwmkey) >1;
