Missing parantheses causes problems with HSTOREs containing newlines

[saurabhnanda]

In PostgreSQL, if an HSTORE column contains a newline, Jailer will export the value as...

'"key"=>"value' || chr(10) || 'with newlines"' :: hstore

... which results in the following error when executing in psql:

psql=# select '"key"=>"value' || chr(10) || 'with newlines"' :: hstore;
ERROR:  Syntax error near "n" at position 5
LINE 1: select '"key"=>"value' || chr(10) || 'with newlines"' :: hst...

However, if the HSTORE value were to be exported with parantheses, as shown below...

('"key"=>"value' || chr(10) || 'with newlines"') :: hstore

...I believe it would work:

psql=# select ('"key"=>"value' || chr(10) || 'with newlines"') :: hstore;
     hstore     
----------------
 "key"=>"value +
 with newlines"
(1 row)

[Wisser]

Thank you for the error report!
This has been fixed in release 16.4.

[saurabhnanda]

@Wisser thank you for the quick response. Actually, I discovered this to be a bug for TEXT columns as well. Does 16.4 fix that as well?

[Wisser]

Yes, it works for all literals. Let me know if you find any other issues.

[saurabhnanda]

@Wisser I've come back to this issue to remove the HACK that I had put in my export model. Unfortunately, in the 16.4 UI, I cannot find where to remove the following from:

#! block filter templates
T;hstore fix;remove_newlines_from_hstore_value(${old-value});enabled;Export;;
C;TYPE;EQUALS;hstore;

[Wisser]

Did the fix work for you?

The entry in the model file is a “filter template”. These can be edited or deleted in the “Filter editor” (“Edit” -> “Filter editor”).
See screenshots.