英文:
How to insert a enum field with default value postgresql?
问题
这里我需要为我的枚举类型设置默认值,以防出现空值。
默认情况下,它不起作用。我在创建表时设置了默认值。
如果我在值中传递默认值,那么默认值将出现,但是我既有空数据又有枚举数据作为输入。
目前我是这样传递的:
INSERT INTO table(enum_value_1, enum_value_2)
VALUES (NULL, 'enum_value')
但是我希望数据如下所示:
INSERT INTO table(enum_value_1, enum_value_2)
VALUES ('enum_value_default', 'enum_value')
创建表的命令如下:
CREATE TYPE enum_candidate_status AS ENUM('attended', 'selected', 'rejected', 'not attended');
CREATE TYPE enum_rating AS ENUM('1', '2', '3', '4', '5');
CREATE TABLE CANDIDATE (
candidate_id SERIAL PRIMARY KEY,
first_name varchar(100),
candidate_status enum_candidate_status DEFAULT 'not attended',
interview_rating enum_rating
);
英文:
Here I need the default value for my enums if a null value comes.
by default, it is not working. I set the default value while I created the table.
if I pass default in values the default value will appear, but I have both null data and enum data for inputs.
INSERT INTO table(enum_value_1, enum_value_2)
VALUES (?, ?)
currently i am passing like this
INSERT INTO table(enum_value_1, enum_value_2)
VALUES (NULL, 'enum_value')
enum_value_1 | enum_value_2 |
---|---|
NULL | enum_value |
but i need data like
enum_value_1 | enum_value_2 |
---|---|
enum_value_default | enum_value |
Create table command
CREATE TYPE enum_candidate_status AS ENUM('attended', 'selected', 'rejected', 'not attended');
CREATE TYPE enum_rating AS ENUM('1', '2', '3', '4', '5');
CREATE TABLE CANDIDATE (
candidate_id SERIAL PRIMARY KEY,
first_name varchar(100),
candidate_status enum_candidate_status DEFAULT 'not attended',
interview_rating enum_rating,
);
答案1
得分: 3
为了管理这个,你必须使用函数和触发器。
请注意并按照以下步骤操作。我会使用你的示例来让你理解。
首先创建类型和表:
CREATE TYPE enum_candidate_status AS ENUM('attended', 'selected', 'rejected', 'not attended');
CREATE TYPE enum_rating AS ENUM('1', '2', '3', '4', '5');
CREATE TABLE CANDIDATE (
candidate_id SERIAL PRIMARY KEY,
first_name varchar(100),
candidate_status enum_candidate_status DEFAULT 'not attended',
interview_rating enum_rating
);
然后创建以下函数:
CREATE OR REPLACE FUNCTION force_candidate_status_defaults()
RETURNS trigger
LANGUAGE plpgsql AS
$func$
BEGIN
NEW.candidate_status := 'not attended';
RETURN NEW;
END
$func$;
最后创建触发器来控制你的查询:
CREATE TRIGGER test_table_before_insert
BEFORE INSERT ON CANDIDATE
FOR EACH ROW
WHEN (NEW.candidate_status IS NULL)
EXECUTE FUNCTION force_candidate_status_defaults();
现在你可以测试查询并享受它:
INSERT INTO CANDIDATE(candidate_status, interview_rating) VALUES (NULL, '3') RETURNING *;
英文:
To manage this, you must use the function and trigger
Pay attention and do as follows. I do your example so that it is understandable to you.
at the first time CREATE TYPE and TABLE
CREATE TYPE enum_candidate_status AS ENUM('attended', 'selected', 'rejected', 'not attended');
CREATE TYPE enum_rating AS ENUM('1', '2', '3', '4', '5');
CREATE TABLE CANDIDATE ( candidate_id SERIAL PRIMARY KEY,
first_name varchar(100),
candidate_status enum_candidate_status DEFAULT 'not attended',
interview_rating enum_rating);
then u must be CREATE FUNCTION like below:
CREATE OR REPLACE FUNCTION force_candidate_status_defaults()
RETURNS trigger
LANGUAGE plpgsql AS
$func$
BEGIN
NEW.candidate_status := 'not attended';
RETURN NEW;
END
$func$;
at the end u must CREATE trigger to control on ur queries as follows:
CREATE TRIGGER test_table_before_insert
BEFORE INSERT ON CANDIDATE
FOR EACH ROW
WHEN (NEW.candidate_status IS NULL) -- !
EXECUTE FUNCTION force_candidate_status_defaults();
now u can test query and enjoy it
INSERT INTO CANDIDATE(candidate_status, interview_rating) VALUES (NULL, '3') RETURNING *;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论