Then, write the keyword FROM with the name of the existing table (in our example: product). Next, use the keyword INTO with the name of the new table you want to create (in our example: florist). We only selected the columns we’re interested in. Notice that there are more columns in the table product. First, write a SELECT clause followed by a list of columns (in our example: id, name, and price) from the existing table (in our example: product). If you would like to create a new table based on the structure and data from another table, you can use the SELECT INTO clause. This syntax is non-standard SQL, but it’s supported by many popular databases. The SELECT INTO StructureĪnother solution is to use SELECT INTO. The number of rows is limited by using a WHERE clause, filtering the records to only retrieve data from the category flower. The new table florist will contain the definition of the columns from the product table ( id, name, category, and price). Then, you can use any SQL clause: WHERE, GROUP BY, HAVING, etc. In this case, we first use the CREATE TABLE clause with the name for new table (in our example: florist), we next write AS and the SELECT query with the names of the columns (in our example: *), and we then write FROM followed by the name of the table from which the data is gathered (in our example: product).

Using CREATE TABLE, you can create a new table by copying data from another table. To create a new table from another table, you can use CREATE TABLE AS SELECT. The table florist doesn’t exist in this database. It is important to note that we are creating a new table. These columns come from the table product but only from the category flower. In the database, let’s create a new table named florist which will store the following columns: id, name, and price. Our database has a table named product with data in the following columns: id (primary key), name, category, and price. You would like to create a new table with data copied from another table.

Select into in postgresql