Quantcast

Can't insert timestamp field with value CURRENT_TIME

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Can't insert timestamp field with value CURRENT_TIME

markww
Hi,

I'm trying to add a timestamp field to an existing table using the following
statement:

    ALTER TABLE test ADD COLUMN lunchtime TIMESTAMP NOT NULL DEFAULT
CURRENT_TIME

this fails with the following error:

    Cannot add a column with non-constant default

Ok that makes sense - but why can we CREATE a table with a timestamp field
whose default is CURRENT_TIME, but not alter one with that as the default
value? I may be misusing the syntax -

Thanks,
Mark
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Can't insert timestamp field with value CURRENT_TIME

Dan Kennedy-4

On Sep 27, 2008, at 7:13 AM, Mark Wyszomierski wrote:

> Hi,
>
> I'm trying to add a timestamp field to an existing table using the  
> following
> statement:
>
>    ALTER TABLE test ADD COLUMN lunchtime TIMESTAMP NOT NULL DEFAULT
> CURRENT_TIME
>
> this fails with the following error:
>
>    Cannot add a column with non-constant default
>
> Ok that makes sense - but why can we CREATE a table with a timestamp  
> field
> whose default is CURRENT_TIME, but not alter one with that as the  
> default
> value? I may be misusing the syntax -

SQLite doesn't modify the underlying table structure when you add
a column using ALTER TABLE. So after the ALTER TABLE is executed,
the existing rows now contain one value less than the table has
columns. When SQLite reads the table, it detects the short row and
returns the default column value in place of any missing values. This
wouldn't work with something like CURRENT_TIME.



> Thanks,
> Mark
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Can't insert timestamp field with value CURRENT_TIME

Ribeiro, Glauber
You can probably do it the hard way: create a new table with the
structure you need, then populate it with data from your old table, then
rename both tables.

-----Original Message-----
From: Dan [mailto:[hidden email]]
Sent: Tuesday, September 30, 2008 11:53 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Can't insert timestamp field with value
CURRENT_TIME


On Sep 27, 2008, at 7:13 AM, Mark Wyszomierski wrote:

> Hi,
>
> I'm trying to add a timestamp field to an existing table using the  
> following
> statement:
>
>    ALTER TABLE test ADD COLUMN lunchtime TIMESTAMP NOT NULL DEFAULT
> CURRENT_TIME
>
> this fails with the following error:
>
>    Cannot add a column with non-constant default
>
> Ok that makes sense - but why can we CREATE a table with a timestamp  
> field
> whose default is CURRENT_TIME, but not alter one with that as the  
> default
> value? I may be misusing the syntax -

SQLite doesn't modify the underlying table structure when you add
a column using ALTER TABLE. So after the ALTER TABLE is executed,
the existing rows now contain one value less than the table has
columns. When SQLite reads the table, it detects the short row and
returns the default column value in place of any missing values. This
wouldn't work with something like CURRENT_TIME.



> Thanks,
> Mark
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Loading...