CODEWITHSUNDEEP
mysqlsqlpostgresql
Tilei Liu
Tilei Liu

Reputation: 155

convert Postgresql to Mysql

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();

Upvotes: 0

Views: 1322

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562871

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.

Upvotes: 1

Related Questions