Postgres: psycopg2.errors.InvalidTextRepresentation: invalid input syntax for type boolean-CodePudding

I'm running the following query in Postgres:

    INSERT INTO "attack_paths_attackpathprevscan"
    ("createdAt","account_id","org_id","event_id","action_id","assets")
    
    VALUES (('2022-10-30 08:51:13.934641')::timestamp,
           ('cd802832-c0e8-4376-a1f7-730836cb0885'::uuid)::uuid,
           ('e5f8eaff-54f0-4ea0-8428-b331a504d744'::uuid)::uuid,
           ('11111111-1111-1111-1111-111111111119')::uuid,
           ('11111111-1111-1111-1111-111111111119')::uuid,
    
    (ARRAY[hstore(ARRAY['id','type','asset_id','group_id','is_internet_facing','is_running','connecting_agent_id','policies'],
    ARRAY['ap1-node1','VmNodeData','11111111-1111-1111-1111-111111111111',
'11111111-1111-1111-1111-111111111112',true,true,'ap1-node1-al',NULL])])::jsonb)

and unfortunately I get the error:

E psycopg2.errors.InvalidTextRepresentation: invalid input syntax for type boolean: "ap1-node1" E LINE 3: ...running','connecting_alert_id','policies'], ARRAY['ap1-node1...

From the error message, I understand that the SQL query expects that the string "ap1-node1" would be a boolean.

Can you explain why?

CodePudding user response：

You left out the part where you are actually doing this in psycopg2.
Postgres arrays cannot have mixed types , so the ...true,true,... is creating the error. You will have to change to something like ...'true','true',... or ...'t','t',...

As demonstration:

select ARRAY['ap1-node1', true];
ERROR:  invalid input syntax for type boolean: "ap1-node1"
LINE 1: select ARRAY['ap1-node1', true];

select ARRAY['ap1-node1', 'true'];
      array       
------------------
 {ap1-node1,true}

Postgres cannot make the elements all be one single type in the first case so it flags the first element as not being the same as the boolean type represented by true.

This (ARRAY[hstore(ARRAY['id' ... whole field looks like it would be better as a child table to attack_paths_attackpathprevscan.