SELECT INTO for table creation is "deprecated".Īs a side note, the syntax for a CTAS with a CTE may look a bit weird., and SELECT INTO may also be some kind of hold over QUEL's RETRIEVE INTO.CREATE TABLE supports more functionality (I assume they're referring to WITH OIDS, and TABLESPACE, IF NOT EXISTS).PostgreSQL thinks it's confusing because SELECT INTO does other stuff in contexts only available in PL/pgSQL, and ECPG. It is best to use CREATE TABLE AS for this purpose in new code. The PostgreSQL usage of SELECT INTO to represent table creation is historical. This indeed is the usage found in ECPG (see Chapter 34) and PL/pgSQL (see Chapter 41). The SQL standard uses SELECT INTO to represent selecting values into scalar variables of a host program, rather than creating a new table. Furthermore, CREATE TABLE AS offers a superset of the functionality offered by SELECT INTO.Īlso in the Compatibility section of the docs of SELECT INTO it goes even further, This command is functionally similar to SELECT INTO, but it is preferred since it is less likely to be confused with other uses of the SELECT INTO syntax. Furthermore, CREATE TABLE AS offers a superset of the functionality provided by SELECT INTO. CREATE TABLE AS is the recommended syntax, since this form of SELECT INTO is not available in ECPG or PL/pgSQL, because they interpret the INTO clause differently. At the bottom of each under NOTES this is cleared up,ĬREATE TABLE AS is functionally similar to SELECT INTO. Without the explanation, always use CREATE TABLE AS without exception. The new table's columns have the names and data types associated with the output columns of the SELECT. The data is not returned to the client, as it is with a normal SELECT. SELECT INTO creates a new table and fills it with data computed by a query. SELECT INTO - define a new table from the results of a query In contrast, a view re-evaluates its defining SELECT statement whenever it is queried. The new table will not track subsequent changes to the source tables of the query. The table columns have the names and data types associated with the output columns of the SELECT (except that you can override the column names by giving an explicit list of new column names).ĬREATE TABLE AS bears some resemblance to creating a view, but it is really quite different: it creates a new table and evaluates the query just once to fill the new table initially. PostgreSQL supports CREATE TABLE AS and SELECT INTO when do I use both?ĬREATE TABLE AS - define a new table from the results of a queryĬREATE TABLE AS creates a table and fills it with data computed by a SELECT command.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |