The version of PostgreSQL we have installed on the course VM is 9.5. Complete manuals are available from the PostgreSQL site. The most useful sections to this class are probably “Tutorial” and “The SQL Language”.
Command-Line Processor
Interactive Mode
Inside your VM shell, you can start the command-line SQL processor psql in the interactive mode by entering the following command:
psql beers
Here, beers is the name of the database you want to access. When you are inside psql, you will get a prompt that looks like the following:
beers=#
You can now use any SQL commands (CREATE, DROP, SELECT, INSERT, DELETE) to manipulate the database. Remember to use semicolon (“;“) to terminate SQL statements. Inside psql, you will also find several (non-SQL) commands useful (these do not need to be terminated by semicolon):
\d: list all tables and views
\d NAME: display the schema information for a table or view with NAME
\q: quit the command-line processor
The command-line processor provides advanced command-line editing and auto-completion support similar to what you would find in popular interactive shells such as bash.
Immediate Mode
You can use psql with the -c option to execute a psql command or SQL statement directly from your shell; e.g.:
psql beers -c "\d"
psql beers -c "select * from beer"
After each command/statement completes, control is immediately returned back to your shell. Note that you do not need to end the SQL statements with ; in this case. You may need to enclose command/statement strings in quotes to prevent the shell from interpreting them (for example, if you do not quote * in the above, the shell would expand * into a list of files in your current directory).
This method of invoking psql may be useful when writing shell scripts. Sometimes you might want to use an extra option -a to have psql echo the command/statement back, which is helpful in debugging the output from scripts.
Batch mode
You can write multiple psql commands and SQL statements in one text file (say, named statements.sql), and then use the command psql beers -af statements.sql to execute them all. Use “;” to signal the end of each SQL statement in the file. You will see examples of this usage in /opt/dbcourse/examples/db-beers/setup.sh, which executes files create.sql and load.sql in the batch mode.
Loading a Database
You can find an example of loading a database from data files in /opt/dbcourse/examples/db-beers/. First, you need to prepare the data files. The example data files in data subdirectory use a delimited ASCII format. Each line of the file represents a row, with columns of the row separated by commas. Then, use the \COPY command to load tables from data (see load.sql for syntax). Remember to check any errors reported.
What if some of the data values contain commas? You should use a different column delimiter, e.g., |. And what if a column has no value and you want to load as having NULL instead of an empty string? The appropriate command would be:
\COPY TABLE_NAME FROM 'DATA_FILE' WITH DELIMITER ',' NULL '' CSV
