Search code examples
.netpostgresqlnpgsql

PostgreSQL equivalent to SQL Server's TVP

SQL Server has Table Value Parameters which allows you to pass an array of values as a parameter.

What is the appropiate way to achieve something similar to a PostgreSQL query so I can do something like:

select * from product where id in ($1)

I'm using Npgsql .NET library.

https://www.nuget.org/packages/Npgsql/3.0.5

Solution

  • In PostgreSQL you can use arrays instead of list of IDs like:

    ... where id = any('{1, 2, 3}'::int[])

    or 

    ... where id = any(array[1, 2, 3])

    which means that id is one of the array's items.

    Read more about arrays operators and functions.

    To pass array as a parameter from third party languages you can use at least first variant:

    ... where id = any($1 ::int[])

    where $1 is a string parameter looks like {1, 2, 3}. Note that a space between $1 and ::int[] - it may be necessary for some clients.

    Not sure about C# is it supports array parameters directly.