convert Postgresql to Mysql

Question

it is the code i got from another developer which is written by Postgresql, however i can only use mysql in my environment. does anyone know how to covert the code from Postgresql to Mysql?

Just a disclaimer, I am virtually a newb to sql, I know almost nothing about the code below I don't even know how to create a table. It really makes me feel lost. that's why I need someone to help me transfer the code to mysql so that I can do some experiments on the table

Part1:

1.declare the function of trigger_set_create_time_update_time() whenever i insert data

2.declare the function of trigger_set_update_time() whenever i update data

CREATE OR REPLACE FUNCTION public.trigger_set_create_time_update_time()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
begin
  NEW.create_time = NOW() at time zone 'utc' ;
  NEW.update_time = NOW() at time zone 'utc' ;
  RETURN NEW;
END;
$function$
;


CREATE OR REPLACE FUNCTION public.trigger_set_update_time()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
BEGIN
  NEW.update_time = NOW() at time zone 'utc' ;
  RETURN NEW;
END;
$function$
;

Part2:

1.Create a table with all the variables and setup a Primary key.

2.Create triggers to call functions when the situation fits.

CREATE TABLE public.good_info (
    id varchar NOT NULL,
    "name" varchar NULL,
    price int8 NOT NULL,
    create_time timestamp NOT NULL,
    update_time timestamp NOT NULL,
    CONSTRAINT good_info_pk PRIMARY KEY (id)
);

create trigger set_create_time_update_time before
insert
    on
    public.good_info for each row execute function trigger_set_create_time_update_time();

create trigger set_update_time before
update
    on
    public.good_info for each row execute function trigger_set_update_time();

I would suggest that you ask a question with sample data, desired results, and a clear explanation of what the trigger is supposed to be doing. — Gordon Linoff
– Gordon Linoff, Commented Aug 29, 2021 at 14:12
If you are asking if there is a way of automating the conversion then the answer is no. Also, no-one on this forum is going to do this for you. Hence, to echo what @GordonLinoff said, please ask a focused question on a specific issue you are having when re-writing the code — NickW
– NickW, Commented Aug 29, 2021 at 14:21
This question was closed abruptly as I was writing an answer. The short answer is that in MySQL, you don't need triggers to maintain timestamps. Read this manual to see how to declare timestamp columns with default behavior: dev.mysql.com/doc/refman/8.0/en/timestamp-initialization.html — Bill Karwin
– Bill Karwin, Commented Aug 29, 2021 at 14:28

Bill Karwin · Accepted Answer · 2021-08-29 18:18:08Z

You seem to be using triggers to implement automatic values for columns create_time and update_time.

CREATE TABLE public.good_info (
    id varchar(10) NOT NULL,
    name varchar(10) NULL,
    price DECIMAL(9,2) NOT NULL,
    create_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    update_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
);

Other changes:

MySQL requires VARCHAR to be declared with a maximum length.
MySQL has no int8 type. It has a BIGINT type, but I made the assumption that your price column is supposed to store a currency value, so I would use DECIMAL with a precision and scale.
In MySQL, the primary key constraint is always named PRIMARY so don't bother to give it a different name.

Demo:

mysql> insert into good_info set id = 1234, name = 'Bill' price=19.95;
Query OK, 1 row affected (0.00 sec)

mysql> select * from good_info;
+------+------+-------+---------------------+---------------------+
| id   | name | price | create_time         | update_time         |
+------+------+-------+---------------------+---------------------+
| 1234 | Bill | 19.95 | 2021-08-29 11:12:49 | 2021-08-29 11:12:49 |
+------+------+-------+---------------------+---------------------+

...

mysql> update good_info set price = 49.95;

mysql> select * from good_info;
+------+------+-------+---------------------+---------------------+
| id   | name | price | create_time         | update_time         |
+------+------+-------+---------------------+---------------------+
| 1234 | Bill | 49.95 | 2021-08-29 11:12:49 | 2021-08-29 11:17:25 |
+------+------+-------+---------------------+---------------------+

You can see from this demo that the create_time and update_time are handled automatically.

HI this is exactly what i want. I really appreciate your help!!

Collectives™ on Stack Overflow

convert Postgresql to Mysql

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related