在PostgreSQL中插入多个ENUM值 [英] Insert multiple ENUM values in PostgreSQL
本文介绍了在PostgreSQL中插入多个ENUM值的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
是否可以在postgres的列中插入多个 ENUM
值?
在Mysql中,我可以做。
创建表'foo'(
'foo_id'smallint(5)unsigned NOT NULL AUTO_INCREMENT,
`foo_enum`枚举('foo','bar','dummy')DEFAULT'foo',
);
insert into'foo'('foo_id','foo_enum')values(1,'foo,bar')
你可以使用 CREATE TYPE
来声明你的枚举:
code> CREATE TYPE tfoo AS ENUM('foo','bar','dummy');
CREATE TABLE foo(foo_id serial,foo_enum tfoo []);
要插入:
INSERT INTO foo(foo_enum)VALUES('{foo,bar}');
或
INSERT INTO foo(foo_enum)VALUES(ARRAY ['foo','bar'] :: tfoo []);
另一种方法是使用另一个表将枚举和外键存储到foo表中。例如:
CREATE TABLE foo(foo_id serial primary key);
CREATE TABLE foo_enums(foo_id integer references foo(foo_id),value tfoo);
他们将多个值插入到 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');
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');
这篇关于在PostgreSQL中插入多个ENUM值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文