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

CodePudding user response：

SQL Server's cross apply is the SQL standard cross join lateral.

SELECT Cname
FROM   decks
       CROSS join lateral (VALUES('card1',card1),
                          ('card2',card2),
                          ('card3',card3),
                          ('card4',card4)) ca (cname, data)
WHERE  data = 3

Demonstration.

However, the real problem is the design of your table. In general, if you have col1, col2, col3... you should instead be using a join table.

create table cards (
  id serial primary key,
  value text
);

create table decks (
  id serial primary key
);

create table deck_cards (
  deck_id integer not null references decks,
  card_id integer not null references cards,
  position integer not null check(position > 0),

  -- Can't have the same card in a deck twice.
  unique(deck_id, card_id),
  -- Can't have two cards in the same position twice.
  unique(deck_id, position)
);

insert into cards(id, value) values (1, 'KH'), (2, 'AH'), (3, '9H'), (4, 'QH');

insert into decks values (1), (2);

insert into deck_cards(deck_id, card_id, position) values
  (1, 1, 1), (1, 3, 2),
  (2, 1, 1), (2, 4, 2), (2, 2, 3);

We've made sure a deck can't have the same card, nor two cards in the same position.

-- Can't insert the same card.
insert into deck_cards(deck_id, card_id, position) values (1, 1, 3);

-- Can't insert the same position
insert into deck_cards(deck_id, card_id, position) values (2, 3, 3);

You can query a card's position directly.

select deck_id, position from deck_cards where card_id = 3

And there is no arbitrary limit on the number of cards in a deck, you can apply one with a trigger.

Demonstration.

CodePudding user response：

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);