On Fri, 31 Aug 2018 at 10:47, a <372660...@qq.com> wrote: > 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 > *To:* "a"<372660...@qq.com>; > *Cc:* "pgsql-general"; > *Subject:* Re: Ways to deal with large amount of columns; > > On Thursday, August 30, 2018, a <372660...@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 -- regards, Tim -- Tim Cross
