Migrating to PostgreSQL


PgAdmin3

 

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, ''), If(catalog.refs_editor <> 'author',   ' ed.', ''), If(catalog.refs_year <> '', ' (', ''), IfNull(catalog.refs_year,   .......

Try this example instead:

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

 

 

 

Tag: postgresql

Share It

Comments are closed