Dynamic column selection in a subquery

Question

I have two tables, one containing polls and the other poll votes, like this (simplified):

CREATE TABLE IF NOT EXISTS polls (
  id SERIAL PRIMARY KEY,
  token VARCHAR(20)
);
CREATE TABLE IF NOT EXISTS poll_votes (
  id SERIAL PRIMARY KEY,
  account VARCHAR(255) NOT NULL,
  poll_id INT NOT NULL,
  token_one BIGINT,
  token_two BIGINT,
  token_three BIGINT
  [etc.]
);

There is a growing number of token columns in the poll_votes table, there can be over a thousand of them. There are multiple poll_votes rows for each poll row and I need to compute how many tokens in total are voting for each poll. The polls.token column has the name of the token column to be used, i.e. it specifies which token shall be used to count the results for that particular poll.

So I have a query like this to calculate the results when there's, say, just one token column:

SELECT p.token,
  (
    SELECT SUM(token_one)
    FROM poll_votes pv
    WHERE p.id = pv.poll_id
    GROUP BY pv.poll_id
  ) AS total_token_one
FROM polls p;

However, this hardcodes the token_one column. Whereas I need it to be dynamically set based on what value there is in the token column of the polls table. How can I do that?

Update:

I came up with this table structure (even though it creates challenges) because there are a large and continuously growing number of tokens. If there is a separate row in poll_votes for each token, then each single vote could require inserting hundreds of rows (since each account can have hundreds of token balances). So for a large number of voting accounts, it becomes a massive number of rows.

Answer 1

Why not change the tables structure? It seems to me that you should remove all the columns token_someNumber from poll_votes table and create another table that links between the vote with the relevant token

Adi

Answer 2

There is no way to use a column value as identifier in a query in a dynamic fashion.

This might be a case for JSON in the database. Define poll_votes like this:

CREATE TABLE poll_votes (
   id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
   account text NOT NULL,
   poll_id bigint REFERENCES polls NOT NULL,
   tokens jsonb

);

The tokens column could look like this:

{
   "token_one": 42,
   "token_three: 99,
   "token_eleven: 1
}

The query could look like:

SELECT p.token,
       sum(CAST ((pv.tokens) -> p.token AS bigint)) AS total_token
FROM polls AS p
   JOIN poll_votes AS pv
      ON pv.poll_id = p.id;

Answer 3

Thanks everyone for your help. While I was writing an update to the question (now updated) to explain more about the reasoning for choosing this table structure, an alternative approach occurred to me. I could have a single poll_votes.token column and dynamically insert the value in it for whichever token is in the polls.token column. (In reality the two columns in the two tables have slightly different names, not the same one.)

In this way I can keep the arrangement of one row for each poll vote. All the other token balances of an account are not recorded in the poll vote but they are actually not necessary.

It feels a bit unintuitive/weird to have a generic column name that can hold balances for different tokens, but I guess it does the job since one can easily find which token exactly is stored. It seems to be working fine.