On Wed, 11 Apr 2001, Diehl, Jeffrey wrote:
> Hi all,
>
> I'm in the final stages of migrating from mysql to postgres and have a few
> more questions...
>
> 1)
> I have a table:
> create table a (
> t timestamp not null,
> ...
> );
>
> I'm thinking that I can define a.t as not null default=now(). But will this
> work? That is, will it update a.t when I modified a given record?
>
>
> 2)
> I have another table:
> create table b (
> id int not null AUTO_INCREMENT,
> ...
> );
>
> To reproduce this behavior, I believe I need to use a sequence. The problem
> is that I have a lot of data to import into this table. How do I import the
> old data without colliding with the new sequence numbers?
1)
DEFAULT values only apply when *adding* a record, not modifying it, so,
no, "DEFAULT now()" (or more portably, DEFAULT CURRENT_TIMESTAMP) won't
change on updates. (I can't imagine any database that does do this for
DEFAULT values!)
If you want to track modifications, you want a trigger to watch for
updates. Look in /contrib/spi/moddatetime for help.
At my org, our important tables have
CREATE TABLE ... ( ... addby varchar(32) not null default current_user, addat timestamp not null default
current_timestamp,chgby varchar(32) not null default current_user, chgat timestamp not null default
current_timestamp
);
and then add the triggers to track change times/users.
2)
You can use a sequence directly, most people would simply say
CREATE TABLE b ( id SERIAL NOT NULL PRIMARY KEY ...
);
If you old data in, that's fine. You can set the start for the sequence
after the importing so that the sequence starts w/the first new number
with SELECT SETVAL('b_id_seq', xxx), where xxx is the number for it to
begin new id numbers.
--
Joel Burton <jburton@scw.org>
Director of Information Systems, Support Center of Washington