Services

 

Web Stuff

Networking

Custom PC's

Modification

Upgrades

Onsite Diagnostics

Back Up/Storage

Sponsor & Affiliates Info

 

 

Extras

Past Sponsors

Affiliates

Reviews

Articles

Guides

Contests

Message Board

Affiliate News

 

 

 Marns CDA

PC News

RSS to JavaScript

 

Local Gamming 

LAN Party Sites

reLANtless.net

RITFest.net

 

 

 

 

Marns CDA Reference & Resources

 

PSQL

Commands / References

 

I would like to thank my reference sites for making great tutorials we used to make this page especially http://jamesthornton.com/postgres/6.5/tutorial/tutorial.htm

 

The Postgres query language is a variant of the SQL3 draft next-generation standard. It has many extensions such as an extensible type system, inheritance, functions and production rules. These are features carried over from the original Postgres query language, PostQuel. This section provides an overview of how to use Postgres SQL to perform simple operations. This manual is only intended to give you an idea of our flavor of SQL and is in no way a complete tutorial on SQL. Numerous books have been written on SQL, including [MELT93] and [DATE97]. You should be aware that some language features are extensions to the ANSI standard.

Interactive Monitor

In the examples that follow, we assume that you have created the mydb database as described in the previous subsection and have started psql. Examples in this manual can also be found in /usr/local/pgsql/src/tutorial/. Refer to the README file in that directory for how to use them. To start the tutorial, do the following:

% cd /usr/local/pgsql/src/tutorial
% psql -s mydb
Welcome to the POSTGRESQL interactive sql monitor:
  Please read the file COPYRIGHT for copyright terms of POSTGRESQL

   type \? for help on slash commands
   type \q to quit
   type \g or terminate with semicolon to execute query
 You are currently connected to the database: postgres

mydb=> \i basics.sql
    

 

The \i command read in queries from the specified files. The -s option puts you in single step mode which pauses before sending a query to the backend. Queries in this section are in the file basics.sql.

psql has a variety of \d commands for showing system information. Consult these commands for more details; for a listing, type \? at the psql prompt.

 

Creating a New Class

You can create a new class by specifying the class name, along with all attribute names and their types:

CREATE TABLE weather (
    city            varchar(80),
    temp_lo         int,           -- low temperature
    temp_hi         int,           -- high temperature
    prcp            real,          -- precipitation
    date            date
);
    

 

Note that both keywords and identifiers are case-insensitive; identifiers can become case-sensitive by surrounding them with double-quotes as allowed by SQL92. Postgres SQL supports the usual SQL types int, float, real, smallint, char(N), varchar(N), date, time, and timestamp, as well as other types of general utility and a rich set of geometric types. As we will see later, Postgres can be customized with an arbitrary number of user-defined data types. Consequently, type names are not syntactical keywords, except where required to support special cases in the SQL92 standard. So far, the Postgres create command looks exactly like the command used to create a table in a traditional relational system. However, we will presently see that classes have properties that are extensions of the relational model.

 

Populating a Class with Instances

The insert statement is used to populate a class with instances:

INSERT INTO weather
    VALUES ('San Francisco', 46, 50, 0.25, '11/27/1994')
    

 

You can also use the copy command to perform load large amounts of data from flat (ASCII) files. This is usually faster because the data is read (or written) as a single atomic transaction directly to or from the target table. An example would be:

COPY INTO weather FROM '/home/user/weather.txt'
    USING DELIMITERS '|';
    

where the path name for the source file must be available to the backend server machine, not the client, since the backend server reads the file directly.

 

Querying a Class

The weather class can be queried with normal relational selection and projection queries. A SQL select statement is used to do this. The statement is divided into a target list (the part that lists the attributes to be returned) and a qualification (the part that specifies any restrictions). For example, to retrieve all the rows of weather, type:

SELECT * FROM WEATHER;
    

and the output should be:

+--------------+---------+---------+------+------------+
|city          | temp_lo | temp_hi | prcp | date       |
+--------------+---------+---------+------+------------+
|San Francisco | 46      | 50      | 0.25 | 11-27-1994 |
+--------------+---------+---------+------+------------+
|San Francisco | 43      | 57      | 0    | 11-29-1994 |
+--------------+---------+---------+------+------------+
|Hayward       | 37      | 54      |      | 11-29-1994 |
+--------------+---------+---------+------+------------+
    

You may specify any arbitrary expressions in the target list. For example, you can do:

SELECT city, (temp_hi+temp_lo)/2 AS temp_avg, date FROM weather;
    

 

Arbitrary Boolean operators (and, or and not) are allowed in the qualification of any query. For example,

SELECT * FROM weather
    WHERE city = 'San Francisco'
    AND prcp > 0.0;
    

results in:

+--------------+---------+---------+------+------------+
|city          | temp_lo | temp_hi | prcp | date       |
+--------------+---------+---------+------+------------+
|San Francisco | 46      | 50      | 0.25 | 11-27-1994 |
+--------------+---------+---------+------+------------+
    

 

As a final note, you can specify that the results of a select can be returned in a sorted order or with duplicate instances removed.

SELECT DISTINCT city
    FROM weather
    ORDER BY city;

Redirecting SELECT Queries

Any select query can be redirected to a new class

SELECT * INTO TABLE temp FROM weather;
    

 

This forms an implicit create command, creating a new class temp with the attribute names and types specified in the target list of the select into command. We can then, of course, perform any operations on the resulting class that we can perform on other classes.

Joins Between Classes

Thus far, our queries have only accessed one class at a time. Queries can access multiple classes at once, or access the same class in such a way that multiple instances of the class are being processed at the same time. A query that accesses multiple instances of the same or different classes at one time is called a join query. As an example, say we wish to find all the records that are in the temperature range of other records. In effect, we need to compare the temp_lo and temp_hi attributes of each EMP instance to the temp_lo and temp_hi attributes of all other EMP instances.

Note: This is only a conceptual model. The actual join may be performed in a more efficient manner, but this is invisible to the user.

We can do this with the following query:

SELECT W1.city, W1.temp_lo AS low, W1.temp_hi AS high,
    W2.city, W2.temp_lo AS low, W2.temp_hi AS high
    FROM weather W1, weather W2
    WHERE W1.temp_lo < W2.temp_lo
    AND W1.temp_hi > W2.temp_hi;

+--------------+-----+------+---------------+-----+------+
|city          | low | high | city          | low | high |
+--------------+-----+------+---------------+-----+------+
|San Francisco | 43  | 57   | San Francisco | 46  | 50   |
+--------------+-----+------+---------------+-----+------+
|San Francisco | 37  | 54   | San Francisco | 46  | 50   |
+--------------+-----+------+---------------+-----+------+
    

Note: The semantics of such a join are that the qualification is a truth expression defined for the Cartesian product of the classes indicated in the query. For those instances in the Cartesian product for which the qualification is true, Postgres computes and returns the values specified in the target list. Postgres SQL does not assign any meaning to duplicate values in such expressions. This means that Postgres sometimes recomputes the same target list several times; this frequently happens when Boolean expressions are connected with an "or". To remove such duplicates, you must use the select distinct statement.

 

In this case, both W1 and W2 are surrogates for an instance of the class weather, and both range over all instances of the class. (In the terminology of most database systems, W1 and W2 are known as range variables.) A query can contain an arbitrary number of class names and surrogates.

 

Updates

You can update existing instances using the update command. Suppose you discover the temperature readings are all off by 2 degrees as of Nov 28, you may update the data as follow:

UPDATE weather
    SET temp_hi = temp_hi - 2,  temp_lo = temp_lo - 2
    WHERE date > '11/28/1994';

 

Deletions

Deletions are performed using the delete command:

DELETE FROM weather WHERE city = 'Hayward';
    

All weather recording belongs to Hayward is removed. One should be wary of queries of the form

DELETE FROM classname;
    

Without a qualification, delete will simply remove all instances of the given class, leaving it empty. The system will not request confirmation before doing this.

Using Aggregate Functions

Like most other query languages, PostgreSQL supports aggregate functions. The current implementation of Postgres aggregate functions have some limitations. Specifically, while there are aggregates to compute such functions as the count, sum, avg (average), max (maximum) and min (minimum) over a set of instances, aggregates can only appear in the target list of a query and not directly in the qualification (the where clause). As an example,

SELECT max(temp_lo) FROM weather;

is allowed, while

SELECT city FROM weather WHERE temp_lo = max(temp_lo);

is not. However, as is often the case the query can be restated to accomplish the intended result; here by using a subselect:

SELECT city FROM weather WHERE temp_lo = (SELECT max(temp_lo) FROM weather);

Aggregates may also have group by clauses:

SELECT city, max(temp_lo)

FROM weather

GROUP BY city;

 

Using postgres

Taken from http://www.fluidthoughts.com

All postgres commands are completed with either a ';', or a backslash-escaped character.

  • \g is the same as ';' (end of line - execute this command)
  • \q means quit
  • \p means print out the current buffer
  • \h means help - which will print out the complete list of special characters.

Basic Commands:

$ psql test

SELECT CURRENT_USER;	#special username query
SELECT 1 + 3;		#can do arithmetic
SELECT 1 +		#can do multi-line
3 + 5 +
6\p			#can print out current buffer contents
\q			#quit

create databases:

$ createdb willn
CREATE DATABASE

list databases:

test=# \list  (or \l)
		List of databases
 Database  |  Owner   | Encoding  
-----------+----------+-----------
 template0 | postgres | SQL_ASCII
 template1 | postgres | SQL_ASCII
 test      | postgres | SQL_ASCII
(3 rows)

By looking through the source code, one can find that the actual SQL statement for this command is:

SELECT datname FROM pg_database WHERE datname !~ '^template';

describe tables:

test=# \d
   List of relations
  Name  | Type  | Owner 
--------+-------+-------
 friend | table | willn
(1 row)

Again, this is equivalent to the following SQL command:

SELECT relname FROM pg_class WHERE relname !~ '^pg_';

User Management:

It's fairly easy to manage access to databases:

$ createuser nobody
Shall the new user be allowed to create databases? (y/n) n
Shall the new user be allowed to create more new users? (y/n) n
CREATE USER

See also:

$ createuser nobody
test=# CREATE USER nobody
test=# ALTER USER nobody
test=# CREATE GROUP testusers WITH USER nobody, someone; 
test=# GRANT SELECT ON friend TO nobody

 

 

 

Command Line Options

Taken from a great resource http://www.faqs.org

 

Here is the complete syntax to start psql:

psql [ options ] [ dbname [ username ] ] 

The optional dbname value specifies the database to initially connect to. The optional username specifies the PostgreSQL user to connect as. If either value is unspecified, psql will default to a database and username with the same name as the operating system user starting the program.

Additionally, several run-time options can be set by command-line flags. By default, psql understands both standard UNIX short options (e.g., -c, and GNU-style long options (e.g., - -command). The latter are not available on all systems. In the following list, the UNIX short options (which are always one letter) are shown first, followed by the equivalent long option.

 

-a, --echo-all
Turns on the 'echo all' option, which displays all lines as they are read by psql. This option can be useful for scripting, and is equivalent to issuing the command: \set ECHO all from within psql.
-A, --no-align
Starts psql in unaligned output formatting mode. If this is not specified, the output formatting mode will be set to aligned.
-c statement, --command statement
Instead of running psql interactively, this option executes the statement that you specify. This must be a syntactically correct SQL statement, and must be devoid of any psql-specific commands.
-d database, --dbname database
Explicitly specifies the database you wish psql to initially connect to.
-e, --echo-queries
Specifies that all queries are echoed to the screen.
-E, --echo-hidden
Displays the hidden queries generated by slash commands. You can also issue the following command from within psql to accomplish the same effect: \set ECHO_HIDDEN.
-f filename, --file filename
Specifies that rather than start in interactive mode, psql should read and execute SQL from the specified filename, and process its contents as it would if input directly. After processing the file, psql exits.
-F separator, --field-separator separator
Specifies that psql should use the specified separator character as the field (column) delimiter.
-h hostname, --host hostname
Specifies the hostname of the backend machine. This is usually not necessary when connecting to a local backend process, which uses UNIX domain sockets. However, if the postmaster initializes its domain socket file somewhere other than the default path of /tmp, specifying a hostname with a leading forward slash will cause psql to interpret the hostname value as a local directory to check for the domain socket file (e.g., -h /var/pgsql will cause psql to look for a domain socket file within /var/pgsql).
-H, --html
Starts psql in HTML output mode.
-l, --list
Displays a list of available databases to connect to.
-o filename, --output filename
Redirects psql output to filename.
-p port, --port port
Specifies TCP/IP port (or numbered UNIX domain socket) that postmasteris currently listening on. By default, this is whatever PGPORT is set to (or the default of 5432).
-P name=value, --pset name=value
Specifies the output formatting options using the same syntax as used with the \pset command. All option names are the same as for \pset, but with this command-line option you must use an an equal sign (=) instead of a space between each formatting option name and its value.
-q, --quiet
Instructs psql to work in quiet mode. No psql-specific informative messages or informational text is displayed.
-R separator, --record-separator separator
Specifies separator as the record (row) delimiter.
-s, --single-step
Specifies that psql will run in "single-step" mode. While in single-step mode, you will be prompted to either continue or cancel upon executing a SQL statement.
-S, --single-line
Specifies that psql will run in "single-line" mode. When running in this mode, a new line acts as a semi-colon to execute a SQL statement.
-t, --tuples-only
Turns off the display of extraneous table information, such as column names and footers. To accomplish this from within psql, use the \t command.
-T table_attribute, --table-attr table_attribute
Sets an HTML attribute that you wish to be placed within the <table> output while in HTML formatting mode (e.g., width=100%). If you pass more than one table_attribute to this flag, they must all be contained within double quotes. You can use \pset from within psql to insert these attributes as well.
-U username, --username username
Connects with the specified username.
-v name=value, --variable name=value
Assigns a value to a variable name, as you would do using the \set command from within psql. When separating a value from a name, use an equal sign instead of a space.
-V, --version
Displays version information.
-W, --password
Prompts for a password before connecting to a database. This setting remains for the duration of the psql session.
-x, --expanded
Activate extended row format mode. Accomplish this from within psql by using the \x slash command.
-X, --no-psqlrc
Do not read or execute the startup file (~/.psqlrc).
-?, --help
Displays brief psql command line argument help.

 

 
Warning
Unstable code was introduced into version 7.0 that causes psql to obtain a password from the user when authentication is requested by the backend process; however, this code is not reliable and will sometimes fail, which will subsequently cause the connection attempt to fail. It is advisable to use the -W (--password) option to force a prompt if you know that such authentication will be necessary.

Slash Commands

Recall that within psql you have several special commands, called slash commands. These commands are psql-specific, and are not sent to the PostgreSQL backend. Explanations of the available psql slash commands follow.

Formatting commands

There are several slash commands available to format output. These include \pset, \a, \C, \f, \H, \t, \T, and \x. Except for \pset, each command controls a different formatting option. The \pset command, which is newer than the others, controls most of those same settings. The other commands exist for compatibility with older versions, and for convenience.

Most of these duplicate the effects of \pset. Each command is detailed within the description of that command and its options. For compatibility with older versions, and convenience, some of these formatting options may still have a slash command devoted entirely to them; these commands have been listed as well.

 

\pset parameter [ value ]
The general parameter setting command; this is the most important (and powerful) formatting command of the list. It encapsulates a variety of display options, and it could easily be the only formatting slash command you ever use. You may pass it various parameters to accomplish different formatting functions.

Within its syntax, parameter is one of the following valid parameters:

 

format
This parameter lets you set the output format to aligned, unaligned, html, or latex. Aligned is the default setting, for readability. Unaligned will set output to be printed all on one line, separated by the current character delimiter. The HTML and LaTeX modes output tables meant for inclusion in HTML and LaTeX documents, respectively.
border
Depending on the formatting mode, this option will make various changes to the borders used within displayed tables. For example, when outputting in HTML mode, this directly affects the border attribute of the <table> tag. This parameter takes a numeric value. Generally, the higher this number is, the larger (or more pronounced) the borders will be.
expanded
Setting this option will toggle between regular and extended format. If you have problems with data being displayed off the screen, or wrapping around in an illegible fashion, try using this option. It will tell psql to format all output into two columns, with the column name on the left, and data on the right.
null
This parameter allows you to set the string that is displayed to you when a null field is displayed. The string you wish to have displayed to represent a null should follow the word null. Ordinarily, that string is set to nothing. To set it back to nothing, you may set it with two apostrophes in a row (''). To set it to some other value, enclose that value in single-quotes. For example: \pset null ' ***null*** '.
fieldsep
This parameter accepts the delimiter to separate column values when working in the unaligned formatting mode. It is set to the pipe symbol (|) by default. You may want to use this to set the delimiter to a more commonly used delimiter, such as the tab (\t) character or comma (,). This has no effect outside of unaligned mode.
recordsep
This parameter specifies the record delimiter (to separate rows) when working in unaligned formatting mode. By default this is the newline character (\n).
tuples_only
This parameter lets you specify whether you want to see table data only (row results), or if you want to see additional characteristics about the table, such as headers and comments.
title
This parameter is used to attach a title to any subsequently printed titles. It will be displayed just above normal output. Use a pair of sequential apostrophes ('') to set to an empty string.
tableattr
This parameter is for use with the HTML format mode; use it to define any table attributes you wish to be included upon formatting table output within the <table> tag (e.g., width, cellpadding, cellspacing). If you wish to define more than a single attribute, be sure to enclose them within double-quotes in a single value.
pager
This parameter toggles off and on the use of a pager for outputting table data. You may set the PAGER environment variable in your shell before starting psql if you wish to use a paging program other than more (such as less).

 

\a
The align command; this toggles psql between aligned and unaligned mode. This is equivalent to successive uses of \pset format aligned and \pset format unaligned.
\C
The query title command; this allows you to set a title that will be displayed at the top of any displayed result set, and is equivalent to \pset title.
\f
The field delimiter command; this sets the field delimiter when using the unaligned formatting mode, and is equivalent to \pset fieldsep.
\H
The HTML output command; this toggles between HTML output formatting and the default aligned formatting, and is equivalent to successive uses of \pset format HTML and \pset format aligned.
\t
The table information command; this toggles the display of optional table information, and is equivalent to \pset tuples_only.
\T
The table attribute command; this defines extra table attributes you wish to be inserted into the table tags of table data displaying while in HTML formatting mode. It is equivalent to \pset tableattr.
\x
The toggle expanded command; this toggles expanded row formatting and off. It is equivalent to \pset expanded.

 

Information display commands

The psql client has many commands to help you with gathering information about the database and various objects within it. Most of these commands are prefixed with \d, as this is an easy mnemonic device for display. Knowing how to use these commands can increase your productivity (or at least your awareness!) within the database.

 

\d [ relation_name ]
The general display command; it is used to view various pieces of information about a specified relation. The relation you specify may be an index, sequence, table, or view. When issued, the command will display all of the relation's columns, types, and special attributes or defaults. When executed without a specified relation, it displays each of the relations available within the currently connected database.
\da [ aggregate_name ]
The aggregate display command; with it, you may retrieve the list of the connected database's aggregate functions, and their accepted data types. If you specify a parameter following the slash command, it will display only the list of aggregate functions whose names begin with the aggregate_name pattern in a case-insensitive comparison.
\dd [ name ]
The general database object display command; it is used to display the descriptions of any specified database object. The object you specify may be any defined aggregate, function, operator, relation, rule, or trigger. If you do not specify an object name, or a partial name, all objects in the database will be displayed.
\df [ function_name ]
The function display command; it is used to display information about a function defined within the database, including its arguments, and return types. You can either specify a function to display, or specify none, and list information about all functions. Like \da and \dd, a full or partial function_name may be supplied for a case-insensitive comparison against all functions from the beginning of each function name.
\d[istvS] [ name ]
A scoped version of the general display command; you may specify any of the options within the brackets:

 

i
Displays indices.
s
Displays sequences.
t
Displays tables.
v
Displays views.
S
Displays system tables.

 

\dl
The large object display command; this command is equivalent to the \lo_list command, which displays the list of large objects within the current database.
\do [ operator_name ]
The operator display command; this displays the list of defined operators within the current database, along with their operands (arguments), and return types. You may specify a complete or partial operator_name to examine, or retrieve information about all available operators.
\dp [ object_name ]
The permissions display command; this retrieves the list of all database objects (or objects at least partially matching an object_name, if provided) currently defined within the database, along with all their associated access permissions (public, user, and group).
\dT [ type_name ]
The data type display command; this displays the list of all available data types. You may again specify a type_name, or partial data type name, or view all available data types in the current database.
\l
The database display command; this lists all defined databases on the server, and their ownership information, and multibyte encoding type. Entering \l+ will display any comments the databases may have (see the Section called Documenting a Database in Chapter 9" in Chapter 9 for how to comment on a database).
\lo_list
The large object display command; this displays the list of all existing large objects within the current database, along with any comments that have been attached to them.
\z [ object_name ]
The permissions display command, equivalent to \dp.

PostgreSQL and psql informative commands

Within psql there is a small set of informative commands that display information about PostgreSQL and psql itself. These are useful primarily for obtaining help with command-related questions you may have.

 

 

\?
The help command; this prints out the list of slash commands documented in this chapter.
\copyright
The copyright command; this displays copyright information about PostgreSQL.
\encoding
The encoding command; if multibyte encoding is enabled, this can set the client encoding. If you do not supply an argument, the current encoding will be displayed on the screen.
\help
The general help command; used without an argument, it will print a list of all commands for which greater help is available. Used with an argument, it will print more information (if there is data available) for the subject. Used with an asterisk (*) as the argument, it will retrieve syntax information for all documented SQL commands.

 

Input and output commands

The psql client's various input and output slash commands allow you to transfer data to and from the database in different ways. You may also specify exactly how psql transfers data. The commands include:

 

\copy table { FROM | TO } file | stdin | stdout
The copy command; this can be used to copy from the client application (and thus, use the permissions of the user who started the client) instead of using the SQL COPY command to copy from the server. This slash command can also accept any of the standard COPY clauses. For more information on the syntax of this command, refer to the COPY entry in the command reference section at the back of this book.

The difference between using \copy over COPY are important to understand and include:

 

  • Data you \copy transfers first through the client (via your connection), which may be quite a bit slower than if it were done directly through the server (i.e., the backend) process.
  • You have access to files on the local filesystem under whatever permissions the user account you are using has, which means you may have more (or less) accessibility to needed files than the backend process.
  • The terms stdin and stdout (standard input and output) have a different meaning; they refer to psql 's input and output stream. On the backend process they are used differently: stdin represents where the COPY was issued from, and stdout represents the query output stream.

 

\echo string
The echo command; this sends a string to the standard output. This can be useful for scripting, because you can add non-database–supplied information into script output (such as comments).
\g [ file ]
The buffer execution command; this is essentially the same as using the semicolon (;) in that it sends the current query buffer to the backend to be processed. Optionally, you can save the result set to a file of your choice, or have psql pipe it to a separate shell command by following the \g with either a filename or piped command name.
\i file
The file input command; this reads input from a file (the name of which you supply as an argument after the \i) and causes psql to parse its content as if it were typed directly into the program's prompt.
\lo_export lo_oid filename
The large object export command; this lets you export the large object with OID lo_oid to filename on your local filesystem. This is different from the lo_export() server function in the same way the \copy and the SQL COPY commands are different.
\lo_import filename [ comment ]
The large object import command; this imports large objects into the database from files on your local filesystem. Optionally, you can attach a comment to the object; this is recommended, as otherwise it will be identifiable only by an OID, which you will need to remember if you wish to access it again. If you attach a comment to the object, issuing the \lo_list command displays your comment with the OID of the object, thus making it easier to find once imported.
\o [ file | |command ]
The output command; this redirects future output (i.e., data retrieved after this command is issued) to either a file of your choice or a pipe to a system command. If not given any arguments, the output channel will reset to standard output; use no arguments when you wish to stop sending output elsewhere. One of the most useful features of this command is the ability to pipe output to commands such as grep, which can then search for a pattern of your choosing, allowing you to search against database and slash command output (which will, of course, only work if grep is installed on your system).
\p
The buffer display command; this prints the psql input currently buffered. If no SQL input has been entered since the last executed statement, the last executed statement is displayed.
\qecho string
The query-output echo command; this sends a string to your chosen query output channel (which is set with the \o command), instead of stdout. This command can be useful when you need to send non-database–related information into the psql output.
\w file | |command
The buffer output command; this outputs the current query buffer to a specified file, or piped system command.

System commands

The following commands pertain to the general, systematic functions of psql. These include database re-connection, external editor invocation, setting and unsetting psql variables, and quitting psql.

 

 

\connect [ database [ username ] ]
The database re-connection command; this connects you to another database from within psql. You may specify the database to connect to and the username to use (if it is not the same as the current username) and omitting this parameter will cause the current username to be used.
\edit [ file ]
The external editor command; with this, you can either edit a file of your choice or (if no file is specified) the current query buffer. After you are done editing, the new buffer is input to the query buffer, and executed if terminated with a semi-colon.

When opening a file for editing with this command, psql searches your environment variables for the following fields (in this order) to find out what editor to use: PSQL_EDITOR, EDITOR, and VISUAL. If none of these are present, it will attempt to launch /bin/vi.

\q
The quit command; this exits the program. You may also use CTRL-D in most terminal applications to quit.
\set [ name [ value ] ]
The variable setting command; used without arguments, this displays all set variables within psql. Otherwise, it sets the variable name to value. If no value is passed, name is set with an empty value. If multiple values are passed, name is assigned the concatenation of each value.
\unset variable
The variable unsetting command; this unsets a specified variable from memory. This is different from assigning a variable with an empty value, which is still technically set.
\! [ command ]
The shell execution command; without arguments, this opens a shell which overrides the psql prompt until it is exited. Otherwise, it executes a specified shell command from within psql, and displays its results to stdout.

 

Using External Files to Enter Queries

As it is possible to use psql to enter queries directly from the prompt, it is possible to create queries and statements within files and let psql read the files and insert their content into the current buffer. This can be useful to some users.

First, enter your query into a file on your filesystem; after it is complete, open psql. The command to insert files into the current query buffer is \i. Example 6-1 shows how to insert a simple file-based SELECT command into the current buffer. Text from the query is displayed on-screen in the example, but this may not happen on your system by default. To see the lines of a file as it is being read, set the ECHO variable to all by typing \set ECHO all.

Example 6-1. Inserting a file into the current buffer
testdb=# \set ECHO all
testdb=# \i /usr/local/pgsql/query
SELECT * FROM employees WHERE firstname='Andrew';
 firstname | lastname | id
-----------+----------+-----
 Andrew    | Brookins | 100
(1 row)

If you find yourself doing this often for the sole purpose of using your favorite editor, using the \edit command would probably be more convenient.

 

Detailed Listing of Test PC's & Equipment

 

 

 

 

Good Links

Other Good Links

Comp Tech Tutorial

Blue Drake

Sound Wave

 

 

Sponsors

&

Affiliates

 

 

Mod Sites & Guides

 

TwistedMods.com

PimpRig.com

Virtual-Hideout.net

Hitechmods.com

PCclub.com

MADSHRIMP

M:6 Hardware

 

 

Affiliates

Want to Be an Affiliate? Click

 

 

 

Manufacturers

 

ATake

ABIT

Albatron

ALPHA

AMD

Antec

Arctic Cooling

ASUS

ATI

CaseArts

CoolMax

CORSAIR

NVidia

MonsterGecko

OCZ

PowerColor

ProLink

Scythe

Soyo USA

Spire

[steel series]

Titan

ThermalRight

ThermalTake

V-Tech

Xtreme Gamer

 

 

 

 
ADSoft Web design Award

 

   

Google

    Copyright © 2004  Marns CDA
All other trademarks and copyrights on this site are owned by their respective owners. All Rights Reserved. Reproduction by permission only. General disclaimer: This website displays the personal opinions of authors and other contributors. Marns CDA assumes absolutely no liability for these opinions nor for any damages that may result from reading and/or implementing any material found on this website.