You can use this to study psql's internal operations. EĮcho the actual queries generated by \d and other backslash commands. This is equivalent to setting the variable ECHO to queries.
eĬopy all SQL commands sent to the server to standard output as well. If so, connection string parameters will override any conflicting command line options. This is equivalent to specifying dbname as the first non-option argument on the command line. Specifies the name of the database to connect to. Switches to CSV (Comma-Separated Values) output mode. It's better to use repeated -c commands or feed multiple commands to psql's standard input, either using echo as illustrated above, or via a shell here-document, for example: This is different from the behavior when the same string is read from a file or fed to psql's standard input, because then psql sends each SQL command separately.īecause of this behavior, putting more than one SQL command in a single -c string often has unexpected results. (See Section 53.2.2.1 for more details about how the server handles multi-query strings.) Also, psql only prints the result of the last SQL command in the string. Because of this, the server executes it as a single transaction even if the string contains multiple SQL commands, unless there are explicit BEGIN/ COMMIT commands included in the string to divide it into multiple transactions. To achieve that, you could use repeated -c options or pipe the string into psql, for example:Įach SQL command string passed to -c is sent to the server as a single request. Thus you cannot mix SQL and psql meta-commands within a -c option. When either -c or -f is specified, psql does not read commands from standard input instead it terminates after processing all the -c and -f options in sequence.Ĭommand must be either a command string that is completely parsable by the server (i.e., it contains no psql-specific features), or a single backslash command. This option can be repeated and combined in any order with the -f option. Specifies that psql is to execute the given command string, command. This is equivalent to setting the variable ECHO to errors. Print failed SQL commands to standard error output. (The default output mode is aligned.) This is equivalent to \pset format unaligned. (This does not apply to lines read interactively.) This is equivalent to setting the variable ECHO to all. This can be done with the \connect meta-command or its shortcut \c.Print all nonempty input lines to standard output as they are read. Often, when working with servers that manage multiple databases, you’ll find the need to jump between databases frequently. Once you have created another database you will want to switch to it in order to create tables and insert data. postgres is the default database you will connect to before you have created any other databases. template0 and template1 are skeleton databases that are or can be used by the CREATE DATABASE command. Most Postgres servers have three databases defined by default: template0, template1 and postgres.
UTF - 8 | = c / postgres + | | | | | postgres = CTc / postgres ( 4 rows ) Switching Databases UTF - 8 | = c / postgres + | | | | | postgres = CTc / postgres template1 | postgres | UTF8 | en_US. UTF - 8 | template0 | postgres | UTF8 | en_US. Postgres =# \ l List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges -+-+-+-+-+- postgres | postgres | UTF8 | en_US. To view all of the defined databases on the server you can use the \list meta-command or its shortcut \l. Each database is stored as a separate set of files in its own directory within the server’s data directory. Listing DatabasesĪ single Postgres server process can manage multiple databases at the same time. They are denoted by a backslash and then followed by the command and its arguments. Meta-commands are commands that are evaluated by psql and often translated into SQL that is issued against the system tables on the server, saving administrators time when performing routine tasks. In addition to being able to submit raw SQL queries to the server via psql you can also take advantage of the psql meta-commands to obtain information from the server.
#POSTGRESQL LIST DATABASES HOW TO#
In this tutorial, we will learn how to answer these key questions from the command line using psql. This involves asking questions like “Which databases reside on this server?” or “What tables are stored in a particular database on this server?”. One of the most common tasks a database administrator performs is simply getting familiar with the environment. However, Postgres itself comes bundled with a powerful command line tool called psql which is great for those familiar with the terminal or looking to script administration tasks. When it comes to administering Postgres databases, there’s a wide variety of third party tools available such as SQL Workbench/J or pgAdmin III.