[prev in list] [next in list] [prev in thread] [next in thread]
List: postgresql-general
Subject: Re: table name "unnest" specified more than once
From: "David G. Johnston" <david.g.johnston () gmail ! com>
Date: 2020-02-28 4:45:06
Message-ID: CAKFQuwasktVbjod4df65Om+qHtp71MntJShhTB7wscOhbQGJkw () mail ! gmail ! com
[Download RAW message or body]
On Thursday, February 27, 2020, Guyren Howe <guyren@gmail.com> wrote:
> # select
> *
> from
> unnest(array[array['a', 'b'], array['c', 'c']]),
> unnest(array[array['1', '2'], array['3', '4']]);
> ERROR: 42712: table name "unnest" specified more than once
>
This specific error is resolved by using the alias feature of the FROM
clause:
alias
A substitute name for the FROM item containing the alias. An alias is used
for brevity or to eliminate ambiguity for self-joins (where the same table
is scanned multiple times)
>
> I'm trying to cross-join multiple two-dimensional arrays, expecting to
> retain the inner arrays. I've been trying for hours without luck; the
> ever-esoteric SQL syntax foils me at every turn.
>
> It's a shame I can't get the unnest function not to just concatenate the
> inner arrays if I just put a bunch of them. This doesn't strike me as
> optimal behavior.
>
Putting unnest in a select-list might get you closer to your goal. Or the
array-array concatenating operator.
>
> For more context, I'm trying to make a system of functions to score a
> Texas Hold ‘Em game. So I have a card type consisting of a pair of suit and
> rank, and I'm tossing them about. The cross-join is so I can build all
> candidate hands for scoring. I'm trying to create a function I can call
> like this:
>
> select
> best_hands_with_river(
> array[
> c('H', 'K'),
> c('D', 'A')
> ],
> array[
> c('C', '2'),
> c('C', 'K'),
> c('S', 'K'),
> c('H', 'A'),
> c('C', 'A')
> ])
>
> Here, c is a function that constructs a card type. Card is a ROW(varchar,
> varchar).
>
> So: how do I cross-join three identical arrays of my card type?
>
Something like:
Select f1.a, f2.b
From (select * from unnest(arr)) as f1 (a)
Cross join (select * From unnest(arr)) as f2 (b)
Etc...Maybe with parentheses...
There may be a more succinct way to write this but going verbose until it
works minimizes the amount of syntax you need to deal with. Though I
personally encourage writing intentional cross join (really, all joins)
using join syntax instead of comma-separated from items.
David J.
[Attachment #3 (text/html)]
On Thursday, February 27, 2020, Guyren Howe <<a \
href="mailto:guyren@gmail.com">guyren@gmail.com</a>> wrote:<br><blockquote \
class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc \
solid;padding-left:1ex"><div \
style="word-wrap:break-word;line-break:after-white-space"><div><div \
style="margin:0px;font-stretch:normal;font-size:10px;line-height:normal;font-family:Monaco;background-color:rgb(255,255,255)"><span># \
select</span></div><div \
style="margin:0px;font-stretch:normal;font-size:10px;line-height:normal;font-family:Monaco;background-color:rgb(255,255,255)"><span> \
* </span></div><div \
style="margin:0px;font-stretch:normal;font-size:10px;line-height:normal;font-family:Monaco;background-color:rgb(255,255,255)"><span>from \
</span></div><div style="margin:0px;font-stretch:normal;font-size:10px;line-height:normal;font-family:Monaco;background-color:rgb(255,255,255)"><span><span \
style="white-space:pre-wrap"> </span>unnest(array[array['a', 'b'], \
array['c', 'c']]), </span></div><div \
style="margin:0px;font-stretch:normal;font-size:10px;line-height:normal;font-family:Monaco;background-color:rgb(255,255,255)"><span><span \
style="white-space:pre-wrap"> </span>unnest(array[array['1', '2'], \
array['3', '4']]);</span></div><div \
style="margin:0px;font-stretch:normal;font-size:10px;line-height:normal;font-family:Monaco;background-color:rgb(255,255,255)"><span>ERROR: \
42712: table name "unnest" specified more than once</span></div></div><div \
style="margin:0px;font-stretch:normal;font-size:10px;line-height:normal;font-family:Mo \
naco;background-color:rgb(255,255,255)"></div></div></blockquote><div><br></div><div>This \
specific error is resolved by using the alias feature of the FROM \
clause:</div><div><br></div><div><dt \
style="box-sizing:border-box;font-family:monospace;color:rgb(13,10,11);font-size:14.399999618530273px"><span \
class="term" style="box-sizing:border-box"><span class="replaceable" \
style="box-sizing:border-box;font-style:italic;font-weight:900"><code \
style="box-sizing:border-box;font-family:monospace,monospace;font-size:1em;word-break: \
break-word;border-top-left-radius:0.25rem;border-top-right-radius:0.25rem;border-bottom-right-radius:0.25rem;border-bottom-left-radius:0.25rem;margin:0.6rem \
0px;color:inherit!important;background-color:rgb(248,249,250)!important">alias</code></span></span></dt><dd \
style="box-sizing:border-box;margin-bottom:0.5rem;margin-left:0px;margin-top:1rem;color:rgb(13,10,11);font-family:"Open \
Sans",sans-serif;font-size:14.399999618530273px"><p \
style="box-sizing:border-box;margin:1rem 0px 1rem 2rem!important">A substitute name \
for the <code class="literal" \
style="box-sizing:border-box;font-family:monospace,monospace;font-size:1em;word-break: \
break-word;border-top-left-radius:0.25rem;border-top-right-radius:0.25rem;border-bottom-right-radius:0.25rem;border-bottom-left-radius:0.25rem;margin:0.6rem \
0px;color:inherit!important;background-color:rgb(248,249,250)!important">FROM</code> \
item containing the alias. An alias is used for brevity or to eliminate ambiguity for \
self-joins (where the same table is scanned multiple times)<span \
style="font-family:"Helvetica \
Neue",Helvetica,Arial,sans-serif;font-size:15px"> \
</span></p></dd></div><blockquote class="gmail_quote" style="margin:0 0 0 \
.8ex;border-left:1px #ccc solid;padding-left:1ex"><div \
style="word-wrap:break-word;line-break:after-white-space"><div \
style="margin:0px;font-stretch:normal;font-size:10px;line-height:normal;font-family:Monaco;background-color:rgb(255,255,255)"></div><div><br></div><div>I'm \
trying to cross-join multiple two-dimensional arrays, expecting to retain the inner \
arrays. I've been trying for hours without luck; the ever-esoteric SQL syntax foils \
me at every turn.</div><div><br></div><div>It's a shame I can't get the unnest \
function not to just concatenate the inner arrays if I just put a bunch of them. This \
doesn't strike me as optimal \
behavior.</div></div></blockquote><div><br></div><div>Putting unnest in a select-list \
might get you closer to your goal. Or the array-array concatenating \
operator.</div><div> </div><blockquote class="gmail_quote" style="margin:0 0 0 \
.8ex;border-left:1px #ccc solid;padding-left:1ex"><div \
style="word-wrap:break-word;line-break:after-white-space"><div><br></div><div>For \
more context, I'm trying to make a system of functions to score a Texas Hold ‘Em \
game. So I have a card type consisting of a pair of suit and rank, and I'm tossing \
them about. The cross-join is so I can build all candidate hands for scoring. I'm \
trying to create a function I can call like \
this:</div><div><br></div><div><div>select</div><div><span \
style="white-space:pre-wrap"> </span>best_hands_with_river(</div><div><span \
style="white-space:pre-wrap"> </span>array[</div><div><span \
style="white-space:pre-wrap"> </span>c('H', 'K'), </div><div><span \
style="white-space:pre-wrap"> </span>c('D', 'A')</div><div><span \
style="white-space:pre-wrap"> </span>],</div><div><span \
style="white-space:pre-wrap"> </span>array[</div><div><span \
style="white-space:pre-wrap"> </span>c('C', '2'),</div><div><span \
style="white-space:pre-wrap"> </span>c('C', 'K'),</div><div><span \
style="white-space:pre-wrap"> </span>c('S', 'K'),</div><div><span \
style="white-space:pre-wrap"> </span>c('H', 'A'),</div><div><span \
style="white-space:pre-wrap"> </span>c('C', 'A')</div><div><span \
style="white-space:pre-wrap"> </span>])</div></div><div><br></div><div>Here, c is a \
function that constructs a card type. Card is a ROW(varchar, \
varchar).</div><div><br></div><div>So: how do I cross-join three identical arrays of \
my card type?</div></div></blockquote><div><br></div><div>Something \
like:</div><div><br></div><div>Select f1.a, f2.b</div><div>From (select * from \
unnest(arr)) as f1 (a)</div><div>Cross join (select * From unnest(arr)) as f2 \
(b)</div><div>Etc...Maybe with parentheses...</div><div><br></div><div>There may be a \
more succinct way to write this but going verbose until it works minimizes the amount \
of syntax you need to deal with. Though I personally encourage writing intentional \
cross join (really, all joins) using join syntax instead of comma-separated from \
items.</div><div><br></div><div>David J.</div><div><br></div><blockquote \
class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc \
solid;padding-left:1ex"><div \
style="word-wrap:break-word;line-break:after-white-space"><img \
src="https://bowtie.mailbutler.io/tracking/hit/49429725-2f4b-45eb-b00f-a9afec9e672a/b94cc7d2-80d8-4828-bb90-fc57e915b422/t.gif" \
align="left" width="0" height="0" style="width:0;height:0;border:0" title="" \
alt=""></div></blockquote>
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic