Date: Tue, 8 Apr 2003 15:13:46 +0300
From: "Victor Yegorov" <viy@pirmabanka.lv>
To: Greg Stark <gsstark@mit.edu>
Cc: Rudi Starcevic <rudi@oasis.net.au>, pgsql-sql@postgresql.org
Subject: Re: CASE
Message-ID: <20030408121346.GA13190@pirmabanka.lv>
Mail-Followup-To: Greg Stark <gsstark@mit.edu>,
	Rudi Starcevic <rudi@oasis.net.au>, 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

--5mCyUwZo2JvN/JJP
Content-Type: text/plain; charset=koi8-r
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

* Greg Stark <gsstark@mit.edu> [08.04.2003 15:07]:
>=20
> "Victor Yegorov" <viy@pirmabanka.lv> 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--