psql
I want to learn more about psql - the terminal-based front-end to PostgreSQL - because I need to use it to do some stuff for the database that is more involved then just making queries through an application like pgadmin.
References
I am not going to list all of the options or meta-commands here. To see them, consult the link above.
Notes
$ psql [option...] [dbname [username]]
psql is a terminal-based front-end to PostgreSQL. It enables you to type in queries interactively, issue them to PostgreSQL, and see the query results. Alternatively, input can be from a file or command line arguments. In addition, psql provides a number of meta commands and various shell-like features to facilitate writing scripts and automating a wide variety of tasks.
psql is a regular PostgreSQL client application. In order to connect to a database you need to know the name of your target database, the host name and port number of the server, and what database user name you want to connect as. psql can be told about those parameters via command line options, namely -d, -h, -p, and -U respectively. If an argument is found that does not belong to any option, it will be interpreted as the database name (or the database user name, if the database name if already given).Not all of these options, there are some useful defaults.
When the defaults aren't quite right, you can save yourself some typing by setting the environment variables PGDATABASE, PGHOST, PGPORT, and/or PGUSER to appropriate values. It is also convenient to have a ~/.pgpass file to avoid regularly having to type in passwords. An alternative way to specify connection parameters is in a conninfo string or a URI, which is used instead of a database name, for example:
$ psql "service=myservice sslmode=require"
$ psql postgresql://dbmaster:5433/mydb?sslmode=require
In normal operation, psql provides a prompt with the name of the database to which psql is currently connected, followed by the string =>:
$ psql testdb
psql (17.2)
Type "help" for help.
testdb=>
At the prompt, the user can type in SQL commands.
Anything you enter in psql that begins with an unquoted backslash is a psql meta-command that is processed by psql itself. These commands make psql more useful for administration or scripting. Meta-commands are often called slash or backslash commands. The format of a psql command is the backslash, followed immediately by a command verb, then any arguments. The arguments are separated from the command verb and each other by any number of whitespace characters., Example:
INSERT INTO tbl1 VALUES ($1, $2) \bind 'first value' 'second value' \g
psql provides variable substitution features similar to common Unix command shells. Variables are simply name/value pairs, where the value can be any string of any length. The name must consist of letters (including non-Latin letters), digits, and underscores. To set a variable, use the psql meta-command \set:
testdb=> \set foo bar
testdb=> \echo :foo
bar
Comments
You have to be logged in to add a comment
User Comments
There are currently no comments for this article.