6

I am creating a database for the first time using Postgres 9.3 on MacOSX.

Let's say I have table A and B. A starts off as empty and B as filled. I would like the number of entries in column all_names in table B to equal the number for each names in table A like table B below. Thus names should contain each unique entry from all_names and number its count. I am not used to the syntax, yet, so I do not really know how to go about it. The birthday column is redundant.

Table A

names | number
------+--------
Carl  | 3
Bill  | 4
Jen   | 2

Table B

 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

Would this be the correct way to go about it?

insert into a (names, number)
select b.all_names, count(b.all_names)
from b
group by b.all_names;
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
jO.
  • 3,384
  • 7
  • 28
  • 38

1 Answers1

8

Answer to original question

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);

Since the introduction of LATERAL in Postgres 9.3 you can do stick to standard SQL: the SRF moves from the SELECT to the FROM 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:

LATERAL can also precede a function-call FROM item, but in this case it is a noise word, because the function expression can refer to earlier FROM items in any case.

Reverse operation

The above is the reverse operation (approximately) of a simple aggregate count():

INSERT INTO a (name, number)
SELECT all_names, count(*)
FROM   b
GROUP  BY 1;

... which fits your updated question.

Note a subtle difference between count(*) and 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 a bit shorter and faster.

About GROUP BY 1:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • `create table b (names text, number int);` `insert into b (names) values ('bill'), ('bill'), ('jen');` `select b.names from b, generate_series(1, b.number) as rn;` Why isn't this working? Thanks. – jO. Nov 10 '13 at 04:51
  • @jO.: In your example, `number` is NULL. So `generate_series()` is not going to generate any rows. Insert values for `number`. – Erwin Brandstetter Nov 10 '13 at 13:11
  • Thanks for your reply and help. I have updated my question. I realized it might not have been clear enough what I was after, even thought your answer worked for me the first time around - oddly enough since `generate_series()` wont work with NULL. I must have entered the data wrongly. Many thanks! – jO. Nov 10 '13 at 21:50
  • @jO.: Basically you are after the inverse operation and your solution should work. I added a bit to my answer. – Erwin Brandstetter Nov 10 '13 at 22:40
  • THANKS! Really appreciate your help and clarification! – jO. Nov 10 '13 at 22:47