Thread: SELECT INTO without columns or star
Hi, hackers
When I exec a sql SELECT INTO without columns or * by mistake, it succeeds:
select * from t1;
a | b
---+---
1 | 2
2 | 3
3 | 4
(3 rows)
select into t2 from t1;
SELECT 3
\pset null '(null)'
Null display is "(null)".
select * from t2;
--
(3 rows)
It seems that t2 has empty rows but not null. Is it an expected behavior?
And what’s the semantic of SELECT INTO without any columns?
I also see lots of that SELECT INTO in out test cases like:
-- SELECT INTO doesn't support USING
SELECT INTO tableam_tblselectinto_heap2 USING heap2 FROM tableam_tbl_heap2;
When I exec a sql SELECT INTO without columns or * by mistake, it succeeds:
select * from t1;
a | b
---+---
1 | 2
2 | 3
3 | 4
(3 rows)
select into t2 from t1;
SELECT 3
\pset null '(null)'
Null display is "(null)".
select * from t2;
--
(3 rows)
It seems that t2 has empty rows but not null. Is it an expected behavior?
And what’s the semantic of SELECT INTO without any columns?
I also see lots of that SELECT INTO in out test cases like:
-- SELECT INTO doesn't support USING
SELECT INTO tableam_tblselectinto_heap2 USING heap2 FROM tableam_tbl_heap2;
Regards,
Zhang Mingli
On Fri, Mar 31, 2023 at 8:10 AM Zhang Mingli <zmlpostgres@gmail.com> wrote:
When I exec a sql SELECT INTO without columns or * by mistake, it succeeds:
Yes, a table may have zero columns by design.
David J.
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Fri, Mar 31, 2023 at 8:10 AM Zhang Mingli <zmlpostgres@gmail.com> wrote:
>> When I exec a sql SELECT INTO without columns or * by mistake, it succeeds:
> Yes, a table may have zero columns by design.
Yup, we've allowed that for some time now; see the compatibility comments
at the bottom of the SELECT man page.
psql's display of zero-column results is a bit weird, which maybe
somebody should fix sometime:
regression=# select from generate_series(1,4);
--
(4 rows)
I'd expect four blank lines there. Expanded format is even less sane:
regression=# \x
Expanded display is on.
regression=# select from generate_series(1,4);
(4 rows)
ISTM that should produce
[ RECORD 1 ]
[ RECORD 2 ]
[ RECORD 3 ]
[ RECORD 4 ]
and no "(4 rows)" footer, because \x mode doesn't normally print that.
This is all just cosmetic of course, but it's still confusing.
regards, tom lane
On Fri, Mar 31, 2023 at 11:26 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Fri, Mar 31, 2023 at 8:10 AM Zhang Mingli <zmlpostgres@gmail.com> wrote:
>> When I exec a sql SELECT INTO without columns or * by mistake, it succeeds:
> Yes, a table may have zero columns by design.
Yup, we've allowed that for some time now; see the compatibility comments
at the bottom of the SELECT man page.
psql's display of zero-column results is a bit weird, which maybe
somebody should fix sometime:
regression=# select from generate_series(1,4);
--
(4 rows)
I'd expect four blank lines there. Expanded format is even less sane:
regression=# \x
Expanded display is on.
regression=# select from generate_series(1,4);
(4 rows)
ISTM that should produce
[ RECORD 1 ]
[ RECORD 2 ]
[ RECORD 3 ]
[ RECORD 4 ]
and no "(4 rows)" footer, because \x mode doesn't normally print that.
This is all just cosmetic of course, but it's still confusing.
regards, tom lane
Tom,
I wouldn't mind working on a patch to fix this... (Especially if it helps the %T get into PSQL<grin>).
I find this output confusing as well.
Should I start a new email thread: Proposal: Fix psql output when selecting no columns
And get the discussion moving. I'd like to get a clear answer on what to output. But I have
And get the discussion moving. I'd like to get a clear answer on what to output. But I have
become more comfortable with PSQL due to messing with readline for windows, and 2-3 other patches
I've been working on.
Thanks, Kirk