Received: from fileserver002.intecsystems.co.uk ([194.200.14.125]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f3BNLv883640 for ; Wed, 11 Apr 2001 19:21:57 -0400 (EDT) (envelope-from Michael.Ansley@intec-telecom-systems.com) Received: from fileserver002.intecsystems.co.uk (unverified) by fileserver002.intecsystems.co.uk (Content Technologies SMTPRS 4.1.5) with ESMTP id ; Thu, 12 Apr 2001 00:29:12 +0100 Received: by fileserver002.intecsystems.co.uk with Internet Mail Service (5.5.2650.21) id <2VVB1G7V>; Thu, 12 Apr 2001 00:29:12 +0100 Message-ID: <7F124BC48D56D411812500D0B747251480F522@fileserver002.intecsystems.co.uk> From: Michael Ansley To: "'Diehl, Jeffrey '" , "'pgsql-sql@postgresql.org '" Subject: RE: DB porting questions... Date: Thu, 12 Apr 2001 00:29:11 +0100 MIME-Version: 1.0 X-Mailer: Internet Mail Service (5.5.2650.21) Content-Type: multipart/alternative ; boundary="----_=_NextPart_001_01C0C2DF.3673F13C" X-Archive-Number: 200104/168 X-Sequence-Number: 2516 This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible. ------_=_NextPart_001_01C0C2DF.3673F13C Content-Type: text/plain; charset="iso-8859-1" Hi, Jeff, >> 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? You need to set the default, but you have to use now() in (single) quotes, otherwise all records will use the time that the CREATE statement was executed: t timestamp not null default 'now()', ...or something close. It's in the docs somewhere too. >> 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? What you do is create the id column of type SERIAL, then import your data, and then immediately afterwards, use setval() to update the current value of the sequence to one more than the highest value that you imported. The syntax for setval() is in the docs. During the import of your data, the sequence will not be used for incrementing the id, as it's only used as the default, not if you actually provide a value. Cheers... MikeA _________________________________________________________________________ This e-mail and any attachments are confidential and may also be privileged and/or copyright material of Intec Telecom Systems PLC (or its affiliated companies). If you are not an intended or authorised recipient of this e-mail or have received it in error, please delete it immediately and notify the sender by e-mail. In such a case, reading, reproducing, printing or further dissemination of this e-mail is strictly prohibited and may be unlawful. Intec Telecom Systems PLC. does not represent or warrant that an attachment hereto is free from computer viruses or other defects. The opinions expressed in this e-mail and any attachments may be those of the author and are not necessarily those of Intec Telecom Systems PLC. This footnote also confirms that this email message has been swept by MIMEsweeper for the presence of computer viruses. __________________________________________________________________________ ------_=_NextPart_001_01C0C2DF.3673F13C Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable RE: [SQL] DB porting questions...

Hi, Jeff,
 

>>  1)
>>  I have a table:
>>      create table a (
>>         &nbs= p;    t       timestamp not nu= ll,
>>         &nbs= p;    ...
>>      );
>> 
>>  I'm thinking that I can define a.t as not= null default=3Dnow().  But >>  will
>>  this
>>  work?  That is, will it update a.t w= hen I modified a given record?
You need to set the default, but you have to use now() i= n (single) quotes, otherwise all records will use the time that the CREATE = statement was executed:

t timestamp not null default 'now()',
...or something close.  It's in the docs somewhere = too.

>>  2)
>>  I have another table:
>>      create table b (
>>         &nbs= p;    id      int not null AUTO_INC= REMENT,
>>         &nbs= p;    ...    
>>      );
>> 
>>  To reproduce this behavior, I believe I n= eed to use a sequence.  The
>>  problem
>>  is that I have a lot of data to import in= to this table.  How do I
>>  import the old data without colliding wit= h the new sequence numbers?
What you do is create the id column of type SERIAL, then= import your data, and then immediately afterwards, use setval() to update = the current value of the sequence to one more than the highest value that y= ou imported.  The syntax for setval() is in the docs.  During the= import of your data, the sequence will not be used for incrementing the id= , as it's only used as the default, not if you actually provide a value.

Cheers...


MikeA




_________________________________________________________________________ This e-mail and any attachments are confidential and may also be privileged= and/or copyright
material of Intec Telecom Systems PLC (or its affiliated companies). If yo= u are not an
intended or authorised recipient of this e-mail or have received it in erro= r, please delete
it immediately and notify the sender by e-mail. In such a case, reading, r= eproducing,
printing or further dissemination of this e-mail is strictly prohibited and= may be unlawful.
Intec Telecom Systems PLC. does not represent or warrant that an attachment= hereto is free
from computer viruses or other defects. The opinions expressed in this e-ma= il and any
attachments may be those of the author and are not necessarily those of Int= ec Telecom
Systems PLC.

This footnote also confirms that this email message has been swept by
MIMEsweeper for the presence of computer viruses.
__________________________________________________________________________<= BR>
------_=_NextPart_001_01C0C2DF.3673F13C--