postgresql how to duplicate rows in result.

On Tue, Feb 14, 2017 at 1:04 PM, Alessandro Baggi <
alessandro.baggi@gmail.com> wrote:

Hi list,
sorry for my english, I will try to example as well. I've a query that
joins multiple tables and return a result like:

id,customers,phone,code,number
1 , aaaaaaaa,33333,123 , 2
2 , aassdsds,33322,211 , 1
3 , oooooooo,21221,221 , 1

I need, where "number" field is > 1, to duplicate the row * N(number field
value) with a result like this:

id,customers,phone,code,number
1 , aaaaaaaa,33333,123 , 2
1 , aaaaaaaa,33333,123 , 2
2 , aassdsds,33322,211 , 1
3 , oooooooo,21221,221 , 1

How I can accomplish to this problem?

I'm using postgresql 9.3.15.

thanks in advance.

Alessandro.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

It would be immensely helpful if you provided the schema of tables involved
with original query.
In the meantime, I suggest you look into the use of UNION.

--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.

On Tue, Feb 14, 2017 at 12:04 PM, Alessandro Baggi
<alessandro.baggi@gmail.com> wrote:

Hi list,
sorry for my english, I will try to example as well. I've a query that joins
multiple tables and return a result like:

id,customers,phone,code,number
1 , aaaaaaaa,33333,123 , 2
2 , aassdsds,33322,211 , 1
3 , oooooooo,21221,221 , 1

I need, where "number" field is > 1, to duplicate the row * N(number field
value) with a result like this:

id,customers,phone,code,number
1 , aaaaaaaa,33333,123 , 2
1 , aaaaaaaa,33333,123 , 2
2 , aassdsds,33322,211 , 1
3 , oooooooo,21221,221 , 1

How I can accomplish to this problem?

SELECT * FROM foo CROSS JOIN LATERAL (1,number);

:-D

merlin

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Tue, Feb 14, 2017 at 12:04 PM, Alessandro Baggi <
alessandro.baggi@gmail.com> wrote:

Hi list,
sorry for my english, I will try to example as well. I've a query that
joins multiple tables and return a result like:

​​
id,customers,phone,code,number
1 , aaaaaaaa,33333,123 , 2
2 , aassdsds,33322,211 , 1
3 , oooooooo,21221,221 , 1

I need, where "number" field is > 1, to duplicate the row * N(number field
value) with a result like this:

id,customers,phone,code,number
1 , aaaaaaaa,33333,123 , 2
1 , aaaaaaaa,33333,123 , 2
2 , aassdsds,33322,211 , 1
3 , oooooooo,21221,221 , 1

How I can accomplish to this problem?

I'm using postgresql 9.3.15

​I don't have PostgreSQL 9.3.15. I am running 9.5.5. But I think the
following recursive CTE should work for you.​

====

tsh009=# \d baggi
Table "public.baggi"
Column | Type | Modifiers
-----------+---------+-----------
id | integer |
customers | text |
phone | text |
code | integer |
number | integer |

tsh009=# select * from baggi;
id | customers | phone | code | number
----+-----------+-------+------+--------
1 | aaaaaaaa | 33333 | 123 | 2
2 | aassdsds | 33322 | 211 | 1
3 | oooooooo | 21221 | 221 | 1
(3 rows)

tsh009=# with recursive multiple(k, id, customers, phone, code, number) as (
select 1, id, customers, phone, code, number from baggi
UNION ALL
select m.k + 1, b.id, b.customers, b.phone, b.code, b.number from multiple
AS m, baggi AS b where m.id = b.id and m.k < b.number )
select id, customers, phone, code, number from multiple order by id
;
id | customers | phone | code | number
----+-----------+-------+------+--------
1 | aaaaaaaa | 33333 | 123 | 2
1 | aaaaaaaa | 33333 | 123 | 2
2 | aassdsds | 33322 | 211 | 1
3 | oooooooo | 21221 | 221 | 1
(4 rows)

====

.

thanks in advance.

Alessandro.

--
"Irrigation of the land with sewater desalinated by fusion power is
ancient. It's called 'rain'." -- Michael McClary, in alt.fusion

Maranatha! <><
John McKown

On Tue, Feb 14, 2017 at 12:42 PM, John McKown
<john.archie.mckown@gmail.com> wrote:

On Tue, Feb 14, 2017 at 12:04 PM, Alessandro Baggi
<alessandro.baggi@gmail.com> wrote:

Hi list,
sorry for my english, I will try to example as well. I've a query that
joins multiple tables and return a result like:

id,customers,phone,code,number
1 , aaaaaaaa,33333,123 , 2
2 , aassdsds,33322,211 , 1
3 , oooooooo,21221,221 , 1

I need, where "number" field is > 1, to duplicate the row * N(number field
value) with a result like this:

id,customers,phone,code,number
1 , aaaaaaaa,33333,123 , 2
1 , aaaaaaaa,33333,123 , 2
2 , aassdsds,33322,211 , 1
3 , oooooooo,21221,221 , 1

How I can accomplish to this problem?

I'm using postgresql 9.3.15

I don't have PostgreSQL 9.3.15. I am running 9.5.5. But I think the
following recursive CTE should work for you.

====

tsh009=# \d baggi
Table "public.baggi"
Column | Type | Modifiers
-----------+---------+-----------
id | integer |
customers | text |
phone | text |
code | integer |
number | integer |

tsh009=# select * from baggi;
id | customers | phone | code | number
----+-----------+-------+------+--------
1 | aaaaaaaa | 33333 | 123 | 2
2 | aassdsds | 33322 | 211 | 1
3 | oooooooo | 21221 | 221 | 1
(3 rows)

tsh009=# with recursive multiple(k, id, customers, phone, code, number) as (
select 1, id, customers, phone, code, number from baggi
UNION ALL
select m.k + 1, b.id, b.customers, b.phone, b.code, b.number from multiple
AS m, baggi AS b where m.id = b.id and m.k < b.number )
select id, customers, phone, code, number from multiple order by id
;
id | customers | phone | code | number
----+-----------+-------+------+--------
1 | aaaaaaaa | 33333 | 123 | 2
1 | aaaaaaaa | 33333 | 123 | 2
2 | aassdsds | 33322 | 211 | 1
3 | oooooooo | 21221 | 221 | 1
(4 rows)

IMO, lateral join (available as of 9.3) is faster and simpler.

merlin

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Tue, Feb 14, 2017 at 12:36 PM, Merlin Moncure <mmoncure@gmail.com> wrote:

On Tue, Feb 14, 2017 at 12:04 PM, Alessandro Baggi
<alessandro.baggi@gmail.com> wrote:

Hi list,
sorry for my english, I will try to example as well. I've a query that joins
multiple tables and return a result like:

id,customers,phone,code,number
1 , aaaaaaaa,33333,123 , 2
2 , aassdsds,33322,211 , 1
3 , oooooooo,21221,221 , 1

I need, where "number" field is > 1, to duplicate the row * N(number field
value) with a result like this:

id,customers,phone,code,number
1 , aaaaaaaa,33333,123 , 2
1 , aaaaaaaa,33333,123 , 2
2 , aassdsds,33322,211 , 1
3 , oooooooo,21221,221 , 1

How I can accomplish to this problem?

SELECT * FROM foo CROSS JOIN LATERAL (1,number);

:-D

oops -- copy/paste error

SELECT * FROM foo CROSS JOIN LATERAL generate_series(1,number);

merlin

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Tue, Feb 14, 2017 at 2:50 PM, Merlin Moncure <mmoncure@gmail.com> wrote:

​<snip>

IMO, lateral join (available as of 9.3) is faster and simpler.

​And, nicely, I've learned something new. I've never used a LATERAL join
before. Interesting.​

merlin

--
"Irrigation of the land with sewater desalinated by fusion power is
ancient. It's called 'rain'." -- Michael McClary, in alt.fusion

Maranatha! <><
John McKown

Il 14/02/2017 21:51, Merlin Moncure ha scritto:

On Tue, Feb 14, 2017 at 12:36 PM, Merlin Moncure <mmoncure@gmail.com> wrote:

On Tue, Feb 14, 2017 at 12:04 PM, Alessandro Baggi
<alessandro.baggi@gmail.com> wrote:

Hi list,
sorry for my english, I will try to example as well. I've a query that joins
multiple tables and return a result like:

id,customers,phone,code,number
1 , aaaaaaaa,33333,123 , 2
2 , aassdsds,33322,211 , 1
3 , oooooooo,21221,221 , 1

I need, where "number" field is > 1, to duplicate the row * N(number field
value) with a result like this:

id,customers,phone,code,number
1 , aaaaaaaa,33333,123 , 2
1 , aaaaaaaa,33333,123 , 2
2 , aassdsds,33322,211 , 1
3 , oooooooo,21221,221 , 1

How I can accomplish to this problem?

SELECT * FROM foo CROSS JOIN LATERAL (1,number);

:-D

oops -- copy/paste error

SELECT * FROM foo CROSS JOIN LATERAL generate_series(1,number);

merlin
.

Hi Merlin,
I've tried your suggested code and with cross join and generate_series I
can generate multiple row. There is a way to put as second args a column
values? I've tried to put "table.number" column values but I got
"generate_series() does not exists". Inserting a simple int like 5 I get
5 results for each row.

I've searched on google but can't find a valid example.

Thanks in advance.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Il 15/02/2017 19:11, Alessandro Baggi ha scritto:

Il 14/02/2017 21:51, Merlin Moncure ha scritto:

On Tue, Feb 14, 2017 at 12:36 PM, Merlin Moncure <mmoncure@gmail.com>
wrote:

On Tue, Feb 14, 2017 at 12:04 PM, Alessandro Baggi
<alessandro.baggi@gmail.com> wrote:

Hi list,
sorry for my english, I will try to example as well. I've a query
that joins
multiple tables and return a result like:

id,customers,phone,code,number
1 , aaaaaaaa,33333,123 , 2
2 , aassdsds,33322,211 , 1
3 , oooooooo,21221,221 , 1

I need, where "number" field is > 1, to duplicate the row * N(number
field
value) with a result like this:

id,customers,phone,code,number
1 , aaaaaaaa,33333,123 , 2
1 , aaaaaaaa,33333,123 , 2
2 , aassdsds,33322,211 , 1
3 , oooooooo,21221,221 , 1

How I can accomplish to this problem?

SELECT * FROM foo CROSS JOIN LATERAL (1,number);

:-D

oops -- copy/paste error

SELECT * FROM foo CROSS JOIN LATERAL generate_series(1,number);

merlin
.

Hi Merlin,
I've tried your suggested code and with cross join and generate_series I
can generate multiple row. There is a way to put as second args a column
values? I've tried to put "table.number" column values but I got
"generate_series() does not exists". Inserting a simple int like 5 I get
5 results for each row.

I've searched on google but can't find a valid example.

Thanks in advance.

Hi Merlin,
I've solved my problem (passing column as number) using a cast
generate_series(1,table.number::int)

thanks to all for answart.

SOLVED

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

For my enlightenment, why use LATERAL here? I get the same result with a
simple CROSS JOIN (though overall I like the clever solution).

Cheers,
Steve

On Thu, Feb 16, 2017 at 12:11 AM, Alessandro Baggi <
alessandro.baggi@gmail.com> wrote:

On Thu, Feb 16, 2017 at 9:16 AM, Steve Crawford <
scrawford@pinpointresearch.com> wrote:

For my enlightenment, why use LATERAL here? I get the same result with a
simple CROSS JOIN (though overall I like the clever solution).

​To be explicit, I think. CROSS JOIN function() implies lateral in the
implementation but you can always specify it if desired. Like saying LEFT
OUTER JOIN instead of LEFT JOIN

If it wasn't lateral the reference to number in "generate_series(1,
number)" would fail.

David J.
​

On Thu, Feb 16, 2017 at 10:27 AM, David G. Johnston
<david.g.johnston@gmail.com> wrote:

If it wasn't lateral the reference to number in "generate_series(1, number)"
would fail.

huh -- I didn't know that! Testing it out, all JOIN types imply
LATERAL if the function call is tlist SRF style (which is pretty werid
IMO) I tend to avoid optional words (with the important exception of
AS for column list renames) but I think it's a good idea to disclose
LATERAL in this case. It's a big clue to the reader what is going on
and the expanded form:

SELECT foo.* FROM foo CROSS JOIN LATERAL (SELECT a,b FROM func(foo.bar)) q;

... requires LATERAL to be explicitly stated. This form is more
general since it can be cleanly used when func() returns more than one
column.

merlin

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Merlin Moncure <mmoncure@gmail.com> writes:

On Thu, Feb 16, 2017 at 10:27 AM, David G. Johnston
<david.g.johnston@gmail.com> wrote:

If it wasn't lateral the reference to number in "generate_series(1, number)"
would fail.

huh -- I didn't know that! Testing it out, all JOIN types imply
LATERAL if the function call is tlist SRF style (which is pretty werid
IMO)

Quite. We wouldn't have done that, except we concluded that the SQL
spec requires it. I don't recall all the details, but I think there
are specific function-like syntaxes (maybe only UNNEST?) that the spec
says act as if they're always LATERAL. It seemed like it was less of
a wart to make that true for all function-in-FROM cases than just for
some of them.

I tend to avoid optional words (with the important exception of
AS for column list renames) but I think it's a good idea to disclose
LATERAL in this case.

Agreed. We do make rule printing show LATERAL explicitly when it's
active:

regression=# create view vv as select * from int8_tbl cross join generate_series(1,q1) g;
CREATE VIEW
regression=# \d+ vv
View "public.vv"
Column | Type | Collation | Nullable | Default | Storage | Description
--------+--------+-----------+----------+---------+---------+-------------
q1 | bigint | | | | plain |
q2 | bigint | | | | plain |
g | bigint | | | | plain |
View definition:
SELECT int8_tbl.q1,
int8_tbl.q2,
g.g
FROM int8_tbl
CROSS JOIN LATERAL generate_series(1::bigint, int8_tbl.q1) g(g);

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general