On 2020-12-03 20:26, Peter Eisentraut wrote:
On 2020-12-03 16:34, Tom Lane wrote:
As I recall, a whole lot of the pain we have with INTO has to do
with the semantics we've chosen for INTO in a set-operation nest.
We think you can write something like
SELECT ... INTO foo FROM ... UNION SELECT ... FROM ...
but we insist on the INTO being in the first component SELECT.
I'd like to know exactly how much of that messiness is shared
by SQL Server.
On sqlfiddle.com, this works:
select a into t3 from t1 union select a from t2;
but this gets an error:
select a from t1 union select a into t4 from t2;
SELECT INTO must be the first query in a statement containing a UNION,
INTERSECT or EXCEPT operator.
So, with that in mind, here is an alternative proposal that points out
that SELECT INTO has some use for compatibility.
From ab0dc84e0519d618a316456598fc277a2f1c9f6f Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <pe...@eisentraut.org>
Date: Wed, 2 Dec 2020 12:09:39 +0100
Subject: [PATCH v2 1/2] Remove gratuitous uses of deprecated SELECT INTO
CREATE TABLE AS has been preferred over SELECT INTO (outside of ecpg
and PL/pgSQL) for a long time. There were still a few uses of SELECT
INTO in tests and documentation, some old, some more recent. This
changes them to CREATE TABLE AS. Some occurrences in the tests remain
where they are specifically testing SELECT INTO parsing or similar.
---
contrib/sepgsql/expected/label.out | 2 +-
contrib/sepgsql/sql/label.sql | 2 +-
doc/src/sgml/hstore.sgml | 2 +-
src/bin/pg_basebackup/t/010_pg_basebackup.pl | 4 ++--
src/bin/pg_checksums/t/002_actions.pl | 2 +-
src/test/regress/expected/create_index.out | 2 +-
src/test/regress/expected/create_misc.out | 2 +-
src/test/regress/expected/random.out | 3 ++-
src/test/regress/expected/select_implicit.out | 6 ++++--
src/test/regress/expected/select_implicit_1.out | 6 ++++--
src/test/regress/expected/select_implicit_2.out | 6 ++++--
src/test/regress/sql/create_index.sql | 2 +-
src/test/regress/sql/create_misc.sql | 2 +-
src/test/regress/sql/random.sql | 3 ++-
src/test/regress/sql/select_implicit.sql | 6 ++++--
15 files changed, 30 insertions(+), 20 deletions(-)
diff --git a/contrib/sepgsql/expected/label.out
b/contrib/sepgsql/expected/label.out
index 0300bc6fb4..b1b7db55f6 100644
--- a/contrib/sepgsql/expected/label.out
+++ b/contrib/sepgsql/expected/label.out
@@ -6,7 +6,7 @@
--
CREATE TABLE t1 (a int, b text);
INSERT INTO t1 VALUES (1, 'aaa'), (2, 'bbb'), (3, 'ccc');
-SELECT * INTO t2 FROM t1 WHERE a % 2 = 0;
+CREATE TABLE t2 AS SELECT * FROM t1 WHERE a % 2 = 0;
CREATE FUNCTION f1 () RETURNS text
AS 'SELECT sepgsql_getcon()'
LANGUAGE sql;
diff --git a/contrib/sepgsql/sql/label.sql b/contrib/sepgsql/sql/label.sql
index d19c6edb4c..76e261bee8 100644
--- a/contrib/sepgsql/sql/label.sql
+++ b/contrib/sepgsql/sql/label.sql
@@ -7,7 +7,7 @@
--
CREATE TABLE t1 (a int, b text);
INSERT INTO t1 VALUES (1, 'aaa'), (2, 'bbb'), (3, 'ccc');
-SELECT * INTO t2 FROM t1 WHERE a % 2 = 0;
+CREATE TABLE t2 AS SELECT * FROM t1 WHERE a % 2 = 0;
CREATE FUNCTION f1 () RETURNS text
AS 'SELECT sepgsql_getcon()'
diff --git a/doc/src/sgml/hstore.sgml b/doc/src/sgml/hstore.sgml
index 14a36ade00..25904d9562 100644
--- a/doc/src/sgml/hstore.sgml
+++ b/doc/src/sgml/hstore.sgml
@@ -841,7 +841,7 @@ <title>Statistics</title>
<para>
Using a table:
<programlisting>
-SELECT (each(h)).key, (each(h)).value INTO stat FROM testhstore;
+CREATE TABLE stat AS SELECT (each(h)).key, (each(h)).value FROM testhstore;
</programlisting>
</para>
diff --git a/src/bin/pg_basebackup/t/010_pg_basebackup.pl
b/src/bin/pg_basebackup/t/010_pg_basebackup.pl
index f674a7c94e..9eba7d8d7d 100644
--- a/src/bin/pg_basebackup/t/010_pg_basebackup.pl
+++ b/src/bin/pg_basebackup/t/010_pg_basebackup.pl
@@ -502,10 +502,10 @@
# create tables to corrupt and get their relfilenodes
my $file_corrupt1 = $node->safe_psql('postgres',
- q{SELECT a INTO corrupt1 FROM generate_series(1,10000) AS a; ALTER
TABLE corrupt1 SET (autovacuum_enabled=false); SELECT
pg_relation_filepath('corrupt1')}
+ q{CREATE TABLE corrupt1 AS SELECT a FROM generate_series(1,10000) AS a;
ALTER TABLE corrupt1 SET (autovacuum_enabled=false); SELECT
pg_relation_filepath('corrupt1')}
);
my $file_corrupt2 = $node->safe_psql('postgres',
- q{SELECT b INTO corrupt2 FROM generate_series(1,2) AS b; ALTER TABLE
corrupt2 SET (autovacuum_enabled=false); SELECT
pg_relation_filepath('corrupt2')}
+ q{CREATE TABLE corrupt2 AS SELECT b FROM generate_series(1,2) AS b;
ALTER TABLE corrupt2 SET (autovacuum_enabled=false); SELECT
pg_relation_filepath('corrupt2')}
);
# set page header and block sizes
diff --git a/src/bin/pg_checksums/t/002_actions.pl
b/src/bin/pg_checksums/t/002_actions.pl
index 4e4934532a..8a81f36a06 100644
--- a/src/bin/pg_checksums/t/002_actions.pl
+++ b/src/bin/pg_checksums/t/002_actions.pl
@@ -21,7 +21,7 @@ sub check_relation_corruption
$node->safe_psql(
'postgres',
- "SELECT a INTO $table FROM generate_series(1,10000) AS a;
+ "CREATE TABLE $table AS SELECT a FROM generate_series(1,10000)
AS a;
ALTER TABLE $table SET (autovacuum_enabled=false);");
$node->safe_psql('postgres',
diff --git a/src/test/regress/expected/create_index.out
b/src/test/regress/expected/create_index.out
index fc6afab58a..ce734f7ef3 100644
--- a/src/test/regress/expected/create_index.out
+++ b/src/test/regress/expected/create_index.out
@@ -1582,7 +1582,7 @@ DROP TABLE syscol_table;
--
-- Tests for IS NULL/IS NOT NULL with b-tree indexes
--
-SELECT unique1, unique2 INTO onek_with_null FROM onek;
+CREATE TABLE onek_with_null AS SELECT unique1, unique2 FROM onek;
INSERT INTO onek_with_null (unique1,unique2) VALUES (NULL, -1), (NULL, NULL);
CREATE UNIQUE INDEX onek_nulltest ON onek_with_null (unique2,unique1);
SET enable_seqscan = OFF;
diff --git a/src/test/regress/expected/create_misc.out
b/src/test/regress/expected/create_misc.out
index cee35ed02f..41bc4d7750 100644
--- a/src/test/regress/expected/create_misc.out
+++ b/src/test/regress/expected/create_misc.out
@@ -5,7 +5,7 @@
-- (any resemblance to real life is purely coincidental)
--
INSERT INTO tenk2 SELECT * FROM tenk1;
-SELECT * INTO TABLE onek2 FROM onek;
+CREATE TABLE onek2 AS SELECT * FROM onek;
INSERT INTO fast_emp4000 SELECT * FROM slow_emp4000;
SELECT *
INTO TABLE Bprime
diff --git a/src/test/regress/expected/random.out
b/src/test/regress/expected/random.out
index 302c3d61c7..a919b28d8d 100644
--- a/src/test/regress/expected/random.out
+++ b/src/test/regress/expected/random.out
@@ -23,7 +23,8 @@ INTERSECT
(0 rows)
-- count roughly 1/10 of the tuples
-SELECT count(*) AS random INTO RANDOM_TBL
+CREATE TABLE RANDOM_TBL AS
+ SELECT count(*) AS random
FROM onek WHERE random() < 1.0/10;
-- select again, the count should be different
INSERT INTO RANDOM_TBL (random)
diff --git a/src/test/regress/expected/select_implicit.out
b/src/test/regress/expected/select_implicit.out
index 61b485fdaa..27c07de92c 100644
--- a/src/test/regress/expected/select_implicit.out
+++ b/src/test/regress/expected/select_implicit.out
@@ -202,7 +202,8 @@ SELECT count(*) FROM test_missing_target x,
test_missing_target y
-- group w/o existing GROUP BY target under ambiguous condition
-- into a table
-SELECT count(*) INTO TABLE test_missing_target2
+CREATE TABLE test_missing_target2 AS
+SELECT count(*)
FROM test_missing_target x, test_missing_target y
WHERE x.a = y.a
GROUP BY x.b ORDER BY x.b;
@@ -318,7 +319,8 @@ LINE 1: SELECT count(b) FROM test_missing_target x,
test_missing_tar...
^
-- group w/o existing GROUP BY target under ambiguous condition
-- into a table
-SELECT count(x.b) INTO TABLE test_missing_target3
+CREATE TABLE test_missing_target3 AS
+SELECT count(x.b)
FROM test_missing_target x, test_missing_target y
WHERE x.a = y.a
GROUP BY x.b/2 ORDER BY x.b/2;
diff --git a/src/test/regress/expected/select_implicit_1.out
b/src/test/regress/expected/select_implicit_1.out
index f277375ebf..d67521e8f8 100644
--- a/src/test/regress/expected/select_implicit_1.out
+++ b/src/test/regress/expected/select_implicit_1.out
@@ -202,7 +202,8 @@ SELECT count(*) FROM test_missing_target x,
test_missing_target y
-- group w/o existing GROUP BY target under ambiguous condition
-- into a table
-SELECT count(*) INTO TABLE test_missing_target2
+CREATE TABLE test_missing_target2 AS
+SELECT count(*)
FROM test_missing_target x, test_missing_target y
WHERE x.a = y.a
GROUP BY x.b ORDER BY x.b;
@@ -318,7 +319,8 @@ LINE 1: SELECT count(b) FROM test_missing_target x,
test_missing_tar...
^
-- group w/o existing GROUP BY target under ambiguous condition
-- into a table
-SELECT count(x.b) INTO TABLE test_missing_target3
+CREATE TABLE test_missing_target3 AS
+SELECT count(x.b)
FROM test_missing_target x, test_missing_target y
WHERE x.a = y.a
GROUP BY x.b/2 ORDER BY x.b/2;
diff --git a/src/test/regress/expected/select_implicit_2.out
b/src/test/regress/expected/select_implicit_2.out
index 91c3a24f92..7a353d0862 100644
--- a/src/test/regress/expected/select_implicit_2.out
+++ b/src/test/regress/expected/select_implicit_2.out
@@ -202,7 +202,8 @@ SELECT count(*) FROM test_missing_target x,
test_missing_target y
-- group w/o existing GROUP BY target under ambiguous condition
-- into a table
-SELECT count(*) INTO TABLE test_missing_target2
+CREATE TABLE test_missing_target2 AS
+SELECT count(*)
FROM test_missing_target x, test_missing_target y
WHERE x.a = y.a
GROUP BY x.b ORDER BY x.b;
@@ -318,7 +319,8 @@ LINE 1: SELECT count(b) FROM test_missing_target x,
test_missing_tar...
^
-- group w/o existing GROUP BY target under ambiguous condition
-- into a table
-SELECT count(x.b) INTO TABLE test_missing_target3
+CREATE TABLE test_missing_target3 AS
+SELECT count(x.b)
FROM test_missing_target x, test_missing_target y
WHERE x.a = y.a
GROUP BY x.b/2 ORDER BY x.b/2;
diff --git a/src/test/regress/sql/create_index.sql
b/src/test/regress/sql/create_index.sql
index 824cb9f9e8..fd4f30876e 100644
--- a/src/test/regress/sql/create_index.sql
+++ b/src/test/regress/sql/create_index.sql
@@ -609,7 +609,7 @@ CREATE INDEX ON syscol_table (a) WHERE ctid >= '(1000,0)';
-- Tests for IS NULL/IS NOT NULL with b-tree indexes
--
-SELECT unique1, unique2 INTO onek_with_null FROM onek;
+CREATE TABLE onek_with_null AS SELECT unique1, unique2 FROM onek;
INSERT INTO onek_with_null (unique1,unique2) VALUES (NULL, -1), (NULL, NULL);
CREATE UNIQUE INDEX onek_nulltest ON onek_with_null (unique2,unique1);
diff --git a/src/test/regress/sql/create_misc.sql
b/src/test/regress/sql/create_misc.sql
index d0b04a821f..c7d0d064c3 100644
--- a/src/test/regress/sql/create_misc.sql
+++ b/src/test/regress/sql/create_misc.sql
@@ -8,7 +8,7 @@
INSERT INTO tenk2 SELECT * FROM tenk1;
-SELECT * INTO TABLE onek2 FROM onek;
+CREATE TABLE onek2 AS SELECT * FROM onek;
INSERT INTO fast_emp4000 SELECT * FROM slow_emp4000;
diff --git a/src/test/regress/sql/random.sql b/src/test/regress/sql/random.sql
index ae6b70a157..8187b2c288 100644
--- a/src/test/regress/sql/random.sql
+++ b/src/test/regress/sql/random.sql
@@ -17,7 +17,8 @@
FROM onek ORDER BY random() LIMIT 1);
-- count roughly 1/10 of the tuples
-SELECT count(*) AS random INTO RANDOM_TBL
+CREATE TABLE RANDOM_TBL AS
+ SELECT count(*) AS random
FROM onek WHERE random() < 1.0/10;
-- select again, the count should be different
diff --git a/src/test/regress/sql/select_implicit.sql
b/src/test/regress/sql/select_implicit.sql
index d815504222..de3aef8d81 100644
--- a/src/test/regress/sql/select_implicit.sql
+++ b/src/test/regress/sql/select_implicit.sql
@@ -86,7 +86,8 @@ CREATE TABLE test_missing_target (a int, b int, c char(8), d
char);
-- group w/o existing GROUP BY target under ambiguous condition
-- into a table
-SELECT count(*) INTO TABLE test_missing_target2
+CREATE TABLE test_missing_target2 AS
+SELECT count(*)
FROM test_missing_target x, test_missing_target y
WHERE x.a = y.a
GROUP BY x.b ORDER BY x.b;
@@ -142,7 +143,8 @@ CREATE TABLE test_missing_target (a int, b int, c char(8),
d char);
-- group w/o existing GROUP BY target under ambiguous condition
-- into a table
-SELECT count(x.b) INTO TABLE test_missing_target3
+CREATE TABLE test_missing_target3 AS
+SELECT count(x.b)
FROM test_missing_target x, test_missing_target y
WHERE x.a = y.a
GROUP BY x.b/2 ORDER BY x.b/2;
--
2.29.2
From 82f9247779658915fe0e57a8797622483de7e4c3 Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <pe...@eisentraut.org>
Date: Wed, 9 Dec 2020 21:35:14 +0100
Subject: [PATCH v2 2/2] doc: Clarify status of SELECT INTO on reference page
The documentation as well as source code comments weren't entirely
clear whether SELECT INTO was truly deprecated (thus in theory
destined to be removed eventually), or just a less recommended
variant. After discussion, it appears that other implementations also
use SELECT INTO in direct SQL in a way similar to PostgreSQL, so it
seems worth keeping it for compatibility. Update the language in the
documentation to that effect.
Discussion:
https://www.postgresql.org/message-id/flat/96dc0df3-e13a-a85d-d045-d6e2c85218da%40enterprisedb.com
---
doc/src/sgml/ref/select_into.sgml | 8 +++++---
1 file changed, 5 insertions(+), 3 deletions(-)
diff --git a/doc/src/sgml/ref/select_into.sgml
b/doc/src/sgml/ref/select_into.sgml
index 7b327d9eee..acc6401485 100644
--- a/doc/src/sgml/ref/select_into.sgml
+++ b/doc/src/sgml/ref/select_into.sgml
@@ -138,9 +138,11 @@ <title>Compatibility</title>
in <application>ECPG</application> (see <xref linkend="ecpg"/>) and
<application>PL/pgSQL</application> (see <xref linkend="plpgsql"/>).
The <productname>PostgreSQL</productname> usage of <command>SELECT
- INTO</command> to represent table creation is historical. It is
- best to use <command>CREATE TABLE AS</command> for this purpose in
- new code.
+ INTO</command> to represent table creation is historical. Some other SQL
+ implementations also use <command>SELECT INTO</command> in this way (but
+ most SQL implementations support <command>CREATE TABLE AS</command>
+ instead). Apart from such compatibility considerations, it is best to use
+ <command>CREATE TABLE AS</command> for this purpose in new code.
</para>
</refsect1>
--
2.29.2
