How to do a PostgreSQL query with where-in clause which contains multiple columns programmatically?

Question

My query is like this:

select * from plat_customs_complex
where (code_t,code_s) 
in (('01013090','10'),('01029010','90'));

It runs well in psql console. My question is how to perform this query in client code.(via C# or Java)

And I already know the following code works well(C#):

string[] codeT = new string[]{"01013090","01029010"};    
connection.Query("SELECT * FROM plat_customs_complex WHERE code_t=ANY(@CodeT)",
new { CodeT = codeT });

guogangj · Accepted Answer · 2019-10-21 11:19:44Z

1

Finally, I found the unnest function can help.

Pure SQL is like that:

select * from plat_customs_complex
where (code_t,code_s) = ANY(select * from unnest(ARRAY['01013090','01029010'],ARRAY['10','90']))

Can convert it to C# code easily:

string[] codeTs = new string[]{"01013090","01029010"}; 
string[] codeSs = new string[]{"10", "90"};
connection.Query("select * from plat_customs_complex
where (code_t,code_s) = ANY(select * from unnest(@CodeTs, @CodeSs))", 
new {CodeTs=codeTs, CodeSs=codeSs});

answered Oct 21, 2019 at 11:19

guogangj

2,4853 gold badges31 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to do a PostgreSQL query with where-in clause which contains multiple columns programmatically?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related