From e.vernichon@borchers.fr Mon May 3 08:03:00 1999 Received: from mail.confluent.fr (mail.confluent.fr [194.79.149.32]) by hub.org (8.9.3/8.9.3) with SMTP id EAA50505 for ; Mon, 3 May 1999 04:03:00 -0400 (EDT) (envelope-from e.vernichon@borchers.fr) Received: from borchers.fr (unverified [194.79.149.183]) by mail.confluent.fr (EMWAC SMTPRS 0.83) with SMTP id ; Mon, 03 May 1999 09:58:09 +0200 Sender: admin Message-ID: <372D5675.9F41DD8F@borchers.fr> Date: Mon, 03 May 1999 09:55:33 +0200 From: vernichon eric X-Mailer: Mozilla 4.08 [en] (X11; I; Linux 2.2.0 i586) MIME-Version: 1.0 To: "'pgsql-sql@hub.org'" Subject: CASE Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Hello , I want transfered query from sql serveur to postgres but i don't know how use CASE with postgres Thank you Eric Vernichon From jose@sferacarta.com Mon May 3 12:45:40 1999 Received: from bologna.nettuno.it (bologna.nettuno.it [193.43.2.1]) by hub.org (8.9.3/8.9.3) with ESMTP id IAA79641 for ; Mon, 3 May 1999 08:45:40 -0400 (EDT) (envelope-from jose@sferacarta.com) Received: from proxy.sferacarta.com (mail@sfcabop1.nettuno.it [193.207.10.213]) by bologna.nettuno.it (8.8.6/8.8.6/NETTuno 3.1) with ESMTP id OAA11809; Mon, 3 May 1999 14:45:25 +0200 (MDT) Received: from rosso.sferacarta.com (sferacarta.com) [10.20.30.5] by proxy.sferacarta.com with esmtp (Exim 2.05 #1 (Debian)) id 10eJwQ-00044t-00; Mon, 3 May 1999 14:43:30 +0000 Message-ID: <372D99EF.583A769@sferacarta.com> Date: Mon, 03 May 1999 14:43:28 +0200 From: =?iso-8859-1?Q?Jos=E9?= Soares X-Mailer: Mozilla 4.5 [it] (Win95; I) X-Accept-Language: it MIME-Version: 1.0 To: vernichon eric CC: "'pgsql-sql@hub.org'" Subject: Re: [SQL] CASE References: <372D5675.9F41DD8F@borchers.fr> Content-Type: multipart/alternative; boundary="------------11E4D550252B63CCC89BD3C3" --------------11E4D550252B63CCC89BD3C3 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit You need v6.5beta, previous releases haven't this feature: SELECT *, CASE WHEN COLUMN1 = 1 THEN 'A' WHEN COLUMN1 = 2 THEN 'B' ELSE 'C' END FROM TABLE2; column1|case -------+---- 1|A 2|B 3|C 4|C (4 rows) José vernichon eric ha scritto: > Hello , > > I want transfered query from sql serveur to postgres but i > don't know how use CASE with postgres > > Thank you > > Eric Vernichon -- ______________________________________________________________ PostgreSQL 6.5.0 on i586-pc-linux-gnu, compiled by gcc 2.7.2.3 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ Jose' --------------11E4D550252B63CCC89BD3C3 Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit You need v6.5beta, previous releases haven't this feature:

SELECT *, CASE
     WHEN COLUMN1 = 1
        THEN 'A'
     WHEN COLUMN1 = 2
        THEN 'B'
     ELSE 'C'
END FROM TABLE2;
column1|case
-------+----
      1|A
      2|B
      3|C
      4|C
(4 rows)

José

vernichon eric ha scritto:

            Hello ,

        I want  transfered query from sql serveur to  postgres but  i
don't know how use CASE with postgres

        Thank you

Eric Vernichon

--
______________________________________________________________
PostgreSQL 6.5.0 on i586-pc-linux-gnu, compiled by gcc 2.7.2.3
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
Jose'
  --------------11E4D550252B63CCC89BD3C3-- From Cwhisperer@vo.lu Mon May 14 19:27:11 2001 Received: from postgresql.org.org (webmail.postgresql.org [216.126.85.28]) by postgresql.org (8.11.3/8.11.1) with SMTP id f4EJRBA46067 for ; Mon, 14 May 2001 15:27:11 -0400 (EDT) (envelope-from pgsql-sql-owner@postgresql.org) Received: from orion.vo.lu (orion.vo.lu [212.24.194.7]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f4EFLDA35441 for ; Mon, 14 May 2001 11:21:14 -0400 (EDT) (envelope-from A.BURCHERI@vo.lu) Received: from cwhisperer ([212.24.197.14]) by orion.vo.lu (Post.Office MTA v3.5.3 release 223 ID# 0-61327U6400L500S0V35) with ESMTP id lu for ; Mon, 14 May 2001 17:21:07 +0200 Date: Mon, 14 May 2001 17:21:07 +0200 From: Cwhisperer X-Mailer: The Bat! (v1.49) Business Reply-To: Cwhisperer Organization: VisualOnline X-Priority: 3 (Normal) Message-ID: <1649286162.20010514172107@vo.lu> To: pgsql-sql@postgresql.org Subject: Case Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Archive-Number: 200105/128 X-Sequence-Number: 2944 Hello, what's wrong when you receive this message from an select statement : CASE types "date" and "text" not matched thank you for any help -- Best regards, Cwhisperer mailto:Cwhisperer@vo.lu From peter_e@gmx.net Mon May 14 20:39:12 2001 Received: from mailout04.sul.t-online.com (mailout04.sul.t-online.com [194.25.134.18]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f4EKdCA76010 for ; Mon, 14 May 2001 16:39:12 -0400 (EDT) (envelope-from peter_e@gmx.net) Received: from fwd06.sul.t-online.de by mailout04.sul.t-online.com with smtp id 14zP7g-0002Zb-01; Mon, 14 May 2001 22:39:20 +0200 Received: from peter.localdomain (520083510237-0001@[212.185.245.122]) by fmrl06.sul.t-online.com with esmtp id 14zP7c-0HMpWKC; Mon, 14 May 2001 22:39:16 +0200 Date: Mon, 14 May 2001 22:39:21 +0200 (CEST) From: Peter Eisentraut To: Cwhisperer cc: Subject: Re: Case In-Reply-To: <1649286162.20010514172107@vo.lu> Message-ID: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Sender: 520083510237-0001@t-dialin.net X-Archive-Number: 200105/134 X-Sequence-Number: 2951 Cwhisperer writes: > what's wrong when you receive this message from an select statement > : CASE types "date" and "text" not matched You probably did something like CASE WHEN something THEN date_value ELSE text_value The data types in the result branches of a case expression need to match. -- Peter Eisentraut peter_e@gmx.net http://funkturm.homeip.net/~peter From rudi@oasis.net.au Tue Apr 8 08:25:34 2003 X-Original-To: pgsql-sql@postgresql.org Received: from spampd.localdomain (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 59B024758E6 for ; Tue, 8 Apr 2003 04:25:34 -0400 (EDT) Received: from water.oasis.net.au (water.oasis.net.au [210.8.139.2]) by postgresql.org (Postfix) with ESMTP id 26A19474E42 for ; Tue, 8 Apr 2003 04:25:28 -0400 (EDT) Received: from oasis.net.au (guardian [210.8.139.5]) by water.oasis.net.au (8.9.3/8.9.3/Debian 8.9.3-21) with ESMTP id SAA13805 for ; Tue, 8 Apr 2003 18:28:32 +1000 X-Authentication-Warning: water.oasis.net.au: Host guardian [210.8.139.5] claimed to be oasis.net.au Message-ID: <3E928853.7090801@oasis.net.au> Date: Tue, 08 Apr 2003 18:29:07 +1000 From: Rudi Starcevic User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.1) Gecko/20020826 X-Accept-Language: en-us, en MIME-Version: 1.0 To: pgsql-sql@postgresql.org Subject: CASE Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Spam-Status: No, hits=-14.3 required=5.0 tests=BAYES_01,USER_AGENT_MOZILLA_UA,X_AUTH_WARNING autolearn=ham version=2.50 X-Spam-Level: X-Spam-Checker-Version: SpamAssassin 2.50 (1.173-2003-02-20-exp) X-Archive-Number: 200304/104 X-Sequence-Number: 12805 Hi, I'm trying to find the maximum date then compare that date to see if I have any current events to show. Here is an SQL snippet ( the e.edate field is of type 'date' ) CASE WHEN COALESCE( MAX(e.edate),'2003-01-01') >= now()::date THEN 'events' ELSE 'noevents' END as myevents, It works fine for those events who do have dates greater or equal to now and also for those events who have a NULL value. However there is one event which is in the past '2003-04-01' which is still coming up as 'events' instead of 'noevents'. I think there may be something wrong with my CASE SQL clause. I can't see any problem right now can anyone here see one ? Thank you kindly Regards Rudi. From viy@pirmabanka.lv Tue Apr 8 09:40:22 2003 X-Original-To: pgsql-sql@postgresql.org Received: from spampd.localdomain (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id CD1EB4758E6 for ; Tue, 8 Apr 2003 05:40:22 -0400 (EDT) Received: from shield.pirmabanka.lv (unknown [195.114.57.4]) by postgresql.org (Postfix) with ESMTP id AD0EE474E42 for ; Tue, 8 Apr 2003 05:40:20 -0400 (EDT) Received: from wall2.pirmabanka.lv (wall2-pub.pirmabanka.lv [195.114.57.14]) by shield.pirmabanka.lv (8.12.9/8.12.9) with SMTP id h389eLx0022357 for ; Tue, 8 Apr 2003 12:40:21 +0300 Received: from share.pirmabanka.lv ([10.0.0.10]) by wall2.pirmabanka.lv; Tue, 08 Apr 2003 12:40:20 +0300 (EET DST) Received: from pc311x1.pirmabanka.lv (localhost.localdomain [127.0.0.1]) by pirmabanka.lv (8.11.6/8.11.6) with SMTP id h389eKa08751 for ; Tue, 8 Apr 2003 12:40:20 +0300 Received: (qmail 12621 invoked by uid 100); 8 Apr 2003 09:40:39 -0000 Date: Tue, 8 Apr 2003 12:40:39 +0300 From: "Victor Yegorov" To: Rudi Starcevic Cc: pgsql-sql@postgresql.org Subject: Re: CASE Message-ID: <20030408094039.GS1712@pirmabanka.lv> Mail-Followup-To: Rudi Starcevic , pgsql-sql@postgresql.org References: <3E928853.7090801@oasis.net.au> Mime-Version: 1.0 Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="PEkEgRdBLZYkpbX2" Content-Disposition: inline In-Reply-To: <3E928853.7090801@oasis.net.au> User-Agent: Mutt/1.4.1i X-Spam-Status: No, hits=-37.8 required=5.0 tests=BAYES_10,IN_REP_TO,PGP_SIGNATURE_2,QUOTED_EMAIL_TEXT, REFERENCES,REPLY_WITH_QUOTES,USER_AGENT_MUTT autolearn=ham version=2.50 X-Spam-Level: X-Spam-Checker-Version: SpamAssassin 2.50 (1.173-2003-02-20-exp) X-Archive-Number: 200304/105 X-Sequence-Number: 12806 --PEkEgRdBLZYkpbX2 Content-Type: text/plain; charset=koi8-r Content-Disposition: inline Content-Transfer-Encoding: quoted-printable * Rudi Starcevic [08.04.2003 11:37]: > CASE=20=20=20=20 > WHEN COALESCE( MAX(e.edate),'2003-01-01') >=3D now()::date THEN 'event= s' > ELSE 'noevents'=20=20=20=20=20=20=20=20=20=20=20=20 > END as myevents, I'm not sure completely, but first thing i saw in your query, is: ...COALESCE( MAX(e.edate),'2003-01-01')... MAX(e.edate): will return date type (just a guess) '2003-01-01': will return char type (or varchar, doesn't matter). Try to cast string into date. --=20 Victor Yegorov --PEkEgRdBLZYkpbX2 Content-Type: application/pgp-signature Content-Disposition: inline -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.1 (GNU/Linux) iD8DBQE+kpkX2RKGLEykUDYRAvNAAJ0TaMczF223WXQBKjKbzLNyFCquEACghJD1 9+aqg1nJRKhbSVP6jbs9gMg= =h0oL -----END PGP SIGNATURE----- --PEkEgRdBLZYkpbX2-- From rudi@oasis.net.au Tue Apr 8 10:54:49 2003 X-Original-To: pgsql-sql@postgresql.org Received: from spampd.localdomain (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 2F8A94758E6 for ; Tue, 8 Apr 2003 06:54:49 -0400 (EDT) Received: from water.oasis.net.au (water.oasis.net.au [210.8.139.2]) by postgresql.org (Postfix) with ESMTP id D3146474E42 for ; Tue, 8 Apr 2003 06:54:47 -0400 (EDT) Received: from oasis.net.au (localhost [127.0.0.1]) by water.oasis.net.au (8.9.3/8.9.3/Debian 8.9.3-21) with ESMTP id UAA01940 for ; Tue, 8 Apr 2003 20:57:59 +1000 X-Authentication-Warning: water.oasis.net.au: Host localhost [127.0.0.1] claimed to be oasis.net.au From: "Rudi Starcevic" To: pgsql-sql@postgresql.org Subject: Re: CASE Date: Tue, 8 Apr 2003 20:57:59 +1000 Message-Id: <20030408105759.M33127@oasis.net.au> In-Reply-To: <20030408094039.GS1712@pirmabanka.lv> References: <3E928853.7090801@oasis.net.au> <20030408094039.GS1712@pirmabanka.lv> X-Mailer: Open WebMail X-OriginatingIP: 203.220.114.18 MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 X-Spam-Status: No, hits=-17.1 required=5.0 tests=BAYES_01,IN_REP_TO,REFERENCES,UPPERCASE_25_50, X_AUTH_WARNING autolearn=ham version=2.50 X-Spam-Level: X-Spam-Checker-Version: SpamAssassin 2.50 (1.173-2003-02-20-exp) X-Archive-Number: 200304/108 X-Sequence-Number: 12809 Victor, Thanks for your time .. When casting inside a COALESCE function should it be something like: WHEN COALESCE( MAX(e.edate),'2003-01-01'::date ) >= now()::date THEN 'events' instead of: WHEN COALESCE( MAX(e.edate),'2003-01-01') >= now()::date THEN 'events' Regards Rudi From viy@pirmabanka.lv Tue Apr 8 11:09:40 2003 X-Original-To: pgsql-sql@postgresql.org Received: from spampd.localdomain (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 233974760E3 for ; Tue, 8 Apr 2003 07:09:40 -0400 (EDT) Received: from shield.pirmabanka.lv (unknown [195.114.57.4]) by postgresql.org (Postfix) with ESMTP id 8D38C4760CC for ; Tue, 8 Apr 2003 07:09:38 -0400 (EDT) Received: from wall2.pirmabanka.lv (wall2-pub.pirmabanka.lv [195.114.57.14]) by shield.pirmabanka.lv (8.12.9/8.12.9) with SMTP id h38B9ex0023327 for ; Tue, 8 Apr 2003 14:09:40 +0300 Received: from share.pirmabanka.lv ([10.0.0.10]) by wall2.pirmabanka.lv; Tue, 08 Apr 2003 14:09:39 +0300 (EET DST) Received: from pc311x1.pirmabanka.lv (localhost.localdomain [127.0.0.1]) by pirmabanka.lv (8.11.6/8.11.6) with SMTP id h38B9di16734 for ; Tue, 8 Apr 2003 14:09:39 +0300 Received: (qmail 13054 invoked by uid 100); 8 Apr 2003 11:09:58 -0000 Date: Tue, 8 Apr 2003 14:09:58 +0300 From: "Victor Yegorov" To: "Rudi Starcevic" Cc: pgsql-sql@postgresql.org Subject: Re: CASE Message-ID: <20030408110958.GX1712@pirmabanka.lv> Mail-Followup-To: "Rudi Starcevic" , pgsql-sql@postgresql.org References: <3E928853.7090801@oasis.net.au> <20030408094039.GS1712@pirmabanka.lv> <20030408105759.M33127@oasis.net.au> Mime-Version: 1.0 Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="zywvytGCXzdVpkje" Content-Disposition: inline In-Reply-To: <20030408105759.M33127@oasis.net.au> User-Agent: Mutt/1.4.1i X-Spam-Status: No, hits=-37.8 required=5.0 tests=BAYES_10,IN_REP_TO,PGP_SIGNATURE_2,QUOTED_EMAIL_TEXT, REFERENCES,REPLY_WITH_QUOTES,USER_AGENT_MUTT autolearn=ham version=2.50 X-Spam-Level: X-Spam-Checker-Version: SpamAssassin 2.50 (1.173-2003-02-20-exp) X-Archive-Number: 200304/109 X-Sequence-Number: 12810 --zywvytGCXzdVpkje Content-Type: text/plain; charset=koi8-r Content-Disposition: inline Content-Transfer-Encoding: quoted-printable * Rudi Starcevic [08.04.2003 14:06]: > When casting inside a COALESCE function should it be something like: >=20 > WHEN COALESCE( MAX(e.edate),'2003-01-01'::date ) >=3D now()::date THEN 'e= vents' Yes. Please, try it and report if it works. --=20 Victor Yegorov --zywvytGCXzdVpkje Content-Type: application/pgp-signature Content-Disposition: inline -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.1 (GNU/Linux) iD8DBQE+kq4G2RKGLEykUDYRAq/JAJ9kF0bpwVI9C+YBlrJIPUE+A33pTgCePXR0 M+zHUtDFcPiAuvKR2JRtetU= =0TtU -----END PGP SIGNATURE----- --zywvytGCXzdVpkje-- From rudi@oasis.net.au Tue Apr 8 11:17:33 2003 X-Original-To: pgsql-sql@postgresql.org Received: from spampd.localdomain (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 3F5DB4758E6 for ; Tue, 8 Apr 2003 07:17:33 -0400 (EDT) Received: from water.oasis.net.au (water.oasis.net.au [210.8.139.2]) by postgresql.org (Postfix) with ESMTP id D6A88474E42 for ; Tue, 8 Apr 2003 07:17:31 -0400 (EDT) Received: from oasis.net.au (localhost [127.0.0.1]) by water.oasis.net.au (8.9.3/8.9.3/Debian 8.9.3-21) with ESMTP id VAA02482 for ; Tue, 8 Apr 2003 21:20:44 +1000 X-Authentication-Warning: water.oasis.net.au: Host localhost [127.0.0.1] claimed to be oasis.net.au From: "Rudi Starcevic" To: pgsql-sql@postgresql.org Subject: Re: CASE Date: Tue, 8 Apr 2003 21:20:44 +1000 Message-Id: <20030408112044.M7251@oasis.net.au> In-Reply-To: <20030408110958.GX1712@pirmabanka.lv> References: <3E928853.7090801@oasis.net.au> <20030408094039.GS1712@pirmabanka.lv> <20030408105759.M33127@oasis.net.au> <20030408110958.GX1712@pirmabanka.lv> X-Mailer: Open WebMail X-OriginatingIP: 203.220.114.18 MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 X-Spam-Status: No, hits=-27.4 required=5.0 tests=BAYES_01,IN_REP_TO,QUOTED_EMAIL_TEXT,REFERENCES, REPLY_WITH_QUOTES,X_AUTH_WARNING autolearn=ham version=2.50 X-Spam-Level: X-Spam-Checker-Version: SpamAssassin 2.50 (1.173-2003-02-20-exp) X-Archive-Number: 200304/110 X-Sequence-Number: 12811 Victor, Thanks again, I'll try first thing in the morning as I'm at home now. Now I can sleep well knowing a solution is at hand :-) I'm very pleased as that was a small part of what I think is an advanced query and look forward to seeing it work. I'll send a reply first thing tomorrow when I get to work. Cheers Rudi. > * Rudi Starcevic [08.04.2003 14:06]: > > When casting inside a COALESCE function should it be something like: > > > > WHEN COALESCE( MAX(e.edate),'2003-01-01'::date ) >= now()::date THEN 'events' > > Yes. > Please, try it and report if it works. > > -- > > Victor Yegorov From gsstark@mit.edu Tue Apr 8 12:06:26 2003 X-Original-To: pgsql-sql@postgresql.org Received: from spampd.localdomain (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 2EF5D476227 for ; Tue, 8 Apr 2003 08:06:26 -0400 (EDT) Received: from sabre.velocet.net (sabre.velocet.net [216.138.209.205]) by postgresql.org (Postfix) with ESMTP id 3C21E476128 for ; Tue, 8 Apr 2003 08:06:25 -0400 (EDT) Received: from stark.dyndns.tv (H162.C233.tor.velocet.net [216.138.233.162]) by sabre.velocet.net (Postfix) with ESMTP id A9816138056; Tue, 8 Apr 2003 08:06:22 -0400 (EDT) Received: from localhost ([127.0.0.1] helo=stark.dyndns.tv ident=foobar) by stark.dyndns.tv with smtp (Exim 3.36 #1 (Debian)) id 192rrk-0002Iv-00; Tue, 08 Apr 2003 08:06:16 -0400 To: "Victor Yegorov" Cc: Rudi Starcevic , pgsql-sql@postgresql.org Subject: Re: CASE References: <3E928853.7090801@oasis.net.au> <20030408094039.GS1712@pirmabanka.lv> In-Reply-To: <20030408094039.GS1712@pirmabanka.lv> From: Greg Stark Organization: The Emacs Conspiracy; member since 1992 Date: 08 Apr 2003 08:06:15 -0400 Message-ID: <877ka519e0.fsf@stark.dyndns.tv> Lines: 44 User-Agent: Gnus/5.09 (Gnus v5.9.0) Emacs/21.2 MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Spam-Status: No, hits=-38.8 required=5.0 tests=BAYES_01,EMAIL_ATTRIBUTION,IN_REP_TO,QUOTED_EMAIL_TEXT, REFERENCES,REPLY_WITH_QUOTES,USER_AGENT_GNUS_UA autolearn=ham version=2.50 X-Spam-Level: X-Spam-Checker-Version: SpamAssassin 2.50 (1.173-2003-02-20-exp) X-Archive-Number: 200304/111 X-Sequence-Number: 12812 "Victor Yegorov" writes: > ...COALESCE( MAX(e.edate),'2003-01-01')... > > MAX(e.edate): will return date type (just a guess) > '2003-01-01': will return char type (or varchar, doesn't matter). No, 'xyz' in postgres isn't necessarily a string constant, it's an untyped constant that the parser decides the type of based on context. In this case it gets decided based on the other return values of the coalesce/case. Notice the types chosen in this query: db=# explain select * from t where t_start = coalesce(now(),'2003-01-01') ; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Seq Scan on t (cost=0.00..48223.40 rows=226 width=423) Filter: ((t_start)::timestamp with time zone = CASE WHEN (now() IS NOT NULL) THEN now() WHEN ('2003-01-01' IS NOT NULL) THEN '2003-01-01 00:00:00-05'::timestamp with time zone ELSE NULL::timestamp with time zone END) (2 rows) However, I suspect for some reason postgres is doing a string comparison in your case. I don't understand why. Note that now() isn't a date, it's a "timestamp with time zone", but I don't think that should be a problem. Try checking what types it uses in the plan for: EXPLAIN SELECT * FROM e WHERE (CASE WHEN coalesce(max(e.edate),'2003-01-01') >= now()::date THEN 'events' ELSE 'noevents' END ) = 'events'; Also, the standard date format should still sort correctly as alphanumeric, so I'm curious what you see when you do: SELECT now(); -- greg From viy@pirmabanka.lv Tue Apr 8 12:13:30 2003 X-Original-To: pgsql-sql@postgresql.org Received: from spampd.localdomain (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 6409C4760E3 for ; Tue, 8 Apr 2003 08:13:30 -0400 (EDT) Received: from shield.pirmabanka.lv (unknown [195.114.57.4]) by postgresql.org (Postfix) with ESMTP id D7EF24760CC for ; Tue, 8 Apr 2003 08:13:28 -0400 (EDT) Received: from wall2.pirmabanka.lv (wall2-pub.pirmabanka.lv [195.114.57.14]) by shield.pirmabanka.lv (8.12.9/8.12.9) with SMTP id h38CDUx0024095 for ; Tue, 8 Apr 2003 15:13:30 +0300 Received: from share.pirmabanka.lv ([10.0.0.10]) by wall2.pirmabanka.lv; Tue, 08 Apr 2003 15:13:28 +0300 (EET DST) Received: from pc311x1.pirmabanka.lv (localhost.localdomain [127.0.0.1]) by pirmabanka.lv (8.11.6/8.11.6) with SMTP id h38CDRN23215 for ; Tue, 8 Apr 2003 15:13:27 +0300 Received: (qmail 13281 invoked by uid 100); 8 Apr 2003 12:13:46 -0000 Date: Tue, 8 Apr 2003 15:13:46 +0300 From: "Victor Yegorov" To: Greg Stark Cc: Rudi Starcevic , pgsql-sql@postgresql.org Subject: Re: CASE Message-ID: <20030408121346.GA13190@pirmabanka.lv> Mail-Followup-To: Greg Stark , Rudi Starcevic , pgsql-sql@postgresql.org References: <3E928853.7090801@oasis.net.au> <20030408094039.GS1712@pirmabanka.lv> <877ka519e0.fsf@stark.dyndns.tv> Mime-Version: 1.0 Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="5mCyUwZo2JvN/JJP" Content-Disposition: inline In-Reply-To: <877ka519e0.fsf@stark.dyndns.tv> User-Agent: Mutt/1.4.1i X-Spam-Status: No, hits=-39.2 required=5.0 tests=BAYES_01,IN_REP_TO,PGP_SIGNATURE_2,QUOTED_EMAIL_TEXT, QUOTE_TWICE_1,REFERENCES,REPLY_WITH_QUOTES,USER_AGENT_MUTT autolearn=ham version=2.50 X-Spam-Level: X-Spam-Checker-Version: SpamAssassin 2.50 (1.173-2003-02-20-exp) X-Archive-Number: 200304/112 X-Sequence-Number: 12813 --5mCyUwZo2JvN/JJP Content-Type: text/plain; charset=koi8-r Content-Disposition: inline Content-Transfer-Encoding: quoted-printable * Greg Stark [08.04.2003 15:07]: >=20 > "Victor Yegorov" writes: >=20 > > ...COALESCE( MAX(e.edate),'2003-01-01')... > >=20 > > MAX(e.edate): will return date type (just a guess) > > '2003-01-01': will return char type (or varchar, doesn't matter). >=20 > No, 'xyz' in postgres isn't necessarily a string constant, it's an untyped > constant that the parser decides the type of based on context. In this ca= se it > gets decided based on the other return values of the coalesce/case. Yes, yes. I know that. This is what I ment by saing `I'm not sure' in the first reply. >=20 > Notice the types chosen in this query: >=20 > db=3D# explain select * from t where t_start =3D coalesce(now(),'2003-01= -01') ; > = QUERY PLAN=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20 > -------------------------------------------------------------------------= ---------------------------------------------------------------------------= ------------------------------------------------------------------------- > Seq Scan on t (cost=3D0.00..48223.40 rows=3D226 width=3D423) > Filter: ((t_start)::timestamp with time zone =3D CASE WHEN (now() IS N= OT NULL) THEN now() WHEN ('2003-01-01' IS NOT NULL) THEN '2003-01-01 00:00:= 00-05'::timestamp with time zone ELSE NULL::timestamp with time zone END) > (2 rows) >=20 >=20 > However, I suspect for some reason postgres is doing a string comparison = in > your case. I don't understand why. Note that now() isn't a date, it's a > "timestamp with time zone", but I don't think that should be a problem.= =20 > Try checking what types it uses in the plan for: To avoid string comparison I've suggested to cast into date format manually. Actually, I have never came across with such situation, so I don't know the exact solution. --=20 Victor Yegorov --5mCyUwZo2JvN/JJP Content-Type: application/pgp-signature Content-Disposition: inline -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.1 (GNU/Linux) iD8DBQE+krz62RKGLEykUDYRAmyXAKCzHu8zxtuULjHlBFL84HetaTpNDwCfSAr0 FhpkpydfeL/6wsuQntXf6JU= =l+BS -----END PGP SIGNATURE----- --5mCyUwZo2JvN/JJP-- From tgl@sss.pgh.pa.us Tue Apr 8 14:30:44 2003 X-Original-To: pgsql-sql@postgresql.org Received: from spampd.localdomain (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 5881D47592C for ; Tue, 8 Apr 2003 10:30:44 -0400 (EDT) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by postgresql.org (Postfix) with ESMTP id B1C4B4758E6 for ; Tue, 8 Apr 2003 10:30:43 -0400 (EDT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.9/8.12.9) with ESMTP id h38EUb2L026706; Tue, 8 Apr 2003 10:30:37 -0400 (EDT) To: Greg Stark Cc: "Victor Yegorov" , Rudi Starcevic , pgsql-sql@postgresql.org Subject: Re: CASE In-reply-to: <877ka519e0.fsf@stark.dyndns.tv> References: <3E928853.7090801@oasis.net.au> <20030408094039.GS1712@pirmabanka.lv> <877ka519e0.fsf@stark.dyndns.tv> Comments: In-reply-to Greg Stark message dated "08 Apr 2003 08:06:15 -0400" Date: Tue, 08 Apr 2003 10:30:37 -0400 Message-ID: <26705.1049812237@sss.pgh.pa.us> From: Tom Lane X-Spam-Status: No, hits=-29.1 required=5.0 tests=BAYES_00,EMAIL_ATTRIBUTION,IN_REP_TO,REFERENCES, REPLY_WITH_QUOTES autolearn=ham version=2.50 X-Spam-Level: X-Spam-Checker-Version: SpamAssassin 2.50 (1.173-2003-02-20-exp) X-Archive-Number: 200304/114 X-Sequence-Number: 12815 Greg Stark writes: > However, I suspect for some reason postgres is doing a string comparison in > your case. I don't understand why. I'm wondering what the data type of e.edate actually is... regards, tom lane From rudi@oasis.net.au Tue Apr 8 23:10:10 2003 X-Original-To: pgsql-sql@postgresql.org Received: from spampd.localdomain (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id D7C3E4758E6 for ; Tue, 8 Apr 2003 19:10:10 -0400 (EDT) Received: from water.oasis.net.au (water.oasis.net.au [210.8.139.2]) by postgresql.org (Postfix) with ESMTP id B936E474E42 for ; Tue, 8 Apr 2003 19:10:08 -0400 (EDT) Received: from oasis.net.au (guardian [210.8.139.5]) by water.oasis.net.au (8.9.3/8.9.3/Debian 8.9.3-21) with ESMTP id JAA27298 for ; Wed, 9 Apr 2003 09:13:25 +1000 X-Authentication-Warning: water.oasis.net.au: Host guardian [210.8.139.5] claimed to be oasis.net.au Message-ID: <3E93579A.3030006@oasis.net.au> Date: Wed, 09 Apr 2003 09:13:30 +1000 From: Rudi Starcevic User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.1) Gecko/20020826 X-Accept-Language: en-us, en MIME-Version: 1.0 To: pgsql-sql@postgresql.org Subject: Re: CASE References: <3E928853.7090801@oasis.net.au> <20030408094039.GS1712@pirmabanka.lv> <877ka519e0.fsf@stark.dyndns.tv> <26705.1049812237@sss.pgh.pa.us> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: quoted-printable X-Spam-Status: No, hits=-15.7 required=5.0 tests=BAYES_20,HTML_20_30,REFERENCES,USER_AGENT_MOZILLA_UA, X_AUTH_WARNING autolearn=ham version=2.50 X-Spam-Level: X-Spam-Checker-Version: SpamAssassin 2.50 (1.173-2003-02-20-exp) X-Archive-Number: 200304/124 X-Sequence-Number: 12825 Hi, Thanks for the replys .. I'm still having a little trouble with my CASE/COALESCE SQL though. This time I provide more information. First the problem again. I'm trying to find the maximum date then compare that date to see if I=20 have any current events to show. CASE WHEN COALESCE( MAX(e.edate),'2003-01-01') >=3D now()::date THEN 'events' ELSE 'noevents'=20=20=20=20=20=20=20=20=20=20=20 END as myevents, It works fine for those events who do have dates greater or equal to now and also for those events who have a NULL value. However there is one event which is in the past '2003-04-01' which is=20 still coming up as 'events' instead of 'noevents'. I think there may be something wrong with my CASE SQL clause. From the sql and data below event_id number 15, which has a date in the=20 past, is coming up for me as 'events' instead of 'noevents'. Sorry but I can't see why. Some help much appreciated. Kind regards Rudi. Second the sql and some dummy data. SELECT m.m_id, m.active, v.*, CASE=20=20=20 WHEN COALESCE( MAX(e.edate),'2003-01-01'::date ) >=3D=20 now()::date THEN 'events' ELSE 'noevents'=20=20=20=20=20=20=20=20=20=20=20 END AS myevents, -- this is 22 in group by clause =20=20=20=20=20=20=20 p.v_id AS photo_v_id, COALESCE(p.purl,'') AS photourl FROM=20=20=20 members m, events e, venues v LEFT OUTER JOIN photos p ON ( p.pactive =3D 't' AND v.v_id =3D p.v_id ) WHERE m.m_id =3D v.m_id AND m.active =3D 't' AND v.vactive =3D 't' AND e.v_id =3D v.v_id GROUP BY=20 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,23,24 CREATE TABLE members ( m_id serial PRIMARY KEY, title varchar(8) NOT NULL, fname varchar(60) NOT NULL, lname varchar(80) NOT NULL, address_1 varchar(200) NOT NULL, suburb varchar(80) NOT NULL, town varchar(60) NOT NULL, pcode integer NOT NULL, state varchar(8) NOT NULL, phone varchar(20) NOT NULL, fax varchar(20), email varchar(160) UNIQUE NOT NULL, pwd varchar(8) NOT NULL, create_date date DEFAULT now() NOT NULL, update_date date DEFAULT now() NOT NULL, active boolean DEFAULT 'f'::bool NOT NULL, seqnum integer NOT NULL ); CREATE TABLE venues ( v_id serial PRIMARY KEY, m_id integer REFERENCES members ON UPDATE CASCADE ON DELETE CASCADE NOT=20 NULL, vname varchar(200) NOT NULL, vdesc text NOT NULL, vtype varchar(40) NOT NULL, vhours text NOT NULL, vaddress_1 varchar(180) NOT NULL, vsub_id integer NOT NULL, vsuburb varchar(80) NOT NULL, vpcode integer NOT NULL, vtown varchar(60) NOT NULL, vstate varchar(8) NOT NULL, vphone varchar(20) NOT NULL, vfax varchar(20), vemail varchar(160) NOT NULL, vurl varchar(180), vcreate_date date DEFAULT now() NOT NULL, vupdate_date date DEFAULT now() NOT NULL, vactive boolean DEFAULT 'f'::bool NOT NULL ); CREATE TABLE events ( e_id serial PRIMARY KEY, v_id int4 REFERENCES venues ON UPDATE CASCADE ON DELETE CASCADE=20 INITIALLY DEFERRED NOT NULL, ename varchar(160), edesc text, edate date, edoorsopen varchar(12), etype varchar(60) ); CREATE TABLE photos ( p_id serial PRIMARY KEY, v_id int4 REFERENCES venues ON UPDATE CASCADE ON DELETE CASCADE NOT=20 NULL, pdesc text NOT NULL, purl text NOT NULL, pcreate_date date DEFAULT now() NOT NULL, pupdate_date date DEFAULT now() NOT NULL, pactive boolean DEFAULT 'f'::bool NOT NULL, seqnum integer NOT NULL ); INSERT INTO "members" ("m_id", "title", "fname", "lname", "address_1", "sub= urb", "town", "pcode", "state", "phone", "fax", "email", "pwd", "create_dat= e", "update_date", "active", "seqnum") VALUES(3, 'Mr.', 'Wally', 'Wilbur', = '6 Elliot Street', 'Bundall', 'Gold Coast', 4217, 'QLD', '54545454', '', 'w= ally@wilbur.com', '2222', '2003-04-08', '2003-04-08', 't', 1004); INSERT INTO "members" ("m_id", "title", "fname", "lname", "address_1", "sub= urb", "town", "pcode", "state", "phone", "fax", "email", "pwd", "create_dat= e", "update_date", "active", "seqnum") VALUES(1, 'Mr.', 'Roger', 'Ramjet', = '37 Bundall Road', 'Bundall', 'Gold Coast', 4217, 'QLD', '54454545', '54455= 454', 'rojer@ramjet.com', '2222', '2003-04-08', '2003-04-08', 't', 1026); INSERT INTO "venues" ("v_id", "m_id", "vname", "vdesc", "vtype", "vhours", = "vaddress_1", "vsub_id", "vsuburb", "vpcode", "vtown", "vstate", "vphone", = "vfax", "vemail", "vurl", "vcreate_date", "vupdate_date", "vactive") VALUES= (1, 1, 'Ramjet Bar', 'Public Bar in the heart of Surfer's Paradise with hap= py hour for Proton Pills', 'Public Bar', '<= td bgcolor=3D\"#666666\" class=3D\"textW\">MondayWednesday', '37 Bundall Road', 2723, 'Surfers Paradise', 4217, 'Gold Coast', 'Q= LD', '54454545', '54455454', 'rudi@who.net', 'http://www.', '2003-04-08', '= 2003-04-08', 't'); INSERT INTO "venues" ("v_id", "m_id", "vname", "vdesc", "vtype", "vhours", = "vaddress_1", "vsub_id", "vsuburb", "vpcode", "vtown", "vstate", "vphone", = "vfax", "vemail", "vurl", "vcreate_date", "vupdate_date", "vactive") VALUES= (3, 3, 'Wally\'s Place', 'Free Beer at Wally's every day', 'Public Bar', '<= table width=3D\"100%\" border=3D\"0\" cellspacing=3D\"1\" cellpadding=3D\"3= \" style=3D\"border: 1px solid black\">

12pm.

12pm.

12pm.

T= rading Hours

open

close

Sunday

12am.

<= /td>

12pm.

12am.

12pm.

Tuesday

12am.

12pm.

12am.

12pm.

Thursday

12am.

12pm.

Friday

12am.

12pm.

Saturday

12am.

12pm.

Trading Hours

open

close

Sunday

12am.

12pm.

= Monday

12am.

Tuesday<= p class=3D\"textW\">12am.

12pm.

We= dnesday

12am.

Thursday

12am.

12pm.

= Friday

12am.

Saturday

12am.

12pm.

', '6 Elliot Street', 2715, 'Chevron Isl= and', 4217, 'Gold Coast', 'QLD', '54545454', '', 'wally@wilbur.com', '', '2= 003-04-08', '2003-04-08', 't'); INSERT INTO "events" ("e_id", "v_id", "ename", "edesc", "edate", "edoorsope= n", "etype") VALUES(1, 1, 'Full Moon Rave', 'asdf sadf sdf sdf dsf sdf sdfs= df sdf sd;lfj dflj fljsd flsdkjf dsfd sklfjdsfl ksdfklj dlfjds sdjf dskljf = s', '2003-04-12', '11pm.', 'Rave'); INSERT INTO "events" ("e_id", "v_id", "ename", "edesc", "edate", "edoorsope= n", "etype") VALUES(2, 1, 'Bridge Challenge', 'Teams of 2 challenge each ot= her for big prizes. All Welcome.', '2003-04-10', '7pm.', 'Card night'); INSERT INTO "events" ("e_id", "v_id", "ename", "edesc", "edate", "edoorsope= n", "etype") VALUES(3, 1, 'Monica and the Moochers', 'Monica and the Mooche= rs Blues Band. Check them out', '2003-04-17', '7pm.', 'Band'); INSERT INTO "events" ("e_id", "v_id", "ename", "edesc", "edate", "edoorsope= n", "etype") VALUES(4, 1, '', '', '2003-04-08', '', ''); INSERT INTO "events" ("e_id", "v_id", "ename", "edesc", "edate", "edoorsope= n", "etype") VALUES(5, 1, '', '', '2003-04-08', '', ''); INSERT INTO "events" ("e_id", "v_id", "ename", "edesc", "edate", "edoorsope= n", "etype") VALUES(6, 1, '', '', '2003-04-08', '', ''); INSERT INTO "events" ("e_id", "v_id", "ename", "edesc", "edate", "edoorsope= n", "etype") VALUES(7, 1, '', '', '2003-04-08', '', ''); INSERT INTO "events" ("e_id", "v_id", "ename", "edesc", "edate", "edoorsope= n", "etype") VALUES(15, 3, 'Poker Night', 'Poker card night. All welcome', = '2003-04-05', '7pm.', 'Card night'); INSERT INTO "events" ("e_id", "v_id", "ename", "edesc", "edate", "edoorsope= n", "etype") VALUES(16, 3, '', '', '2003-04-09', '', ''); INSERT INTO "events" ("e_id", "v_id", "ename", "edesc", "edate", "edoorsope= n", "etype") VALUES(17, 3, '', '', '2003-04-09', '', ''); INSERT INTO "events" ("e_id", "v_id", "ename", "edesc", "edate", "edoorsope= n", "etype") VALUES(18, 3, '', '', '2003-04-09', '', ''); INSERT INTO "events" ("e_id", "v_id", "ename", "edesc", "edate", "edoorsope= n", "etype") VALUES(19, 3, '', '', '2003-04-09', '', ''); INSERT INTO "events" ("e_id", "v_id", "ename", "edesc", "edate", "edoorsope= n", "etype") VALUES(20, 3, '', '', '2003-04-09', '', ''); INSERT INTO "events" ("e_id", "v_id", "ename", "edesc", "edate", "edoorsope= n", "etype") VALUES(21, 3, '', '', '2003-04-09', '', ''); INSERT INTO "photos" ("p_id", "v_id", "pdesc", "purl", "pcreate_date", "pup= date_date", "pactive", "seqnum") VALUES(1, 1, 'Ramjet Bar', 'http://www.ozp= ubsclubs.com.au/ozimages/bilbos-pub.jpeg', '2003-04-08', '2003-04-08', 't',= 1026); INSERT INTO "photos" ("p_id", "v_id", "pdesc", "purl", "pcreate_date", "pup= date_date", "pactive", "seqnum") VALUES(2, 3, 'Wally\'s Place', 'http://www= .ozpubsclubs.com.au/ozimages/bilbos-pub.bmp', '2003-04-08', '2003-04-08', '= t', 1004); From tgl@sss.pgh.pa.us Wed Apr 9 04:47:29 2003 X-Original-To: pgsql-sql@postgresql.org Received: from spampd.localdomain (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id A852347622E for ; Wed, 9 Apr 2003 00:47:29 -0400 (EDT) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by postgresql.org (Postfix) with ESMTP id 71A5F476227 for ; Wed, 9 Apr 2003 00:47:28 -0400 (EDT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.9/8.12.9) with ESMTP id h394lS2L004380; Wed, 9 Apr 2003 00:47:28 -0400 (EDT) To: Rudi Starcevic Cc: pgsql-sql@postgresql.org Subject: Re: CASE In-reply-to: <3E93579A.3030006@oasis.net.au> References: <3E928853.7090801@oasis.net.au> <20030408094039.GS1712@pirmabanka.lv> <877ka519e0.fsf@stark.dyndns.tv> <26705.1049812237@sss.pgh.pa.us> <3E93579A.3030006@oasis.net.au> Comments: In-reply-to Rudi Starcevic message dated "Wed, 09 Apr 2003 09:13:30 +1000" Date: Wed, 09 Apr 2003 00:47:28 -0400 Message-ID: <4379.1049863648@sss.pgh.pa.us> From: Tom Lane X-Spam-Status: No, hits=-32.5 required=5.0 tests=BAYES_01,EMAIL_ATTRIBUTION,IN_REP_TO,QUOTED_EMAIL_TEXT, REFERENCES,REPLY_WITH_QUOTES autolearn=ham version=2.50 X-Spam-Level: X-Spam-Checker-Version: SpamAssassin 2.50 (1.173-2003-02-20-exp) X-Archive-Number: 200304/125 X-Sequence-Number: 12826 Rudi Starcevic writes: > However there is one event which is in the past '2003-04-01' which is > still coming up as 'events' instead of 'noevents'. But you're grouping over members and venues, and taking the max across event dates in each group. A past event for a venue won't affect anything, if there are later events for that venue. regards, tom lane From rudi@oasis.net.au Wed Apr 9 04:55:28 2003 X-Original-To: pgsql-sql@postgresql.org Received: from spampd.localdomain (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 7CDCF4758E6 for ; Wed, 9 Apr 2003 00:55:28 -0400 (EDT) Received: from water.oasis.net.au (water.oasis.net.au [210.8.139.2]) by postgresql.org (Postfix) with ESMTP id 29FC8474E42 for ; Wed, 9 Apr 2003 00:55:27 -0400 (EDT) Received: from oasis.net.au (guardian [210.8.139.5]) by water.oasis.net.au (8.9.3/8.9.3/Debian 8.9.3-21) with ESMTP id OAA02226 for ; Wed, 9 Apr 2003 14:58:49 +1000 X-Authentication-Warning: water.oasis.net.au: Host guardian [210.8.139.5] claimed to be oasis.net.au Message-ID: <3E93A8B6.1000407@oasis.net.au> Date: Wed, 09 Apr 2003 14:59:34 +1000 From: Rudi Starcevic User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.1) Gecko/20020826 X-Accept-Language: en-us, en MIME-Version: 1.0 To: pgsql-sql@postgresql.org Subject: Re: CASE References: <3E928853.7090801@oasis.net.au> <20030408094039.GS1712@pirmabanka.lv> <877ka519e0.fsf@stark.dyndns.tv> <26705.1049812237@sss.pgh.pa.us> <3E93579A.3030006@oasis.net.au> <4379.1049863648@sss.pgh.pa.us> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Spam-Status: No, hits=-27.3 required=5.0 tests=BAYES_01,EMAIL_ATTRIBUTION,REFERENCES, USER_AGENT_MOZILLA_UA,X_AUTH_WARNING autolearn=ham version=2.50 X-Spam-Level: X-Spam-Checker-Version: SpamAssassin 2.50 (1.173-2003-02-20-exp) X-Archive-Number: 200304/126 X-Sequence-Number: 12827 Tom, I see .. Thanks. I'll need to rewrite my query. I'm not sure I understand right now so it will be a good challenge for me .. Any tips appreciated :-) Cheers Rudi. Tom Lane wrote: >Rudi Starcevic writes: > > >>However there is one event which is in the past '2003-04-01' which is >>still coming up as 'events' instead of 'noevents'. >> >> > >But you're grouping over members and venues, and taking the max across >event dates in each group. A past event for a venue won't affect >anything, if there are later events for that venue. > > regards, tom lane > > > From jtx@hatesville.com Mon May 19 22:36:40 2003 X-Original-To: pgsql-sql@postgresql.org Received: from localhost (unknown [64.117.224.193]) by developer.postgresql.org (Postfix) with ESMTP id 6E3269260BE for ; Mon, 19 May 2003 18:36:40 -0400 (EDT) Received: from developer.postgresql.org ([64.117.224.193]) by localhost (developer.postgresql.org [64.117.224.193:10024]) (amavisd-new) with ESMTP id 47162-02 for ; Mon, 19 May 2003 18:36:36 -0400 (EDT) Received: from smtp801.mail.sc5.yahoo.com (smtp801.mail.sc5.yahoo.com [66.163.168.180]) by developer.postgresql.org (Postfix) with SMTP id EA60A925547 for ; Mon, 19 May 2003 02:59:38 -0400 (EDT) Received: from adsl-67-114-255-130.dsl.lsan03.pacbell.net (HELO theplague) (heavenstrash@sbcglobal.net@67.114.255.130 with login) by smtp-sbc-v1.mail.vip.sc5.yahoo.com with SMTP; 19 May 2003 06:59:37 -0000 Reply-To: From: "James Taylor" To: Subject: CASE Date: Sun, 18 May 2003 23:59:46 -0700 Message-ID: <002201c31dd4$3f34c100$0000fea9@theplague> MIME-Version: 1.0 Content-Type: text/plain; charset="US-ASCII" Content-Transfer-Encoding: 7bit X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook, Build 10.0.2616 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165 Importance: Normal X-Virus-Scanned: by amavisd-new X-Archive-Number: 200305/370 X-Sequence-Number: 13535 Hi everyone, I'm trying to figure out how to get CASE working in a sum aggregate function. Pretty simple query: select sum(numbers) from lists where uid=1; It's possible that there may be 0 rows where uid == 1. Instead of returning 1 blank row, I want it to return '0'. So, I tried this: select case(sum(numbers)) when null then 0 else sum(numbers) end from list_results; That... Doesn't work. Any suggestions on how to do this and where I can find some decent information on use of CASE? From sszabo@megazone23.bigpanda.com Tue May 20 00:45:15 2003 X-Original-To: pgsql-sql@postgresql.org Received: from localhost (unknown [64.117.224.193]) by developer.postgresql.org (Postfix) with ESMTP id 3F55592351C for ; Mon, 19 May 2003 20:45:15 -0400 (EDT) Received: from developer.postgresql.org ([64.117.224.193]) by localhost (developer.postgresql.org [64.117.224.193:10024]) (amavisd-new) with ESMTP id 96371-09 for ; Mon, 19 May 2003 20:45:12 -0400 (EDT) Received: from megazone.bigpanda.com (megazone.bigpanda.com [63.150.15.178]) by developer.postgresql.org (Postfix) with ESMTP id 7E9F6924D1D for ; Mon, 19 May 2003 18:49:22 -0400 (EDT) Received: by megazone.bigpanda.com (Postfix, from userid 1001) id E07E4D645; Mon, 19 May 2003 15:49:21 -0700 (PDT) Received: from localhost (localhost [127.0.0.1]) by megazone.bigpanda.com (Postfix) with ESMTP id D62475C0B; Mon, 19 May 2003 15:49:21 -0700 (PDT) Date: Mon, 19 May 2003 15:49:21 -0700 (PDT) From: Stephan Szabo To: James Taylor Cc: Subject: Re: CASE In-Reply-To: <002201c31dd4$3f34c100$0000fea9@theplague> Message-ID: <20030519154658.Q43251-100000@megazone23.bigpanda.com> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Virus-Scanned: by amavisd-new X-Archive-Number: 200305/373 X-Sequence-Number: 13538 On Sun, 18 May 2003, James Taylor wrote: > Hi everyone, I'm trying to figure out how to get CASE working in a sum > aggregate function. Pretty simple query: > > select sum(numbers) from lists where uid=1; > > It's possible that there may be 0 rows where uid == 1. Instead of > returning 1 blank row, I want it to return '0'. So, I tried this: > > select case(sum(numbers)) when null then 0 else sum(numbers) end from > list_results; I believe the case would be: case when sum(numbers) is null then 0 else sum(numbers) end It's more like the ternary operator than a switch statement. From rbt@rbt.ca Tue May 20 01:05:55 2003 X-Original-To: pgsql-sql@postgresql.org Received: from localhost (unknown [64.117.224.193]) by developer.postgresql.org (Postfix) with ESMTP id AC40C9253E7 for ; Mon, 19 May 2003 21:05:55 -0400 (EDT) Received: from developer.postgresql.org ([64.117.224.193]) by localhost (developer.postgresql.org [64.117.224.193:10024]) (amavisd-new) with ESMTP id 13666-03 for ; Mon, 19 May 2003 21:05:52 -0400 (EDT) Received: from tomts7-srv.bellnexxia.net (tomts7.bellnexxia.net [209.226.175.40]) by developer.postgresql.org (Postfix) with ESMTP id E667B924FFE for ; Mon, 19 May 2003 19:24:46 -0400 (EDT) Received: from [192.168.1.200] ([64.231.213.3]) by tomts7-srv.bellnexxia.net (InterMail vM.5.01.05.32 201-253-122-126-132-20030307) with ESMTP id <20030519232445.PAQJ23765.tomts7-srv.bellnexxia.net@[192.168.1.200]>; Mon, 19 May 2003 19:24:45 -0400 Subject: Re: CASE From: Rod Taylor To: jtx@hatesville.com Cc: pgsql-sql@postgresql.org In-Reply-To: <002201c31dd4$3f34c100$0000fea9@theplague> References: <002201c31dd4$3f34c100$0000fea9@theplague> Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="=-x6SPYLWv4yFJUehA1nSZ" Organization: Message-Id: <1053386689.24151.49.camel@jester> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.2.4 Date: 19 May 2003 19:24:49 -0400 X-Virus-Scanned: by amavisd-new X-Archive-Number: 200305/374 X-Sequence-Number: 13539 --=-x6SPYLWv4yFJUehA1nSZ Content-Type: text/plain Content-Transfer-Encoding: quoted-printable On Mon, 2003-05-19 at 02:59, James Taylor wrote: > Hi everyone, I'm trying to figure out how to get CASE working in a sum > aggregate function. Pretty simple query: >=20 > select sum(numbers) from lists where uid=3D1; >=20 > It's possible that there may be 0 rows where uid =3D=3D 1. Instead of > returning 1 blank row, I want it to return '0'. So, I tried this: >=20 > select case(sum(numbers)) when null then 0 else sum(numbers) end from > list_results; Use Coalesce: SELECT coalesce(sum(numbers), 0) FROM lists WHERE uid =3D 1; To use case, efficiently, if you really want: SELECT CASE WHEN sum_numbers IS NULL THEN 0 ELSE sum_numbers END AS sum_numbers FROM (SELECT sum(numbers) AS sum_numbers FROM lists WHERE uid =3D 1 ) as tab; --=20 Rod Taylor PGP Key: http://www.rbt.ca/rbtpub.asc --=-x6SPYLWv4yFJUehA1nSZ Content-Type: application/pgp-signature; name=signature.asc Content-Description: This is a digitally signed message part -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.2 (FreeBSD) iD8DBQA+yWfA6DETLow6vwwRAht7AJ9N7kFauyQMt1DvB4uLLfX8hhHknQCfamQX QqjfSsv8KMF2qr6ubKsvJxM= =H6tI -----END PGP SIGNATURE----- --=-x6SPYLWv4yFJUehA1nSZ-- From mkoi-pg@aon.at Tue May 20 01:13:40 2003 X-Original-To: pgsql-sql@postgresql.org Received: from localhost (unknown [64.117.224.193]) by developer.postgresql.org (Postfix) with ESMTP id D6D24924E45 for ; Mon, 19 May 2003 21:13:40 -0400 (EDT) Received: from developer.postgresql.org ([64.117.224.193]) by localhost (developer.postgresql.org [64.117.224.193:10024]) (amavisd-new) with ESMTP id 18025-06 for ; Mon, 19 May 2003 21:13:37 -0400 (EDT) Received: from email02.aon.at (WARSL402PIP7.highway.telekom.at [195.3.96.94]) by developer.postgresql.org (Postfix) with SMTP id 873B0924EDE for ; Mon, 19 May 2003 19:53:28 -0400 (EDT) Received: (qmail 385210 invoked from network); 19 May 2003 23:53:26 -0000 Received: from m169p028.dipool.highway.telekom.at (HELO cantor) ([62.46.11.28]) (envelope-sender ) by qmail2rs.highway.telekom.at (qmail-ldap-1.03) with SMTP for ; 19 May 2003 23:53:26 -0000 From: Manfred Koizar To: Cc: Subject: Re: CASE Date: Tue, 20 May 2003 01:52:41 +0200 Message-ID: <6ericvkpd536s9a51atc05fsm45oib7mpf@4ax.com> References: <002201c31dd4$3f34c100$0000fea9@theplague> In-Reply-To: <002201c31dd4$3f34c100$0000fea9@theplague> X-Mailer: Forte Agent 1.8/32.548 MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new X-Archive-Number: 200305/375 X-Sequence-Number: 13540 On Sun, 18 May 2003 23:59:46 -0700, "James Taylor" wrote: >select case(sum(numbers)) when null then 0 else sum(numbers) end from >list_results; > >That... Doesn't work. SELECT CASE WHEN SUM(numbers) IS NULL THEN 0 ELSE SUM(numbers) END ... or SELECT COALESCE(SUM(numbers), 0) FROM ... Servus Manfred From tgl@sss.pgh.pa.us Tue May 20 01:26:09 2003 X-Original-To: pgsql-sql@postgresql.org Received: from localhost (unknown [64.117.224.193]) by developer.postgresql.org (Postfix) with ESMTP id DB631925544 for ; Mon, 19 May 2003 21:26:09 -0400 (EDT) Received: from developer.postgresql.org ([64.117.224.193]) by localhost (developer.postgresql.org [64.117.224.193:10024]) (amavisd-new) with ESMTP id 24498-05 for ; Mon, 19 May 2003 21:26:04 -0400 (EDT) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by developer.postgresql.org (Postfix) with ESMTP id 19A639256E6 for ; Mon, 19 May 2003 20:17:31 -0400 (EDT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.9/8.12.9) with ESMTP id h4K0HUU6024790; Mon, 19 May 2003 20:17:30 -0400 (EDT) To: jtx@hatesville.com Cc: pgsql-sql@postgresql.org Subject: Re: CASE In-reply-to: <002201c31dd4$3f34c100$0000fea9@theplague> References: <002201c31dd4$3f34c100$0000fea9@theplague> Comments: In-reply-to "James Taylor" message dated "Sun, 18 May 2003 23:59:46 -0700" Date: Mon, 19 May 2003 20:17:30 -0400 Message-ID: <24789.1053389850@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new X-Archive-Number: 200305/376 X-Sequence-Number: 13541 "James Taylor" writes: > select case(sum(numbers)) when null then 0 else sum(numbers) end from > list_results; The reason the above doesn't work is that it expands to CASE WHEN sum(numbers) = null THEN ... which always fails ("= null" does not mean "is null"). Easier would be SELECT COALESCE(sum(numbers), 0) FROM list_results regards, tom lane