Home Ask Login Register

Developers Planet

Your answer is one click away!

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


Quote of the day: live life