Migrating to PostgreSQL

 

Moving to PostgreSQL from MySQL has been pretty straight forward so far. My motivations for moving are simple. I was interested in the PostGIS spatial extensions and becoming concerned with the move in Linux distributions from MySQL to MariaDB. While the move to MariaDB may not be a real concern, it was a good excuse to try out PostgreSQL.

In the move I have found a few tips ... and there are sure to be plenty more... 

here is one for starters ...

Oops ... in MySQL, I was using ifNull() , which does not work in PostgreSQL, to build a generic bibliography field for the database VIEW.

 e.g.      "Concat(IfNull(catalog.authorall, '&#39, If(catalog.refs_editor <> 'author',   ' ed.', '&#39, If(catalog.refs_year <> '', ' (', '&#39, IfNull(catalog.refs_year,   .......

Try this example instead:

concat(COALESCE(catalog.authorall, '&#39,
        CASE
            WHEN catalog.refs_editor <> 'author' THEN ' ed.' ELSE ''
        END,
        CASE
            WHEN catalog.refs_year <> '' THEN ' ('
            ELSE ''
        END,
COALESCE(catalog.refs_year, '&#39,
        CASE
            WHEN catalog.refs_year <> '' THEN &#39'
            ELSE ''
        END,
        CASE
            WHEN catalog.refs_title <> '' THEN ' '
            ELSE ''
        END,
COALESCE(catalog.refs_title, '&#39,
        CASE
            WHEN catalog.refs_stitle <> '' THEN ': '
            ELSE ''
        END,
COALESCE(catalog.refs_stitle, '&#39,
        CASE
            WHEN catalog.refs_edition <> '' THEN ', '
            ELSE ''
        END,
COALESCE(catalog.refs_edition, '&#39,
        CASE
            WHEN catalog.refs_edition <> '' THEN ' ed.'
            ELSE ''
        END,
        CASE
            WHEN catalog.refs_publication <> '' THEN ', In: '
            ELSE ''
        END,
COALESCE(catalog.refs_publication, '&#39,
        CASE
            WHEN catalog.refs_volume <> '' THEN ', Vol.'
            ELSE ''
        END,
COALESCE(catalog.refs_volume, '&#39,
        CASE
            WHEN catalog.refs_issue <> '' THEN ' ('
            ELSE ''
        END,
COALESCE(catalog.refs_issue, '&#39,
        CASE
            WHEN catalog.refs_issue <> '' THEN &#39 '
            ELSE ''
        END,
COALESCE(catalog.refs_spage, '&#39,
        CASE
            WHEN catalog.refs_spage <> '' AND catalog.refs_epage <> '' THEN '-'
            ELSE ''
        END,
COALESCE(catalog.refs_epage, '&#39,
        CASE
            WHEN catalog.refs_numpages <> '' THEN ', '
            ELSE ''
        END,
COALESCE(catalog.refs_numpages, '&#39,
        CASE
            WHEN catalog.refs_numpages <> '' THEN ' pages'
            ELSE ''
        END,
        CASE
            WHEN catalog.refs_conference <> '' THEN ', Presented at: '
            ELSE ''
        END,
COALESCE(catalog.refs_conference, '&#39,
        CASE
            WHEN catalog.refs_publisher <> '' THEN ', '
            ELSE ''
        END,
COALESCE(catalog.refs_publisher, '&#39, '.&#39 AS bibliography,       

 

 

 

Tag: postgresql