[postgis-users] Convert from character to timestamptz

[Sandro Santilli]

On Fri, Sep 30, 2011 at 12:33:30PM -0400, Mathieu Basille wrote:
> Dear Sandro,
> 
> You're right, I'm being too vague (in my defence, error messages are
> in French for me!).

See if exporting LANG=C helps there.

> Let's try to be more specific. Attached is a
> simple shapefile (4 points). It imports correctly with:
> 
> shp2pgsql -s 26919 -I test.shp test | psql -h localhost -d geodb -U
> caribou -q
> 
> Now, if I try:
> 
> SELECT TIMESTAMP WITH TIME ZONE 'date' FROM test;
> 
> I receive the following error:
> 
> ********** Error **********
> 
> ERREUR: syntaxe en entrée invalide pour le type timestamp with time
> zone : « date » [~ invalid syntax for the type timestamp with time
> zone]

Right, it's saying that 'date' (the string composed by 4 letters 'd', 'a',
't', 'e') is not a valid "timestamp with time" value...

> ALTER TABLE test
>     ALTER COLUMN date SET DATA TYPE timestamptz;
> 
> the error is:
> 
> ********** Error **********
> 
> ERREUR: la colonne « date » ne peut pas être convertie vers le type
> timestamp with time zone [~ column 'date' can't be converted to type
> type timestamp with time zone]

Right, it wants to know how.

> And if I try what you suggested:
> 
> ALTER TABLE test
>     ALTER COLUMN date TYPE timestamptz
>     WITH date::timestamptz;
> 
> the error is:
> 
> ********** Error **********
> 
> ERREUR: erreur de syntaxe sur ou près de « WITH date » [~ syntax
> error on or near 'WITH date']

I was wrong about the syntax, but the idea is that you tell it
how to do the conversion. Maybe it was USING instead of WITH.
The definitive answer is in the PostgreSQL reference manual, under
the SQL section, in the ALTER TABLE syntax.

--strk;

  ()   Free GIS & Flash consultant/developer
  /\   http://strk.keybit.net/services.html