Hello Greg,

> I am trying to specify a default value for an existing TIMESTAMP column in
> our firebird database.
>
> The following syntax:
> alter table perstyprate add default '01/01/1900 00:00:00' for
> ptrrateeffstartdate
>
> Results in the following error:
> *** IBPP::SQLException ***
> Context: Statement::Prepare( alter table perstyprate add default
> '01/01/1900 00:00:00' for PTRRATEEFFSTARTDATE )
> Message: isc_dsql_prepare failed
>
> SQL Message : -104
> Invalid token
>
> Engine Code : 335544569
> Engine Message :
> Dynamic SQL Error
> SQL error code = -104
> Token unknown - line 1, column 29
> default
>
> I also tried the following:
> alter table perstyprate add constraint defbegdate default '01/01/1900
> 00:00:00' for PTRRATEEFFSTARTDATE
>
> Which gives me the same error.
>
> The column as added as follows:
> ALTER TABLE PERSTYPRATE ADD PTRRATEEFFSTARTDATE TIMESTAMP
>
> By the way, the following works when adding the column:
> ALTER TABLE PERSTYPRATE ADD PTRRATEEFFSTARTDATE TIMESTAMP DEFAULT
> '01/01/1900 00:00:00';
>
> What is the correct syntax for adding a default value to an existing
> column?

It seems you're used to MS SQL Server? :-)

In Firebird 2.1 and up, it's:

ALTER TABLE <tablename> ALTER [COLUMN] <columnname> SET DEFAULT
default_value;
or
DROP DEFAULT;

If you're using a version below 2.1, you cannot easily set a (new) default,
but
Database Workbench (download the FREE Lite Edition from www.upscene.com )
can do it for you.

With regards,

Martijn Tonies
Upscene Productions
http://www.upscene.com

Download Database Workbench for Oracle, MS SQL Server, Sybase SQL
Anywhere, MySQL, InterBase, NexusDB and Firebird!

Database questions? Check the forum:
http://www.databasedevelopmentforum.com