Stories
Slash Boxes
Comments
NOTE: use Perl; is on undef hiatus. You can read content, but you can't post it. More info will be forthcoming forthcomingly.

All the Perl that's Practical to Extract and Report

use Perl Log In

Log In

[ Create a new account ]

janus (3624)

janus
  (email not shown publicly)
http://janus.errornet.de/

Journal of janus (3624)

Saturday February 28, 2009
12:56 PM

pseudo-referential integrity with ENUMs and ARRAYs

[ #38570 ]

After all the whining from users of the most popular open source
database over the years, here's some cheering about the most advanced
open source database: pseudo-referential integrity with ENUMs and ARRAYs

Have you ever been in the situation where you needed an array or didn't
want to introduce an extra mapping table for just a few well-defined
values without introducing inconcistencies?

Since PostgreSQL got ENUM support it's pretty easy to solve:

test=# CREATE TYPE foo AS ENUM ('a', 'b', 'c');
CREATE TYPE
test=# CREATE TABLE bar (
test(# id INTEGER PRIMARY KEY,
test(# foo foo[] NOT NULL DEFAULT '{a}'
test(# );
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "bar_pkey" for table "bar"
CREATE TABLE
test=#

Now try to insert unkown values:

test=# INSERT INTO bar (id, foo) VALUES (1, '{x}');
ERROR: invalid input value for enum foo: "x"
test=#

Works as expected (the common feeling when i'm working with this database btw).

It surely accepts valid values:

test=# INSERT INTO bar (id, foo) VALUES (1, '{b}');
INSERT 0 1
test=# INSERT INTO bar (id, foo) VALUES (2, '{b,c,a}');
INSERT 0 1
test=# INSERT INTO bar (id, foo) VALUES (3, '{a,b,c,a,a}');
INSERT 0 1
test=#

Let's try the default value:

test=# INSERT INTO bar (id) VALUES (4);
INSERT 0 1
test=#

And look at the table contents:

test=# SELECT * FROM bar;
  id | foo
----+-------------
    1 | {b}
    2 | {b,c,a}
    3 | {a,b,c,a,a}
    4 | {a}
(4 rows)

test=#

And of course we can only UPDATE to defined ENUM values:

test=# UPDATE bar SET foo = '{x}' WHERE id = 4;
ERROR: invalid input value for enum foo: "x"
test=# UPDATE bar SET foo = '{b}' WHERE id = 4;
UPDATE 1
test=#

So much about whining... NOT! ;-)

EDIT/P.S.: lbr pointed that extending the ENUM wouldn't be that easy.
First, i don't think you should use this approach if you're expecting the
ENUM values to change.
Second, it can be done like this:

test=# CREATE TYPE foo2 AS ENUM ('a', 'b', 'c', 'd');
CREATE TYPE
test=# ALTER TABLE bar ALTER COLUMN foo DROP DEFAULT,
test-# ALTER COLUMN foo TYPE foo2[] USING string_to_array(array_to_string(foo, ','), ',')::foo2[],
test-# ALTER COLUMN foo SET DEFAULT '{a}';
ALTER TABLE
test=#

Thanks to lbr for pointing it out and making me happy again by proving
that PostgreSQL works and works and works.... and works :-)

The Fine Print: The following comments are owned by whoever posted them. We are not responsible for them in any way.
 Full
 Abbreviated
 Hidden
More | Login | Reply
Loading... please wait.