Practical introduction to relational databases

Insert as select from another table

This method is super useful if you're moving data schemas, or you need to populate a temporary table while you do something with the data you selected.

For example, let's suppose you have order data going back 5 years, but you're only really interested in the orders that were in USD in the past year, and only the amount, the date, and the payment method.

You could do whatever you want to do on this data in the original table, but for separation of concerns, or portability, that is not ideal. For example let's suppose you hired a data analyist, and they asked you for a copy of the data. You wouldn't want to give them the entire source database, just the bits they need, because privacy laws are important, and you're a great citizen of the internet, and abide by all laws and principles of data minimization and anonymisation.

So here's what this would look like:

We start with the source and the target tables. The source table is long and wide, having a lot of rows, and a lot of columns. The target table only has 3 columns, and will end up having fewer rows.

We're only interested in some of the columns, and some of the rows. Earlier we learned that that can be achieved with select col1, col2 from table where col1 = 'value'; or something similar.

The intersection of those, the magenta bits, are what we need from the source table, and that data is going to 100% fill out the target table.

To tie this together, this is what a query looks like:

INSERT INTO schema.target_table
SELECT col1, col2, col3
FROM schema.source_table
WHERE currency = 'USD'
AND date > '2020-12-31 23:59:59;

You'll notice that this is a simple select query with an insert into schema.target_table coming before it.

There are a few important points you need to look out for:

  1. As with basic inserts, you need to make sure that either the columns have default values, are nullable, or have data going into them. If the target table has 3 columns, none have default values, none are nullable, your query needs to return 3 columns
  2. The data returned in the select query needs to match the type of the columns, including the names of the columns between the table and the select query

Insert into select documentation

The documentation lives in two different places for this. On one side, Postgres's insert docs page is the one to look out for.

On that page the relevant bit looks like this:

INSERT INTO table_name [ AS alias ] [ ( column_name [, ...] ) ]
    ...
    { DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) [, ...] | query }
    ...

Where query refers to "A query (SELECT statement) that supplies the rows to be inserted. Refer to the SELECT statement for a description of the syntax.".

The link for the select statement takes you to the docs page for selects.

And that's everything you need to know about the basics of inserting data into a table. When you're ready, let's move on to learn about updating data!