best way to handle enum type
Hey everybody. I have a field that, in my earlier mySQL days would have
been an enum('q','y','m','c'), and I'm wondering what's the best way to
handle this in pgsql. I've googled enough to find out that pgsql doesn't
have a built in enum type (it's of course possible that what I read was
outdated, please let me know if it was), but I've found a couple popular
workarounds, the first being
CHECK (favourite_colour IN ('red', 'blue', 'yellow', 'purple'))
as well as a suggestion to create another small table containing the
possible values and then placing
color text references color
in the create table sql. Now this field doesn't absolutely have to be an
enum, I'm sure I could work with matching the values to numbers and
making it an int. What I'm wondering is what's generally considered
'best practice' for this situation. I'd like to have a solid db more
than an enum type, what's my best move?
Tom Hart wrote:
Hey everybody. I have a field that, in my earlier mySQL days would have
been an enum('q','y','m','c'), and I'm wondering what's the best way to
handle this in pgsql.
If it's an option, upgrade to 8.3 and use the new enum support.
--
Peter Eisentraut
http://developer.postgresql.org/~petere/
Peter Eisentraut wrote:
Tom Hart wrote:
Hey everybody. I have a field that, in my earlier mySQL days would have
been an enum('q','y','m','c'), and I'm wondering what's the best way to
handle this in pgsql.If it's an option, upgrade to 8.3 and use the new enum support.
Oops, I think I just got caught not doing my homework :-) Thanks for
being nice about it Peter.
I don't think I'll be able to convince my supervisor to install a beta
while we're still developing the system, but once it becomes more stable
(both my system and 8.3) then it's definitely something we'll look at.
Thanks for your reply.
Tom Hart wrote:
Peter Eisentraut wrote:
Tom Hart wrote:
Hey everybody. I have a field that, in my earlier mySQL days would have
been an enum('q','y','m','c'), and I'm wondering what's the best way to
handle this in pgsql.If it's an option, upgrade to 8.3 and use the new enum support.
Oops, I think I just got caught not doing my homework :-) Thanks for
being nice about it Peter.I don't think I'll be able to convince my supervisor to install a beta
while we're still developing the system, but once it becomes more
stable (both my system and 8.3) then it's definitely something we'll
look at.Thanks for your reply.
On a side note, I was just reading through the 8.3 changelog, (where I
read about the enum datatype) and I noticed this line
*
Widen the MONEY data type to 64 bits (D'Arcy Cain)
This greatly increases the range of supported MONEY values.
I may be mistaken, but when I started developing this system (a data
mine for a financial institution) I was told that the money datatype was
deprecated and should not be used. Is this datatype still being worked
on, and would it be viable to use in my development, as it is currently
or in preparation for 8.3?
Tom Hart wrote:
Tom Hart wrote:
Peter Eisentraut wrote:
Tom Hart wrote:
Hey everybody. I have a field that, in my earlier mySQL days would have
been an enum('q','y','m','c'), and I'm wondering what's the best way to
handle this in pgsql.If it's an option, upgrade to 8.3 and use the new enum support.
Oops, I think I just got caught not doing my homework :-) Thanks for
being nice about it Peter.I don't think I'll be able to convince my supervisor to install a beta
while we're still developing the system, but once it becomes more
stable (both my system and 8.3) then it's definitely something we'll
look at.Thanks for your reply.
On a side note, I was just reading through the 8.3 changelog, (where I
read about the enum datatype) and I noticed this line*
Widen the MONEY data type to 64 bits (D'Arcy Cain)
This greatly increases the range of supported MONEY values.
I may be mistaken, but when I started developing this system (a data
mine for a financial institution) I was told that the money datatype was
deprecated and should not be used. Is this datatype still being worked
on, and would it be viable to use in my development, as it is currently
or in preparation for 8.3?
Uh, yea, we did discuss this during 8.3 development, and you will see in
the 8.3 docs that MONEY is no longer marked as depricated.
http://momjian.us/main/writings/pgsql/sgml/datatype-money.html
Basically MONEY had some major limitations but now someone is working on
improve it so we probably will keep it.
We still have these TODO items for MONEY:
* Add locale-aware MONEY type, and support multiple currencies
http://archives.postgresql.org/pgsql-general/2005-08/msg01432.php
http://archives.postgresql.org/pgsql-hackers/2007-03/msg01181.php
* MONEY dumps in a locale-specific format making it difficult to
restore to a system with a different locale
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://postgres.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
Bruce Momjian wrote:
Tom Hart wrote:
On a side note, I was just reading through the 8.3 changelog, (where I
read about the enum datatype) and I noticed this line*
Widen the MONEY data type to 64 bits (D'Arcy Cain)
This greatly increases the range of supported MONEY values.
I may be mistaken, but when I started developing this system (a data
mine for a financial institution) I was told that the money datatype was
deprecated and should not be used. Is this datatype still being worked
on, and would it be viable to use in my development, as it is currently
or in preparation for 8.3?Uh, yea, we did discuss this during 8.3 development, and you will see in
the 8.3 docs that MONEY is no longer marked as depricated.http://momjian.us/main/writings/pgsql/sgml/datatype-money.html
Basically MONEY had some major limitations but now someone is working on
improve it so we probably will keep it.We still have these TODO items for MONEY:
* Add locale-aware MONEY type, and support multiple currencies
http://archives.postgresql.org/pgsql-general/2005-08/msg01432.php
http://archives.postgresql.org/pgsql-hackers/2007-03/msg01181.php* MONEY dumps in a locale-specific format making it difficult to
restore to a system with a different locale
Ok, that's pretty much what I was told. Our data mine doesn't need to be
aware of other locales/monetary formats atm, but given the changes that
are happening with the datatype, I think I'll just have to stick with
numeric(12,2). Are those TODO items scheduled to be worked on in 8.4 or
are they more of a 'someday' item?
Tom Hart wrote:
Ok, that's pretty much what I was told. Our data mine doesn't need to be
aware of other locales/monetary formats atm, but given the changes that are
happening with the datatype, I think I'll just have to stick with
numeric(12,2). Are those TODO items scheduled to be worked on in 8.4 or are
they more of a 'someday' item?
I think the sanest course of action is to consider all TODO items to be
of the "someday" type, unless you are planning working on them yourself
(and even then, your patch could be rejected).
--
Alvaro Herrera http://www.amazon.com/gp/registry/5ZYLFMCVHXC
"No necesitamos banderas
No reconocemos fronteras" (Jorge Gonz�lez)