How to insert a enum field with default value postgresql?

user_16484951 0
Viewed 70

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,
);
go enums postgresql sql
1 Answers

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