How to convert a Mysql view to Postgresql?

Asked

Viewed 25 times

-1

I’m making a migration from a Mysql database to Postgresql, but my view is not being accepted, I’ve been all over the internet looking for a way to modify it no longer worked, someone knows how it could be done, or a tutorial where I can learn? 

CREATE VIEW anuncio_points
AS
SELECT
a.*,
(if(p.points is null, 0, sum(p.points)) + u.points) as points
FROM anuncios as a
LEFT JOIN users as u ON u.id = a.user_id
LEFT JOIN points as p ON p.user_id = u.id
GROUP BY a.id;

By the way, the problem is no if:

ERROR:  function if(boolean, integer, numeric) does not exist
LINE 5: (if(p.points is null, 0, sum(p.points)) + u.points) as point...
mysql database postgresql

1 answer

0


Use CASE:

CREATE VIEW anuncio_points
AS
    SELECT
    a.*,
    (CASE WHEN p.points is null THEN 0 ELSE sum(p.points) END) + u.points as points
    FROM anuncios as a
    LEFT JOIN users as u ON u.id = a.user_id
    LEFT JOIN points as p ON p.user_id = u.id
    GROUP BY a.id;

or, more simply:

CREATE VIEW anuncio_points
AS
    SELECT
    a.*,
    (sum(COALESCE(p.points, 0) + u.points) as points
    FROM anuncios as a
    LEFT JOIN users as u ON u.id = a.user_id
    LEFT JOIN points as p ON p.user_id = u.id
    GROUP BY a.id;

Browser other questions tagged mysql database postgresql

You are not signed in. Login or sign up in order to post.