psql - PostgreSQL: Up and Running - page 46

console window will open from pgAdmin with psql and already connected to the da‐
tabase.
Interactive versus Noninteractive psql
You can run psql interactively by simply typing psql from your OS command line. Your
prompt will switch to the psql prompt, signaling that you are now in the interactive psql
console. Begin typing in commands. Don't forget to terminate SQL statements with a
semicolon. If you press Enter without a semicolon, psql will assume that your statement
continues.
Typing \? while in the psql console brings up a list of all available commands. For
convenience, we reprinted this list in the appendix, highlighting new additions in the
latest versions; see “psql Interactive Commands” on page 199 . Typing \h followed by the
command will bring up the relevant sections of the PostgreSQL documentation per‐
taining to the command.
To use psql noninteractively, execute psql from your OS prompt and pass it a script file.
Within this script you can mix an unlimited number of SQL and psql commands. Al‐
ternatively you can pass in one or more SQL statements surrounded by double quotes.
Noninteractive psql is well-suited for automated tasks. Batch your commands into a
file, and then schedule it to run at regular intervals using a job-scheduling agent like
pgAgent (covered in “Job Scheduling with pgAgent” on page 73 ), Linux/Unix crontab ,
or Windows scheduler. For situations in which many commands must be run in se‐
quence or repeatedly, you're better off creating a script first and then running it using
psql. Syntax-wise, noninteractively offers just a few options because the script file does
most of the work. To execute a file, use the -f option:
psql -f some_script_file
To execute SQL statements on the fly, use the -c option. Join multiple statements with
a semicolon:
psql -d postgresql_book -c "DROP TABLE IF EXISTS dross; CREATE SCHEMA staging;"
For the listing of all options, see “psql Noninteractive Commands” on page 201 .
You can embed interactive commands inside script files. Suppose you created the script
in Example 3-1 and named it build_stage.psql :
Example 3-1. Script with interactive psql commands
\a \t
SELECT 'CREATE TABLE
staging.count_to_50 (array_to_string(array_agg('x' || i::text ' varchar(10)));' As
create_sql
FROM generate_series(1,9) As i;