Re: [SQL] DISTINCT ON not working...?
Dnia Tue, 20 Feb 2007 15:36:32 +1100, Phillip Smith napisał(a): > Removing the CASE statement all together: > SELECT DISTINCT ON (ean) > ean, > count(*) > FROMTMPTABLE > WHERE code NOT IN (SELECT code FROM stock_deleted) > ANDean IS NOT NULL > GROUP BY ean > > Still gives me: > 3246576919422 2 Wild guess - have you tried reindexing this table? I haven't seen corrupted indexes since 7.1, though - it usually means subtle hardware problems. -- | And Do What You Will be the challenge | http://apcoln.linuxpl.org |So be it in love that harms none | http://biznes.linux.pl | For this is the only commandment. | http://www.juanperon.info `---* JID: [EMAIL PROTECTED] *---' http://www.naszedzieci.org ---(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] can someone explain confusing array indexing nomenclature
Yes, it is confusing. I have an update to the array documentation that
should clarify it --- attached.
---
Achilleas Mantzios wrote:
> ? 16 ??? 2007 20:35, ?/? chrisj ??:
> > I am quite sure the [2] is not discarded, easy enough to test but I don't
> > have access to PG at the moment.
>
> Well it should, since
>
> dynacom=# SELECT
> (CAST( '{{meeting,lunch},{training,presentation}}' as text[][]))[1:1];
>text
> ---
> {{meeting,lunch}}
> (1 row)
>
> dynacom=# SELECT
> (CAST( '{{meeting,lunch},{training,presentation}}' as text[][]))[1:1][1];
> text
> -
> {{meeting}}
> (1 row)
>
> dynacom=# SELECT
> (CAST( '{{meeting,lunch},{training,presentation}}' as text[][]))[1:1][2];
>text
> ---
> {{meeting,lunch}}
> (1 row)
>
> dynacom=# SELECT
> (CAST( '{{meeting,lunch},{training,presentation}}' as text[][]))[1:1][3];
>text
> ---
> {{meeting,lunch}}
> (1 row)
>
> dynacom=# SELECT
> (CAST( '{{meeting,lunch},{training,presentation}}' as text[][]))[1:1][1000];
>text
> ---
> {{meeting,lunch}}
> (1 row)
>
> dynacom=#
>
> >
> > Achilleas Mantzios wrote:
> > > ?? 15 ??? 2007 18:55, ?/? chrisj ??:
> > >> Thanks Achilleas,
> > >>
> > >> I see what you are saying, but if we consider just the index "[2]" for a
> > >> moment,
> > >> it means something different depending upon the context (in one case it
> > >> means "2" and in the other case it means "1:2") and the context is
> > >> determined by the format of indexes on other dimensions.
> > >>
> > >> I believe I understandbut incredibly confusing.
> > >
> > > Now that i think about it again, i speculate that the [2] is discarded.
> > >
> > >> - chris
> > >>
> > >> Achilleas Mantzios wrote:
> > >> > ?? 14 ?? 2007 21:31, ??/??
> > >>
> > >> chrisj
> > >
> > > :
> > >> >> given the following table:
> > >> >>
> > >> >> protocal2=> select * from sal_emp ;
> > >> >> name | pay_by_quarter | schedule
> > >>
> > >> ---+---+
> > >>
> > >> >>--- Bill | {1,1,1,1} |
> > >> >> {{meeting,lunch},{training,presentation}}
> > >> >> Carol | {2,25000,25000,25000} |
> > >> >> {{breakfast,consulting},{meeting,lunch}}
> > >> >> (2 rows)
> > >> >>
> > >> >> why do the following two queries yield different results??
> > >> >>
> > >> >> protocal2=> SELECT schedule[1][2] FROM sal_emp WHERE name = 'Bill';
> > >> >> schedule
> > >> >> --
> > >> >> lunch
> > >> >> (1 row)
> > >> >>
> > >> >> protocal2=> SELECT schedule[1:1][2] FROM sal_emp WHERE name = 'Bill';
> > >> >> schedule
> > >> >> ---
> > >> >> {{meeting,lunch}}
> > >> >> (1 row)
> > >> >
> > >> > The [n:m] notation denotes a slice of the array (not element).
> > >> > So schedule[1][2] is the Array element on 2nd col of 1st row,
> > >> > while schedule[1:1][2] could mean
> > >> > the second row of the subarray schedule[1:1][1:2].
> > >> > So these two are foundamentally different things.
> > >> > In my 7.4 even if you gave
> > >> > SELECT schedule[1:1][888] FROM sal_emp WHERE name = 'Bill';
> > >> > you would still get {{meeting,lunch}} as a result.
> > >> > (Right or wrong is another story).
> > >> > Anyway the first time you query for a "text",
> > >> > the second time you query for a "text[]", so you should expect
> > >> > different results.
> > >> > --
> > >> > Achilleas Mantzios
> > >> >
> > >> > ---(end of
> > >>
> > >> broadcast)---
> > >>
> > >> > TIP 3: Have you checked our extensive FAQ?
> > >> >
> > >> >http://www.postgresql.org/docs/faq
> > >
> > > --
> > > Achilleas Mantzios
> > >
> > > ---(end of broadcast)---
> > > TIP 3: Have you checked our extensive FAQ?
> > >
> > >http://www.postgresql.org/docs/faq
>
> --
> Achilleas Mantzios
>
> ---(end of broadcast)---
> TIP 7: You can help support the PostgreSQL project by donating at
>
> http://www.postgresql.org/about/donate
--
Bruce Momjian <[EMAIL PROTECTED]> http://momjian.us
EnterpriseDB http://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
Index: doc/src/sgml/array.sgml
===
RCS file: /cvsroot/pgsql/doc/src/sgml/array.sgml,v
retrieving revision 1.56
diff -c -c -r1.56 array.sgml
*** doc/src/sgml/array.sgml 1 Feb 2007 00:28:16 - 1.56
--- doc/src/sgml/array.sgml 20 Feb 2007 03:43:28 -
***
*** 243,260
(1 row)
! We could also have written:
Re: [SQL] DISTINCT ON not working...?
This is a temporary table (with no indexes) that gets created in the same transaction block as the SELECT gets run, but I tried creating an index on the ean column anyway with no luck: CREATE INDEX ean_idx ON TMPTABLE USING btree (ean); SELECT DISTINCT ON (ean) ean, count(*) FROMTMPTABLE WHERE code NOT IN (SELECT code FROM stock_deleted) ANDean IS NOT NULL GROUP BY ean; Still returns: 3246576919422 2 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Marcin Stêpnicki Sent: Tuesday, 20 February 2007 23:34 To: pgsql-sql@postgresql.org Subject: Re: [SQL] DISTINCT ON not working...? Wild guess - have you tried reindexing this table? I haven't seen corrupted indexes since 7.1, though - it usually means subtle hardware problems. ***Confidentiality and Privilege Notice*** The material contained in this message is privileged and confidential to the addressee. If you are not the addressee indicated in this message or responsible for delivery of the message to such person, you may not copy or deliver this message to anyone, and you should destroy it and kindly notify the sender by reply email. Information in this message that does not relate to the official business of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta. Weatherbeeta, its employees, contractors or associates shall not be liable for direct, indirect or consequential loss arising from transmission of this message or any attachments ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] timestamp subtraction (was Re: [SQL] formatting intervals with to_char)
One problem with removing justify_hours() is that this is going to
return '24:00:00', rather than '1 day:
test=> select '2004-01-02 00:00:00'::timestamptz - '2004-01-01
00:00:00'::timestamptz;
?column?
--
24:00:00
(1 row)
---
Jim Nasby wrote:
> On Oct 5, 2006, at 11:50 AM, Tom Lane wrote:
> > regression=# select ('2006-09-15 23:59:00'::timestamp - '2006-09-01
> > 09:30:41'::timestamp);
> > ?column?
> > --
> > 14 days 14:28:19
> > (1 row)
> >
> > should be reporting '350:28:19' instead.
> >
> > This is a hack that was done to minimize the changes in the regression
> > test expected outputs when we changed type interval from months/
> > seconds
> > to months/days/seconds. But I wonder whether it wasn't a dumb idea.
> > It is certainly inconsistent, as noted in the code comments.
> >
> > I'm tempted to propose that we remove the justify_hours call, and tell
> > anyone who really wants the old results to apply justify_hours() to
> > the
> > subtraction result for themselves. Not sure what the fallout would
> > be,
> > though.
>
> I suspect there's applications out there that are relying on that
> being nicely formated for display purposes.
>
> I agree it should be removed, but we might need a form of backwards
> compatibility for a version or two...
> --
> Jim Nasby[EMAIL PROTECTED]
> EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
>
>
>
> ---(end of broadcast)---
> TIP 3: Have you checked our extensive FAQ?
>
>http://www.postgresql.org/docs/faq
--
Bruce Momjian <[EMAIL PROTECTED]> http://momjian.us
EnterpriseDB http://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
