How will it be in PostgreSQL?

Question

i have code for MySQL but i need to PostgreSQL. Can you help me convert from MySQL to PostgreSQL?

Create TRIGGER date_reservation
BEFORE INSERT ON reservation
FOR EACH ROW 
BEGIN 
IF 
NEW.date_reservation IS NULL OR NEW.date_reservation = ''
THEN 
SET NEW.date_reservation = NOW();
END IF 
END

Title needs rewriting to summarize your specific technical issue. — Basil Bourque, Jan 21 '20 at 03:57
Welcome to SO. You're asking us to convert code for you, but you haven't showed any effort toward solving the problem. SO isn't a code-conversion site, instead, we expect you to research and try, many, many times, and when you can't figure out the solution for days THEN ask. "[Writing The Perfect Question](https://codeblog.jonskeet.uk/2010/08/29/writing-the-perfect-question/)" will help. — the Tin Man, Jan 21 '20 at 05:21

score 1 · Answer 1 · answered Jan 20 '20 at 23:13

In Postgres, your first need to create a function:

CREATE FUNCTION date_reservation_function()
RETURNS trigger AS $$
BEGIN
    IF NEW.date_reservation IS NULL OR NEW.date_reservation = '' THEN
        NEW.date_reservation := NOW();
    END IF;
    RETURN NEW;
END$$ LANGUAGE 'plpgsql'

Then, you create a trigger that invokes the function:

CREATE TRIGGER date_reservation_trigger
BEFORE INSERT ON reservation
FOR EACH ROW
EXECUTE PROCEDURE date_reservation_function()

score 1 · Answer 2 · answered Jan 20 '20 at 23:30

1

In both MySQL and Postgres, I would recommend that you simply do this using a default value:

create table date_reservation (
    . . . 
    date_reservation timestamp default now(),
    . . . 
);

You can actually do something similar in MySQL.

answered Jan 20 '20 at 23:30

Gordon Linoff

1,242,037
58
646
786

How will it be in PostgreSQL?

2 Answers2