Hello,
I have to migrate a MySQL database to a PostgreSQL database without
procedures.
The problem is that this MySQL database uses ENUM, do you see what can I
do to migrate ENUM into PostgreSQL ?
Thanks in advance :-)
-------------------------------------
Bruno BAGUETTE - pg******@baguette.net
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster 8 8483
On 3 Sep 2003 at 14:30, Bruno BAGUETTE wrote: Hello,
I have to migrate a MySQL database to a PostgreSQL database without procedures.
The problem is that this MySQL database uses ENUM, do you see what can I do to migrate ENUM into PostgreSQL ?
varchar with check constraints. Add constraits to allow only certain values of
varchar string.
Check http://archives.postgresql.org/pgsql...3/msg00273.php
Bye
Shridhar
--
All new: Parts not interchangeable with previous model.
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
>>>>> "SD" == Shridhar Daithankar <sh*****************@persistent.co.in> writes:
SD> On 3 Sep 2003 at 14:30, Bruno BAGUETTE wrote: The problem is that this MySQL database uses ENUM, do you see what can I do to migrate ENUM into PostgreSQL ?
SD> varchar with check constraints. Add constraits to allow only
SD> certain values of varchar string.
I used to do this. It turns out to be horribly inflexible when you
need to alter the enum values since the constraints cannot easily be
changed.
What I do is create a short table for the enum like this:
CREATE TABLE status_levels (
status varchar(10) PRIMARY KEY
) WITHOUT OIDS;
INSERT INTO status_levels (status) VALUES ('active');
INSERT INTO status_levels (status) VALUES ('overdue');
INSERT INTO status_levels (status) VALUES ('suspended');
INSERT INTO status_levels (status) VALUES ('terminated');
then reference it via foreign key from the "enum" field:
CREATE TABLE whatever (
...
*status varchar(10) NOT NULL DEFAULT 'active' REFERENCES status_levels(status),
...
);
--
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Vivek Khera, Ph.D. Khera Communications, Inc.
Internet: kh***@kciLink.com Rockville, MD +1-240-453-8497
AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives? http://archives.postgresql.org
that's how I'd do it, since in reality, that's all an ENUM is, is a mini
foreign table internalized onto a column in another table.
Vivek Khera wrote: >>"SD" == Shridhar Daithankar <sh*****************@persistent.co.in> writes: >> >> SD> On 3 Sep 2003 at 14:30, Bruno BAGUETTE wrote:
The problem is that this MySQL database uses ENUM, do you see what can I do to migrate ENUM into PostgreSQL ?
SD> varchar with check constraints. Add constraits to allow only SD> certain values of varchar string.
I used to do this. It turns out to be horribly inflexible when you need to alter the enum values since the constraints cannot easily be changed.
What I do is create a short table for the enum like this:
CREATE TABLE status_levels ( status varchar(10) PRIMARY KEY ) WITHOUT OIDS; INSERT INTO status_levels (status) VALUES ('active'); INSERT INTO status_levels (status) VALUES ('overdue'); INSERT INTO status_levels (status) VALUES ('suspended'); INSERT INTO status_levels (status) VALUES ('terminated');
then reference it via foreign key from the "enum" field:
CREATE TABLE whatever ( ... ?status varchar(10) NOT NULL DEFAULT 'active' REFERENCES status_levels(status), ... );
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
On Wed, 2003-09-03 at 09:50, Vivek Khera wrote: >> "SD" == Shridhar Daithankar <sh*****************@persistent.co.in> writes: SD> On 3 Sep 2003 at 14:30, Bruno BAGUETTE wrote: The problem is that this MySQL database uses ENUM, do you see what can I do to migrate ENUM into PostgreSQL ? SD> varchar with check constraints. Add constraits to allow only SD> certain values of varchar string.
I used to do this. It turns out to be horribly inflexible when you need to alter the enum values since the constraints cannot easily be changed.
It'll be better when domains have alterable constraints. Your
way is the traditional (and best, IMO) way, though.
What I do is create a short table for the enum like this:
CREATE TABLE status_levels ( status varchar(10) PRIMARY KEY ) WITHOUT OIDS; INSERT INTO status_levels (status) VALUES ('active'); INSERT INTO status_levels (status) VALUES ('overdue'); INSERT INTO status_levels (status) VALUES ('suspended'); INSERT INTO status_levels (status) VALUES ('terminated');
then reference it via foreign key from the "enum" field:
CREATE TABLE whatever ( ... status varchar(10) NOT NULL DEFAULT 'active' REFERENCES status_levels(status), ... );
--
-----------------------------------------------------------------
Ron Johnson, Jr. ro***********@cox.net
Jefferson, LA USA
"You ask us the same question every day, and we give you the
same answer every day. Someday, we hope that you will believe us..."
U.S. Secretary of Defense Donald Rumsfeld, to a reporter
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Ron Johnson wrote: On Wed, 2003-09-03 at 09:50, Vivek Khera wrote:>>>> "SD" == Shridhar Daithankar <sh*****************@persistent.co.in> writes:
SD> On 3 Sep 2003 at 14:30, Bruno BAGUETTE wrote:> The problem is that this MySQL database uses ENUM, do you see what can I > do to migrate ENUM into PostgreSQL ?
SD> varchar with check constraints. Add constraits to allow only SD> certain values of varchar string.
I used to do this. It turns out to be horribly inflexible when you need to alter the enum values since the constraints cannot easily be changed.
It'll be better when domains have alterable constraints. Your way is the traditional (and best, IMO) way, though.
In 7.4 we have:
Add DOMAIN CHECK constraints (Rod)
Add ALTER DOMAIN .. SET / DROP NOT NULL, SET / DROP DEFAULT, ADD / DROP
CONSTRAINT (Rod)
--
Bruce Momjian | http://candle.pha.pa.us pg***@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives? http://archives.postgresql.org
On Wed, 2003-09-03 at 18:05, Bruce Momjian wrote: Ron Johnson wrote: On Wed, 2003-09-03 at 09:50, Vivek Khera wrote: >>>>> "SD" == Shridhar Daithankar <sh*****************@persistent.co.in> writes:
SD> On 3 Sep 2003 at 14:30, Bruno BAGUETTE wrote: >> The problem is that this MySQL database uses ENUM, do you see what can I >> do to migrate ENUM into PostgreSQL ?
SD> varchar with check constraints. Add constraits to allow only SD> certain values of varchar string.
I used to do this. It turns out to be horribly inflexible when you need to alter the enum values since the constraints cannot easily be changed.
It'll be better when domains have alterable constraints. Your way is the traditional (and best, IMO) way, though.
In 7.4 we have:
Add DOMAIN CHECK constraints (Rod) Add ALTER DOMAIN .. SET / DROP NOT NULL, SET / DROP DEFAULT, ADD / DROP CONSTRAINT (Rod)
"Rod"??
--
-----------------------------------------------------------------
Ron Johnson, Jr. ro***********@cox.net
Jefferson, LA USA
Regarding war zones: "There's nothing sacrosanct about a hotel
with a bunch of journalists in it."
Marine Lt. Gen. Bernard E. Trainor (Retired)
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)
On Wed, 3 Sep 2003, Ron Johnson wrote: On Wed, 2003-09-03 at 09:50, Vivek Khera wrote:>>>> "SD" == Shridhar Daithankar <sh*****************@persistent.co.in> writes:
SD> On 3 Sep 2003 at 14:30, Bruno BAGUETTE wrote:> The problem is that this MySQL database uses ENUM, do you see what can I > do to migrate ENUM into PostgreSQL ?
SD> varchar with check constraints. Add constraits to allow only SD> certain values of varchar string.
I used to do this. It turns out to be horribly inflexible when you need to alter the enum values since the constraints cannot easily be changed.
It'll be better when domains have alterable constraints. Your way is the traditional (and best, IMO) way, though.
Speaking of enumeration, I noticed that the old copy of the SQL3 spec I
have lists an enumerated type. Is that still in the SQL 3 spec, and if
so, are there any plans to implement it in pgsql?
Just wondering.
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives? http://archives.postgresql.org
On Wed, 3 Sep 2003, Ron Johnson wrote: It'll be better when domains have alterable constraints. Your way is the traditional (and best, IMO) way, though.
This is similar to what I have tended to use, but I have always wondered
about the efficency, and have used an int4 serial column as the primary
key and used this to reference the status data.
This has made it a little frustrating sometimes to write queries though.
Is referencing the varchar column generally reasonable in postgresql, and
not likely to give big performance issues as the tables get larger?
(sorry, this is a little hand wavy and not very exact, but I am wondering
if i am getting carried away giving everything an id)
This is the type of thing I have used: (edited from a couple of posts ago) CREATE TABLE status_levels ( status_levels_id serial primary key,
status varchar(10) ) WITHOUT OIDS; INSERT INTO status_levels (status) VALUES ('active'); INSERT INTO status_levels (status) VALUES ('overdue'); INSERT INTO status_levels (status) VALUES ('suspended'); INSERT INTO status_levels (status) VALUES ('terminated');
then reference it via foreign key from the "enum" field:
CREATE TABLE whatever ( ... status int4 NOT NULL DEFAULT 1 REFERENCES
status_levels(status_levels_id), ... );
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: gonzalo briceno |
last post by:
I have been using phplib for a while and I really like the framework
except for form creation. Maybe it is me but I in my opinion there
isn't a good way to create forms or should I say, everything...
|
by: Job Lot |
last post by:
I have an enumeration as follows
Public Enum Delimiters
Tab
Semicolon
Comma
Space
End Enum
How can I return character equivalent of the elements in the enumeration?
|
by: Neil Zanella |
last post by:
Hello,
I have seen code that does the following:
enum Letter { X, Y, Z, numLetters };
Is this considered good or bad code? On one hand, the code seems more
maintainable because no matter...
|
by: Morgan Cheng |
last post by:
Hi,
In my program module, there are some Constants should be defined to be
integer key value of std::map. In the module, methods of a few classes
will return std::map containing value indexed by...
|
by: Mullin Yu |
last post by:
At Vb6, we can do the following, and then call typing SendUsingMethod, it
will pop up SendUsingPickup and SendUsingPort option
Public Enum eEDMSMailSendUsing
SendUsingPickup = 1
SendUsingPort =...
|
by: Nadav |
last post by:
Hi,
I am tring to pass messages between threads, using the good old C++ I would call the GetMessage/PostThreadMessage APIs, Now, I am using C# and i can't find any equivalenty for these calls, any...
|
by: Arronax |
last post by:
Just a basic question after I didn't see any examples on the web other
than the "None" enum.
Is this just a way of sending "hints" to server/client, or is there
another more valuable use to...
|
by: rchenna |
last post by:
Hi All,
We have some performance issues with 'CONNECT BY PRIOR' SQL statement and trying to find an alternate SQL which gives the same output.
Example:
ENum-----------------Mgr
Julie...
|
by: Just_a_fan |
last post by:
In VB6, I could easily take the value from a combo box and make a
command with it, this: baudrate = cboBaud(listindex).
With the new dotted stuff in VB9, I can't seem to do that. Here's an...
|
by: DolphinDB |
last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation.
Take...
|
by: DolphinDB |
last post by:
Tired of spending countless mintues downsampling your data? Look no further!
In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: Vimpel783 |
last post by:
Hello!
Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
|
by: jfyes |
last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
|
by: ArrayDB |
last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
|
by: PapaRatzi |
last post by:
Hello,
I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
|
by: Shællîpôpï 09 |
last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
| |