Practical introduction to relational databases

Inserting data

Inserting data into a table can be done in a myriad of different ways, we're gonna learn 3 of them. Or rather 2.5.

To do an insert successfully, we need to know

  • where we're inserting to (which schema.table)
  • which columns we're inserting into in that table, which is going to make sense in a few minutes
  • what the data that we're inserting into there is

The three different methods that we're going to go through are:

  1. inserting data but leaving out some columns and we let the database figure out what should go there in the inserting with defaults section. This is the half part
  2. inserting one or more records where we specify what the data is in the bulk insert section
  3. and lastly inserting data into a table where the source is a select query from another table

Insertions that we're not exploring

Inserting data into a table from a file is an advanced level functionality. By the time you need to do that you have a lot of data, and if you do have a lot of data, you will need to wade into the waters of advanced features.

If you'd like to skip ahead, you can read the docs on the sql copy command, or its psql command line alternative, the\copy meta command.

The difference between them is that the SQL command (the first one) is going to be significantly faster, but the file you want to use needs to exist on the same machine the database is running on, as it does a local filesystem read.

The \copy meta command however can take a filepath that sites on your / the client computer, but the downside is that the data needs to pass through the network between the client and the server, which is orders of magnitude slower.

First up, let's deal with the defaults.