![]() If no list of column names is given at all, the default is all the columns of the table in their declared order or the first N column names, if there are only N columns supplied by the VALUES clause or query. The target column names can be listed in any order. One can insert one or more rows specified by value expressions, or zero or more rows resulting from a query. In this tutorial, you have learned how to use the PostgreSQL CREATE TABLE AS statement to create a new table from the result of a query.INSERT inserts new rows into a table. In addition, the CREATE TABLE AS statement provides a superset of functionality offered by the SELECT INTO statement. Note that the CREATE TABLE AS statement is similar to the SELECT INTO statement, but the CREATE TABLE AS statement is preferred because it is not confused with other uses of the SELECT INTO syntax in PL/pgSQL. To check the structure of the film_rating table, you use the following command in psql tool: \d film_rating Code language: SQL (Structured Query Language) ( sql ) ![]() It explicitly specified the column names for the new table instead of using the column names from the SELECT clause. This example statement created a new table film_rating and filled it with the summary data from the film table. If the SELECT clause contains expressions, it is a good practice to override the columns, for example: CREATE TABLE IF NOT EXISTS film_rating (rating, film_count) To check the structure of the action_film, you can use the following command in the psql tool: \d action_film Code language: SQL (Structured Query Language) ( sql )Īs clearly shown in the output, the names and data types of the action_film table are derived from the columns of the SELECT clause. To verify the table creation, you can query data from the action_film table: SELECT * FROM action_film CREATE TABLE action_film AS SELECTĬode language: SQL (Structured Query Language) ( sql ) The following statement creates a table that contains action films that belong to category one. We will use the film and film_category table from the sample database for the demonstration. In case you want to avoid an error by creating a new table that already exists, you can use the IF NOT EXISTS option as follows: CREATE TABLE IF NOT EXISTS new_table_nameĪS query Code language: SQL (Structured Query Language) ( sql ) PostgreSQL CREATE TABLE AS statement examples If you want the table columns to have different names, you can specify the new table columns after the new table name: CREATE TABLE new_table_name ( column_name_list) The columns of the new table will have the names and data types associated with the output columns of the SELECT clause. The UNLOGGED keyword allows the new table to be created as an unlogged table: CREATE UNLOGGED TABLE new_table_name The TEMPORARY or TEMP keyword allows you to to create a temporary table: CREATE TEMP TABLE new_table_name Second, provide a query whose result set is added to the new table after the AS keyword.First, specify the new table name after the CREATE TABLE clause.The following shows the syntax of the CREATE TABLE AS statement: CREATE TABLE new_table_nameĪS query Code language: SQL (Structured Query Language) ( sql ) The CREATE TABLE AS statement creates a new table and fills it with the data returned by a query. Introduction to the PostgreSQL CREATE TABLE statement Summary: in this tutorial, you will learn how to use the PostgreSQL CREATE TABLE AS statement to create a new table from the result set of a query.
0 Comments
Leave a Reply. |