search

matthewfranglen / postgres-elasticsearch-fdw

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

Could it support fields with capital letters? #17

Closed canob closed 3 years ago

canob commented 3 years ago

Hi. Thanks for your amazing work!

A question: are capital letter fields in ElasticSearch, like client_source_IP and client_source_Port, supported? Because they showed empty, and they have information in Elasticsearch. Also, te correct type for an ip datatype of Elasticsearch is INET in PostgreSQL, right? And for the port, that in Elasticsearch is a long datatype, I'm using BIGINT in PostgreSQL, and is empty too. Is this correct?

Thanks!

matthewfranglen commented 3 years ago

Hi,

Can you just check you are quoting your column names? https://stackoverflow.com/a/20880247

I haven't tried using the INET datatype. I think BIGINT should work though.

canob commented 3 years ago

Perfect!!! It works perfectly if in the creation of the FOREIGN TABLE I add quotes to the columns names that have capital letters, like this: CREATE FOREIGN TABLE pihole ( timestamp TIMESTAMP, index TEXT, query_list BIGINT, agent_hostname TEXT, "client_source_IP" INET, "client_source_Port" BIGINT, programname TEXT, "query_answer_IP" INET, query_domain TEXT, tags TEXT, source TEXT, message TEXT, sort TEXT )

And I can confirm that INET datatype works perfectly too.

Thanks for pointing me to that link!

matthewfranglen commented 3 years ago

Awesome!

I think this concludes this ticket? If that is the case then please close it.

canob commented 3 years ago

Yes, I'm closing right now. Thanks for your help again.