pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
DB porting questions...
11+ messages / 7 participants
[nested] [flat]

* DB porting questions...
@ 2001-04-11 23:02  Diehl, Jeffrey <jdiehl@sandia.gov>
  0 siblings, 3 replies; 11+ messages in thread

From: Diehl, Jeffrey @ 2001-04-11 23:02 UTC (permalink / raw)
  To: pgsql-sql

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




^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* RE: DB porting questions...
@ 2001-04-11 23:29  Michael Ansley <Michael.Ansley@intec-telecom-systems.com>
  0 siblings, 0 replies; 11+ messages in thread

From: Michael Ansley @ 2001-04-11 23:29 UTC (permalink / raw)
  To: 'Diehl, Jeffrey ' <jdiehl@sandia.gov>; pgsql-sql

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. 
__________________________________________________________________________

^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: DB porting questions...
@ 2001-04-11 23:29  Josh Berkus <josh@agliodbs.com>
  parent: Diehl, Jeffrey <jdiehl@sandia.gov>
  2 siblings, 0 replies; 11+ messages in thread

From: Josh Berkus @ 2001-04-11 23:29 UTC (permalink / raw)
  To: Diehl, Jeffrey <jdiehl@sandia.gov>; pgsql-sql

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 information technology      josh@agliodbs.com
   and data management solutions       (415) 565-7293
  for law firms, small businesses        fax 621-2533
    and non-profit organizations.      San Francisco



^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: DB porting questions...
@ 2001-04-11 23:34  Joel Burton <jburton@scw.org>
  parent: Diehl, Jeffrey <jdiehl@sandia.gov>
  2 siblings, 0 replies; 11+ messages in thread

From: Joel Burton @ 2001-04-11 23:34 UTC (permalink / raw)
  To: Diehl, Jeffrey <jdiehl@sandia.gov>; +Cc: 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




^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* RE: DB porting questions...
@ 2001-04-12 18:31  Diehl, Jeffrey <jdiehl@sandia.gov>
  0 siblings, 0 replies; 11+ messages in thread

From: Diehl, Jeffrey @ 2001-04-12 18:31 UTC (permalink / raw)
  To: Diehl, Jeffrey <jdiehl@sandia.gov>; pgsql-sql

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 





^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* RE: DB porting questions...
@ 2001-04-12 18:50  Diehl, Jeffrey <jdiehl@sandia.gov>
  0 siblings, 0 replies; 11+ messages in thread

From: Diehl, Jeffrey @ 2001-04-12 18:50 UTC (permalink / raw)
  To: Diehl, Jeffrey <jdiehl@sandia.gov>; pgsql-sql

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 





^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: DB porting questions...
@ 2001-04-17 15:45  Vivek Khera <khera@kciLink.com>
  parent: Diehl, Jeffrey <jdiehl@sandia.gov>
  2 siblings, 1 reply; 11+ messages in thread

From: Vivek Khera @ 2001-04-17 15:45 UTC (permalink / raw)
  To: pgsql-sql

>>>>> "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.



^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: Re: DB porting questions...
@ 2001-04-18 01:40  Roberto Mello <rmello@cc.usu.edu>
  parent: Vivek Khera <khera@kciLink.com>
  0 siblings, 0 replies; 11+ messages in thread

From: Roberto Mello @ 2001-04-18 01:40 UTC (permalink / raw)
  To: Vivek Khera <khera@kciLink.com>; +Cc: pgsql-sql

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!



^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* RE: Re: DB porting questions...
@ 2001-04-18 18:28  Diehl, Jeffrey <jdiehl@sandia.gov>
  0 siblings, 0 replies; 11+ messages in thread

From: Diehl, Jeffrey @ 2001-04-18 18:28 UTC (permalink / raw)
  To: 'Vivek Khera ' <khera@kciLink.com>; pgsql-sql

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.



^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* RE: Re: DB porting questions...
@ 2001-04-18 21:06  Diehl, Jeffrey <jdiehl@sandia.gov>
  0 siblings, 1 reply; 11+ messages in thread

From: Diehl, Jeffrey @ 2001-04-18 21:06 UTC (permalink / raw)
  To: 'Rick Robino ' <rrobino@wavedivision.com>; Diehl, Jeffrey <jdiehl@sandia.gov>; +Cc: pgsql-sql

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




^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* RE: Re: DB porting questions...
@ 2001-04-18 21:47  Michael Fork <mfork@toledolink.com>
  parent: Diehl, Jeffrey <jdiehl@sandia.gov>
  0 siblings, 0 replies; 11+ messages in thread

From: Michael Fork @ 2001-04-18 21:47 UTC (permalink / raw)
  To: Diehl, Jeffrey <jdiehl@sandia.gov>; +Cc: 'Rick Robino ' <rrobino@wavedivision.com>; pgsql-sql

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>




^ permalink  raw  reply  [nested|flat] 11+ messages in thread


end of thread, other threads:[~2001-04-18 21:47 UTC | newest]

Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-04-11 23:02 DB porting questions... Diehl, Jeffrey <jdiehl@sandia.gov>
2001-04-11 23:29 ` Josh Berkus <josh@agliodbs.com>
2001-04-11 23:34 ` Joel Burton <jburton@scw.org>
2001-04-17 15:45 ` Vivek Khera <khera@kciLink.com>
2001-04-18 01:40   ` Roberto Mello <rmello@cc.usu.edu>
2001-04-11 23:29 RE: DB porting questions... Michael Ansley <Michael.Ansley@intec-telecom-systems.com>
2001-04-12 18:31 RE: DB porting questions... Diehl, Jeffrey <jdiehl@sandia.gov>
2001-04-12 18:50 RE: DB porting questions... Diehl, Jeffrey <jdiehl@sandia.gov>
2001-04-18 18:28 RE: Re: DB porting questions... Diehl, Jeffrey <jdiehl@sandia.gov>
2001-04-18 21:06 RE: Re: DB porting questions... Diehl, Jeffrey <jdiehl@sandia.gov>
2001-04-18 21:47 ` Michael Fork <mfork@toledolink.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox