I have a table representing a card deck with 4 cards that each have a unique ID. Now i want to look for a specific card id in the table and find out which card in the deck it is.

if my table would like this for example I would like to do something like :
SELECT column_name WHERE cardID3 IN (card1, card2, card3, card4)

looking for an answer i found this: SQL Server : return column names based on a record's value

but this doesn’t seem to work for PostgreSQl

>Solution :

This is a rather bad idea. Column names belong to the database structure, not to the data. So you can select IDs and names stored as data, but you should not have to select column names. And actually a user using your app should not be interested in column names; they can be rather technical.

It would probably be a good idea you changed the data model and stored card names along with the IDs, but I don’t know how exactly you want to work with your data of course.

Anyway, if you want to stick with your current database design, you can still select those names, by including them in your query:

select
  case when card1 = 123 then 'card1'
       when card2 = 123 then 'card2'
       when card3 = 123 then 'card3'
       when card4 = 123 then 'card4'
  end as card_column
from cardtable
where 123 in (card1, card2, card3, card4);