Thread: DB porting questions...

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?

Thanx in advance,
Mike Diehl,
Network Monitoring Tool Devl.
284-3137
jdiehl@sandia.gov

<p><font size="2">Hi, Jeff,</font><br /><font size="2"> </font><p><font size="2">>>  1)</font><br /><font
size="2">>> I have a table:</font><br /><font size="2">>>      create table a (</font><br /><font
size="2">>>             t       timestamp not null,</font><br /><font size="2">>>             
...</font><br/><font size="2">>>      );</font><br /><font size="2">>>  </font><br /><font
size="2">>> I'm thinking that I can define a.t as not null default=now().  But >>  will</font><br /><font
size="2">>> this</font><br /><font size="2">>>  work?  That is, will it update a.t when I modified a given
record?</font><br/><font size="2">You need to set the default, but you have to use now() in (single) quotes, otherwise
allrecords will use the time that the CREATE statement was executed:</font><p><font size="2">t timestamp not null
default'now()',</font><br /><font size="2">...or something close.  It's in the docs somewhere too.</font><p><font
size="2">>> 2)</font><br /><font size="2">>>  I have another table:</font><br /><font size="2">>> 
   create table b (</font><br /><font size="2">>>              id      int not null AUTO_INCREMENT,</font><br
/><fontsize="2">>>              ...     </font><br /><font size="2">>>      );</font><br /><font
size="2">>> </font><br /><font size="2">>>  To reproduce this behavior, I believe I need to use a
sequence. The</font><br /><font size="2">>>  problem</font><br /><font size="2">>>  is that I have a lot of
datato import into this table.  How do I </font><br /><font size="2">>>  import the old data without colliding
withthe new sequence numbers?</font><br /><font size="2">What you do is create the id column of type SERIAL, then
importyour data, and then immediately afterwards, use setval() to update the current value of the sequence to one more
thanthe highest value that you imported.  The syntax for setval() is in the docs.  During the import of your data, the
sequencewill not be used for incrementing the id, as it's only used as the default, not if you actually provide a
value.</font><p><fontsize="2">Cheers...</font><br /><p><font size="2">MikeA</font><br /><code><font size="3"><br /><br
/>_________________________________________________________________________<br /> This e-mail and any attachments are
confidentialand may also be privileged and/or copyright <br /> material of Intec Telecom Systems PLC (or its affiliated
companies).If you are not an <br /> intended or authorised recipient of this e-mail or have received it in error,
pleasedelete <br /> it immediately and notify the sender by e-mail. In such a case, reading, reproducing, <br />
printingor further dissemination of this e-mail is strictly prohibited and may be unlawful. <br /> Intec Telecom
SystemsPLC. does not represent or warrant that an attachment hereto is free <br /> from computer viruses or other
defects.The opinions expressed in this e-mail and any <br /> attachments may be those of the author and are not
necessarilythose of Intec Telecom <br /> Systems PLC. <br /><br /> This footnote also confirms that this email message
hasbeen swept by<br /> MIMEsweeper for the presence of computer viruses. <br />
__________________________________________________________________________<br/></font></code> 

Mike,
You'll be overjoyed to know that both of your questions have simple
answers.

> 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?

No.  Defaults only take effect when you INSERT a record, and only if you
don't supply a value.  Thus, a.t will be updated with the time each new
record was added.  If you want the time a record was modified, you need
to add an update trigger to the table that auto-updates the t field
whenever other changes are made.

See the development documentation for information on writing triggers.

> 
> 
> 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?

Not a problem at all.  Sequence numbers are merely defaults, and may be
overridden by a specific insert.  Thus:

1. Create the id field as type SERIAL.
2. Insert your records into the new table, including the ID value.
3. Crank up the SERIAL sequence to the number of the highest ID present:
SELECT SETVAL('b_id_seq',10315);

-Josh Berkus



______AGLIO DATABASE SOLUTIONS___________________________                                      Josh Berkus Complete
informationtechnology      josh@agliodbs.com  and data management solutions       (415) 565-7293 for law firms, small
businesses       fax 621-2533   and non-profit organizations.      San Francisco
 

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

Between Josh Berkus and Michael Ansley, I believe I know how to do what I
want to do.  Thanx guys! 

Mike Diehl, 
Network Monitoring Tool Devl. 
284-3137 
jdiehl@sandia.gov 

I forgot to mention Joel Burton.  His input was very informative.
I figure that these people took the time to help me, I should at least try
to show my appreciation.
Thanx again,
Mike Diehl, 
Network Monitoring Tool Devl. 
284-3137 
jdiehl@sandia.gov 

-----Original Message-----
From: Diehl, Jeffrey 
Sent: April 12, 2001 12:31 PM
To: Diehl, Jeffrey; 'pgsql-sql@postgresql.org '
Subject: RE: [SQL] DB porting questions...


Between Josh Berkus and Michael Ansley, I believe I know how to do what I
want to do.  Thanx guys! 

Mike Diehl, 
Network Monitoring Tool Devl. 
284-3137 
jdiehl@sandia.gov 

>>>>> "JD" == Jeffrey Diehl <jdiehl@sandia.gov> writes:

JD> I'm in the final stages of migrating from mysql to postgres and have a few
JD> more questions...

I'm just starting, but I've got two questions.  I've found some
scripts out there that claim to do the conversion of the SQL create
commands, but none does the right thing it seems.

I've now found out how to handle the timestamp for insert times and
how to do auto-increment fields.

My unsderstanding of MySQL's enum type is to use something like this
in postgres:
owner_status varchar(9) check        (owner_status in ('pending','active','suspended'))       NOT NULL default
'pending',

But how does one handle the "set" dataype?  The archive for the
mailing lists is not helping me find out how to deal with that.

Basically, I have a field with a bunch of flags defining the
attributes of a user, and storing that in a bit-field makes sense.
Currently in MySQL I have this:
owner_features set('premium','haveccinfo') default NULL,

for example.  Some other fiels may have about 20 such values, and
MySQL lets me keep these in 3 bytes as a bit-field behind the scenes.

From what I see, my choice in Postgres is to store this as a
comma-separated string and let my application work as before.

Does anyone have a script that actually handles properly doing auto
increments with the SERIAL type, and does the set/enum conversions?

-- 
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Vivek Khera, Ph.D.                Khera Communications, Inc.
Internet: khera@kciLink.com       Rockville, MD       +1-240-453-8497
AIM: vivekkhera Y!: vivek_khera   http://www.khera.org/~vivek/

On Tue, Apr 17, 2001 at 11:45:08AM -0400, Vivek Khera wrote:
> I'm just starting, but I've got two questions.  I've found some
> scripts out there that claim to do the conversion of the SQL create
> commands, but none does the right thing it seems.
Please help better these scripts then. That way you're helping
everybody, including yourself (with gained experience).
> I've now found out how to handle the timestamp for insert times and
> how to do auto-increment fields.
> My unsderstanding of MySQL's enum type is to use something like this
> in postgres:
> 
>  owner_status varchar(9) check 
>         (owner_status in ('pending','active','suspended'))
>         NOT NULL default 'pending',
That's standard SQL, which PostgreSQL supports. You could use this
same statement in Oracle, or other compliant DBs.
> Currently in MySQL I have this:
> 
>  owner_features set('premium','haveccinfo') default NULL,
> 
> for example.  Some other fiels may have about 20 such values, and
> MySQL lets me keep these in 3 bytes as a bit-field behind the scenes.
MySQL is helping you get into trouble by giving you a non-standard way
to do something for which there's a standard.

> >From what I see, my choice in Postgres is to store this as a
> comma-separated string and let my application work as before.
For columns with more than a couple values, I'd suggest normalizing
your tables. In the "owner_features" case above, you could do something
like:
create table owner_features (    feature_id serial         constraint owner_features_pk primary key,    feature
varchar(30)       constraint owner_features_feature_nn not null);
 
Then your table would just reference owner_features.feature_id. Much
cleaner, especially for tables with lots of cases.

> Does anyone have a script that actually handles properly doing auto
> increments with the SERIAL type, and does the set/enum conversions?
What do you mean by "propely doing auto increments"? What's the
problem you are having?
-Roberto
-- 
+----| http://fslc.usu.edu USU Free Software & GNU/Linux Club |------+ Roberto Mello - Computer Science, USU -
http://www.brasileiro.net      http://www.sdl.usu.edu - Space Dynamics Lab, Developer    
 
Linux: What Windows will NEVER BE!

Well, I'm glad that my efforts to climb the learning curve are helping
others... <grin>

I'm not aware of any scripts which will implement sets in psql.  But, the
underlying implementation of a set is quite simple and could be done in a
pl/sql function or application code.

Conceptually, you assign numeric values to that attributes in the set:
red=1, white=2, blue=4, plaid=8, etc.  Then you add the values of the
attributes which are in the set.

Var = red + white = 1 + 2 = 3

Write a function which tests if a given attribute is in the set...

If you need more, lemme know, I'll try to help.

BTW, I have a psql database which gets more than 5 Million inserts a day.
Is there anyone with a larger database?  Is there any interest in comments
on running such a large database.  No, I can't tell you what it does in much
detail.

Take care,
Mike Diehl. 

-----Original Message-----
From: Vivek Khera
To: pgsql-sql@postgresql.org
Sent: 4/17/2001 9:45 AM
Subject: [SQL] Re: DB porting questions...

>>>>> "JD" == Jeffrey Diehl <jdiehl@sandia.gov> writes:

JD> I'm in the final stages of migrating from mysql to postgres and have
a few
JD> more questions...

I'm just starting, but I've got two questions.  I've found some
scripts out there that claim to do the conversion of the SQL create
commands, but none does the right thing it seems.

I've now found out how to handle the timestamp for insert times and
how to do auto-increment fields.

My unsderstanding of MySQL's enum type is to use something like this
in postgres:
owner_status varchar(9) check        (owner_status in ('pending','active','suspended'))       NOT NULL default
'pending',

But how does one handle the "set" dataype?  The archive for the
mailing lists is not helping me find out how to deal with that.

Basically, I have a field with a bunch of flags defining the
attributes of a user, and storing that in a bit-field makes sense.
Currently in MySQL I have this:
owner_features set('premium','haveccinfo') default NULL,

for example.  Some other fiels may have about 20 such values, and
MySQL lets me keep these in 3 bytes as a bit-field behind the scenes.

From what I see, my choice in Postgres is to store this as a
comma-separated string and let my application work as before.

Does anyone have a script that actually handles properly doing auto
increments with the SERIAL type, and does the set/enum conversions?

-- 
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Vivek Khera, Ph.D.                Khera Communications, Inc.
Internet: khera@kciLink.com       Rockville, MD       +1-240-453-8497
AIM: vivekkhera Y!: vivek_khera   http://www.khera.org/~vivek/

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command   (send "unregister YourEmailAddressHere" to
majordomo@postgresql.org)

Yes, it is being used to store/analyze a high-traffic log.  I have a table
for each day.  I keep about 30-45 days worth of data.  At the end of 45
days, I'm going to dump the data to a directory that gets backed up to tape,
then I simply drop the table.

I had to turn indexing off because I was unable to insert fast enough to
keep up with the data source.  I also had to do a copy into instead of a
insert into.  I still have to option of indexing the older day's tables.  

Querying the entire database is difficult, and very important to me.  I
tried to setup a "view of a union" scheme.  That isn't supported.  Right now
I am using a perl function to rewrite my sql in such a way that it queries
any of the tables I want and coelesces the output.  Aggregate functions even
work with this method.

I'm in the process of expanding from 115Gb to 362Gb of drive space.  This
will help... <grin>

If you have any other questions, please feel free.

Mike Diehl. 

-----Original Message-----
From: Rick Robino
To: Diehl, Jeffrey
Sent: 4/18/2001 1:35 PM
Subject: Re: [SQL] Re: DB porting questions...

Jeffrey,

I was interested in your comment below which says one of your db's gets
5
million inserts a day. I was wondering, are those rows cumulative? I get
this
picture in my head that you have a few tables in this database and that
the
whole thing is growing _alot_ every day.

Or maybe these daily inserts get transferred or concentrated... I ask
because I
have seen many people apparently making databases to take high-traffic
logfiles
as input. I've always thought this interesting, but didn't think that
pgsql (or
any db) would be in very good shape after a "bazillion" rows added up at
the end
of a month, or quarter, etc.

Just curious. Cheers,

--Rick

"Diehl, Jeffrey" wrote:

> Well, I'm glad that my efforts to climb the learning curve are helping
> others... <grin>
>
> I'm not aware of any scripts which will implement sets in psql.  But,
the
> underlying implementation of a set is quite simple and could be done
in a
> pl/sql function or application code.
>
> Conceptually, you assign numeric values to that attributes in the set:
> red=1, white=2, blue=4, plaid=8, etc.  Then you add the values of the
> attributes which are in the set.
>
> Var = red + white = 1 + 2 = 3
>
> Write a function which tests if a given attribute is in the set...
>
> If you need more, lemme know, I'll try to help.
>
> BTW, I have a psql database which gets more than 5 Million inserts a
day.
> Is there anyone with a larger database?  Is there any interest in
comments
> on running such a large database.  No, I can't tell you what it does
in much
> detail.
>
> Take care,
> Mike Diehl.
>
> -----Original Message-----
> From: Vivek Khera
> To: pgsql-sql@postgresql.org
> Sent: 4/17/2001 9:45 AM
> Subject: [SQL] Re: DB porting questions...
>
> >>>>> "JD" == Jeffrey Diehl <jdiehl@sandia.gov> writes:
>
> JD> I'm in the final stages of migrating from mysql to postgres and
have
> a few
> JD> more questions...
>
> I'm just starting, but I've got two questions.  I've found some
> scripts out there that claim to do the conversion of the SQL create
> commands, but none does the right thing it seems.
>
> I've now found out how to handle the timestamp for insert times and
> how to do auto-increment fields.
>
> My unsderstanding of MySQL's enum type is to use something like this
> in postgres:
>
>  owner_status varchar(9) check
>         (owner_status in ('pending','active','suspended'))
>         NOT NULL default 'pending',
>
> But how does one handle the "set" dataype?  The archive for the
> mailing lists is not helping me find out how to deal with that.
>
> Basically, I have a field with a bunch of flags defining the
> attributes of a user, and storing that in a bit-field makes sense.
> Currently in MySQL I have this:
>
>  owner_features set('premium','haveccinfo') default NULL,
>
> for example.  Some other fiels may have about 20 such values, and
> MySQL lets me keep these in 3 bytes as a bit-field behind the scenes.
>
> >From what I see, my choice in Postgres is to store this as a
> comma-separated string and let my application work as before.
>
> Does anyone have a script that actually handles properly doing auto
> increments with the SERIAL type, and does the set/enum conversions?
>
> --
> =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
> Vivek Khera, Ph.D.                Khera Communications, Inc.
> Internet: khera@kciLink.com       Rockville, MD       +1-240-453-8497
> AIM: vivekkhera Y!: vivek_khera   http://www.khera.org/~vivek/
>
> ---------------------------(end of
broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to
majordomo@postgresql.org)
>
> ---------------------------(end of
broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster<<Card for Rick Robino>> 

On Wed, 18 Apr 2001, Diehl, Jeffrey wrote:

<snip>
> Querying the entire database is difficult, and very important to me.  
> I tried to setup a "view of a union" scheme.  That isn't supported.  
> Right now I am using a perl function to rewrite my sql in such a way
> that it queries any of the tables I want and coelesces the output.  
> Aggregate functions even work with this method.
</snip>

To get around this same problem, I created a base table from which all
other tables were inherited.  Doing this allows for you to query on 1
table, or all data easily.

i.e.

CREATE TABLE wwwlogs (id INT4, url TEXT);
CREATE TABLE wwwlogs_041801 INHERITS (wwlogs);
CREATE TABLE wwwlogs_041701 INHERITS (wwlogs);

HTH...

Michael Fork - CCNA - MCP - A+
Network Support - Toledo Internet Access - Toledo Ohio


<snip>
> Querying the entire database is difficult, and very important to me.  I
> tried to setup a "view of a union" scheme.  That isn't supported.  Right now
> I am using a perl function to rewrite my sql in such a way that it queries
> any of the tables I want and coelesces the output.  Aggregate functions even
> work with this method.
</snip>