Postgres Pro
Downloads
Home   >   mailing lists

Thread: SELECT INTO and ON COMMIT

SELECT INTO and ON COMMIT

From
Yves Dorfsman
Date:
Is there any way to add an ON COMMIT clause to a SELECT INTO TEMP TABLE?


Thanks.
--
http://yves.zioup.com
gpg: 4096R/32B0F416

Re: SELECT INTO and ON COMMIT

From
Bruce Momjian
Date:
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. +

Re: SELECT INTO and ON COMMIT

From
"David G. Johnston"
Date:
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.
​"​


​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.


Re: SELECT INTO and ON COMMIT

From
Yves Dorfsman
Date:
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

Re: SELECT INTO and ON COMMIT

From
Bruce Momjian
Date:
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. +

Re: SELECT INTO and ON COMMIT

From
Tom Lane
Date:
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

Re: SELECT INTO and ON COMMIT

From
Melvin Davidson
Date:
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.

Re: SELECT INTO and ON COMMIT

From
Alvaro Herrera
Date:
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