PostgreSQL 9.6.17 COALESCE function error

coalescepostgresqlpostgresql-9.3postgresql-9.6type conversion

I'm trying a SQL query with the function COALESCE in PostgreSQL 9.6.17, but it produces an error:

invalid syntax for type double precision: ""

Example query:

SELECT COALESCE(date_part('year', s.date_pp),'')
FROM public.sendoc s.

This query is working on PostgreSQL 9.3.17.

How to get around the error at the server DB level without changing the source code of the program?

Best Answer

I don't think you can "fix" this without changing your query or going back to Postgres 9.3, where apparently type casting rules were more relaxed.

date_part() returns a floating point value, and in the absence of an implicit type cast Postgres tries to convert the other operand of coalesce() to a compatible value, and an empty string ('') cannot be so converted.

Related Solutions

Postgresql – “Wrong affix file formate for flag” while loading dictionaries from LibreOffice in PostgreSQL 9.3

So I found the answer for Debian at least. Hunspell is the successor of Myspell, and they ship with your distribution (in my case Debian). These are the dictionaries you can use. You can install these by executing

apt-get install hunspell-en-us

These dictionaries are installed in /usr/share/hunspell or /usr/share/myspell. The PostgreSQL dictionaries that are automatically generate out of these file when installing through apt-get are located in /var/cache/postgresql/dicts. Furthermore apt-get automatically creates links to the files generated in you /usr/share/postgresql/VERSION/tsearch_data dir. In short you are ready to use them.

If you want to manually install hunspell or myspell dictionaries you can place them in the /usr/share/hunspell or /usr/share/myspell dirs. And execute

pg_updatedicts

This will generate PostgreSQL compatible dictionaries in

/var/cache/postgresql/dicts

and you will have to link them yourself /usr/share/postgresql/VERSION/tsearch_data

ln –s /var/cache/postgresql/dicts/de_de.affix           \
  /usr/share/postgesql/VERSION/tsearch_data/            \

ln –s /var/cache/postgresql/dicts/de_de.dict            \
  /usr/share/postgesql/VERSION/tsearch_data/            \

PostgreSQL 9.5, getting “cached plan must not change result type” error

FireDAC FDQuery.

Cursor kind in fetch options was set to ckAutomatic. I changed it to ckDefault and it works now.

Best Answer

Related Solutions

Postgresql – “Wrong affix file formate for flag” while loading dictionaries from LibreOffice in PostgreSQL 9.3

PostgreSQL 9.5, getting “cached plan must not change result type” error

Related Question