Thread: [GENERAL] postgresql how to duplicate rows in result.

[GENERAL] postgresql how to duplicate rows in result.

From

Alessandro Baggi

Date:

15 February 2017, 00:04:09

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.

Re: [GENERAL] postgresql how to duplicate rows in result.

From

Melvin Davidson

Date:

15 February 2017, 00:34:11

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.

Re: [GENERAL] postgresql how to duplicate rows in result.

From

Merlin Moncure

Date:

15 February 2017, 00:36:40

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

Re: [GENERAL] postgresql how to duplicate rows in result.

From

John McKown

Date:

15 February 2017, 00:42:44

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

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

Re: [GENERAL] postgresql how to duplicate rows in result.

From

Merlin Moncure

Date:

15 February 2017, 02:50:17

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

Re: [GENERAL] postgresql how to duplicate rows in result.

From

Merlin Moncure

Date:

15 February 2017, 02:51:41

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

Re: [GENERAL] postgresql how to duplicate rows in result.

From

John McKown

Date:

15 February 2017, 02:58:38

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

Re: [GENERAL] postgresql how to duplicate rows in result.

From

Alessandro Baggi

Date:

16 February 2017, 00:11:53

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.

Re: [GENERAL] postgresql how to duplicate rows in result.

From

Alessandro Baggi

Date:

16 February 2017, 14:11:54

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

Re: [GENERAL] postgresql how to duplicate rows in result.

From

Steve Crawford

Date:

16 February 2017, 22:16:24

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:

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

Re: [GENERAL] postgresql how to duplicate rows in result.

From

"David G. Johnston"

Date:

16 February 2017, 22:27:38

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.

Re: [GENERAL] postgresql how to duplicate rows in result.

From

Merlin Moncure

Date:

22 February 2017, 02:14:36

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

Re: [GENERAL] postgresql how to duplicate rows in result.

From

Tom Lane

Date:

22 February 2017, 02:58:00

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