[SQL] count( distinct x )
Apologies if this has been asked b4, but got this result when attemplting to search the archives on the website Not Found The requested URL /mhonarc/pgsql-sql/search.cgi was not found on this server. Apache/1.3.12 Server at postgresql.rmplc.co.uk Port 80 The problem I have is with this statement: select count( distinct area ) from areapostcode where postcode like 'BS1%' the above statement fails with ERROR: parser: parse error at or near "distinct" I am not the greatest when it comes to SQL, but the pgsql docs implied that the above would work. What I am trying to do is get a count of the no of matches from the statement below select distinct area from areapostcode where postcode like 'BS1%' Not the count of: select area from areapostcode where postcode like 'BS1%' Can anyone help? TIA Bap.
Re: [SQL] count( distinct x )
Jose Rodrigo Fernandez Menegazzo wrote: > > The problem I have is with this statement: > > > > select count( distinct area ) from areapostcode where postcode like > > 'BS1%' > > > > the above statement fails with > > ERROR: parser: parse error at or near "distinct" > > > > I am not the greatest when it comes to SQL, but the pgsql docs implied > > that the above would work. > > > > What I am trying to do is get a count of the no of matches from the > > statement below > > select distinct area from areapostcode where postcode like 'BS1%' > > > > Not the count of: > > select area from areapostcode where postcode like 'BS1%' > > > > I don't have where to try it, but have you tried: > > select distinct count(area) from areapostcode where postcode like 'BS1%' > > Rodrigo F. yes, it responds as if distinct is not in the query string. Thanks, Bap.
Re: [SQL] count( distinct x )
Michael Fork wrote: > I think you want > > SELECT count(distinct(area)) FROM areapostcode WHERE postcode LIKE 'BS1%' > psql still not happy :( SELECT count(distinct(area)) FROM areapostcode WHERE postcode LIKE 'BS1%'; ERROR: parser: parse error at or near "distinct" Thanks, Bap. > > Michael Fork - CCNA - MCP - A+ > Network Support - Toledo Internet Access - Toledo Ohio > > On Mon, 27 Nov 2000, Anthony wrote: > > > Apologies if this has been asked b4, but got this result when > > attemplting to search the archives on the website > > > > Not Found > > > > The requested URL /mhonarc/pgsql-sql/search.cgi was not found on this > > server. > > > > > > Apache/1.3.12 Server at postgresql.rmplc.co.uk Port 80 > > > > The problem I have is with this statement: > > > > select count( distinct area ) from areapostcode where postcode like > > 'BS1%' > > > > the above statement fails with > > ERROR: parser: parse error at or near "distinct" > > > > I am not the greatest when it comes to SQL, but the pgsql docs implied > > that the above would work. > > > > What I am trying to do is get a count of the no of matches from the > > statement below > > select distinct area from areapostcode where postcode like 'BS1%' > > > > Not the count of: > > select area from areapostcode where postcode like 'BS1%' > > > > Can anyone help? > > > > TIA > > Bap. > >
Re: [SQL] count( distinct x )
Kenn Thompson wrote: > What about > > select count(*) from (select distinct area from areapostcode where postcode like >'BS1%') > select count(*) from (select distinct area from areapostcode where postcode like 'BS1%'); ERROR: parser: parse error at or near "select" Thanks, any more ideas? > > >>> Anthony <[EMAIL PROTECTED]> 11/27/00 12:24PM >>> > Jose Rodrigo Fernandez Menegazzo wrote: > > > > The problem I have is with this statement: > > > > > > select count( distinct area ) from areapostcode where postcode like > > > 'BS1%' > > > > > > the above statement fails with > > > ERROR: parser: parse error at or near "distinct" > > > > > > I am not the greatest when it comes to SQL, but the pgsql docs implied > > > that the above would work. > > > > > > What I am trying to do is get a count of the no of matches from the > > > statement below > > > select distinct area from areapostcode where postcode like 'BS1%' > > > > > > Not the count of: > > > select area from areapostcode where postcode like 'BS1%' > > > > > > > I don't have where to try it, but have you tried: > > > > select distinct count(area) from areapostcode where postcode like 'BS1%' > > > > Rodrigo F. > > yes, it responds as if distinct is not in the query string. > > Thanks, > Bap.
Re: [SQL] count( distinct x )
Tom Lane wrote: > Anthony <[EMAIL PROTECTED]> writes: > > select count( distinct area ) from areapostcode where postcode like > > 'BS1%' > > the above statement fails with > > ERROR: parser: parse error at or near "distinct" > > What Postgres version are you running? Support for count(distinct foo) > was added in 7.0, IIRC. > > regards, tom lane select version(); version --- PostgreSQL 6.5.3 on i686-pc-linux-gnu, compiled by gcc egcs-2.91.66 (1 row) bugger! any ideas how to get same result at relative speed with different query? Thanks Tom.
Re: [SQL] count( distinct x )
Najm Hashmi wrote: > Anthony wrote: > > > Michael Fork wrote: > > > > > I think you want > > > > > > SELECT count(distinct(area)) FROM areapostcode WHERE postcode LIKE 'BS1%' > > > > > > > psql still not happy :( > > > > SELECT count(distinct(area)) FROM areapostcode WHERE postcode LIKE 'BS1%'; > > ERROR: parser: parse error at or near "distinct" > > > > Thanks, > > Bap. > > > > > > > > Michael Fork - CCNA - MCP - A+ > > > Network Support - Toledo Internet Access - Toledo Ohio > > > > > > On Mon, 27 Nov 2000, Anthony wrote: > > > > > > > Apologies if this has been asked b4, but got this result when > > > > attemplting to search the archives on the website > > > > > > > > Not Found > > > > > > > > The requested URL /mhonarc/pgsql-sql/search.cgi was not found on this > > > > server. > > > > > > > > > > > > Apache/1.3.12 Server at postgresql.rmplc.co.uk Port 80 > > > > > > > > The problem I have is with this statement: > > > > > > > > select count( distinct area ) from areapostcode where postcode like > > > > 'BS1%' > > > > > > > > the above statement fails with > > > > ERROR: parser: parse error at or near "distinct" > > > > > > > > I am not the greatest when it comes to SQL, but the pgsql docs implied > > > > that the above would work. > > > > > > > > What I am trying to do is get a count of the no of matches from the > > > > statement below > > > > select distinct area from areapostcode where postcode like 'BS1%' > > > > > > > > Not the count of: > > > > select area from areapostcode where postcode like 'BS1%' > > > > > > > > Can anyone help? > > > > > > > > TIA > > > > Bap. > > > > > > Hi, > I think this might work: It works on my machine, and I have postgres 7.xx > SELECT distinct(count(area)) FROM areapostcode WHERE postcode LIKE 'BS1%' > > Regrads > Najm no, this one succedes, but returns the count of select area from areapostcode where postcode like 'BS1%' not the count of select distinct area from areapostcode where postcode like 'BS1%' but I have just replied to this list with the following select version(); version --- PostgreSQL 6.5.3 on i686-pc-linux-gnu, compiled by gcc egcs-2.91.66 (1 row)
Re: [SQL] count( distinct x )
Kenn Thompson wrote: > Ok- messy, but it works > > CREATE VIEW testview AS > select distinct area from areapostcode where postcode like 'BS1%'; > > SELECT COUNT(*) FROM testview; > > kenn > > >>> Anthony <[EMAIL PROTECTED]> 11/27/00 01:07PM >>> > Kenn Thompson wrote: > > > What about > > > > select count(*) from (select distinct area from areapostcode where postcode like >'BS1%') > > > > select count(*) from (select distinct area from areapostcode where > postcode like 'BS1%'); > ERROR: parser: parse error at or near "select" > > Thanks, any more ideas? > > > > > >>> Anthony <[EMAIL PROTECTED]> 11/27/00 12:24PM >>> > > Jose Rodrigo Fernandez Menegazzo wrote: > > > > > > The problem I have is with this statement: > > > > > > > > select count( distinct area ) from areapostcode where postcode like > > > > 'BS1%' > > > > > > > > the above statement fails with > > > > ERROR: parser: parse error at or near "distinct" > > > > > > > > I am not the greatest when it comes to SQL, but the pgsql docs implied > > > > that the above would work. > > > > > > > > What I am trying to do is get a count of the no of matches from the > > > > statement below > > > > select distinct area from areapostcode where postcode like 'BS1%' > > > > > > > > Not the count of: > > > > select area from areapostcode where postcode like 'BS1%' > > > > > > > > > > I don't have where to try it, but have you tried: > > > > > > select distinct count(area) from areapostcode where postcode like 'BS1%' > > > > > > Rodrigo F. > > > > yes, it responds as if distinct is not in the query string. > > > > Thanks, > > Bap. CREATE VIEW testview AS -> select distinct area from areapostcode where postcode like 'BS1%'; ERROR: DISTINCT not supported in views I think it's time to get Mr. Sysadmin to upgrade to v7 ;) Thanks all, if anyone has an ideas of how to get this working on 6.5.3, then please help. I'll check back in the morning, and try any suggestions, if no joy will try to get PostgreSQL upgraded. Thanks all. Bap.
[SQL] Multiple Parameters to an Aggregate Function
I have a sub in a Perl script that loops execution the following
statement:
SELECT url,name FROM links
And formats it like so:
$name
A variable ($link_list) is in the loop, holding the concatenated last
statement with the new one.
I would rather do this with FUNCTIONS (and/or AGGREGATES).
So, I CREATEd a FUNCTION:
CREATE FUNCTION format_link(text,text)
RETURNS text AS '
return qq[http://www.domain.com/$_[0]";
class="body_link">$_[1]];
' LANGUAGE 'plperl';
So in my Perl script, the SQL statement becomes:
SELECT format_link(url,name) FROM links
However, I still have to loop with Perl -- it would be nice to use an
AGGREGATE to do some of this for me.
I can create an AGGREGATE but from what I can tell, the format of the
sfunc can only have two parameters like so:
sfunc_name(type,type)
Where the first parameter is what was passed before, and the second
parameter is the 'new' info to do stuff to. Is it not possible to do
something similar to?:
sfunc_name(type,type,type)
So that I can pass the url and name to the AGGREGATE (so it can in turn
pass it to the sfunc)? Where the sfunc could be something like so:
CREATE FUNCTION link_agg (text,text,text)
RETURNS text AS '
return $_[0] . qq[http://www.domain.com/$_[0]";
class="body_link">$_[1]];
' LANGUAGE 'plperl';
Because then I gain benefit of a stored procedure and cut the SQL in the
script down to:
SELECT link_agg(url,name) FROM link;
Which will return the entire list at once, instead of needing the script
to loop through multiple fetches.
...
Of course, I may be going about this in an entirely incorrect manner.
Telling me so, with a bit of direction, would also be greatly
appreciated.
BTW: I tried searching the archives, but there is a database error
("PQconnectPoll() -- connect() failed: Connection refused Is the
postmaster running (with -i) at 'db.hub.org' and accepting connections
on TCP/IP port 5439?"), just so someone knows.
Thanks In Advance,
Anthony Bouvier
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Time differences between rows, not columns?
Amit,
You say "I want to know how much time it took for a bill to be
accepted after it was submitted"
So, do you want between 10 and 40, not 10 and 20?
I assume you meant 10 and 40.
Ok, there's a few approaches to your questions, first
To get "how much time it took for a bill to be accepted after
it was submitted":
select bill_id,
(max(case when status_id = 40
then statustime end) -
max(case when status_id = 10
then statustime end))
from bill_status
where status_id in ( 10,40 )
group by bill_id;
that will give you the amt of time (simple date arithmetic, so, in days)
from submission to being accepted.
You also, want a simple distribution, you can use the technique above
But with SUM:
select sum(case when diff < 7 then 1 else 0 end) as lt_7,
sum(case when diff between 7 and 15 then 1 else 0 end) as
btw_7_15,
sum(case when diff > 15 then 1 else 0 end) as gt_15
from (
select bill_id,
(max(case when status_id = 40
then statustime end) -
max(case when status_id = 10
then statustime end)) as diff
from bill_status
where status_id in ( 10,40 )
group by bill_id
) x;
I have a recipe in my upcoming book ('The SQL Cookbook' by O'Reilly)
So, I'd like to know if what I suggested works out for you.
Obviously, you'll need whatever tweaks to make it perfect for your
system, the feedback I am concerned about is the technique.
hope that helps,
Anthony
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of
[EMAIL PROTECTED]
Sent: Tuesday, August 30, 2005 5:45 AM
To: [email protected]
Subject: [SQL] Time differences between rows, not columns?
Importance: High
Using postgresSQL 8.0 for windows, running on windows 2003 server 16gb
RAM, 3Ghz dual p4.
Language: Java - JDBC postgres driver: postgresql-8.0-310.jdbc3.jar
I have an application with 3 tables (in this context that is)
Table bills
bill_id NOT NULL serial
... And other columns
Table bill_status
bill_id (references bills.bill_id)
statusid int4 (references bill_statuslookup.statusid)
statustime datetime
Table bill_statuslookup
statusid serial not null
statusname varchar(255)
The application basically tracks a workflow of bills flowing fromone
department to another.
Everytime the bill moves one step, an entry is made into the bill_status
table.
Eg.
Bills table
-
Bill_id otherfield1
1
Bill_status table:
-
Bill_id statusidstatustime
1 10 2005-04-04 00:34:31
1 20 2005-04-05 00:55:00
Bill_statuslookup table:
-
Statusid Statusname
10submitted
20received
30rejected
40accepted
..
..
Now my problem is this:
1. Find the time taken for each bill to reach from status 10 to status
20 , given the time of status 10 should be between t1 and t2.
Eg I want to know how much time it took for a bill to be accepted after
it was submitted (criteria: submitted between yesterday and today)
2. I want to know how many bills took <7 days, how many tok 7-15 days,
how many took >15 days etc.
The status is a lookup table because the workflow constantly changes,
and I can't have submitted_on, recd_on accepted_on times etc in the main
bills table as columns because there are way too many statuses in the
life of a bill (read invoice).
Hope its clear as to what I'm looking for.
Any help is greatly appreciated!!
Regards,
Amit
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [SQL] Time differences between rows, not columns?
Amit, Glad it worked out :) As for the performance, lemme say that while I'm a huge fan Of postgres, my experience in regards to optimization in a production Environment is limited to Oracle and DB2. In oracle for example, if you have an index on a numeric field and perform min/max on it, there's a very cool algorithm that allows very fast retrieval of that data (basically the index scan is optimized cuz oracle knows you want only the extreme values). So, on oracle I'd use the MAX and SUM versions I suggested because There's a specific optimization to facilitate it and the query, since It accesses the table only once, is doing less logical reads. So, at the very least, in the version I suggested, since you are not Joining, you are performing less logical reads, which is always good. But, ultimately, you have to test and see what works for you. The self join might be just fine. 10k rows should not be a problem for either method, assuming you have an index on statusid and bill_id. Give it a spin and update this thread. Regards, Anthony -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 30, 2005 8:08 AM To: Anthony Molinaro; [email protected] Subject: RE: [SQL] Time differences between rows, not columns? Importance: High Thanks! Amit,You say "I want to know how much time it took for a bill to be accepted after it was submitted" So, do you want between 10 and 40, not 10 and 20? I assume you meant 10 and 40... ...Could be any status to any status, I wanted to generalize the concept for future usage. I tried the below: 1. select bill_id,(max(case when statusid = 40 then statustime end) - max(case when statusid = 10 then statustime end)) from ap.vits_statusupdate where statusid in ( 10,40 ) group by bill_id; Took 16 ms... But meanwhile I also tried: select a.bill_id, b.statustime-a.statustime from ap.vits_statusupdate a, ap.vits_statusupdate b where a.statusid = 10 and b.statusid = 40 and a.bill_id = b.bill_id Took 15 ms The second query is taking approximately 1 ms lesser time.. (have tried about 30 times in pgadmin) Got about 10 records in my test resultset. Surprisingly, if I add the order by bill_id cluase at the end of both queries, then your query performs 1 ms faster than mine, Don't know why, Is there any performance issues if this is run over 1+ rows? Which (yours or mine?) do you think will be faster, and more efficient? 2. select sum(case when diff < 7 then 1 else 0 end) as lt_7,sum(case when diff between 7 and 15 then 1 else 0 end) as btw_7_15,sum(case when diff > 15 then 1 else 0 end) as gt_15 from (select bill_id,(max(case when statusid = 40 then statustime end) - max(case when statusid = 10 then statustime end)) as diff from ap.vits_statusupdate where statusid in ( 10,40 ) group by bill_id) x; Tried this one too with both the queries in the sub-select (yours and mine), and works perfectly well for me, with accurate answers, and exactly what I'm looking for! shows 0 ms (??).. Next question.. Which one should I use? The joined query? Or the max() query? Thanks a lot for your time! Regards, Amit -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Anthony Molinaro Sent: Tuesday, August 30, 2005 4:59 PM To: Wadhwa, Amit; [email protected] Subject: Re: [SQL] Time differences between rows, not columns? Amit, You say "I want to know how much time it took for a bill to be accepted after it was submitted" So, do you want between 10 and 40, not 10 and 20? I assume you meant 10 and 40. Ok, there's a few approaches to your questions, first To get "how much time it took for a bill to be accepted after it was submitted": select bill_id, (max(case when status_id = 40 then statustime end) - max(case when status_id = 10 then statustime end)) from bill_status where status_id in ( 10,40 ) group by bill_id; that will give you the amt of time (simple date arithmetic, so, in days) from submission to being accepted. You also, want a simple distribution, you can use the technique above But with SUM: select sum(case when diff < 7 then 1 else 0 end) as lt_7, sum(case when diff between 7 and 15 then 1 else 0 end) as btw_7_15, sum(case when diff > 15 then 1 else 0 end) as gt_15 from ( select bill_id, (max(case when status_id = 40 then statustime end) - max(case when status_id = 10 then statustime end)) as diff from bill_status where status_id in ( 10,40 ) group by bill_id ) x; I have a recipe in my upcoming book ('The SQL Cookbook' by O'Reilly) So, I'd like to know if what I suggested works out for you. Obviously, you'll need whatever tweaks to make it perfect for your system, the feedback I am concerned about is the technique. hope
Re: [SQL] how to replace
Michael,
You practically solved it yourself in the subject of the email ;)
select replace('abcd','b','') from your_table;
Hope that helps,
Anthony
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of "Michael Höller"
Sent: Thursday, September 08, 2005 5:25 PM
To: [email protected]
Subject: [SQL] how to replace
Hello,
I initially thought this is simple.. I want to relpace a character to
nothing. Eg. relace "B" to "" -> ABCD to ACD.
All me approches vaild but I am sure that I have seen it already and
think it was not tricky..
Can someone please help me ?
Thanks a lot
Michael
---(end of broadcast)---
TIP 1: 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
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [SQL] how to do 'deep queries'?
that query is 100% correct. it’s just an equijoin (a type of inner join) between 3 tables. the syntax you show is how queries should be written and is more representative of what a joins between relations really are: Cartesian products with filters applied the ansi syntax, the explicit JOIN … ON stuff is (imho) unnecessary, useful only for outer joins since all the vendors did it differently. what you have will work for postgreSQL, I used the syntax you show in my book for every single join recipe except for outjoins. are you seeing errors? regards, Anthony -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of jeff sacksteder Sent: Monday, September 26, 2005 8:34 PM To: [email protected] Subject: [SQL] how to do 'deep queries'? Is there supported syntax to do 'deep' queries? That is where A relates to B relates to C, returning fields from each table? This doesn't seem to work. Is there a google-able term for this sort of query? select foo.aaa, bar.bbb, baz.ccc from foo,bar,baz where foo.bar_id = bar.id and bar.baz_id = baz.id
Re: [SQL] how to do 'deep queries'?
Daryl, > Whether you feel that is unnecessary or not, it *is* the ANSI Standard > and is thus, by definition, "how queries should be written." I disagree 100%. Oracle and db2 introduced window functions years before Ansi added them. Should we not have used them? It absurd to avoid using a feature cuz it's not ansi. Honestly, Don't be a slave to ansi, you miss out on all the great vendor specific functionality *that you're already paying for* > it was added to make the *intention* of the query clearer. More clearer to whom? Certainly not developers who have been working for many years using the old syntax. The intention of the old syntax is perfect. Realize that the problem is not the old syntax, the problem is the watered down database field today. I see this more and more with each interview I conduct looking for dba's and developers. You know, it used to be that database developers had a solid background in math and relational theory. Sadly, that's not the case anymore... select * from a,b where a.id=b.id Suggests a Cartesian product between two relations then a filter to keep only matching rows. That's a join. And that syntax is a *perfect* representation of it. So to whom is ansi more clear? To the person who knows nothing about databases and decided one day to get a certification and call themselves an expert? Or maybe the person who decided one day on a whim to get into databases and not realize that tons of code from the prior decade use the old style syntax? > Because others are likely to read your query many more times than you > write it, clarity of intent *is* important. I've never worked in a place that used ANSI only syntax and I've never had a problem with clarity nor any developers I've worked with. So, I don't at all get what you're saying... Old style is short and sweet and perfect. Ansi dumbed it down, that's the bottom line. And for people who've been developing for sometime, It's wholly unnecessary. Regards, Anthony -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Daryl Richter Sent: Tuesday, September 27, 2005 9:24 AM To: [email protected] Subject: Re: [SQL] how to do 'deep queries'? Anthony Molinaro wrote: > that query is 100% correct. > > it's just an equijoin (a type of inner join) between 3 tables. > > the syntax you show is how queries should be written and is more > representative of what a joins between relations really are: > Cartesian products with filters applied > > the ansi syntax, the explicit JOIN ... ON stuff is (imho) unnecessary, > useful only for outer joins since all the vendors did it differently. > Whether you feel that is unnecessary or not, it *is* the ANSI Standard and is thus, by definition, "how queries should be written." In addition to cleaning up the outer join issue, it was added to make the *intention* of the query clearer. Because others are likely to read your query many more times than you write it, clarity of intent *is* important. > what you have will work for postgreSQL, I used the syntax you show in my > book > for every single join recipe except for outjoins. > > are you seeing errors? > > regards, > Anthony [original snipped] -- Daryl "We want great men who, when fortune frowns, will not be discouraged." -- Colonel Henry Knox, 1776 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] how to do 'deep queries'?
> Well, perhaps you will one day and a developer will hose your server > with a "accidental" cross join and then you will understand. Hehe :)) hey man, that's what testing and code review is all about (dev teams still do that don't they?) Accidental cartesians don't get to production ;) Regards, Anthony -Original Message- From: Daryl Richter [mailto:[EMAIL PROTECTED] Sent: Tuesday, September 27, 2005 11:35 AM To: Anthony Molinaro Cc: [email protected] Subject: Re: [SQL] how to do 'deep queries'? Anthony Molinaro wrote: > Daryl, > > >>Whether you feel that is unnecessary or not, it *is* the ANSI Standard > > >>and is thus, by definition, "how queries should be written." > > > I disagree 100%. Oracle and db2 introduced window functions years > before > Ansi added them. Should we not have used them? It absurd to avoid using > a feature cuz it's not ansi. > Of course it would be absurd, I have not suggested otherwise. Joins are not a *new* feature. > Honestly, Don't be a slave to ansi, you miss out on all the great > vendor specific functionality *that you're already paying for* > > >>it was added to make the *intention* of the query clearer. > > > More clearer to whom? > > Certainly not developers who have been working for many years > using the old syntax. > > The intention of the old syntax is perfect. Realize that the problem is > not the old syntax, the problem is the watered down database field > today. > I see this more and more with each interview I conduct looking > for dba's and developers. > I generally agree with your assessment of the state of database knowledge (particularly re developers). It is, however, the reality we live in. [snipped nostalgia and back-patting] > I've never worked in a place that used ANSI only syntax and I've never > had a problem with clarity nor any developers I've worked with. > So, I don't at all get what you're saying... > Old style is short and sweet and perfect. > Ansi dumbed it down, that's the bottom line. > And for people who've been developing for sometime, > It's wholly unnecessary. > Well, perhaps you will one day and a developer will hose your server with a "accidental" cross join and then you will understand. But hopefully not. ;) > Regards, > Anthony > [rest snipped] -- Daryl Director of Technology (( Brandywine Asset Management ) ( "Expanding the Science of Global Investing" ) ( http://www.brandywine.com )) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Selecting records not present in related tables
Jeziel, there are a couple techniques you can try, two I like are set difference and anti-joins. here’s the set diff: select id from messages except select id from usermessages that will returns all id from messages not in usermessages if ID is indexed on both tables, you may wanna try an anti join: select m.id from messages m left join usermessages um on ( m.id = um.id ) where um.id is null both techniques can be visciously efficient. good luck, Anthony -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Hector Rosas Sent: Thursday, October 06, 2005 3:44 PM To: [email protected] Subject: [SQL] Selecting records not present in related tables Hello, I'm trying to select records in a table not present in a related table, in example, I've a table with message information (subject, message, date, etc) and another (usermessages) with where user(s) has that message, its state, etc. Records in this table will be deleted in a certain time (just some extra info). I want to select messages records that aren't present in the other table (usermessages), I got the next two queries, maybe someone can suggest a better one. SELECT m.id FROM messages AS m WHERE (SELECT count(um.*) FROM usermessages AS um WHERE um.idmessage=m.id )=0; SELECT m.id FROM messages AS m where id NOT IN (select um.idmessage FROM usermessages um); Both queries work, but doing a EXPLAIN ANALYZE I got the next results. bd=# explain analyze SELECT m.id FROM messages AS m bd-# WHERE (SELECT count(um.*) FROM usermessages AS um WHERE um.idmessage=m.id)=0; QUERY PLAN --- Seq Scan on messages m (cost=0.00..3915.75 rows=3 width=4) (actual time=40.531..40.531 rows=0 loops=1) Filter: ((subplan) = 0) SubPlan -> Aggregate (cost=9.11..9.11 rows=1 width=4) (actual time=0.098..0.104 rows=1 loops=355) -> Index Scan using message_selection on usermessages um (cost=0.00..9.10 rows=3 width=4) (actual time=0.067..0.078 rows=1 loops=355) Index Cond: (idmessage = $0) Total runtime: 40.605 ms (7 rows) bd=# explain analyze select m.id FROM messages AS m where id NOT IN (select um.idmessage FROM usermessages um); QUERY PLAN -- Seq Scan on messages m (cost=9.68..43.00 rows=213 width=4) (actual time=20.329..20.329 rows=0 loops=1) Filter: (NOT (hashed subplan)) SubPlan -> Seq Scan on usermessages um (cost=0.00..8.54 rows=454 width=4) (actual time=0.008..13.094 rows=454 loops=1) Total runtime: 20.386 ms (5 rows) In first query, cost can be between 0 and almost 4 sec, and also I see that loops value, that I don't know what performance issues could arise. In second query, I see a seq scan, which I don't like, I think that with too many records this query could take ages, or maybe not, but loops value is 1. I hope someone can give some advice with those queries , or maybe a better query. I've not decided which query I'm going to use, thanks! Jeziel.
Re: [SQL] pg, mysql comparison with "group by" clause
You're 100% correct, this is a bug in mysql. Sadly, they tout this as a feature! -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Rick Schumeyer Sent: Tuesday, October 11, 2005 5:12 PM To: [email protected] Subject: [SQL] pg, mysql comparison with "group by" clause I'm not sure what I was thinking, but I tried the following query in pg: SELECT * FROM t GROUP BY state; pg returns an error. Mysql, OTOH, returns the first row for each state. (The first row with "AK", the first row with "PA", etc.) I'm no SQL expert, but it seems to me that the pg behavior is correct, and the mysql result is just weird. Am I correct? ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] pg, mysql comparison with "group by" clause
> Not always, but I'd rather get the right answer with difficulty than the wrong one with ease. :) agreed. I made it a point to mention this so called "feature" in my book. This is a bug they never fixed and they decided to call it a feature. It is, imo, *ridiculous*. Regards, Anthony -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Scott Marlowe Sent: Wednesday, October 12, 2005 6:25 PM To: Greg Stark Cc: Stephan Szabo; Rick Schumeyer; [email protected] Subject: Re: [SQL] pg, mysql comparison with "group by" clause On Wed, 2005-10-12 at 16:54, Greg Stark wrote: > Stephan Szabo <[EMAIL PROTECTED]> writes: > > > On Tue, 11 Oct 2005, Rick Schumeyer wrote: > > > > > I'm not sure what I was thinking, but I tried the following query in pg: > > > > > > SELECT * FROM t GROUP BY state; > > > > > > pg returns an error. > > > > > > Mysql, OTOH, returns the first row for each state. (The first row with > > > "AK", the first row with "PA", etc.) > > > > > > I'm no SQL expert, but it seems to me that the pg behavior is correct, and > > > the mysql result is just weird. Am I correct? > > > > In your case, it sounds like the mysql result is wrong. I believe SQL99 > > would allow it if the other columns were functionally dependant upon state > > (as there'd by definition only be one value for the other columns per > > group). > > I believe this is a documented feature. Hehe. When I turn on my windshield wipers and my airbag deploys, is it a documented "feature" if the dealership told me about this behaviour ahead of time? In much the same way, while this behaviour may be documented by MySQL, I can't imagine it really being called a feature. But at least this misbehaviour is documented. However, I think most people in the MySQL universe just stumble onto it by accident when they try it and it works. I'd at least prefer it to throw a warning or notice or something. > MySQL treats "select a,b from t group by a" equivalently to Postgres's > "select distinct on (a) a,b from t" > > I suppose "equivalent" isn't quite true. It's more general since it allows > aggregate functions as well. The equivalently general Postgres syntax is to > have a first() aggregate function and do "select a,first(b) from t group by a". A Subselect would let you do such a thing as well, and while it's more complicated to write, it is likely to be easier to tell just what it's doing. > I'm sure it's very convenient. Not always, but I'd rather get the right answer with difficulty than the wrong one with ease. :) ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] pg, mysql comparison with "group by" clause
> I don't see why you think people stumble on this by accident. I think it's > actually an extremely common need. I can't imagine how that's a common need at all. It makes no sense. When you add an additional column in the select, it must be included in the group by as it changes the meaning of the query. Consider: select deptno, count(*) from emp group by deptno; DEPTNO COUNT(*) -- -- 10 3 20 5 30 6 the query above counts the number of employees in each department. Now consider the following query: select deptno,job,count(*) from emp group by deptno,job; DEPTNO JOB COUNT(*) -- - -- 10 CLERK 1 10 MANAGER1 10 PRESIDENT 1 20 CLERK 2 20 ANALYST2 20 MANAGER1 30 CLERK 1 30 MANAGER1 30 SALESMAN 4 the query above counts the number of different job types in each department. In mysql, you would be allowed to do the following: select deptno,job,count(*) from emp group by deptno; but it makes no sense. What value would it return and what does it mean? How can that possibly represent reliable data? What would the result set above look like? It would be meaningless in a production system. honestly. It's a silly bug that mysql has touted has a feature and I can't imagine why people think it's useful. > I think most MySQL users don't stumble on it, they learn it as the way to > handle the common use case when you join a master table against a detail > table and then want to aggregate all the detail records. Huh? I don't follow that at all Perhaps your confusing the concept with window functions that neither pg nor mysql have implemented yet? For example, using window functions allows you to return aggregate and detailed info simultaneously: select ename, deptno, job, count(*)over(partition by deptno) as emps_per_dept, count(*)over(partition by deptno,job) as job_per_dept, count(*)over() as total from emp ENAME DEPTNO JOB EMPS_PER_DEPT JOB_PER_DEPT TOTAL -- -- - - - MILLER 10 CLERK 3114 CLARK 10 MANAGER 3114 KING 10 PRESIDENT 3114 SCOTT 20 ANALYST 5214 FORD 20 ANALYST 5214 SMITH 20 CLERK 5214 ADAMS 20 CLERK 5214 JONES 20 MANAGER 5114 JAMES 30 CLERK 6114 BLAKE 30 MANAGER 6114 ALLEN 30 SALESMAN 6414 MARTIN 30 SALESMAN 6414 TURNER 30 SALESMAN 6414 WARD 30 SALESMAN 6414 But this is not a group by, this is aggregating and windowing, which is quite different from mysql adding that nasty little bug and calling it a feature. - a -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Greg Stark Sent: Wednesday, October 12, 2005 9:13 PM To: Scott Marlowe Cc: Greg Stark; Stephan Szabo; Rick Schumeyer; [email protected] Subject: Re: [SQL] pg, mysql comparison with "group by" clause Scott Marlowe <[EMAIL PROTECTED]> writes: > Hehe. When I turn on my windshield wipers and my airbag deploys, is it > a documented "feature" if the dealership told me about this behaviour > ahead of time? Well it's more like my car where the dashboard dims when I turn on my headlights which annoys me to no end since I learned to always put my headlights on even in the day. > In much the same way, while this behaviour may be documented by MySQL, I > can't imagine it really being called a feature. But at least this > misbehaviour is documented. However, I think most people in the MySQL > universe just stumble onto it by accident when they try it and it works. I'd > at least prefer it to throw a warning or notice or something. I don't see why you think people stumble on this by accident. I think it's actually an extremely common need. So common that Postgres has the same feature (though less general) and invented a whole syntax to handle it. I think most MySQL users don't stumble on it, they learn it as the way to handle the common use case when you join a master table against a detail table and then want to aggregate all the detail records. In standard SQL you have to write GROUP BY ... and list every single column you need from the master table. Forcing the database to do a lot of redundant comparisons and sort on uselessly long keys where in fact you only really need it to sort and group by the primary key. Remember, m
Re: [SQL] pg, mysql comparison with "group by" clause
Tom, Don't do it man... It's not the same. The standard has it wrong (that or I misunderstood your explanation). But this statement: > you shouldn't have to explicitly list the other columns from that > table --- they are guaranteed to have unique values per group anyway ...is way off. By changing the values in the select/group by you are changing Group! How can you arbitrarily add or exclude a column? You can't do it. Look, all it takes is one example to prove the standard wrong... Consider this simple example (in oracle, but the same in pg): create table foo (id number primary key, name varchar2(10)); insert into foo values (1,'joe'); insert into foo values (2,'joe'); insert into foo values (3,'jim'); select * from foo; ID NAME -- -- 1 joe 2 joe 3 jim ok, lets get the count by ID (which is unique): select id, count(*) from foo group by id; ID COUNT(*) -- -- 1 1 2 1 3 1 makes sense... the values for ID, 1,2,3 are our groups and the count aggregate op shows that there's one member per group. now let's get the count by name: select name, count(*) from foo group by name; NAME COUNT(*) -- -- jim 1 joe 2 so far so good. The queries and results are representative of the actual data. The values for name, "jim" and "joe" are our groups and the count aggregate op shows that there's 1 member in the "jim" group and two members in the "joe" group. But, as soon as we add id to the group by... select name, count(*) from foo group by name,id; NAME COUNT(*) -- -- jim 1 joe 1 joe 1 it changes the query! Even tho id is unique, it changes the query. The group by it's definition (it's in the group by) is no longer Name or id, it's both name and id. How can you simply remove id? Which result set should it return The first or second? Makes no sense... If it's aint obvious why, simply plug id into the select: select id, name, count(*) from foo group by name,id; ID NAME COUNT(*) --- -- -- 3 jim 1 1 joe 1 2 joe 1 so, how can it be that you don't have to list the other columns (in this case "name")? it makes no sense because if you remove the either column from the group by (id or name) it changes the meaning of the query. The way you guys do it now is correct. Mysql has it wrong. And if the standard states that you can leave out the pk from a group by, They are wrong too, as the simple examples above prove. Fyi, Oracle just bought innodb, so, I'd not be too concerned with mysql and they so called "features" anyway. Regards, Anthony -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Tom Lane Sent: Wednesday, October 12, 2005 9:51 PM To: Greg Stark Cc: Scott Marlowe; Stephan Szabo; Rick Schumeyer; [email protected] Subject: Re: [SQL] pg, mysql comparison with "group by" clause Greg Stark <[EMAIL PROTECTED]> writes: > I think most MySQL users don't stumble on it, they learn it as the way > to handle the common use case when you join a master table against a > detail table and then want to aggregate all the detail records. In > standard SQL you have to write GROUP BY ... and list every single > column you need from the master table. Forcing the database to do a > lot of redundant comparisons and sort on uselessly long keys where in > fact you only really need it to sort and group by the primary key. Actually, if you're grouping by a table's primary key, the SQL99 spec says you shouldn't have to explicitly list the other columns from that table --- they are guaranteed to have unique values per group anyway. This is a single case in the "functional dependency" stuff. That verbiage is incredibly dense and I don't think we want to tackle all of it any time soon, but the primary-key case probably wouldn't be very hard to implement. We really ought to have this in TODO ... I'm sure it's been discussed before. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match ---(end of broadcast)--- TIP 1: 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] pg, mysql comparison with "group by" clause
Greg, You'll have to pardon me... I saw this comment: "I don't see why you think people stumble on this by accident. I think it's actually an extremely common need." Which, if referring to the ability to have items in the select that do not need to be included in the group, (excluding constants and the like) is just silly. OTOH, if you're all poking fun at a mysql bug that they try to pass off as a feature, then yes, I'm a clod and I missed that the first time around :) Regards, Anthony -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 12, 2005 11:43 PM To: Anthony Molinaro Cc: Tom Lane; Greg Stark; Scott Marlowe; Stephan Szabo; Rick Schumeyer; [email protected] Subject: Re: [SQL] pg, mysql comparison with "group by" clause "Anthony Molinaro" <[EMAIL PROTECTED]> writes: > By changing the values in the select/group by you are changing > Group! How can you arbitrarily add or exclude a column? > You can't do it. Go back and reread the previous posts again. You missed the whole point. -- greg ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] pg, mysql comparison with "group by" clause
Greg, Ok, I think I see where you're going (I don't agree, but I think I get you now). So, using your example of: "dept_name is guaranteed to be the same for all records with the same dept_id." Here: select d.deptno,d.dname from emp e, dept d where e.deptno=d.deptno DEPTNO DNAME -- -- 10 ACCOUNTING 10 ACCOUNTING 10 ACCOUNTING 20 RESEARCH 20 RESEARCH 20 RESEARCH 20 RESEARCH 20 RESEARCH 30 SALES 30 SALES 30 SALES 30 SALES 30 SALES 30 SALES ok, so there's your scenario. And you're suggesting that one should be able to Do the following query? select d.deptno,d.dname,count(*) from emp e, dept d where e.deptno=d.deptno if that's what you suggest, then we'll just have to agree to disagree. That query needs a group by. What you're suggesting is, imo, a wholly unnecessary shortcut (almost as bad as that ridiculous "natural join" - whoever came up with that should be tarred and feathered). I think I see your point now, I just disagree. Your depending on syntax to work based on data integrity? Hmmm don't think I like that idea What performance improvement do you get from leaving group by out? Look at the query above, doesn't a count of distinct deptno,dname pairs have to take place anyway? What do you save by excluding group by? Are you suggesting COUNT be computed for each row (windowed) or that COUNT is computed for each group? If you want repeating rows, then you want windowing. For example: select d.deptno,d.dname,count(*)over(partition by d.deptno,d.dname) cnt from emp e, dept d where e.deptno=d.deptno DEPTNO DNAME CNT -- -- --- 10 ACCOUNTING 3 10 ACCOUNTING 3 10 ACCOUNTING 3 20 RESEARCH 5 20 RESEARCH 5 20 RESEARCH 5 20 RESEARCH 5 20 RESEARCH 5 30 SALES6 30 SALES6 30 SALES6 30 SALES6 30 SALES6 30 SALES6 if you want "groups", then use group by: select d.deptno,d.dname,count(*) cnt from emp e, dept d where e.deptno=d.deptno group by d.deptno,d.dname DEPTNO DNAME CNT -- -- --- 10 ACCOUNTING 3 20 RESEARCH 5 30 SALES6 what your suggesting doesn't seem to fit in at all, particularly when pg implements window functions. If you're suggesting the pg optimizer isn't doing the right thing with group by queries, then this is an optimizer issue and that should be hacked, not group by. If you're suggesting certain rows be ditched or shortcuts be taken, then the optimizer should do that, not the programmer writing sql. Db2 and oracle have no problem doing these queries, I don't see why pg should have a problem. imo, the only items that should not be listed in the group by are: 1. constants and deterministic functions 2. scalar subqueries 3. window functions 1 - because the value is same for each row 2&3 - because they are evaluated after the grouping takes place regards, Anthony -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Thursday, October 13, 2005 12:25 AM To: Anthony Molinaro Cc: [EMAIL PROTECTED]; Tom Lane; Scott Marlowe; Stephan Szabo; Rick Schumeyer; [email protected] Subject: Re: [SQL] pg, mysql comparison with "group by" clause "Anthony Molinaro" <[EMAIL PROTECTED]> writes: > Greg, > You'll have to pardon me... > > I saw this comment: > > "I don't see why you think people stumble on this by accident. > I think it's actually an extremely common need." > > Which, if referring to the ability to have items in the select that do not > need to be included in the group, (excluding constants and the like) is just > silly. Well the "constants and the like" are precisely the point. There are plenty of cases where adding the column to the GROUP BY is unnecessary and since Postgres makes no attempt to prune them out, inefficient. And constants aren't the only such case. The most common case is columns that are coming from a table where the primary key is already included in the GROUP BY list. In the case of columns coming from a table where the primary key is already in the GROUP BY list it's possible for the database to deduce that it's unnecessary to group on that column. But it's also possible to have cases where the programmer has out of band knowledge that it's unnecessary but the database doesn't have that knowledge. The most obvious case that comes to mind is a denormalized data model that includes a redundant column. select dept_id, dept_name, count(*) from employee_list For example if dept_name is guaranteed to be the same f
Re: [SQL] pg, mysql comparison with "group by" clause
Greg, " You would prefer: select user_id, any(username) as username, any(firstname) as firstname, any(lastname) as lastname, any(address) as address, any(city) as city, any(street) as street, any(phone) as phone, any(last_update) as last_update, any(last_login) as last_login, any(referrer_id) as referrer_id, any(register_date) as register_date, ... sum(money) as balance, count(money) as num_txns from user join user_money using (user_id) group by user_id " yes, that's right! Guess what? It's been that way for years. Why change it now? You're arguing something that works perfectly and has been understood for years. Changing the syntax cuz pg doesn't optimize it the way you like is ridiculous. Perhaps this change would make the newbies happy but I cant imagine an experienced developer asking for this, let alone argue for it. > I'm pretty unsympathetic to the "we should make a language less powerful > and more awkward because someone might use it wrong" argument. More awkward? What *you're* suggesting is more awkward. You realize that right? How can syntax that is understood and accepted for years be more awkward? Again, you're asking for changes that no one but a newbie would ask for I'm not at all suggesting you are/aren't a newbie (so don't take offense to this :), all I'm saying is that for experienced developers, we'd hope that the source code developers for pg/oracle/db2 etc are focusing on more important things, not rewriting things that already work because something doesn't wanna type out column names... regards, Anthony -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Greg Stark Sent: Thursday, October 13, 2005 2:27 PM To: Scott Marlowe Cc: Greg Stark; Stephan Szabo; Rick Schumeyer; [email protected] Subject: Re: [SQL] pg, mysql comparison with "group by" clause Scott Marlowe <[EMAIL PROTECTED]> writes: > Sorry, but it's worse than that. It is quite possible that two people > could run this query at the same time and get different data from the > same set and the same point in time. That shouldn't happen accidentally > in SQL, you should know it's coming. I'm pretty unsympathetic to the "we should make a language less powerful and more awkward because someone might use it wrong" argument. > > In standard SQL you have to > > write GROUP BY ... and list every single column you need from the master > > table. Forcing the database to do a lot of redundant comparisons and sort on > > uselessly long keys where in fact you only really need it to sort and group by > > the primary key. > > But again, you're getting whatever row the database feels like giving > you. A use of a simple, stupid aggregate like an any() aggregate would > be fine here, and wouldn't require a lot of overhead, and would meet the > SQL spec. Great, so I have a user table with, oh, say, 40 columns. And I want to return all those columns plus their current account balance in a single query. The syntax under discussion would be: select user.*, sum(money) from user join user_money using (user_id) group by user_id You would prefer: select user_id, any(username) as username, any(firstname) as firstname, any(lastname) as lastname, any(address) as address, any(city) as city, any(street) as street, any(phone) as phone, any(last_update) as last_update, any(last_login) as last_login, any(referrer_id) as referrer_id, any(register_date) as register_date, ... sum(money) as balance, count(money) as num_txns from user join user_money using (user_id) group by user_id Having a safeties is fine but when I have to disengage the safety for every single column it starts to get more than a little annoying. Note that you cannot write the above as a subquery since there are two aggregates. You could write it as a join against a view but don't expect to get the same plans from Postgres for that. > Actually, for things like aggregates, I've often been able to improve > performance with sub selects in PostgreSQL. If your experience is like mine it's a case of two wrongs cancelling each other out. The optimizer underestimates the efficiency of nested loops which is another problem. Since subqueries' only eligible plan is basically a nested loop it often turns out to be faster than the more exotic plans a join can reach. In an ideal world subqueries would be transformed into the equivalent join (or some more general join structure that can cover both sets of semantics) and then planned through the same code path. In an ideal world the user should be guaranteed that equivalent queries would always result
Re: [SQL] pg, mysql comparison with "group by" clause
Well... An additional gripe is that this isn't a good feature (standard or not). Oracle doesn't do it. Db2 doesn't do it. I strongly suggest you guys don't do it. If you wanna do the optimizations under the covers, cool, but I can't imagine how this would be useful other than for saving some typing... Seems more trouble than it's worth and changes a concept that's tried and true for many years. Regards, Anthony -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Tom Lane Sent: Thursday, October 13, 2005 2:50 PM To: Scott Marlowe Cc: Greg Stark; Stephan Szabo; Rick Schumeyer; [email protected] Subject: Re: [SQL] pg, mysql comparison with "group by" clause >>>> In standard SQL you have to >>>> write GROUP BY ... and list every single column you need from the master >>>> table. This thread seems to have gone off on a tangent that depends on the assumption that the above is a correct statement. It's not. It *was* true, in SQL92, but SQL99 lets you omit unnecessary GROUP BY columns. The gripe against mysql, I think, is that they don't enforce the conditions that guarantee the query will give a unique result. The gripe against postgres is that we haven't implemented the SQL99 semantics yet. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] pg, mysql comparison with "group by" clause
Tom, I'm sure there's all sorts of cool optimizations under the covers to perhaps maybe to this short circuiting, but as the sql goes, yeah, I'm sure. Here's an example on oracle 10g release 2 (copy paste from my screen so you can see the error messages and all): SQL> create table foo(id number primary key, name varchar2(10)); Table created. SQL> insert into foo values (1,'sam'); 1 row created. SQL> insert into foo values (2,'sam'); 1 row created. SQL> commit; Commit complete. SQL> select id, count(*) from foo; select id, count(*) from foo * ERROR at line 1: ORA-00937: not a single-group group function SQL> select name, count(*) from foo; select name, count(*) from foo * ERROR at line 1: ORA-00937: not a single-group group function SQL> select name, count(*) from foo group by id; select name, count(*) from foo group by id * ERROR at line 1: ORA-00979: not a GROUP BY expression SQL> select name, count(*) from foo group by name; NAME COUNT(*) -- -- sam 2 SQL> select name, count(*) from foo group by name,id; NAME COUNT(*) -- -- sam 1 sam 1 SQL> I can't imagine Oracle making a change such as the one we're discussing at this point. Perhaps in 8.1.6, ~7 years ago, when *tons* of sql changes were implemented (analytics, CASE, ordering in inline views, CUBE, ROLLUP), but not now... then again, oracle is 100% completely driven by money, so, if enough customers ask for it, it will happen eventually. I just can't imagine anyone asking for this feature when we're paying 40k per cpu just to run oracle; there are much more important things for them to be workin on... Regards, Anthony -Original Message----- From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: Thursday, October 13, 2005 3:17 PM To: Anthony Molinaro Cc: Scott Marlowe; Greg Stark; Stephan Szabo; Rick Schumeyer; [email protected] Subject: Re: [SQL] pg, mysql comparison with "group by" clause "Anthony Molinaro" <[EMAIL PROTECTED]> writes: > An additional gripe is that this isn't a good feature (standard or not). > Oracle doesn't do it. Db2 doesn't do it. You sure about that? It's hard to believe that the SQL committee would put a feature into the spec that neither Oracle nor IBM intended to implement. Those two pretty much control the committee after all ... regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] pg, mysql comparison with "group by" clause
Sheesh... I work with 2 MIT guys, and man, you guys will never admit you're wrong. Must be something in the water down there ;) Hey man, you know what, to each his own (but Apostol is one of my favorites, so maybe have that in common? :) You apparently like this shortcut, so be it. I'll say this tho, Oracle and db2 don't do it even tho it's in the standard and their optimizer is already doing the right thing. That's gotta tell ya something, no? don't look for this feature to be something you can do everywhere. If for some reason postgres implements it, it will be the only vendor to do so (though, this seems like a very MySQL-ish thing to do so maybe not just Postgres) take care, Anthony -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Friday, October 14, 2005 3:21 AM To: Anthony Molinaro Cc: Greg Stark; Scott Marlowe; Stephan Szabo; Rick Schumeyer; [email protected] Subject: Re: [SQL] pg, mysql comparison with "group by" clause "Anthony Molinaro" <[EMAIL PROTECTED]> writes: > More awkward? What *you're* suggesting is more awkward. You realize that > right? How can syntax that is understood and accepted for years be more > awkward? Well gosh, I would say that that's something only a newbie could say about SQL of all things... I had a whole thing written after that but I just deleted it. I grow tired of this thread. I am pretty happy to hear that the SQL standard endorsed the idea having the right thing happen if the primary key is present in the grouping list. That would be a wonderful feature for Postgres. -- greg ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[SQL] Data Entry and Query forms
Hi I am new in PostgreSQL, I have just installed and created a database, I use MS-SQL 2000 and planning to Migrate to Open Source. PostgreSQL is installed on Linux Redhat Server. We have 50 Clients PC using WinXP. Before continuing our Plans, I need to know how can I do with PostgreSQL in order to have Data Entry and Query Forms on clients side (How can I design Data Entry and Query Forms). If you can advice me the open source Software to add on PostgreSQL to solve my problem will be better. This will help the Management to accept PostgreSQL and plan Migration from MS-SQL 2000. Thanx Anthony Kinyage Try the all-new Yahoo! Mail . "The New Version is radically easier to use" The Wall Street Journal
Re: [SQL] table joining duplicate records
In your case Survey , Categories, Questions and Answers TABLES are parents tables, and Question_answers TABLE is a Child Table. Since you want to have survery, from Survey Table, Category from Category Table, Question from Question Table and Answer from Answer Table and alll these Atributes are on Question_answer Table, in your case you can use the following: SELECT * FROM question_answer; Try this STATEMENTonin <[EMAIL PROTECTED]> wrote: hi all,somebody please help me with my sql statement.or point me to the right documentation that i need to read.what i want to working on right now is to create a db for a survey appmy tables look thissurveyssurvey_id:survey1 : survey1categoriescategory_id:category1 :category1questionsquestion_id:question1 :question1answersanswer_id:answer1 :answer12 :answer2questions_answersquestion_answer_id:survey_id:category_id:question_id:answer_id1 :1 :1 :11 :1 :1 :2and my query looks like thisselect s.survey, c.category, q.question, a.answer from questions_answers qa, answers a, questions q, categories c, surveys swhere qa.answer_id = a.answer_id and qa.question_id = q.question_id and qa.category_id = c.category_id and qa.survey_id = s.survey_id;after executing the code,i get results like this.--query results--survey :category :question :answersurvey1:category1:question1:answer1survey1:category1:question1:answer2how can i elimate duplicates on my query results?an also am i using the right 'table joining' or table design for my survey app?any help would be appriciated.thanks in advance.-- "A whim, a thought, and more is sought... awake, my mind... thy will be wrought!"---(end of broadcast)---TIP 2: Don't 'kill -9' the postmaster Try the all-new Yahoo! Mail . "The New Version is radically easier to use" The Wall Street Journal
[SQL] ON INSERT view rule
Hello, pgsql-sql! What I've got here are a couple of ON INSERT rules for a view. The second rule is what I'm concerned about. I wrote it with PostgreSQL's ACID compliance in mind, but can I trust it? From what I gather, if I were to simply use NEW.address_line_id rather than address_lines_id_seq.last_value, it would be replaced by nextval(address_line_id_seq), so I'm trying to work around that. If there is there a better way to do this, I'm all ears. Would lastval() work for me in this case? Thanks! -- Anthony Chavez http://anthonychavez.org/ mailto:[EMAIL PROTECTED] jabber:[EMAIL PROTECTED] CREATE OR REPLACE VIEW addresses_address_lines AS SELECT a.id AS address_id, al.id AS address_line_id, line, ordering FROM addresses a INNER JOIN address_lines al ON al.address_id = a.id LEFT OUTER JOIN junctions ON parent_table = 'address_lines' AND parent_id = al.id AND child_table = 'display_orderings' LEFT OUTER JOIN display_orderings o ON o.id = child_id; CREATE OR REPLACE RULE insert_address_lines AS ON INSERT TO addresses_address_lines DO INSTEAD INSERT INTO address_lines (address_id, line) VALUES (NEW.address_id, NEW.line); CREATE OR REPLACE RULE insert_display_orderings AS ON INSERT TO addresses_address_lines DO UPDATE display_orderings SET ordering = NEW.ordering FROM address_lines_id_seq INNER JOIN junctions ON parent_table = 'address_lines' AND parent_id = last_value AND child_table = 'display_orderings' WHERE ordering <> NEW.ordering AND display_orderings.id = child_id; pgpSI6vATjFFf.pgp Description: PGP signature
Re: [SQL] ON INSERT view rule
Anthony Chavez <[EMAIL PROTECTED]> writes: > What I've got here are a couple of ON INSERT rules for a view. The > second rule is what I'm concerned about. I wrote it with PostgreSQL's > ACID compliance in mind, but can I trust it? Oops, forgot to mention two things: 1. The addresses_address_lines view assumes that a row already exists in the addresses relation because that relation has some NOT NULL attributes that lack defaults. Hence, there is no insert_addresses rule. I suppose I should create one, but choosing a default value for some of the foreign keys in that relation would be difficult. 2. I have an AFTER INSERT trigger function on the addresses relation that inserts a default display_orderings tuple (with ordering = 0) and sets up the association in the junctions table. Hence the use of UPDATE in the insert_display_orderings rule. Cheers! -- Anthony Chavez http://anthonychavez.org/ mailto:[EMAIL PROTECTED] jabber:[EMAIL PROTECTED] pgp83LIixWmPl.pgp Description: PGP signature
[SQL] How can I select all of the tables with field name 'area'?
I have a database with several tables. I'd like to pull out a list of names for the tables that contain the field (class) name 'area'. Can this be done? -Tony
Re: [SQL] How can I select all of the tables with field name 'area'?
Thanks Darrin and Stuart. -Tony Darrin Ladd wrote: > Here's what you are looking for: > > SELECT pg_class.relname > FROM pg_class, pg_attribute > WHERE pg_attribute.attname = 'area' > AND pg_attribute.attrelid = pg_class.oid; > > This should give you all of the classes (tables) which have the attribute > (field) 'area'. > >
[SQL] "avg" function for arrays?
I know that there's an "average" function (avg) for some datatypes.
Is there something comparable for float or int arrays?
e.g.
select avg(time_instants[1:5]) from ellipse_proc where rep = 1;
time_instants
-
{"148","167.8","187.6","207.4","227.2"}
(1 row)
select avg(time_instants[1:5]) from ellipse_proc where rep = 1;
ERROR: Unable to select an aggregate function avg(_float4)
Thanks.
-Tony
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] calling function
Hi, I have a little trouble. I'm newbie in postgresql. Consider this function example: create or replace function testcall(int4,varchar,bool,int2) return setof record as ' declare r record; a int4; b varchar; c bool; d int2; begin a=$1; b=$2; c=$3; d=$4; for r in select * from "T_Customer" loop return next r; end loop; return r; end;' language 'plpgsql' when i tried to call it using: select * from testcall(12,'ABCD',true,2); it says: ERROR: function testcall(integer, "unknown", boolean, integer) does not exist then I tried to change to: select * from testcall(12,varchar'ABCD',true,2); it says: ERROR: function testcall(integer, character varying, boolean, integer) does not exist I've tried them using jdbc prepared statement and callable statement (both with/without parameters), but the result is the same. what should i do? Thanks William Need a new email address that people can remember Check out the new EudoraMail at http://www.eudoramail.com ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] function returning resultset
Hi, I'm using PGSQL 7.4.1 and JDBC 75dev client. I want to ask about PL/PGSQL function returning resultset. I read in the docs, there are 'setof' and 'refcursor' method, is there another method to returning resultset? What advantages and disadvantages for each method? Which is the best? Plz explain to me. Thx. W.A. Need a new email address that people can remember Check out the new EudoraMail at http://www.eudoramail.com ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] working with schema
Christoph, First, is it safe for multi user? I mean maybe first user need working with D200402, second one need with D200403, if I do this in first user connection: SET search_path to D200402 ; does it affect to the second user search path? Second, I want it dinamic. So, if I want to using D200402, I just need to pass 'D200402' string in the argument of the function. Got my point? Thanks anyway, William >> >> Hi all, >> >> I'm just experimenting with schema usage. I'm going to use it as a fake >> 'multi-database' system. Is Postgresql support coding schema name using string >> variable so I can pass it with parameter? I'm give u an example: >> >> I have schema: D200401,D200402.D200403,D200404, etc. >> >> I've set my user just like the schema name, so who login with D200401 will be using >> D200401 schema. When someone using D200401 schema, they sometime want to access >> another schema, so in my thought I can use variable like this: >> >> sPointer='D200403' >> >> select * from sPointer.myTable -- Question: How to write it to work properly? >> >> Thanks >> >> >> William >> >> >SET search_path to D200401 ; >SET search_path to D200402 ; >... >should do the job. > >Regards, Christoph > > >---(end of broadcast)--- >TIP 4: Don't 'kill -9' the postmaster > Need a new email address that people can remember Check out the new EudoraMail at http://www.eudoramail.com ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] working with schema
There is something still annoying me Scott..
eg: I want to have function that take a string argument that indicates which schema i
want to use.
create function testf(varchar)
returns ...
..
..
..
..
My question is how to use the argument in the function, maybe looks like:
select * from $1.test
or
set search_path to $1
select * from test
or maybe I defined a string variable to hold it,
workschema='D200402'
select * from workschema.test
Do they work?
Thanks,
William
>On Wed, 12 May 2004, William Anthony Lim wrote:
>
>> Christoph,
>>
>> First, is it safe for multi user? I mean maybe first user need working with
>> D200402, second one need with D200403, if I do this in first user connection:
>>
>> SET search_path to D200402 ;
>>
>> does it affect to the second user search path?
>
>No, search paths are session vars.
>
>> Second, I want it dinamic. So, if I want to using D200402, I just need to pass
>> 'D200402' string in the argument of the function. Got my point?
>
>You should be able to do it with dot notation:
>
>postgres=# create schema a;
>CREATE SCHEMA
>postgres=# create schema b;
>CREATE SCHEMA
>postgres=# create table a.test (info text);
>CREATE TABLE
>postgres=# create table b.test (info text);
>CREATE TABLE
>postgres=# insert into a.test values ('abc');
>INSERT 1400496 1
>postgres=# insert into b.test values ('123');
>INSERT 1400497 1
>
>-- Now we try to look up the table without setting a search path and no
>-- dot notation:
>
>postgres=# select * from test;
>ERROR: relation "test" does not exist
>ERROR: relation "test" does not exist
>
>-- Now we set the search path, notice the order:
>
>postgres=# set search_path=public,a,b;
>SET
>postgres=# select * from test;
> info
>--
> abc
>(1 row)
>
>-- Reverse the order of a and b
>
>postgres=# set search_path=public,b,a;
>SET
>postgres=# select * from test;
> info
>--
> 123
>(1 row)
>
>-- now without a
>
>postgres=# set search_path=public,b;
>SET
>postgres=# select * from test;
> info
>--
> 123
>(1 row)
>
>postgres=# set search_path=public,a;
>SET
>postgres=# select * from test;
> info
>--
> abc
>(1 row)
>
>-- Now we use dot notation. first a, then b. Notice that
>-- b, which isn't in our search path, works fine.
>
>postgres=# select * from a.test;
> info
>--
> abc
>(1 row)
>
>postgres=# select * from b.test;
> info
>--
> 123
>(1 row)
>
>
>>
>> Thanks anyway,
>>
>> William
>>
>> >>
>> >> Hi all,
>> >>
>> >> I'm just experimenting with schema usage. I'm going to use it as a fake
>> >> 'multi-database' system. Is Postgresql support coding schema name using string
>> >> variable so I can pass it with parameter? I'm give u an example:
>> >>
>> >> I have schema: D200401,D200402.D200403,D200404, etc.
>> >>
>> >> I've set my user just like the schema name, so who login with D200401 will be
>> >> using D200401 schema. When someone using D200401 schema, they sometime want to
>> >> access another schema, so in my thought I can use variable like this:
>> >>
>> >> sPointer='D200403'
>> >>
>> >> select * from sPointer.myTable -- Question: How to write it to work properly?
>> >>
>> >> Thanks
>> >>
>> >>
>> >> William
>> >>
>> >>
>> >SET search_path to D200401 ;
>> >SET search_path to D200402 ;
>> >...
>> >should do the job.
>> >
>> >Regards, Christoph
>> >
>> >
>> >---(end of broadcast)---
>> >TIP 4: Don't 'kill -9' the postmaster
>> >
>>
>>
>>
>> Need a new email address that people can remember
>> Check out the new EudoraMail at
>> http://www.eudoramail.com
>>
>> ---(end of broadcast)---
>> TIP 6: Have you searched our list archives?
>>
>>http://archives.postgresql.org
>>
>
>
>---(end of broadcast)---
>TIP 7: don't forget to increase your free space map settings
>
Need a new email address that people can remember
Check out the new EudoraMail at
http://www.eudoramail.com
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] working with schema
Hi all, I'm just experimenting with schema usage. I'm going to use it as a fake 'multi-database' system. Is Postgresql support coding schema name using string variable so I can pass it with parameter? I'm give u an example: I have schema: D200401,D200402.D200403,D200404, etc. I've set my user just like the schema name, so who login with D200401 will be using D200401 schema. When someone using D200401 schema, they sometime want to access another schema, so in my thought I can use variable like this: sPointer='D200403' select * from sPointer.myTable -- Question: How to write it to work properly? Thanks William Need a new email address that people can remember Check out the new EudoraMail at http://www.eudoramail.com ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] (No Subject)
is it possible to dump within procedural language/SQL syntax? Using pg_dump from console is very confusing for some end user who don't have Linux skills. so I decide to create a function to do that, and they may call it from my application. Thanks William Need a new email address that people can remember Check out the new EudoraMail at http://www.eudoramail.com ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
