Scripting - PostgreSQL - JSONB

I’m on 8.1.18

I have a pgSQL table where I store my config into a jsonb field. The insert work well…

Now I want query my table with a system.db.runPrepUpdate to find if my config already exist, so I want to do a WHERE on my jsonb field compare with a full payload of my config.

When I try I got error => Caused by: com.inductiveautomation.ignition.client.gateway_interface.GatewayException: No hstore extension installed.

my query

system.db.runPrepQuery("SELECT * FROM tbl_config_data WHERE config_b = ?::jsonb",[{"/metal_feeder/MF_feeder/ready_to_pour_timer/config/duration/millisecond":0,"/metal_feeder/config/pour_steps/0/speed/deg_per_second":0.0}])

Is there another possibly more detail exception in the gateway logs?

Best I can tell this is coming from the JDBC driver and indicates you don’t have the ‘hstore’ extension installed on your PostreSQL server.

I do my test in the scripting consol, so I d’ont have any log…

Install the hstore extension in your Postgre server :man_shrugging:

Yup. You can save and retrieve json itself without hstore. You are using json in a comparison (where clause), so you need the hierarchical storage extension.

I don’t find hierarchical storage extension in postgres

It goes all the way back to PostgreSQL v8.3. You will need to install it in your DB server and register the extension in your DB instance.

Find the docs for your DB server version. Install the extension if not already present. In early versions, you would need the contrib extra install files.

hierarchical storage extension is the hstore recommended by @Kevin.Herron , I have installed hstore and now i’m able to query the full payload…