英文:
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 triggerLANGUAGE plpgsql AS$func$BEGINNEW.candidate_status := 'not attended';RETURN NEW;END$func$;
最后创建触发器来控制你的查询：
CREATE TRIGGER test_table_before_insertBEFORE INSERT ON CANDIDATEFOR EACH ROWWHEN (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 triggerLANGUAGE plpgsql AS$func$BEGINNEW.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_insertBEFORE INSERT ON CANDIDATEFOR EACH ROWWHEN (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 *;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论