473,839 Members | 1,362 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

What is the good equivalent for ENUM ?

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******@baguet te.net
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 11 '05 #1
8 8515
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

Nov 11 '05 #2
>>>>> "SD" == Shridhar Daithankar <sh************ *****@persisten t.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(s tatus),
...
);


--
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Vivek Khera, Ph.D. Khera Communications, Inc.
Internet: kh***@kciLink.c om 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

Nov 11 '05 #3
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************ *****@persisten t.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(s tatus),
...
);


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 11 '05 #4
On Wed, 2003-09-03 at 09:50, Vivek Khera wrote:
>> "SD" == Shridhar Daithankar <sh************ *****@persisten t.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(s tatus),
...
);


--
-----------------------------------------------------------------
Ron Johnson, Jr. ro***********@c ox.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

Nov 11 '05 #5
Ron Johnson wrote:
On Wed, 2003-09-03 at 09:50, Vivek Khera wrote:
>>>> "SD" == Shridhar Daithankar <sh************ *****@persisten t.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.ph a.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

Nov 11 '05 #6
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************ *****@persisten t.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***********@c ox.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 YourEmailAddres sHere" to ma*******@postg resql.org)

Nov 11 '05 #7
On Wed, 3 Sep 2003, Ron Johnson wrote:
On Wed, 2003-09-03 at 09:50, Vivek Khera wrote:
>>>> "SD" == Shridhar Daithankar <sh************ *****@persisten t.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

Nov 11 '05 #8


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_i d 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(s tatus_levels_id ), ...
);

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postg resql.org so that your
message can get through to the mailing list cleanly

Nov 11 '05 #9

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
2741
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 else is so well done that the way you create forms seems to be too cumbersome, in particular making it so that a pull down menu selects a value that you assign it. In any case, does anyone know of any php based (or other readily accepted web...
2
2144
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?
5
1330
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 whether letters are added or removed the value of numLetters will be correct (as long as no letter is given a number with the equal sign which makes this untrue).
7
2491
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 constant integer key value. I am wondering what is a good way to define these constants. 1) #define A = 0 #define B = 1 #define C = 2
2
1655
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 = 2 End Enum Public Sub SendUsingMethod(ByVal eSendUsingMethod As eEDMSMailSendUsing) .....
9
13465
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 Idea how to communicate between threads and access the threads message queue will be appriciated... -- Nadav http://www.ddevel.com
3
3507
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 this? I'm speaking of the following: socket.BeginReceive(data, 0, 255, SocketFlags.None, new AsyncCallback(ReceiveCallback), data);
0
1952
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 Andrew---------------Julie Mark-----------------Andrew
14
1785
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 example of my problem. I have a combo box with various baud rates in it. The user selects one and I want to use it. However, the command to use it is in the format:
0
9698
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10589
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10654
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10297
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9426
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7833
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7021
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5867
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
3
3136
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.