Search code examples
pythonpostgresqlhstore

PostgreSQL hstore - Error when storing data with different types

I am using PostGreSQL 9.2 with the hstore extension 1.1

I have a python dict which is containing data with different types ie. integer and char.

dict = {"type": 1 , "precision": 0 , "width": 20 , "name": "test" }

When saving this dict into an hstore field, I got an error with the data "test" because its not an integer.

If I save all data in char, there would be no error

dict = {"type": "1" , "precision": "0" , "width": "20" , "name": "test" }

Is it normal that I could only have data with a unique type in an hstore field? Is there a way to store data with different types?

Solution

  • Hstore Documentation says:

    This module implements the hstore data type for storing sets of key/value pairs within a single PostgreSQL value. Keys and values are simply text strings.

    If you want to interpret keys/values as not strings you should do it in Python:

    • convert your keys/values to string before saving them to db
    • do the reverse operation when fetching data from db