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"<372660...@qq.com>; Cc: "pgsql-general"; Subject: Re: Ways to deal with large amount of columns; a <372660...@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
