# Postgresql connector skipping query **URL:** \ **Category:** Elasticsearch\ **Tags:** docker, connectors\ **Created:** [September 4, 2024, 5:24pm UTC](https://discuss.elastic.co/t/postgresql-connector-skipping-query/366058 "2024-09-04T17:24:18Z")\ **Posts on this page:** 8\ **Page:** 1
**Author:** ![mrtl](https://avatars.discourse-cdn.com/v4/letter/m/e9bcb4/32.png) [@mrtl](https://discuss.elastic.co/u/mrtl)\ **Post date:** [September 4, 2024, 5:24pm UTC](https://discuss.elastic.co/t/postgresql-connector-skipping-query/366058/1 "2024-09-04T17:24:18Z")
Hi, my connector is correcly connected to elasticsearch but in the log ther e is this line : > Skipping query select [...] as primary key column name is not present in query. I don't understand if it's related to elastic or postgesql ---
**Author:** ![Sean\_Story](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/sean_story/32/69987_2.png) [@Sean\_Story](https://discuss.elastic.co/u/Sean_Story)\ **Post date:** [September 4, 2024, 5:46pm UTC](https://discuss.elastic.co/t/postgresql-connector-skipping-query/366058/2 "2024-09-04T17:46:22Z")
Hi @mrtl , What version of connectors are you using? The behavior here changed in [[PostgreSQL] Add new field id\_columns in sync rules to generate id and adding support for views by parthpuri-elastic · Pull Request #2681 · elastic/connectors · GitHub](https://github.com/elastic/connectors/pull/2681), which looks like it was released in 8.15.0. This behavior is from our Postgres Connector. Basically, the advanced sync rule logic previously required that you include primary keys in your custom queries, otherwise we didn't know how to identify the resulting records. In the newer versions, we let you specify how the results of your queries should be uniquely identified. ---
**Author:** ![arjun2001](https://avatars.discourse-cdn.com/v4/letter/a/c6cbf5/32.png) [@arjun2001](https://discuss.elastic.co/u/arjun2001)\ **Post date:** [September 4, 2024, 7:16pm UTC](https://discuss.elastic.co/t/postgresql-connector-skipping-query/366058/3 "2024-09-04T19:16:53Z")
hi, The log message indicates that the query being executed is missing a primary key column, which is essential for uniquely identifying records. This often occurs when the query does not include a column that Elasticsearch uses to identify individual documents. **Solution:** 1. Ensure your query includes the primary key field, which in Elasticsearch is typically the `_id` field or another unique identifier specified in your schema. 2. Verify that your Elasticsearch index mapping includes a unique identifier for the documents and that your query is correctly referencing this field. thanks. ---
**Author:** ![mrtl](https://avatars.discourse-cdn.com/v4/letter/m/e9bcb4/32.png) [@mrtl](https://discuss.elastic.co/u/mrtl)\ **Post date:** [September 4, 2024, 9:33pm UTC](https://discuss.elastic.co/t/postgresql-connector-skipping-query/366058/4 "2024-09-04T21:33:56Z")
> [@arjun2001](#): > > - Ensure your query includes the primary key field, which in Elasticsearch is typically the `_id` field or another unique identifier specified in your schema. My query includes severals primary keys. Does it mean I need to ad an `_id` alias direcly in the select clause ? > [@arjun2001](#): > > - Verify that your Elasticsearch index mapping includes a unique identifier for the documents and that your query is correctly referencing this field. I'm not sure how to define a field as unique identifier in the mapping. ---
**Author:** ![mrtl](https://avatars.discourse-cdn.com/v4/letter/m/e9bcb4/32.png) [@mrtl](https://discuss.elastic.co/u/mrtl)\ **Post date:** [September 4, 2024, 9:38pm UTC](https://discuss.elastic.co/t/postgresql-connector-skipping-query/366058/5 "2024-09-04T21:38:24Z")
I was in version 8.14. I upgraded to 8.15 and tried to include 'id\_columns' but the query is still skipped > Skipping query select "internalId" from sheets for tables sheets as primary key column or unique ID column name is not present in query. My sync rules (kept as simple as possible for testing purpose) : ```auto [ { "tables": [ "sheets" ], "query": "select \"internalId\" from sheets", "id_columns": [ "internalId" ] } ] ``` ---
**Author:** ![Sean\_Story](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/sean_story/32/69987_2.png) [@Sean\_Story](https://discuss.elastic.co/u/Sean_Story)\ **Post date:** [September 5, 2024, 5:18pm UTC](https://discuss.elastic.co/t/postgresql-connector-skipping-query/366058/6 "2024-09-05T17:18:44Z")
@mrtl thanks, this extra info is helpful. It looks like the issue is now coming from quoting `select \"internalId\"`, as opposed to just `select internalId`. Do you need the quotes for your column name? If so, can you try doing: ```auto "id_columns": [ "\"internalId\"" ] ``` ? The logic here is not particularly intelligent, and is just checking to make sure that the ID columns you identify are in the outputs, using string matching. And `"internalId"` != `internalId`. ---
**Author:** ![mrtl](https://avatars.discourse-cdn.com/v4/letter/m/e9bcb4/32.png) [@mrtl](https://discuss.elastic.co/u/mrtl)\ **Post date:** [September 6, 2024, 9:36am UTC](https://discuss.elastic.co/t/postgresql-connector-skipping-query/366058/7 "2024-09-06T09:36:40Z")
Yes, I need those quotes, otherwise Postgres will grumble. > SQL Error [42703]: ERROR: column "internalId" does not exist > Hint: Perhaps you meant to reference the column "sheets.internalId". Even with your solution or an alias without quotes it still not working. To avoid the need of quotes, I added a field "internal\_id" in which I copied the values from "internalId" and then it finally worked. But the behavior is not what I was expected. Instead of adding the field "internal\_id", it has added the field "public\_sheets\_internal\_id" and some fields that I did not requested like "schema", "database", "\_timestamp" and "table". Is there a way to import fields as defined in the mapping ? ---
**Author:** ![Sean\_Story](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/sean_story/32/69987_2.png) [@Sean\_Story](https://discuss.elastic.co/u/Sean_Story)\ **Post date:** [September 6, 2024, 6:38pm UTC](https://discuss.elastic.co/t/postgresql-connector-skipping-query/366058/8 "2024-09-06T18:38:19Z")
> [@mrtl](#): > > Even with your solution or an alias without quotes it still not working. Oh bummer. That surprises me. Same error, or something different? > [@mrtl](#): > > To avoid the need of quotes, I added a field "internal\_id" in which I copied the values from "internalId" and then it finally worked. Yay! Glad you found a workaround. > [@mrtl](#): > > But the behavior is not what I was expected. Instead of adding the field "internal\_id", it has added the field "public\_sheets\_internal\_id" We do the table-prefixing of the output field names because otherwise we'd be forcing you to have the same types between all tables that have the same field name. Like if you have an `id` field in a User table that's a string, but an `id` field in a Product table that's an auto-increment integer, and the Product table syncs first, Elasticsearch would expect all `id` field values going forward to also be numeric, and then would crash when you got to your User table. To work around this, we prefix fields with the table name to keep them distinct. You can use Ingest Pipelines to rename/move these fields if you'd prefer. > [@mrtl](#): > > and some fields that I did not requested like "schema", "database", "\_timestamp" and "table". Yep, all our connectors are going to add as much metadata as possible, with the goal of facilitating search down the line. If you don't want these fields, you can drop them in your ingest pipeline.