[postgis-users] Convert from character to timestamptz

[Mathieu Basille]

Dear Sandro,

You're right, I'm being too vague (in my defence, error messages are in 
French for me!). 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]
SQL state: 22007
Character: 33

With the command:

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]
SQL state: 42804

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']
SQL state: 42601
Character: 61

Thanks again for your help!
Mathieu.



Le 30/09/2011 12:15, Sandro Santilli a écrit :
> On Fri, Sep 30, 2011 at 12:10:00PM -0400, Mathieu Basille wrote:
>> Dear PostGIS users,
>>
>> I've been trying for hours with no result... My problem is that I
>> import a simple shapefile (points) to PostGIS using shp2pgsql. In
>> the attribute table, there is a 'date' column formatted as
>> '2005-03-20 00:00:00 EST5EDT'. This column is then imported as a
>> 'character' column into PostGIS. Now, I'd like to convert this
>> column to a real 'timestamp with time zone'. I tried many things
>> like:
>>
>> ALTER TABLE test
>>      ALTER COLUMN date SET DATA TYPE timestamptz;
>>
>> or
>>
>> SELECT TIMESTAMP WITH TIME ZONE 'date' FROM test;
>>
>> and all return an error
>
> You should report the error, when you get one.
> I can just "guess" that the second is invalid syntax as you're
> using the literal string 'date' rather than the identifier "date".
> And I can "guess" that the first is a syntax error (but I'm not sure).
> I'd write the first as:
>
>   ALTER TABLE test
>     ALTER COLUMN date TYPE timestamptz
>     WITH date::timestamptz;
>
> Or something like that (but would really first lookup the reference).
>
> --strk;
>
>    ()   Free GIS&  Flash consultant/developer
>    /\   http://strk.keybit.net/services.html
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users

-- 

~$ whoami
Mathieu Basille, Post-Doc

~$ locate
Laboratoire d'Écologie Comportementale et de Conservation de la Faune
+ Centre d'Étude de la Forêt
Département de Biologie
Université Laval, Québec

~$ info
http://ase-research.org/basille

~$ fortune
``If you can't win by reason, go for volume.''
Calvin, by Bill Watterson.
-------------- next part --------------
A non-text attachment was scrubbed...
Name: test.dbf
Type: application/x-dbf
Size: 393 bytes
Desc: not available
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20110930/bc69c848/attachment.bin>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: test.shp
Type: application/x-esri-shape
Size: 212 bytes
Desc: not available
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20110930/bc69c848/attachment-0001.bin>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: test.shx
Type: application/x-esri-shape
Size: 131 bytes
Desc: not available
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20110930/bc69c848/attachment-0002.bin>