I am creating a database for the first time using Postgres 9.3 on MacOSX.
Let's say I have table
names | number
Carl | 3
Bill | 4
Jen | 2
all_names | birthday
Carl | 17/03/1980
Carl | 22/08/1994
Carl | 04/09/1951
Bill | 02/12/2003
Bill | 11/03/1975
Bill | 04/06/1986
Bill | 08/07/2005
Jen | 05/03/2009
Jen | 01/04/1945
insert into a (names, number) select b.all_names, count(b.all_names) from b group by b.all_names;
Postgres allows set-returning functions (SRF) to multiply rows.
generate_series() is your friend:
INSERT INTO b (all_names, birthday) SELECT names, current_date -- AS birthday ?? FROM (SELECT names, generate_series(1, number) FROM a);
However, this syntax is frowned upon and may be removed in future versions.
Since the introduction of
LATERAL in Postgres 9.3 you can use this more future-proof version (SRF in the
FROM list instead in the target list):
INSERT INTO b (all_names, birthday) SELECT a.names, current_date -- AS birthday ?? FROM a, generate_series(1, a.number) AS rn
LATERAL is implicit here, as explained in the manual:
LATERALcan also precede a function-call
FROMitem, but in this case it is a noise word, because the function expression can refer to earlier FROM items in any case.
The above is the reverse operation (approximately) of a simple aggregate
INSERT INTO a (name, number) SELECT all_names, count(*) FROM b GROUP BY 1;
... which fits your updated question.
There is a subtle difference between
count(all_names). The former counts all rows, no matter what, while the latter only counts rows where
all_names IS NOT NULL. If your column
all_names is defined as
NOT NULL, both return the same, but
count(*) is slightly shorter and faster.
Consider this related answer for more about
GROUP BY 1:
GROUP BY + CASE statement