Re: Ways to deal with large amount of columns;

On Fri, 31 Aug 2018 at 10:47, a <372660931(at)qq(dot)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"<372660931(at)qq(dot)com>;
> *Cc:* "pgsql-general";
> *Subject:* Re: Ways to deal with large amount of columns;
>
> On Thursday, August 30, 2018, a <372660931(at)qq(dot)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