Remove gratuitous uses of deprecated SELECT INTO

petere · petere · commit b034ef9b376d · 2021-01-28T14:28:41.000+01:00
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. Discussion: https://www.postgresql.org/message-id/flat/96dc0df3-e13a-a85d-d045-d6e2c85218da%40enterprisedb.com
diff --git 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
@@ -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
@@ -883,7 +883,7 @@ SELECT * FROM each('aaa=&gt;bq, b=&gt;NULL, ""=&gt;1');
   <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
@@ -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
@@ -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
@@ -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
@@ -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
@@ -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
@@ -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
@@ -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
@@ -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
@@ -609,7 +609,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);
 
diff --git 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
@@ -17,7 +17,8 @@ INTERSECT
   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
@@ -86,7 +86,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;
@@ -142,7 +143,8 @@ SELECT count(b) FROM test_missing_target x, test_missing_target y
 
 --   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;
