Phill February 2016

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

Answers


klin February 2016

In Postgres you can use IN operator in two ways:

expression IN (value [, ...])
expression IN (subquery)

Read in the documetation: first variant, second variant or this overview.


Abelisto February 2016

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.

Post Status

Asked in February 2016
Viewed 3,006 times
Voted 12
Answered 2 times

Search




Leave an answer