SELECT INTO and ON COMMIT
Is there any way to add an ON COMMIT clause to a SELECT INTO TEMP TABLE?
Thanks.
--
http://yves.zioup.com
gpg: 4096R/32B0F416
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Wed, May 13, 2015 at 05:29:36PM -0600, Yves Dorfsman wrote:
Is there any way to add an ON COMMIT clause to a SELECT INTO TEMP TABLE?
Well CREATE TABLE has a ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP }
clause, but I don't see it in SELECT INTO, so it seems you have to
create the temp table using CREATE TABLE, then INSERT ... SELECT.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ Everyone has their own god. +
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Wed, May 13, 2015 at 4:38 PM, Bruce Momjian <bruce@momjian.us> wrote:
On Wed, May 13, 2015 at 05:29:36PM -0600, Yves Dorfsman wrote:
Is there any way to add an ON COMMIT clause to a SELECT INTO TEMP TABLE?
Well CREATE TABLE has a ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP }
clause, but I don't see it in SELECT INTO, so it seems you have to
create the temp table using CREATE TABLE, then INSERT ... SELECT.
From the documentation of SELECT INTO
"
The PostgreSQL usage of SELECT INTO to represent table creation is
historical. It is best to use CREATE TABLE AS for this purpose in new code.
"
http://www.postgresql.org/docs/9.4/interactive/sql-createtableas.html
Which effectively means consider the feature deprecated. Especially since
CREATE TABLE is standard conforming and SELECT INTO is not.
Given this I'm not sure why we bothered to add "UNLOGGED" to SELECT INTO
back in 9.1 ...
David J.
On Wed, May 13, 2015 at 05:29:36PM -0600, Yves Dorfsman wrote:
Is there any way to add an ON COMMIT clause to a SELECT INTO TEMP TABLE?
On 2015-05-13 17:56, David G. Johnston wrote:
From the documentation of SELECT INTO
"
The PostgreSQL usage of SELECT INTO to represent table creation is historical.
It is best to use CREATE TABLE AS for this purpose in new code.
"http://www.postgresql.org/docs/9.4/interactive/sql-createtableas.html
Which effectively means consider the feature deprecated. Especially since
CREATE TABLE is standard conforming and SELECT INTO is not.
Ah! This works. Thanks.
Will `SELECT INTO` be deprecated? It is very convenient when writing pgplsql
functions, to select into a record.
--
http://yves.zioup.com
gpg: 4096R/32B0F416
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Wed, May 13, 2015 at 06:05:43PM -0600, Yves Dorfsman wrote:
On Wed, May 13, 2015 at 05:29:36PM -0600, Yves Dorfsman wrote:
Is there any way to add an ON COMMIT clause to a SELECT INTO TEMP TABLE?
On 2015-05-13 17:56, David G. Johnston wrote:
From the documentation of SELECT INTO
"
The PostgreSQL usage of SELECT INTO to represent table creation is historical.
It is best to use CREATE TABLE AS for this purpose in new code.
"http://www.postgresql.org/docs/9.4/interactive/sql-createtableas.html
Which effectively means consider the feature deprecated. Especially since
CREATE TABLE is standard conforming and SELECT INTO is not.Ah! This works. Thanks.
Will `SELECT INTO` be deprecated? It is very convenient when writing pgplsql
functions, to select into a record.
The pl/psql `SELECT INTO` is not related to the SQL SELECT INTO command
--- yeah, confusing, so no, the pl/psql ability is not deprecated.--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ Everyone has their own god. +
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Bruce Momjian <bruce@momjian.us> writes:
On Wed, May 13, 2015 at 06:05:43PM -0600, Yves Dorfsman wrote:
Will `SELECT INTO` be deprecated? It is very convenient when writing pgplsql
functions, to select into a record.
The pl/psql `SELECT INTO` is not related to the SQL SELECT INTO command --- yeah, confusing, so no, the pl/psql ability is not deprecated.
Part of the argument for deprecating the SQL-level SELECT INTO is
exactly that it's confusingly spelled the same as plpgsql's SELECT INTO,
but it means something totally different.
regards, tom lane
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
So perhaps replace the SQL SELECT INTO with SQL ADD INTO ?
On Wed, May 13, 2015 at 8:54 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Bruce Momjian <bruce@momjian.us> writes:
On Wed, May 13, 2015 at 06:05:43PM -0600, Yves Dorfsman wrote:
Will `SELECT INTO` be deprecated? It is very convenient when writing
pgplsql
functions, to select into a record.
The pl/psql `SELECT INTO` is not related to the SQL SELECT INTO command --- yeah, confusing, so no, the pl/psql ability is not deprecated.Part of the argument for deprecating the SQL-level SELECT INTO is
exactly that it's confusingly spelled the same as plpgsql's SELECT INTO,
but it means something totally different.regards, tom lane
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
Melvin Davidson wrote:
So perhaps replace the SQL SELECT INTO with SQL ADD INTO ?
No, the alternative spelling is CREATE TABLE AS; we already have it.
(To simply insert a query result into an existing table, the spelling is
INSERT INTO .. SELECT).
--
�lvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general