[prev in list] [next in list] [prev in thread] [next in thread]
List: postgresql-general
Subject: RE: SELECT INTO question
From: Kevin Brannen <KBrannen () efji ! com>
Date: 2019-07-25 19:52:42
Message-ID: DM6PR19MB34512D4CDC0EBF4D044F405BA4C10 () DM6PR19MB3451 ! namprd19 ! prod ! outlook ! com
[Download RAW message or body]
-----Original Message-----
From: Tom Lane <tgl@sss.pgh.pa.us>
Sent: Thursday, July 25, 2019 2:47 PM
To: Adrian Klaver <adrian.klaver@aklaver.com>
Cc: Kevin Brannen <KBrannen@efji.com>; pgsql-generallists.postgresql.org \
<pgsql-general@lists.postgresql.org>
Subject: Re: SELECT INTO question
Adrian Klaver <adrian.klaver@aklaver.com<mailto:adrian.klaver@aklaver.com>> writes:
> On 7/25/19 12:23 PM, Kevin Brannen wrote:
> > We're trying to understand what happened with a SELECT INTO. The
> > problem can be see with this example:
> >
> > nms=# select into t2 from t1;
> > SELECT 5
> > # select * from t2;
> > --
> > (5 rows)
> The list of output expressions after SELECT can be empty, producing a
> zero-column result table. This is not valid syntax according to the
> SQL standard. PostgreSQL allows it to be consistent with allowing
> zero-column tables. However, an empty list is not allowed when
> DISTINCT is used.
Right, you selected no columns from t1, so t2 has no columns (and yet five rows). \
Worth noting here is that psql is a bit squirrely about displaying zero-column \
results --- it drops the column-names header line, and it doesn't emit a \
blank-line-per-row as one might expect.
Perhaps somebody ought to fix that, but it's such a corner case that no one has \
bothered yet.
Hmm, I don't particularly like that answer as I'd have preferred a "syntax error", \
but I do understand it.
Thanks for the answer, Adrian; and thanks for the expansion, Tom.
Kevin
This e-mail transmission, and any documents, files or previous e-mail messages \
attached to it, may contain confidential information. If you are not the intended \
recipient, or a person responsible for delivering it to the intended recipient, you \
are hereby notified that any disclosure, distribution, review, copy or use of any of \
the information contained in or attached to this message is STRICTLY PROHIBITED. If \
you have received this transmission in error, please immediately notify us by reply \
e-mail, and destroy the original transmission and its attachments without reading \
them or saving them to disk. Thank you.
[Attachment #3 (text/html)]
<html xmlns:v="urn:schemas-microsoft-com:vml" \
xmlns:o="urn:schemas-microsoft-com:office:office" \
xmlns:w="urn:schemas-microsoft-com:office:word" \
xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" \
xmlns="http://www.w3.org/TR/REC-html40"> <head>
<meta http-equiv="Content-Type" content="text/html; charset=us-ascii">
<meta name="Generator" content="Microsoft Word 15 (filtered medium)">
<style><!--
/* Font Definitions */
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri",sans-serif;}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:#0563C1;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:#954F72;
text-decoration:underline;}
p.MsoPlainText, li.MsoPlainText, div.MsoPlainText
{mso-style-priority:99;
mso-style-link:"Plain Text Char";
margin:0in;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Arial",sans-serif;}
span.PlainTextChar
{mso-style-name:"Plain Text Char";
mso-style-priority:99;
mso-style-link:"Plain Text";
font-family:"Arial",sans-serif;}
.MsoChpDefault
{mso-style-type:export-only;
font-family:"Calibri",sans-serif;}
@page WordSection1
{size:8.5in 11.0in;
margin:1.0in 1.0in 1.0in 1.0in;}
div.WordSection1
{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
</head>
<body lang="EN-US" link="#0563C1" vlink="#954F72">
<div class="WordSection1">
<p class="MsoPlainText" style="margin-left:.5in"><font size="3" face="Arial"><span \
style="font-size:12.0pt">-----Original Message-----<br>
From: Tom Lane <tgl@sss.pgh.pa.us> <br>
Sent: Thursday, July 25, 2019 2:47 PM<br>
To: Adrian Klaver <adrian.klaver@aklaver.com><br>
Cc: Kevin Brannen <KBrannen@efji.com>; pgsql-generallists.postgresql.org \
<pgsql-general@lists.postgresql.org><br>
Subject: Re: SELECT INTO question</span></font></p>
<p class="MsoPlainText" style="margin-left:.5in"><font size="3" \
face="Arial"><o:p> </o:p></font></p> <p class="MsoPlainText" \
style="margin-left:.5in"><font size="3" face="Arial"><span \
style="font-size:12.0pt">Adrian Klaver <<a \
href="mailto:adrian.klaver@aklaver.com"><font color="black"><span \
style="color:windowtext;text-decoration:none">adrian.klaver@aklaver.com</span></font></a>>
writes:<o:p></o:p></span></font></p>
<p class="MsoPlainText" style="margin-left:.5in"><font size="3" face="Arial"><span \
style="font-size:12.0pt">> On 7/25/19 12:23 PM, Kevin Brannen \
wrote:<o:p></o:p></span></font></p> <p class="MsoPlainText" \
style="margin-left:.5in"><font size="3" face="Arial"><span \
style="font-size:12.0pt">>> We're trying to understand what happened with a \
SELECT INTO. The <o:p></o:p></span></font></p>
<p class="MsoPlainText" style="margin-left:.5in"><font size="3" face="Arial"><span \
style="font-size:12.0pt">>> problem can be see with this \
example:<o:p></o:p></span></font></p> <p class="MsoPlainText" \
style="margin-left:.5in"><font size="3" face="Arial"><span \
style="font-size:12.0pt">>> <o:p></o:p></span></font></p>
<p class="MsoPlainText" style="margin-left:.5in"><font size="3" face="Arial"><span \
style="font-size:12.0pt">>> nms=# select into t2 from \
t1;<o:p></o:p></span></font></p> <p class="MsoPlainText" \
style="margin-left:.5in"><font size="3" face="Arial"><span \
style="font-size:12.0pt">>> SELECT 5<o:p></o:p></span></font></p> <p \
class="MsoPlainText" style="margin-left:.5in"><font size="3" face="Arial"><span \
style="font-size:12.0pt">>> # select * from t2;<o:p></o:p></span></font></p> <p \
class="MsoPlainText" style="margin-left:.5in"><font size="3" face="Arial"><span \
style="font-size:12.0pt">>> --<o:p></o:p></span></font></p> <p \
class="MsoPlainText" style="margin-left:.5in"><font size="3" face="Arial"><span \
style="font-size:12.0pt">>> (5 rows)<o:p></o:p></span></font></p> <p \
class="MsoPlainText" style="margin-left:.5in"><font size="3" \
face="Arial"><o:p> </o:p></font></p> <p class="MsoPlainText" \
style="margin-left:.5in"><font size="3" face="Arial"><span \
style="font-size:12.0pt">> The list of output expressions after SELECT can be \
empty, producing a <o:p></o:p></span></font></p>
<p class="MsoPlainText" style="margin-left:.5in"><font size="3" face="Arial"><span \
style="font-size:12.0pt">> zero-column result table. This is not valid syntax \
according to the <o:p></o:p></span></font></p>
<p class="MsoPlainText" style="margin-left:.5in"><font size="3" face="Arial"><span \
style="font-size:12.0pt">> SQL standard. PostgreSQL allows it to be consistent \
with allowing <o:p></o:p></span></font></p>
<p class="MsoPlainText" style="margin-left:.5in"><font size="3" face="Arial"><span \
style="font-size:12.0pt">> zero-column tables. However, an empty list is not \
allowed when <o:p></o:p></span></font></p>
<p class="MsoPlainText" style="margin-left:.5in"><font size="3" face="Arial"><span \
style="font-size:12.0pt">> DISTINCT is used.<o:p></o:p></span></font></p> <p \
class="MsoPlainText" style="margin-left:.5in"><font size="3" \
face="Arial"><o:p> </o:p></font></p> <p class="MsoPlainText" \
style="margin-left:.5in"><font size="3" face="Arial"><span \
style="font-size:12.0pt">Right, you selected no columns from t1, so t2 has no columns \
(and yet five rows). Worth noting here is that psql is a bit squirrely about \
displaying zero-column results --- it drops the column-names header line, and it \
doesn't emit a blank-line-per-row as one might expect.<o:p></o:p></span></font></p> \
<p class="MsoPlainText" style="margin-left:.5in"><font size="3" face="Arial"><span \
style="font-size:12.0pt">Perhaps somebody ought to fix that, but it's such a corner \
case that no one has bothered yet.<o:p></o:p></span></font></p> <p \
class="MsoPlainText"><font size="3" face="Arial"><o:p> </o:p></font></p> <p \
class="MsoPlainText"><font size="3" face="Arial"><o:p> </o:p></font></p> <p \
class="MsoPlainText"><font size="3" color="black" \
face="Arial"><o:p> </o:p></font></p> <p class="MsoNormal"><font size="3" \
color="black" face="Arial"><span \
style="font-size:12.0pt;font-family:"Arial",sans-serif;color:black">Hmm, I \
don't particularly like that answer as I'd have preferred a "syntax error", \
but I do understand it.<o:p></o:p></span></font></p> <p class="MsoNormal"><font \
size="3" color="black" face="Arial"><span \
style="font-size:12.0pt;font-family:"Arial",sans-serif;color:black"><o:p> </o:p></span></font></p>
<p class="MsoNormal"><font size="3" color="black" face="Arial"><span \
style="font-size:12.0pt;font-family:"Arial",sans-serif;color:black">Thanks \
for the answer, Adrian; and thanks for the expansion, \
Tom.<o:p></o:p></span></font></p> <p class="MsoNormal"><font size="3" color="black" \
face="Arial"><span style="font-size:12.0pt;font-family:"Arial",sans-serif;color:black"><o:p> </o:p></span></font></p>
<p class="MsoNormal"><font size="3" color="black" face="Arial"><span \
style="font-size:12.0pt;font-family:"Arial",sans-serif;color:black">Kevin<o:p></o:p></span></font></p>
<p class="MsoPlainText"><font size="3" color="black" \
face="Arial"><o:p> </o:p></font></p> </div>
This e-mail transmission, and any documents, files or previous e-mail messages \
attached to it, may contain confidential information. If you are not the intended \
recipient, or a person responsible for delivering it to the intended recipient, you \
are hereby notified that any disclosure, distribution, review, copy or use of any of \
the information contained in or attached to this message is STRICTLY PROHIBITED. If \
you have received this transmission in error, please immediately notify us by reply \
e-mail, and destroy the original transmission and its attachments without reading \
them or saving them to disk. Thank you. </body>
</html>
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic