[Dbix-class] issue with deploy() and pgsql's timestamp with time zoneUser-Agent: SquirrelMail/1.5.2 [SVN]

[Moritz Onken]

Am 23.05.2009 um 22:14 schrieb Moritz Onken:

>
> Am 23.05.2009 um 19:32 schrieb Guillaume Chambriat:
>
>> Hi,
>>
>> I've a PostgreSQL table:
>>
>> create table date_fields (
>>   id          serial primary key,
>>   message     integer references messages(id)
>>               on delete cascade,
>>   value       timestamp with time zone
>> );
>>
>> And the corresponding schema class, generated by
>> DBIx::Class::Schema::Loader, has the following code for the "value"
>> column :
>>
>> "value",
>> {
>>   data_type => "timestamp with time zone",
>>   default_value => undef,
>>   is_nullable => 1,
>>   size => 8,
>> },
>>
>> When I try to deploy this schema to an empty DB, I get the following
>> error message (roughly translated from French into English):
>>
>> DBIx::Class::Schema::deploy(): DBI Exception: DBD::Pg::db do failed:
>> ERROR:  syntax error near " ( "
>> LINE 5:   "value" timestamp with time zone(6),
>>                                         ^ at ./script/ 
>> myapp_hub_spawn_db.pl line 45
>> (running "
>> CREATE TABLE "date_fields" (
>> "id" serial NOT NULL,
>> "message" smallint,
>> "value" timestamp with time zone(6),
>> PRIMARY KEY ("id")
>> )") at /usr/local/share/perl/5.10.0/DBIx/Class/Storage/DBI.pm line
>> 1811, <STDIN> line 4.
>>
>> The same SQL code, copied in a psql shell, triggers the same
>> error message. Removing '(6)' obviously fixes it.
>>
>> Any idea why IdontKnowWhichModule (SQL::Translator, another one?)  
>> adds
>> '(6)' to the generated SQL code?
>
> This is because you defined a size on that column.
>
> Remove size => 8 and deploy will do the right thing. I think it's  
> both a problem in Schema::Loader and SQL::Translator.
>
> moritz

Actually it's a SQL::Translator problem:

The correct postgresql syntax for a timestamp column with a precision  
is:

timestamp (6) with time zone

instead of "timestamp with time zone (6)".

Patches welcome :-)

moritz