search

matthewfranglen / postgres-elasticsearch-fdw

Postgres to Elastic Search Foreign Data Wrapper
MIT License
111 stars 32 forks source link

Array type field select error #31

Open rinne0120 opened 2 years ago

rinne0120 commented 2 years ago

When the field is defined as an array type, the insert succeeds, but an error is thrown on query.

CREATE FOREIGN TABLE "public"."_es_spt_product" ( "id" varchar(20) COLLATE "pg_catalog"."default", "product_id" varchar(20) COLLATE "pg_catalog"."default", "product_name" varchar(100) COLLATE "pg_catalog"."default", "cate_ids" text[] ) SERVER "multicorn_es" OPTIONS ( "host" '127.0.0.1', "port" '9200', "index" 'spt_product', "rowid_column" 'id', "query_dsl" 'true', "default_sort" 'product_id', "refresh" 'false', "timeout" '20', "username" 'elastic', "password" 'elastic' );

INSERT INTO "_es_spt_product" VALUES ( '194805', '194805', 'TIMBUK2', '{7JIA2_CATE_04,7JIA2_CATE_0402,7JIA2_CATE_040203}')

"hits" : [ { "_index" : "spt_product", "_type" : "_doc", "_id" : "194805", "_score" : 1.0, "_source" : { "product_id" : "194805", "product_name" : "TIMBUK2", "cate_ids" : [ "7JIA2_CATE_04", "7JIA2_CATE_0402", "7JIA2_CATE_040203" ] } }

SELECT * FROM "_es_spt_product"

ERROR: malformed array literal: "["7JIA2_CATE_04", "7JIA2_CATE_0401", "7JIA2_CATE_040101"]" DETAIL: "[" must introduce explicitly-specified array dimensions.

matthewfranglen commented 2 years ago

Thanks for opening this ticket. To address your issue quickly you may want to consider using a JSON or JSONB column. I will look into this specific error though.