Thank you very much. Creating a function seems to be a good idea :)
------------------ Original message ------------------
From: "David G. Johnston";
Sendtime: Thursday, Aug 30, 2018 8:31 PM
Cc: "pgsql-general";
Subject: Re: Ways to deal with large amount of columns;
On Thursday, August 30, 2018, a <372660931@qq.com> wrote:
Hi all:
I need to make a table contains projected monthly cashflow for multiple agents (10,000 around).
Therefore, the column number would be 1000+.
I would need to perform simple aggregate function such as count, sum or average on each cashflow projected.
So if there is anyway of doing this? Will there be anything like define a macro in C that I can manipulate multiple columns by simple word that representing them.
Better to design a data model that doesn't have so many columns. Otherwise generating dynamic SQL via the for,at function and loops and such is your best bet. Can be down in pl/pgsql or in your preferred programming language. Psql variables can maybe be useful too.
David J.
Perhaps post your proposed table design/definition. There is nothing in what you have described so far which would indicate a necessity to have more columns as you increase the number of agents. It would be normal to have something like
| agent_id | year | cash_on_hand | bank | creditors | debtors | ....
and queries like
select sum(cash_on_hand)
from table
where agent_id = 'agent1'
and yesr = 2018;
to get the sum of cash on hand for agent1 in 2018.
instead of something like
| agent1_cash2018 | agent2_cash2017 | ....
which will not work well.
Tim