# CrateDB/postgres\_fdw/IN filter

**URL:** <https://community.cratedb.com/t/cratedb-postgres-fdw-in-filter/1860>\
**Category:** Integrations\
**Created:** [September 20, 2024, 7:59pm UTC](https://community.cratedb.com/t/cratedb-postgres-fdw-in-filter/1860 "2024-09-20T19:59:59Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![jazzphoenix](https://avatars.discourse-cdn.com/v4/letter/j/ee7513/32.png) [@jazzphoenix](https://community.cratedb.com/u/jazzphoenix)\
**Post date:** [September 20, 2024, 8:00pm UTC](https://community.cratedb.com/t/cratedb-postgres-fdw-in-filter/1860/1 "2024-09-20T20:00:00Z")

</div>

Hello everyone!!

First of all, thank you very much for how amazing CrateDB is.  
I am trying to use FDW to visualize the CrateDB tables from PostgreSQL. Some queries work perfectly for me, but one query where I use **IN** throws an exception.

**Query**

```auto
SELECT * FROM doc.events WHERE ((server_id = ANY ('{76358,1256}'::integer[])))

```

**ERROR**

```auto
Cannot cast expressions from type `text` to type `integer_array`

```

**CONTEXT**

```auto
io.crate.exceptions.SQLExceptions.esToCrateException(SQLExceptions.java:211)
io.crate.exceptions.SQLExceptions.prepareForClientTransmission(SQLExceptions.java:200)
io.crate.protocols.postgres.Messages.sendErrorResponse(Messages.java:188)
io.crate.protocols.postgres.PostgresWireProtocol$MessageHandler.channelRead0(PostgresWireProtocol.java:305)
io.crate.protocols.postgres.PostgresWireProtocol$MessageHandler.channelRead0(PostgresWireProtocol.java:282)
io.netty.channel.SimpleChannelInboundHandler.channelRead(SimpleChannelInboundHandler.java:99)
io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:444)
io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:420)
io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:412)
io.netty.handler.codec.ByteToMessageDecoder.fireChannelRead(ByteToMessageDecoder.java:346)
io.netty.handler.codec.ByteToMessageDecoder.fireChannelRead(ByteToMessageDecoder.java:333)
io.netty.handler.codec.ByteToMessageDecoder.callDecode(ByteToMessageDecoder.java:455)
io.netty.handler.codec.ByteToMessageDecoder.channelRead(ByteToMessageDecoder.java:290)
io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:444)
io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:420)
io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:412)
io.netty.channel.ChannelInboundHandlerAdapter.channelRead(ChannelInboundHandlerAdapter.java:93)
org.elasticsearch.transport.netty4.Netty4InboundStatsHandler.channelRead(Netty4InboundStatsHandler.java:99)
io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:444)
io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:420)

```

I guess this is a similar error to the one reported at [CrateDB/postgres\_fdw/LIMIT parameter](https://community.cratedb.com/t/cratedb-postgres-fdw-limit-parameter/1420). It is quite strange that a query with only one value in the filter (SELECT \* FROM doc.events WHERE server\_id in (78654)) does not give an error; so I suppose the problem is with “,” char.

## Setup

**psql (16.4 (Ubuntu 16.4-1.pgdg22.04+1))**  
**Crate 5.8.3**

```auto
CREATE EXTENSION postgres_fdw;
CREATE SERVER crate_server FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host '------', dbname 'doc');
CREATE USER MAPPING FOR public SERVER crate_server OPTIONS (user '....', password '-----');

```

```auto
CREATE FOREIGN TABLE events (
    server_id INTEGER,
    metric_id INTEGER,
    metric_value INTEGER,
    metric_info JSONB,
    system_date TIMESTAMP,
);

SERVER crate_server OPTIONS (table_name 'events', schema_name 'doc');

```

Thank so much and best regards!!

---

<div class="post-metadata">

**Author:** ![jeeminso](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/jeeminso/32/1289_2.png) [@jeeminso](https://community.cratedb.com/u/jeeminso)\
**Post date:** [September 23, 2024, 6:23pm UTC](https://community.cratedb.com/t/cratedb-postgres-fdw-in-filter/1860/2 "2024-09-23T18:23:05Z")

</div>

Hi @jazzphoenix would it be possible for you to try rewriting `'{76358,1256}'::integer[]` to `array[76358, 1256]`? Thank you

---

<div class="post-metadata">

**Author:** ![jazzphoenix](https://avatars.discourse-cdn.com/v4/letter/j/ee7513/32.png) [@jazzphoenix](https://community.cratedb.com/u/jazzphoenix)\
**Post date:** [September 24, 2024, 8:45am UTC](https://community.cratedb.com/t/cratedb-postgres-fdw-in-filter/1860/3 "2024-09-24T08:45:47Z")

</div>

Hi @jeeminso .

The real query in PostgreSQL is “SELECT \* FROM doc.events WHERE server\_id in (76358,1256)”. I can’t figure out how to send {} in this query.

On the other hand, do you know if its possible to use FDW in CrateDB to conect to MySQL Database. The documentation ([https://cratedb.com/docs/crate/reference/en/latest/admin/fdw.html#administration-fdw](https://cratedb.com/docs/crate/reference/en/latest/admin/fdw.html#administration-fdw)) seems to indicate that only PostgreSQL Database are allowed.

Best regards.