Re: DB porting questions... - Mailing list pgsql-sql

From Joel Burton
Subject Re: DB porting questions...
Date
Msg-id Pine.LNX.4.21.0104111912410.17795-100000@olympus.scw.org
Whole thread Raw
In response to DB porting questions...  ("Diehl, Jeffrey" <jdiehl@sandia.gov>)
List pgsql-sql
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



pgsql-sql by date:

Previous
From: "Josh Berkus"
Date:
Subject: Re: DB porting questions...
Next
From: Roberto Mello
Date:
Subject: Timezone conversion