Is it possible insert more than one ENUM value in a column in postgres?
e.g. In Mysql, I can do.
create table 'foo'(
'foo_id' smallint(5) unsigned NOT NULL AUTO_INCREMENT,
`foo_enum` enum('foo','bar','dummy') DEFAULT 'foo',
);
insert into 'foo' ('foo_id', 'foo_enum') values (1, 'foo, bar')
You can use CREATE TYPE to declare your enum:
CREATE TYPE tfoo AS ENUM('foo','bar','dummy');
And use an array of it to store the values:
CREATE TABLE foo (foo_id serial, foo_enum tfoo[]);
To insert:
INSERT INTO foo(foo_enum) VALUES('{foo,bar}');
Or
INSERT INTO foo(foo_enum) VALUES(ARRAY['foo','bar']::tfoo[]);
Another approach would be using another table to store the enums and a foreign key to the foo table. Example:
CREATE TABLE foo (foo_id serial primary key);
CREATE TABLE foo_enums (foo_id integer references foo(foo_id), value tfoo);
And them insert the multiple values into foo_enums:
INSERT INTO foo(foo_id) VALUES(nextval('foo_id_seq'));
INSERT INTO foo_enums(foo_id, value) VALUES
(currval('foo_id_seq'), 'foo'),
(currval('foo_id_seq'), 'bar');
'nor that stupid backtick from MySQL.'foo'is not a table name. It's string literal (even with MySQL).