Follow Techotopia on Twitter

On-line Guides
All Guides
eBook Store
iOS / Android
Linux for Beginners
Office Productivity
Linux Installation
Linux Security
Linux Utilities
Linux Virtualization
Linux Kernel
System/Network Admin
Programming
Scripting Languages
Development Tools
Web Development
GUI Toolkits/Desktop
Databases
Mail Systems
openSolaris
Eclipse Documentation
Techotopia.com
Virtuatopia.com
Answertopia.com

How To Guides
Virtualization
General System Admin
Linux Security
Linux Filesystems
Web Servers
Graphics & Desktop
PC Hardware
Windows
Problem Solutions
Privacy Policy

  




 

 

Databases - Practical PostgreSQL
Previous Page Home Next Page

Executing Queries

Entering and executing queries within psql can be done two different ways. When using the client in interactive mode, the normal method is to directly enter queries into the prompt (i.e., standard input, or stdin). However, through the use of psql  's \i slash command, you can have psql read and interpret a file on your local filesystem as the query data.

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.

Example 4-3. Entering statements into psql

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.

Example 4-4. Leaving end-characters open

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.

Example 4-5. Setting the EDITOR variable

$ 
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.

Databases - Practical PostgreSQL
Previous Page Home Next Page

 
 
  Published under the terms of the Open Publication License Design by Interspire