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@scw.org>
Director of Information Systems, Support Center of Washington