1. Creating Temporary Tables
Up to this point, you've run queries and viewed the results. But what if you want to keep the results of a query around for reference?
You need special permissions in a database to create or update tables, but most users can create temporary tables that only they can see and that only last for the duration of a database session.
2. Syntax
One way to create a temporary table is with a select query. The results of the query are saved as a table that you can use later. To do this, we preface any select query with the words create temp table, then a name for the table we're creating, and finally the keyword as. This copies the result of the select query into a new table that has no connection to the original table.
There are other ways to create temporary tables as well. You may have seen the "select into" syntax before. You add a special clause into the middle of a select query to direct the results into a new temp table.
In this example, the added clause is the middle line of code. Both of these queries do the same thing, just with different syntax. We're going to use the create table syntax in this course. It's the method recommended by Postgres, and it allows you to use options not available with the "select into" syntax.
3. Create a table
As an example let's make a temporary table called top_companies
with just the rank and title of the top 10 companies in fortune500. We preface our select query with the create temp table syntax. After we've created the table, we can then select from it. Note that the column names are taken from the column names of the result
4. Insert into table
We can also insert new rows into a table after we've created it. We use an "insert into" statement with the name of the table, followed by a select query that will generate the rows we want to add to the table. The columns generated by the select query must match those already in the table.
Here we add companies with ranks 11 to 20 to the table. In many database clients, after you run the command,you'll get a confirmation message that 10 rows were inserted into the table.
In the DataCamp editor, you won't see any message when rows are inserted. Now if we select from the temp table top_companies again,
you can see the new rows have been added.
5. Delete (drop) table
To delete a table, use the drop table command. The table will be deleted immediately without warning. Dropping a table can be useful if you made a mistake when creating it or when inserting values into it. Temporary tables will also be deleted automatically when you disconnect from the database.
A variation on the drop table command adds the clause if exists before the table name. This means to only try to delete the table after confirming that such a table exists. This variation is often used in scripts because it won't cause an error if the table doesn't exist.
6. Time to create some tables!
Temporary tables can be useful for keeping results for later reference, or for breaking complicated queries into smaller pieces. Now it's your turn to create some tables of your own.