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”.
Inside your VM shell, you can start the command-line SQL processor
psql in the interactive mode by entering the following command:
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:
You can now use any SQL commands (
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
\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
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.
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
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