From: | Roberto Mello <rmello(at)cc(dot)usu(dot)edu> |
---|---|
To: | Vivek Khera <khera(at)kciLink(dot)com> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: Re: DB porting questions... |
Date: | 2001-04-18 01:40:18 |
Message-ID: | 20010417194018.A6239@cc.usu.edu |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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!
From | Date | Subject | |
---|---|---|---|
Next Message | Roberto Mello | 2001-04-18 01:42:02 | Re: [SQL] Where are the 7.1 RPM's? |
Previous Message | clayton | 2001-04-18 01:12:40 | Re: [SQL] Where are the 7.1 RPM's? |