Entering queries at the psql prompt

To enter queries directly into the prompt, open psql and make sure you are connected to the correct database (and logged in as the correct user). You will be presented with a prompt that, by default, is set to display the name of the database you are currently connected to. The prompt will look like this: psql :

testdb=#

To pass SQL statements to PostgreSQL, simply type them into the prompt. Anything you type (barring a slash command) will be queued until you terminate the query with a semicolon. This is the case even if you start a new line of type, thus allowing you to spread query statements across multiple lines. Examine Example 4-3 to see how this is done.

testdb=# 
SELECT * FROM employees

testdb-# 
         WHERE firstname = 'Michael';

The query entered in Example 4-3 will return a table that consists of all employees whose first name is Michael. The query could be broken up over multiple lines to improve readability, and psql would not send it to the backend until the terminating semicolon was sent. The prompt will show the end-character of a previous line if the character requires a closing character, such as a parenthesis or a quote (this is not shown in the example). If you were to issue a CREATE TABLE command to start a statement, and then hit enter to begin a new line for readability purposes, you would see a prompt similar to the one displayed in Example 4-4.

testdb=# 
CREATE TABLE employees (

testdb(#

At this point you could continue the statement. The psql prompt is informing you of the open parenthesis by inserting an open parenthesis symbol into the prompt.

Editing the query buffer

Use the \e command to edit the current query buffer with the editor that your EDITOR environment variable is set to. Doing so can be very useful when entering queries and statements in psql , as you can easily view and modify all lines of your query or statement before it is committed. Example 4-5 shows how to set the EDITOR variable. The vi editor will be used if EDITOR is not set.

$ 
set EDITOR='joe'

$ 
export EDITOR

You can also use this command to save your current buffer as a file. Issue the \e command to enter editing mode. This will open your editor and load the buffer as if it were a file. Complete whatever work you wish to do with the buffer, then use your editor's save function to save the buffer and return to psql . To save the query as a normal file, use your editor's save-as function and save it as a file other than the .tmp created by \e.