DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=message-id:date:from:to:subject:cc:in-reply-to:mime-version
	:content-type:content-transfer-encoding:content-disposition
	:references;
	b=EfOu/24AwHZ6YZI49rZ2B2pApO6C8ZONXs7R1G6BmcLHsQ5DZQ4WGgiZ5EocxBqhN8
	nru7JUH4ivtlUyyl/I5UJ10HwpwYRr9IjaeWSgrZ74a5aQqF6IwZxRc/aXPRTT7s1Hp5
	HTOwWoMLkC4spIvraf5JNui+s3FIO0AhckuhY=
Message-ID: <4544e0330806281227y21773c99ja0dbce9fb74be302@mail.gmail.com>
Date: Sat, 28 Jun 2008 14:27:57 -0500
From: "Peter Koczan" <pjkoczan@gmail.com>
To: "Roberto Garcia" <roberto.garcia@cptec.inpe.br>
Subject: Re: Major upgrade advice
Cc: pgsql-admin@postgresql.org,
	"Steve Crawford" <scrawford@pinpointresearch.com>
In-Reply-To: <48595B63.4080000@cptec.inpe.br>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
References: <48594429.7040001@pinpointresearch.com>
	<48595B63.4080000@cptec.inpe.br>

On Wed, Jun 18, 2008 at 2:00 PM, Roberto Garcia
<roberto.garcia@cptec.inpe.br> wrote:
> Just to mention one issue we had here:
>
> In 8.1 we did this to retrieve all data from a specific date:
> SELECT * FROM xxx
> WHERE <timestamp_column> LIKE '2008-05-20%'
>
> In 8.3 we had to change to:
> SELECT * FROM xxx
> WHERE <timestamp_column> >= CAST('2008-05-20' as timestamp) AND
>     <timestamp_column> < CAST('2008-05-21' as timestamp)

Also, don't forget that bareword numbers don't automatically cast to
text anymore.

For instance, assuming col is a text type (char, varchar, text), the query

select * from table where col = 1000

Will throw an error in 8.3 whereas it will work under pre-8.3
releases. You can one either of the following to make it work.

select * from table where col = 1000::text
select * from table where col = '1000'

I've been bitten by that bug a few times.

Peter