Bad Data

Getting Enums out of Postgres

Tue Jul 7 05:19:50 2015

I am working on a basic ticketing system, and in the ticket table I have an "Impact" column, which should only allow a particular set of pre-defined impacts. The problem with using a numeric value for "severity" or similar is that people don't know what the numbers are supposed to mean and they tend to overstate the severity of their particular ticket.

So I have, rather than "severity", "impact". The values I have tentatively decided on are

These aren't necessarily the best names, "Inconvenient" should perhaps be separated into "Has Work Around", and "Partial Functionality" or something like that.

We would however like to sort by the impact. There are a couple of ways to do this naively.

You could cleverly choose the names of the field to be in alphabetical order. I mention this only because it occurred to me. There are probably enough synonyms that you could always make this work, but the names would more likely than not become quite tortured.

The other way is to have a separate "impact type" table with the name and a sort order or numerical level. But you'd have to join this table every time you wanted to sort or every time you wanted to get the name for the level depending on whether you chose the name or the number to be the primary key.

Neither of these solutions is very elegant. So, enter the enum.

We can create an enum thus:

create type impact as enum (
    'Informational',
    'Inconvenient',
    'Unusable',
    'Production Outage'
);

Which allows us to sort, compare, and generally use the labels instead of numbers. Internally these are stored in four bytes and postgres handles the conversions for you. They're effectively a 32 bit float as far as the storage is concerned.

So far so good, but I'm writing an external application which needs to know what order the enum labels are in. Fortunately, it is simple to query the catalogs and get this information. I wrote a helper function:

create function enumlist(enumname text) returns
setof pg_enum language 'sql' as $$
    select * from pg_enum where enumtypid = enumname::regtype::oid
$$ stable strict;

Which can be used to get the labels and sort order for them for use by an external application.

% psql -c "select * from enumlist('impact')"
 enumtypid | enumsortorder |     enumlabel     
-----------+---------------+-------------------
  19525184 |             1 | Informational
  19525184 |             2 | Inconvenient
  19525184 |             3 | Unusable
  19525184 |             4 | Production Outage
(4 rows)

% 

The advantage of this is that you don't have to hard-code the values anywhere, and the database is the master repository of your label definitions. If your applications query this as needed, any change in the database will be automatically reflected in your application logic and displays. Any time you can avoid a double edit, that's a win for maintainability.

Posted in / software / postgres

75 comments.

Comments

order amoxicillin uk

Sun Nov 21 22:55:10 2021

taking prednisone alternatives to prednisone for allergies what are the worst side effects of prednisone

ivermectin ireland

Thu Nov 25 00:52:50 2021

ivermectin dose dogs stromectol online canada ivermectin for dogs with mange why doesnt ivermectin require prescription

lasix pill

Thu Nov 25 16:01:12 2021

amoxicillin tooth infection buy amoxicilin online amoxicillin cause diarrhea amoxil instructs a purple minion

chloroquine tablet

Fri Nov 26 18:45:30 2021

poxet dapoxetine 60mg priligy online australia vip rxshop net viagra dapoxetine what color should dapoxetine tablets be?

stromectol generic

Sat Nov 27 05:59:48 2021

plaquenil generic name hydroxychloroquine 800 mg plaquenil - avoiding stomach diarrhea how long until plaquenil starts working

ivermectin ebay

Sat Nov 27 15:28:54 2021

use of furosemide generic lasix 40 mg pastillas lasix furosemide 40 mg a nurse is caring for a client who has heart failure and is taking oral furosemide

gabapentin price

Sun Nov 28 05:40:16 2021

zithromax tab buy azithromycin 500 what std does azithromycin treat what does azithromycin look like

albuterol in mexico

Sun Nov 28 11:39:39 2021

zithromax diarrhea azithromycin 250mg generic cost zithromax over the counter cvs what does azithromycin do

lasix cheap

Mon Nov 29 04:52:10 2021

neurontin effects neurontin prescription what is neurontin used for pain what is gabapentin for dogs

neurontin 600mg

Mon Nov 29 07:39:27 2021

azithromycin with food zithromax 600 mg tablets is azithromycin related to penicillin what not to eat while taking azithromycin

modafinil provigil

Tue Nov 30 05:10:48 2021

amoxil and beer how to buy amoxicillin online amoxicillin and doxycycline fishbiotic amoxicillin 500mg

zithromax buying

Tue Nov 30 05:42:43 2021

albuterol warnings ventolin price us is salbutamol the same as ventolin how does albuterol lower potassium

amoxil 875

Wed Dec 1 01:48:51 2021

plaquenil and bleeding hydroxychloroquine tablet ip 200 mg is hydroxychloroquine the same as plaquenil what does plaquenil treat

modafinil online

Wed Dec 1 05:36:25 2021

define furosemide lasix tablet cost is lasix the same as furosemide how much burdock root should i take to replace 40 mg of furosemide?

stromectol online

Thu Dec 2 04:52:47 2021

neurontin sleep neurontin cost increasing dose of neurontin for peripheral neuropathy how does gabapentin help with pain

prednisone 9 mg

Wed Dec 8 07:21:51 2021

metformin back pain glucophage 750 mg benefits of metformin 500 mg how dies metformin work

prednisone without rx

Fri Dec 10 21:45:13 2021

levitra 20mg pills buying levitra online taking levitra after eating levitra online bestellen ohne rezept

commander cialis

Fri Dec 10 21:48:57 2021

ivermectin human dose order stromectol online without prescription how to inject ivermectin in a pig how long does ivermectin stay in manure after dosing

cialis 20 mg prix

Mon Dec 13 04:54:14 2021

antidepresseur et viagra viagra gГ©nГ©rique pharmacie viagra en ligne livraison rapide viagra 25 50 ou 100 mg

cialis 5 mg generico

Mon Dec 13 10:18:55 2021

albuterol saba albuterol tablets online pharmacy ventolin hfa over the counter how many puffs in a ventolin hfa inhaler

plaquenil uk

Thu Dec 16 00:23:09 2021

benefits of metformin metformin tablets where to buy metformin weight loss pcos success stories metformin how does it work

cialis livraison 24h

Thu Dec 16 16:16:49 2021

plaquenil covid dosing hydroxychloroquine 40 mg how long for plaquenil to work how to switch from plaquenil to methotrexate

viagra apotheke preis

Sat Dec 18 10:45:38 2021

ivermectin and lice ivermectin iv what is ivermectin used for in dogs how to treat scabies with ivermectin

cialis italia

Sun Dec 19 00:50:13 2021

sex mit cialis cialis ersatz kaufen cialis in polen rezeptfrei cialis wirkung wann

stromectol ivermectin

Tue Dec 21 13:44:20 2021

natural viagra canada viagra online prescription best over the counter viagra substitute what happens if women take viagra

viagra crema

Thu Dec 23 10:25:30 2021

cialis reviews 5mg tadalafil no prescription when will generic cialis be available what happens if a woman takes viagra or cialis

viagra online

Thu Dec 23 21:09:28 2021

lupus treatment plaquenil plaquenil 200 mg 60 tab can plaquenil cause eye twitching what is the maximum dose of plaquenil

viagra livraison 24h

Sun Dec 26 03:22:22 2021

vimeo visual viagra new viagra commercials how to take liquid cialis why do men take viagra

viagra cialis

Sun Dec 26 19:04:05 2021

plaquenil dosage plaquenil for psoriatic arthritis why is plaquenil so expensive what is a screening eye exam for plaquenil

quineprox 200 mg

Tue Dec 28 07:07:23 2021

viagra dangereux viagra france pharmacy acheter viagra en ligne paypal viagra 25 mg ou 50 mg

new viagra

Wed Dec 29 16:49:13 2021

plaquenil hair growth quineprox 0.5 treated with plaquenil and methotrexate arthritis where can i buy plaquenil without a doctor prescription

cheap generic viagra

Thu Dec 30 13:18:18 2021

viagra from india mixing cialis and viagra where to buy female viagra where buy viagra

prix cialis 20 mg

Sat Jan 1 01:23:55 2022

cialis compresse cialis dosaggio meglio cialis originale o generico come si chiama il farmaco generico del cialis

vendita online cialis

Sat Jan 1 19:13:07 2022

spedra o cialis cialis generico quando in commercio cialis 5 mg prezzo in farmacia italia quanti tipi di cialis ci sono

20 mg sildenafil

Mon Jan 3 09:53:17 2022

viagra soft new viagra cialis and high blood pressure what is viagra for females

is cialis generic

Tue Jan 4 01:19:21 2022

ques cialis cialis generico 5 mg espaГ±a viagra cialis levitra comparacion precios donde comprar cialis seguro en espaГ±a

plaquenil sulfate

Wed Jan 5 16:47:03 2022

ivermectin medicine ivermectin 200 mcg ivermectin tablet dosage for humans ivermectin what products

viagra oral

Thu Jan 6 04:12:13 2022

cialis weight loss viagra prices australia cost of cialis in australia where to buy viagra sydney

plaquenil medication

Sat Jan 8 03:02:17 2022

viagra paypal using viagra where can i buy cialis without prescription when to take cialis 20mg for best results

acquistare cialis

Sat Jan 8 23:07:42 2022

recette viagra maison viagra 25 mg difference entre viagra et spedra quelle difference entre viagra 50 et 100

where to get viagra

Tue Jan 11 11:30:05 2022

cvs plaquenil plaquenil price plaquenil dosage for malaria prevention how did plaquenil help you

stromectol tablets uk

Thu Jan 13 00:03:04 2022

lobivon viagra comprare viagra senza ricetta il viagra si puo comprare in farmacia quanto tempo prima prendere il viagra

cheap viagra

Sat Jan 15 08:12:28 2022

cialis maximum dosage on line viagra does cialis have a generic how much for cialis

liquid viagra

Mon Jan 17 05:26:56 2022

cialis sur internet cialis 10mg cialis 5mg boite de 84 combien coute une boite de cialis 20mg

tadalafil

Wed Jan 19 02:31:58 2022

viagra online contrassegno sandoz viagra viagra fa male alla prostata cosa fa il viagra

cialis costo

Sat Jan 22 13:46:18 2022

viagra mechanism womens viagra australia buy kamagra 100mg oral jelly where do u buy viagra

aralen online

Sat Jan 29 04:58:15 2022

molnupiravir tablets in india molnupiravir tablets molnupiravir fda label indonesia molnupiravir

clomid tablets

Sat Jan 29 05:14:29 2022

zanaflex without prescription 10 zanaflex does zanaflex work for sleep what is zanaflex 4 mg used for?

aralen 50mg

Mon Jan 31 09:06:58 2022

virginia latisse bimatoprost reviews latisse side effects blue eyes what is the actual name of latisse

aralen tabs

Mon Jan 31 10:56:31 2022

nolvadex sperm production nolvadex generic cost will 20mg nolvadex cause eye problems steroidology how to use nolvadex pct

buy clomid pills

Wed Feb 2 12:01:35 2022

nolvadex bodybuilding dosage nolvadex order online why do people get drowsy from nolvadex how to dose nolvadex for pct

buy clomid india

Wed Feb 2 16:13:52 2022

latisse 3ml price where can i buy latisse prevage eyelash serum vs latisse how long does it take to see latisse work

merck covid pill

Fri Feb 4 17:55:12 2022

gyno nolvadex nolvadex rx where to clomid or nolvadex how much nolvadex to take when on trt

baricitinib fda

Sat Feb 5 00:06:02 2022

jak olumiant xeljanz baricitinib cost baricitinib agreement ruxolitinib eli lilly graft versus host olumiant fda approval

nolvadex drug

Sun Feb 6 21:41:52 2022

molnupiravir tablet price molnupiravir 400mg buy molnupiravir online molnupiravir youtube

molnupiravir order

Mon Feb 7 05:25:58 2022

neulash vs latisse latisse prescriptions can latisse change your eye color how long does large bottle of latisse last

molnupiravir stock

Wed Feb 9 01:39:59 2022

clomid endometriosis buy clomid online tomber enceinte rapidement avec clomid when you can ovulation after taking clomid

buy aralen cheap

Wed Feb 9 11:23:43 2022

zanaflex tinnitus tizanidine 2018 zanaflex vs flexeril for fibromyalgia what are zanaflex

latisse online

Fri Feb 11 06:49:21 2022

molnupiravir tablet covid pill molnupiravir dosing molnupiravir ribonucleoside analog

zanaflex 6 mg

Fri Feb 11 18:07:15 2022

liquid nolvadex nolvadex oral hcg clomid nolvadex t the same time what is the benefit of nolvadex

sesuatu seperti ini

Fri Feb 18 15:19:08 2022

Kami telah menyusun list dari finery parole slot tanpa stored penawaran baik sekarang , benar-benar di sini. Semua bonus ditawarkan oleh principle dan kasino honorable yang terima telah diuji dan diverifikasi sebagai sah lalu ahli kami.

Tanpa putaran consign free, get paid secara tidak sengaja untuk mendramatisir selama gratis dan untung nyata koin tanpa harus melakukan deposit. Dan jika Anda tidak yakin yang pendaftaran Anda dapat memilih, Anda dapat memeriksa luar negeri tanpa syarat dan ambil akrab dengan beberapa yang paling di terlebih dahulu.

zeus olympus slot

Setelah selesai, Anda dapat memilih no share favorit Anda dan mulai bermain slot unlock dengan extra open pergi.

SlonRum

Wed May 25 05:33:28 2022

8llke

cgqgz

kp5y

SlonRum

Thu May 26 03:26:20 2022

syipe

6dx8d

gm8n

SlonRum

Fri May 27 11:06:54 2022

kze6i

bsida

goxo

SlonRum

Sun May 29 09:36:53 2022

8zbag

i7j97

f19v

SlonRum

Tue May 31 08:16:12 2022

4ywxz

37xq1

3cm3

SlonRum

Thu Jun 2 07:00:54 2022

1ql1w

pwrnw

fenn

zanaflex 4

Thu Jun 16 13:29:52 2022

zithromax strep throat zithromax cost uk how long does zithromax take to work what is azithromycin 500mg used to treat

cartia xr

Thu Jun 16 16:43:26 2022

zanaflex withdrawl zanaflex 48 mg zanaflex 4 mg vs diclofenac what test would you take if you took to many zanaflex

dapoxetine price

Sun Jun 19 15:06:10 2022

cialis no prescription buy cialis online safely how long to take cialis before what is female viagra

neurontin pfizer

Wed Jun 22 11:46:24 2022

kamagra en france kamagra jelly on sale jak dziaЕ‚a kamagra na kobietД™ where to find kamagra oral jelly in angeles

8mg zanaflex

Wed Jun 22 18:45:05 2022

robaxin vs zanaflex zanaflex for headaches can a dog die from eating zanaflex how to prep zanaflex for injector

Mileskit

Sun Jun 26 11:21:20 2022

электрический штабелер http://elektroshtabeler-kupit.ru/

cialis discount card

Mon Jun 27 03:15:50 2022

propecia medicine finasteride in india does propecia halt muscle development how can i get proscription for propecia

latisse 5ml

Mon Jun 27 05:34:35 2022

latisse for glaucoma bimatoprost, intracameral implant i used latisse while pregnant where to get prescription for latisse

Comment on this post.

Name
Comment