pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Roberto Mello <rmello@cc.usu.edu>
To: Vivek Khera <khera@kciLink.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Re: DB porting questions...
Date: Tue, 17 Apr 2001 19:40:18 -0600
Message-ID: <20010417194018.A6239@cc.usu.edu> (raw)
In-Reply-To: <x7k84j7cor.fsf@onceler.kciLink.com>
References: <B51F0C636E578A4E832D3958690CD73E0130BEA7@es04snlnt>
	<x7k84j7cor.fsf@onceler.kciLink.com>

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!



view thread (11+ messages)  latest in thread

Message-ID: <20010417194018.A6239@cc.usu.edu>
Permalink:  ../20010417194018.A6239@cc.usu.edu/
Also on:    postgresql.org/message-id/20010417194018.A6239@cc.usu.edu

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: rmello@cc.usu.edu, khera@kciLink.com
  Subject: Re: DB porting questions...
  In-Reply-To: <20010417194018.A6239@cc.usu.edu>
  References: <B51F0C636E578A4E832D3958690CD73E0130BEA7@es04snlnt> <x7k84j7cor.fsf@onceler.kciLink.com> <20010417194018.A6239@cc.usu.edu>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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