如何在 PostgreSQL 中插入具有默认值的枚举字段?

huangapple go评论85阅读模式
英文:

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 *;

huangapple
  • 本文由 发表于 2022年6月21日 21:41:02
  • 转载请务必保留本文链接:https://go.coder-hub.com/72701874.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定