How to use Concat on WhereIn clause in postgress-CodePudding

Let's say I have this data on my table:

[
  {
    name: "test1",
    address: "hello",
    value: "hi1"
  },
  {
    name: "test2",
    address: "hello2",
    value: "hi2"
  },
  {
    name: "test3",
    address: "hello2",
    value: "hi3"
  },
  {
    name: "test4",
    address: "hello",
    value: "hi4"
  },
]

I have a wherein function where I need to select the item with name (test1 AND address hello) AND (name test2 address hello2).

So what I did to achieve this is by concat.

select concat(name, '-', address) as a, name, address from tableName
where a in ("test1-hello", "test2-hello2")

This works ok in mysql, but in postgress you can't use alias on where so I tried:

select concat(name, '-', address) as a, name, address from tableName
where concat(name, '-', address) in ("test1-hello", "test2-hello2")

The code above gives me a syntax error. Any ideas how to achieve this?

UPDATE:

I found an answer to my problem as per @jarlh's comment where I don't need to use concat and just do:

where (name, address) in (('test1', 'hello'), ('test2', 'hello2'))

instead. However I will leave the issue open as there might be someone willing to answer on how to use concat on where clause.

CodePudding user response：

"test1-hello" is read as an identifier, i.e. here as a column name. Use single quotes instead to write string literals.

However, I'd do it as:

where (name, address) in (('test1', 'hello'), ('test2', 'hello2'))