Serializing Dictionary Type

[rotten]

When I return a dictionary to Multicorn I end up with a string object like this in PostgreSQL:

'"key1"=>"value1", "key2"=>"value2"'

If I set the column type in PostgreSQL to JSON, it fails:

ERROR: invalid input syntax for type json DETAIL: Token "=" is invalid. CONTEXT: JSON data

If I set the column type to VARCHAR I can see the old fashioned => syntax in the string.

However if dictionaries were serialized to a standard json representation (the way python's print statement does):

{my_column: {key1: value1, key2: value2}}

I believe PostgreSQL would be able to parse it and store it in a JSON column type.

[rdunklau]

You're right, the current behavior tries to cast a python dictionary to a string represenation suitable for HStore.

I'm not sure what the best course of action would be here:

• drop the casting to hstore altogether, since json can easily be cast to hstore in the query
• detect when the column type is JSON, JSONB or some kind of text and only then cast to JSON.

The best approach would be to detect that the column is of the hstore type, but since hstore is not part of the core but an extension, that is very expensive.

In the meantime, you can work around the issue by using json.dumps in the FDW itself, since that is probably what the implementation will end up doing.

[rotten]

Thanks for the quick reply! Since queries return multiple columns, and some of them may not be json serializable, I'll have to check each column type whether it needs to be converted before returning them. I might try to do this with a decorator. [ I ran into this issue in the RethinkDB FDW -- https://github.com/wilsonrmsorg/rethinkdb-multicorn-postgresql-fdw ]