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 :-)
++++++++
Pg is a really nice tool.
