Re: DB porting questions...

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(at)scw(dot)org>
Director of Information Systems, Support Center of Washington