Hi thanks for your mail.
So my data is arranged as this due to the nature of business. I need the projected cash flow in each agent to calculate such as present value or other related issue to report to regulator. Furthermore, some basic functions such as sum, average and other aggregate functions are needed. I also considered to store them as array, but it would not be easy to write SQL for summing the select rows.
My need to discuss further on designing of the model.
Thank you again.
Shore
------------------ Original message ------------------
From: "Tim Cross";
Sendtime: Friday, Aug 31, 2018 6:24 AM
To: "a"<372660931@qq.com>;
Cc: "pgsql-general";
Subject: Re: Ways to deal with large amount of columns;
a <
372660931@qq.com> writes:
> 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+.
>
>
Not sure your data model is correct. Typically, with something like
this, increasing the number of agents would result in tables with more
rows rather than more columns. Tables with large numbers of columns is
often a 'code smell' and indicates the underlying data model needs to be
reviewed. Designs which result in new columns being required because you
are adding new data sources is almost certainly an indication of the
need to review the data model.
Postgres (and many other databases) have lots of functionality to help
deal with tables that have large numbers of rows, but working with
tables that have large numbers of columns has less functionality and
options.
While it is very tempting to create a table and then start coding, you
will almost always get a much better result and simpler code if you
spend some initial time to really analyse your domain, understand the
data elements and how they relate to each other, map them out into a
data model and then start development. Have a look at
https://en.wikipedia.org/wiki/Database_normalization for some background
on the normal forms and why they are useful.
HTH
Tim
--
Tim Cross