From c2c2fd57eecd1c70f0bf10954581bbca7977b7f7 Mon Sep 17 00:00:00 2001 From: Bruce Momjian Date: Thu, 20 Jun 2002 16:00:44 +0000 Subject: [PATCH] Improve COPY syntax to use WITH clause, keep backward compatibility. --- doc/src/sgml/keywords.sgml | 8 +- doc/src/sgml/ref/copy.sgml | 209 ++- doc/src/sgml/ref/psql-ref.sgml | 1573 +++++++++-------- src/backend/commands/copy.c | 77 +- src/backend/commands/dbcommands.c | 4 +- src/backend/nodes/copyfuncs.c | 11 +- src/backend/nodes/equalfuncs.c | 12 +- src/backend/parser/gram.y | 134 +- src/backend/parser/keywords.c | 3 +- src/backend/tcop/utility.c | 18 +- .../pgaccess/doc/html/tutorial/tut_edit.html | 2 +- src/bin/pgaccess/lib/help/copy.hlp | 11 +- src/bin/pgaccess/lib/mainlib.tcl | 2 + src/bin/psql/copy.c | 61 +- src/include/commands/copy.h | 7 +- src/include/commands/dbcommands.h | 4 +- src/include/nodes/parsenodes.h | 9 +- .../postgresql/jdbc1/DatabaseMetaData.java | 4 +- .../postgresql/jdbc2/DatabaseMetaData.java | 4 +- .../de_DE.ISO8859-1/test-de-upper.sql.in | 2 +- .../locale/de_DE.ISO8859-1/test-de.sql.in | 2 +- .../locale/gr_GR.ISO8859-7/test-gr.sql.in | 2 +- src/test/locale/koi8-r/test-koi8.sql.in | 2 +- .../locale/koi8-to-win1251/test-koi8.sql.in | 2 +- 24 files changed, 1215 insertions(+), 948 deletions(-) diff --git a/doc/src/sgml/keywords.sgml b/doc/src/sgml/keywords.sgml index 0f29b704f6..4c1a174025 100644 --- a/doc/src/sgml/keywords.sgml +++ b/doc/src/sgml/keywords.sgml @@ -1,4 +1,4 @@ - + <acronym>SQL</acronym> Key Words @@ -890,6 +890,12 @@ reserved reserved + + DELIMITER + non-reserved + + + DELIMITERS non-reserved diff --git a/doc/src/sgml/ref/copy.sgml b/doc/src/sgml/ref/copy.sgml index 33454dbd4a..28ca264c65 100644 --- a/doc/src/sgml/ref/copy.sgml +++ b/doc/src/sgml/ref/copy.sgml @@ -1,5 +1,5 @@ @@ -21,14 +21,20 @@ PostgreSQL documentation 1999-12-11 -COPY [ BINARY ] table [ WITH OIDS ] +COPY table FROM { 'filename' | stdin } - [ [USING] DELIMITERS 'delimiter' ] - [ WITH NULL AS 'null string' ] -COPY [ BINARY ] table [ WITH OIDS ] + [ [ WITH ] + [ BINARY ] + [ OIDS ] + [ DELIMITER [ AS ] 'delimiter' ] + [ NULL [ AS ] 'null string' ] ] +COPY table TO { 'filename' | stdout } - [ [USING] DELIMITERS 'delimiter' ] - [ WITH NULL AS 'null string' ] + [ [ WITH ] + [ BINARY ] + [ OIDS ] + [ DELIMITER [ AS ] 'delimiter' ] + [ NULL [ AS ] 'null string' ] ] @@ -42,57 +48,57 @@ COPY [ BINARY ] table [ WITH OIDS ] - BINARY + table - Changes the behavior of field formatting, forcing all data to be - stored or read in binary format rather than as text. - The DELIMITERS and WITH NULL options are irrelevant for binary format. + The name (possibly schema-qualified) of an existing table. - table + filename - The name (possibly schema-qualified) of an existing table. + The absolute Unix path name of the input or output file. - WITH OIDS + stdin - Specifies copying the internal object id (OID) for each row. + Specifies that input comes from the client application. - filename + stdout - The absolute Unix path name of the input or output file. + Specifies that output goes to the client application. - stdin + BINARY - Specifies that input comes from the client application. + Changes the behavior of field formatting, forcing all data to be + stored or read in binary format rather than as text. You can not + specify DELIMITER or NULL in binary mode. - stdout + OIDS - Specifies that output goes to the client application. + Specifies copying the internal object id (OID) for each row. @@ -111,8 +117,8 @@ COPY [ BINARY ] table [ WITH OIDS ] The string that represents a NULL value. The default is - \N (backslash-N). - You might prefer an empty string, for example. + \N (backslash-N). You might + prefer an empty string, for example. @@ -172,34 +178,33 @@ ERROR: reason COPY moves data between - PostgreSQL tables and - standard file-system files. + PostgreSQL tables and standard file-system + files. COPY TO copies the entire contents of a table - to - a file, while COPY FROM copies data from a - file to a - table (appending the data to whatever is in the table already). + to a file, while COPY FROM copies + data from a file to a table (appending the data to + whatever is in the table already). - COPY with a file name instructs - the PostgreSQL backend - to directly read from or write to a file. - The file must be accessible to the backend and the name must be specified - from the viewpoint of the backend. - When stdin or stdout is - specified, data flows through the client frontend to the backend. + COPY with a file name instructs the + PostgreSQL backend to directly read from + or write to a file. The file must be accessible to the backend and + the name must be specified from the viewpoint of the backend. When + stdin or stdout is + specified, data flows through the client frontend to the backend. Do not confuse COPY with the - psql instruction \copy. - \copy invokes COPY FROM stdin - or COPY TO stdout, and then fetches/stores the data - in a file accessible to the psql client. - Thus, file accessibility and access rights depend on the client - rather than the backend when \copy is used. + psql instruction + \copy. \copy invokes + COPY FROM stdin or COPY TO + stdout, and then fetches/stores the data in a file + accessible to the psql client. Thus, + file accessibility and access rights depend on the client rather + than the backend when \copy is used. @@ -225,20 +230,19 @@ ERROR: reason By default, a text copy uses a tab ("\t") character as a delimiter - between fields. The field delimiter may be changed to any other single - character with the keyword phrase USING DELIMITERS. Characters - in data fields that happen to match the delimiter character will - be backslash quoted. + between fields. The field delimiter may be changed to any other + single character with the keyword DELIMITER. Characters in data + fields that happen to match the delimiter character will be + backslash quoted. You must have select privilege on any table - whose values are read by - COPY TO, and - insert privilege on a - table into which values are being inserted by COPY FROM. - The backend also needs appropriate Unix permissions for any file read - or written by COPY. + whose values are read by COPY TO, and + insert privilege on a table into which values + are being inserted by COPY FROM. The backend also + needs appropriate Unix permissions for any file read or written by + COPY. @@ -247,28 +251,25 @@ ERROR: reason - COPY stops operation at the first error. This - should not lead to problems in the event of - a COPY TO, but the - target relation will already have received earlier rows in a - COPY FROM. These rows will not be visible or - accessible, but they still occupy disk space. This may amount to a - considerable amount - of wasted disk space if the failure happened well into a large copy - operation. You may wish to invoke VACUUM to recover - the wasted space. + COPY stops operation at the first error. This + should not lead to problems in the event of a COPY + TO, but the target relation will already have received + earlier rows in a COPY FROM. These rows will not + be visible or accessible, but they still occupy disk space. This may + amount to a considerable amount of wasted disk space if the failure + happened well into a large copy operation. You may wish to invoke + VACUUM to recover the wasted space. Files named in a COPY command are read or written - directly by the backend, not by the client application. Therefore, + directly by the backend, not by the client application. Therefore, they must reside on or be accessible to the database server machine, - not the client. They must be accessible to and readable or writable + not the client. They must be accessible to and readable or writable by the PostgreSQL user (the user ID the - server runs as), not the client. - COPY naming a file is only allowed to database - superusers, since it allows reading or writing any file that the backend - has privileges to access. + server runs as), not the client. COPY naming a + file is only allowed to database superusers, since it allows reading + or writing any file that the backend has privileges to access. @@ -282,11 +283,11 @@ ERROR: reason It is recommended that the file name used in COPY - always be specified as an absolute path. This is enforced by the backend - in the case of COPY TO, but for COPY - FROM you do have the option of reading from a file specified - by a relative path. The path will be interpreted relative to the - backend's working directory (somewhere below + always be specified as an absolute path. This is enforced by the + backend in the case of COPY TO, but for + COPY FROM you do have the option of reading from + a file specified by a relative path. The path will be interpreted + relative to the backend's working directory (somewhere below $PGDATA), not the client's working directory. @@ -312,8 +313,8 @@ ERROR: reason place of attributes that are NULL. - If WITH OIDS is specified, the OID is read or written as the first column, - preceding the user data columns. (An error is raised if WITH OIDS is + If OIDS is specified, the OID is read or written as the first column, + preceding the user data columns. (An error is raised if OIDS is specified for a table that does not have OIDs.) @@ -325,11 +326,11 @@ ERROR: reason Backslash characters (\) may be used in the - COPY data to quote data characters that might otherwise - be taken as row or column delimiters. In particular, the following - characters must be preceded by a backslash if they appear - as part of an attribute value: backslash itself, newline, and the current - delimiter character. + COPY data to quote data characters that might + otherwise be taken as row or column delimiters. In particular, the + following characters must be preceded by a backslash if + they appear as part of an attribute value: backslash itself, + newline, and the current delimiter character. The following special backslash sequences are recognized by @@ -412,9 +413,8 @@ ERROR: reason Binary Format The file format used for COPY BINARY changed in - PostgreSQL v7.1. - The new format consists of a file header, zero or more - tuples, and a file trailer. + PostgreSQL v7.1. The new format consists + of a file header, zero or more tuples, and a file trailer. @@ -446,9 +446,9 @@ filters, dropped nulls, dropped high bits, or parity changes.) Integer layout field -int32 constant 0x01020304 in source's byte order. -Potentially, a reader could engage in byte-flipping of subsequent fields -if the wrong byte order is detected here. +int32 constant 0x01020304 in source's byte order. Potentially, a reader +could engage in byte-flipping of subsequent fields if the wrong byte +order is detected here. @@ -457,14 +457,14 @@ if the wrong byte order is detected here. Flags field -int32 bit mask to denote important aspects of the file -format. Bits are numbered from 0 (LSB) to 31 (MSB) --- note that this -field is stored with source's endianness, as are all subsequent integer -fields. Bits 16-31 are reserved to denote critical file format issues; -a reader should abort if it finds an unexpected bit set in this range. -Bits 0-15 are reserved to signal backwards-compatible format issues; -a reader should simply ignore any unexpected bits set in this range. -Currently only one flag bit is defined, and the rest must be zero: +int32 bit mask to denote important aspects of the file format. Bits are +numbered from 0 (LSB) to 31 (MSB) --- note that this field is stored +with source's endianness, as are all subsequent integer fields. Bits +16-31 are reserved to denote critical file format issues; a reader +should abort if it finds an unexpected bit set in this range. Bits 0-15 +are reserved to signal backwards-compatible format issues; a reader +should simply ignore any unexpected bits set in this range. Currently +only one flag bit is defined, and the rest must be zero: Bit 16 @@ -620,7 +620,7 @@ The following example copies a table to standard output, delimiter: -COPY country TO stdout USING DELIMITERS '|'; +COPY country TO stdout WITH DELIMITER '|'; To copy data from a Unix file into a table country: @@ -629,9 +629,9 @@ COPY country TO stdout USING DELIMITERS '|'; COPY country FROM '/usr1/proj/bray/sql/country_data'; - Here is a sample of data suitable for copying into a table - from stdin (so it -has the termination sequence on the last line): + Here is a sample of data suitable for copying into a table from + stdin (so it has the termination sequence on the + last line): AF AFGHANISTAN @@ -645,13 +645,12 @@ ZW ZIMBABWE Note that the white space on each line is actually a TAB. - The following is the same data, output in binary format on a Linux/i586 - machine. The data is shown after filtering through - the Unix utility od -c. The table has - three fields; the first is char(2), - the second is text, and the third is - integer. All the - rows have a null value in the third field. + The following is the same data, output in binary format on a + Linux/i586 machine. The data is shown after filtering through the + Unix utility od -c. The table has three fields; + the first is char(2), the second is text, + and the third is integer. All the rows have a null value + in the third field. 0000000 P G B C O P Y \n 377 \r \n \0 004 003 002 001 diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml index 2dede1c99c..85bc6852be 100644 --- a/doc/src/sgml/ref/psql-ref.sgml +++ b/doc/src/sgml/ref/psql-ref.sgml @@ -1,5 +1,5 @@ @@ -36,13 +36,13 @@ PostgreSQL documentation Summary - 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. - In addition, it 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 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. In addition, it provides a + number of meta-commands and various shell-like features to + facilitate writing scripts and automating a wide variety of tasks. @@ -64,26 +64,28 @@ PostgreSQL documentation Connecting To A Database - 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 user name you want to connect as. psql can be - told about those parameters via command line options, namely , - , , and respectively. - If an argument is found that does not belong to any option it will be interpreted - as the database name (or the user name, if the database name is also - given). Not all these options are required, defaults do apply. - If you omit the host name psql will connect via a Unix domain socket - to a server on the - local host. The default port number is compile-time determined. Since the database - server uses the same default, you will not have to specify the port in most - cases. The default user name is your Unix user name, as is the default - database name. - Note that you can't just connect to any database under any user name. Your database - administrator should have informed you about your access rights. To save you some typing - you can also set the environment variables PGDATABASE, - PGHOST, PGPORT and PGUSER - to appropriate values. + 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 user + name you want to connect as. psql can be + told about those parameters via command line options, namely + , , , and + respectively. If an argument is found that does + not belong to any option it will be interpreted as the database name + (or the user name, if the database name is also given). Not all + these options are required, defaults do apply. If you omit the host + name psql will connect via a Unix domain socket to a server on the + local host. The default port number is compile-time determined. + Since the database server uses the same default, you will not have + to specify the port in most cases. The default user name is your + Unix user name, as is the default database name. Note that you can't + just connect to any database under any user name. Your database + administrator should have informed you about your access rights. To + save you some typing you can also set the environment variables + PGDATABASE, PGHOST, + PGPORT and PGUSER to appropriate + values. @@ -101,9 +103,10 @@ PostgreSQL documentation Entering Queries - In normal operation, psql provides a prompt with - the name of the database to which psql is currently - connected, followed by the string =>. For example, + In normal operation, psql provides a + prompt with the name of the database to which + psql is currently connected, followed by + the string =>. For example, $ psql testdb Welcome to psql, the PostgreSQL interactive terminal. @@ -119,11 +122,12 @@ testdb=> - At the prompt, the user may type in SQL queries. - Ordinarily, input lines are sent to the backend when a query-terminating - semicolon is reached. An end of line does not terminate a query! Thus queries - can be spread over several lines for clarity. If the query was sent and without - error, the query results are displayed on the screen. + At the prompt, the user may type in SQL queries. + Ordinarily, input lines are sent to the backend when a + query-terminating semicolon is reached. An end of line does not + terminate a query! Thus queries can be spread over several lines for + clarity. If the query was sent and without error, the query results + are displayed on the screen. @@ -143,12 +147,13 @@ testdb=> <application>psql</application> Meta-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 are what makes - psql interesting for administration or scripting. - Meta-commands are more commonly called slash or backslash 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 are what makes + psql interesting for administration or + scripting. Meta-commands are more commonly called slash or backslash + commands. @@ -159,46 +164,49 @@ testdb=> - To include whitespace into an argument you must quote it with a single - quote. To include a single quote into such an argument, precede it by - a backslash. Anything contained in single quotes is furthermore subject to - C-like substitutions for \n (new line), \t - (tab), \digits, + To include whitespace into an argument you must quote it with a + single quote. To include a single quote into such an argument, + precede it by a backslash. Anything contained in single quotes is + furthermore subject to C-like substitutions for + \n (new line), \t (tab), + \digits, \0digits, and - \0xdigits - (the character with the given decimal, octal, or hexadecimal code). + \0xdigits (the + character with the given decimal, octal, or hexadecimal code). If an unquoted argument begins with a colon (:), - it is taken as a variable and the value of the variable is taken as the - argument instead. + it is taken as a variable and the value of the variable is taken as + the argument instead. - Arguments that are quoted in backticks (`) - are taken as a command line that is passed to the shell. The output of the - command (with a trailing newline removed) is taken as the argument value. - The above escape sequences also apply in backticks. + Arguments that are quoted in backticks + (`) are taken as a command line that is passed to + the shell. The output of the command (with a trailing newline + removed) is taken as the argument value. The above escape sequences + also apply in backticks. - Some commands take the name of an SQL identifier (such as - a table name) as argument. These arguments follow the syntax rules of - SQL regarding double quotes: an identifier without - double quotes is coerced to lower-case. For all other commands - double quotes are not special and will become part of the argument. + Some commands take the name of an SQL identifier + (such as a table name) as argument. These arguments follow the + syntax rules of SQL regarding double quotes: an + identifier without double quotes is coerced to lower-case. For all + other commands double quotes are not special and will become part of + the argument. - Parsing for arguments stops when another unquoted backslash occurs. This - is taken as the beginning of a new meta-command. The special sequence - \\ - (two backslashes) marks the end of arguments and continues parsing - SQL queries, if any. That way SQL and - psql commands can be freely mixed on a line. - But in any case, the arguments of a meta-command cannot continue beyond the end - of the line. + Parsing for arguments stops when another unquoted backslash occurs. + This is taken as the beginning of a new meta-command. The special + sequence \\ (two backslashes) marks the end of + arguments and continues parsing SQL queries, if + any. That way SQL and + psql commands can be freely mixed on a + line. But in any case, the arguments of a meta-command cannot + continue beyond the end of the line. @@ -222,8 +230,8 @@ testdb=> Change the current working directory to - directory. Without argument, - change to the current user's home directory. + directory. Without argument, change + to the current user's home directory. @@ -238,12 +246,13 @@ testdb=> \C [ title ] - Set the title of any tables being printed as the result of a query or - unset any such title. This command is equivalent to - \pset title title. - (The name of this - command derives from caption, as it was previously only - used to set the caption in an HTML table.) + Set the title of any tables being printed as the result of a + query or unset any such title. This command is equivalent to + \pset title title. (The name of + this command derives from caption, as it was + previously only used to set the caption in an + HTML table.) @@ -252,78 +261,86 @@ testdb=> \connect (or \c) [ dbname [ username ] ] - Establishes a connection to a new database and/or under a user name. The - previous connection is closed. - If dbname is - + Establishes a connection to a new database and/or under a user + name. The previous connection is closed. If dbname is - the current database name is assumed. - If username is omitted - the current user name is assumed. - + If username is + omitted the current user name is assumed. - As a special rule, \connect without any arguments will connect - to the default database as the default user (as you would have gotten - by starting psql without any arguments). + As a special rule, \connect without any + arguments will connect to the default database as the default + user (as you would have gotten by starting + psql without any arguments). - If the connection attempt failed (wrong user name, access denied, etc.), the - previous connection will be kept if and only if psql is - in interactive mode. When executing a non-interactive script, processing - will immediately stop with an error. This distinction was chosen as a user - convenience against typos on the one hand, and a safety mechanism that - scripts are not accidentally acting on the wrong database on the other hand. + If the connection attempt failed (wrong user name, access + denied, etc.), the previous connection will be kept if and only + if psql is in interactive mode. When + executing a non-interactive script, processing will immediately + stop with an error. This distinction was chosen as a user + convenience against typos on the one hand, and a safety + mechanism that scripts are not accidentally acting on the wrong + database on the other hand. \copy table - [ with oids ] { from | to } + { from | to } filename | stdin | stdout - [ using delimiters 'characters' ] - [ with null as 'string' ] + [ with ] + [ oids ] + [ delimiter [as] 'character' ] + [ null [as] 'string' ] - Performs a frontend (client) copy. This is an operation that runs an - SQL command, - but instead of the backend's reading or writing the specified file, and - consequently requiring backend access and special user privilege, - as well as being bound to the file system accessible by the backend, - psql reads or writes the - file and routes the data between the backend and the local file system. + Performs a frontend (client) copy. This is an operation that + runs an SQL command, but instead of the backend's + reading or writing the specified file, and consequently + requiring backend access and special user privilege, as well as + being bound to the file system accessible by the backend, + psql reads or writes the file and + routes the data between the backend and the local file system. - The syntax of the command is similar to that of the SQL - COPY command (see its description for the details). - Note that, because of this, special parsing rules apply to the - \copy command. In particular, the variable - substitution rules and backslash escapes do not apply. + The syntax of the command is similar to that of the + SQL COPY command (see its + description for the details). Note that, because of this, + special parsing rules apply to the \copy + command. In particular, the variable substitution rules and + backslash escapes do not apply. - This operation is not as efficient as the SQL - COPY command because all data must pass through the - client/server IP or socket connection. For large amounts of data the other - technique may be preferable. + This operation is not as efficient as the SQL + COPY command because all data must pass + through the client/server IP or socket connection. For large + amounts of data the other technique may be preferable. - Note the difference in interpretation of stdin and stdout - between frontend and backend copies: in a frontend copy these always refer - to psql's input and output stream. On a backend - copy stdin comes from wherever the COPY - itself came from (for example, a script run with the option), - and stdout refers to the query output stream (see + Note the difference in interpretation of + stdin and stdout between + frontend and backend copies: in a frontend copy these always + refer to psql's input and output + stream. On a backend copy stdin comes from + wherever the COPY itself came from (for + example, a script run with the option), and + stdout refers to the query output stream (see \o meta-command below). @@ -334,7 +351,8 @@ testdb=> \copyright - Shows the copyright and distribution terms of PostgreSQL. + Shows the copyright and distribution terms of + PostgreSQL. @@ -344,25 +362,26 @@ testdb=> - Shows all columns of relation - (which could be a table, view, index, or sequence), - their types, and any special attributes such as NOT NULL - or defaults, if any. - If the relation is, in fact, a table, any defined indices, primary keys, unique - constraints and check constraints are also listed. - If the relation is a view, the view definition is also shown. + Shows all columns of relation (which could be a + table, view, index, or sequence), their types, and any special + attributes such as NOT NULL or defaults, if + any. If the relation is, in fact, a table, any defined indices, + primary keys, unique constraints and check constraints are also + listed. If the relation is a view, the view definition is also + shown. - The command form \d+ is identical, but any comments - associated with the table columns are shown as well. + The command form \d+ is identical, but any + comments associated with the table columns are shown as well. If \d is called without any arguments, it is - equivalent to \dtvs which will show a list - of all tables, views, and sequences. This is purely a convenience + equivalent to \dtvs which will show a list of + all tables, views, and sequences. This is purely a convenience measure. @@ -374,9 +393,10 @@ testdb=> - Lists all available aggregate functions, together with the data type they operate on. - If pattern - (a regular expression) is specified, only matching aggregates are shown. + Lists all available aggregate functions, together with the data + type they operate on. If pattern (a regular expression) + is specified, only matching aggregates are shown. @@ -385,10 +405,12 @@ testdb=> \dd [ object ] - Shows the descriptions of object - (which can be a regular expression), or of all objects if no argument is given. - (Object covers aggregates, functions, operators, types, relations - (tables, views, indexes, sequences, large objects), rules, and triggers.) For example: + Shows the descriptions of object (which can be a regular + expression), or of all objects if no argument is given. + (Object covers aggregates, functions, operators, + types, relations (tables, views, indexes, sequences, large + objects), rules, and triggers.) For example: => \dd version Object descriptions @@ -400,14 +422,14 @@ testdb=> - Descriptions for objects can be generated with the COMMENT ON - SQL command. + Descriptions for objects can be generated with the + COMMENT ON SQL command. - PostgreSQL stores the object descriptions in the - pg_description system table. + PostgreSQL stores the object + descriptions in the pg_description system table. @@ -419,9 +441,9 @@ testdb=> \dD [ pattern ] - Lists all available domains (derived types). - If pattern - (a regular expression) is specified, only matching domains are shown. + Lists all available domains (derived types). If pattern (a regular expression) + is specified, only matching domains are shown. @@ -432,10 +454,11 @@ testdb=> - Lists available functions, together with their argument and return types. - If pattern - (a regular expression) is specified, only matching functions are shown. - If the form \df+ is used, additional information about + Lists available functions, together with their argument and + return types. If pattern (a regular expression) + is specified, only matching functions are shown. If the form + \df+ is used, additional information about each function, including language and description, is shown. @@ -447,17 +470,18 @@ testdb=> - This is not the actual command name: The letters i, s, t, v, S stand for - index, sequence, table, view, and system table, respectively. You can specify - any or all of them in any order to obtain a listing of them, together with - who the owner is. + This is not the actual command name: The letters i, s, t, v, S + stand for index, sequence, table, view, and system table, + respectively. You can specify any or all of them in any order to + obtain a listing of them, together with who the owner is. - If pattern is specified, - it is a regular expression that restricts the listing to those objects - whose name matches. If one appends a + to the command name, - each object is listed with its associated description, if any. + If pattern is + specified, it is a regular expression that restricts the listing + to those objects whose name matches. If one appends a + + to the command name, each object is listed with + its associated description, if any. @@ -467,7 +491,8 @@ testdb=> \dl - This is an alias for \lo_list, which shows a list of large objects. + This is an alias for \lo_list, which shows a + list of large objects. @@ -478,8 +503,8 @@ testdb=> Lists available operators with their operand and return types. - If name - is specified, only operators with that name will be shown. + If name is + specified, only operators with that name will be shown. @@ -489,8 +514,9 @@ testdb=> \dp [ pattern ] - This is an alias for \z which was included for its - greater mnemonic value (display permissions). + This is an alias for \z which was included + for its greater mnemonic value (display + permissions). @@ -500,8 +526,9 @@ testdb=> \dT [ pattern ] - Lists all data types or only those that match pattern. - The command form \dT+ shows extra information. + Lists all data types or only those that match pattern. The command form + \dT+ shows extra information. @@ -511,7 +538,8 @@ testdb=> \du [ pattern ] - Lists all configured users or only those that match pattern. + Lists all configured users or only those that match pattern. @@ -522,28 +550,29 @@ testdb=> - If filename is specified, - the file is edited; after the editor exits, its content is copied - back to the query buffer. If no argument is given, the current query - buffer is copied to a temporary file which is then edited in the same - fashion. + If filename is + specified, the file is edited; after the editor exits, its + content is copied back to the query buffer. If no argument is + given, the current query buffer is copied to a temporary file + which is then edited in the same fashion. - The new query buffer is then re-parsed according to the normal rules of - psql, where the whole buffer is treated as - a single line. (Thus you cannot make scripts this way. - Use \i for that.) This means also that - if the query ends with (or rather contains) a semicolon, it is immediately - executed. In other cases it will merely wait in the query buffer. + The new query buffer is then re-parsed according to the normal + rules of psql, where the whole buffer + is treated as a single line. (Thus you cannot make scripts this + way. Use \i for that.) This means also that + if the query ends with (or rather contains) a semicolon, it is + immediately executed. In other cases it will merely wait in the + query buffer. - psql searches the environment variables - PSQL_EDITOR, EDITOR, and VISUAL - (in that order) for an editor to use. If all of them are unset, - /bin/vi is run. + psql searches the environment + variables PSQL_EDITOR, EDITOR, and + VISUAL (in that order) for an editor to use. If + all of them are unset, /bin/vi is run. @@ -554,8 +583,8 @@ testdb=> \echo text [ ... ] - Prints the arguments to the standard output, separated by one space and - followed by a newline. This can be useful to + Prints the arguments to the standard output, separated by one + space and followed by a newline. This can be useful to intersperse information in the output of scripts. For example: => \echo `date` @@ -567,8 +596,9 @@ Tue Oct 26 21:40:57 CEST 1999 - If you use the \o command to redirect your query output - you may wish to use \qecho instead of this command. + If you use the \o command to redirect your + query output you may wish to use \qecho + instead of this command. @@ -592,9 +622,10 @@ Tue Oct 26 21:40:57 CEST 1999 - Sets the field separator for unaligned query output. The default is -pipe (|). See also \pset for a generic way - of setting output options. + Sets the field separator for unaligned query output. The default + is pipe (|). See also + \pset for a generic way of setting output + options. @@ -605,12 +636,14 @@ pipe (|). See also \pset for a generic way - Sends the current query input buffer to the backend and optionally - saves the output in filename - or pipes the output into a separate Unix shell to execute - command. A bare \g - is virtually equivalent to a semicolon. A \g with argument - is a one-shot alternative to the \o command. + Sends the current query input buffer to the backend and + optionally saves the output in filename or pipes the output + into a separate Unix shell to execute command. A bare + \g is virtually equivalent to a semicolon. A + \g with argument is a one-shot + alternative to the \o command. @@ -619,19 +652,20 @@ pipe (|). See also \pset for a generic way \help (or \h) [ command ] - Give syntax help on the specified SQL command. - If command is not specified, - then psql will - list all the commands for which syntax help is - available. If command - is an asterisk (*), then - syntax help on all SQL commands is shown. + Give syntax help on the specified SQL + command. If command + is not specified, then psql will list + all the commands for which syntax help is available. If + command is an + asterisk (*), then syntax help on all + SQL commands is shown. - To simplify typing, commands that consists of several words do not have to be quoted. - Thus it is fine to type \help alter table. + To simplify typing, commands that consists of several words do + not have to be quoted. Thus it is fine to type \help + alter table. @@ -642,10 +676,11 @@ pipe (|). See also \pset for a generic way \H - Turns on HTML query output format. If the HTML - format is already on, it is switched back to the default aligned text format. This - command is for compatibility and convenience, but see \pset about - setting other output options. + Turns on HTML query output format. If the + HTML format is already on, it is switched + back to the default aligned text format. This command is for + compatibility and convenience, but see \pset + about setting other output options. @@ -655,13 +690,15 @@ pipe (|). See also \pset for a generic way \i filename - Reads input from the file filename - and executes it as though it had been typed on the keyboard. + Reads input from the file filename and executes it as + though it had been typed on the keyboard. - If you want to see the lines on the screen as they are read you must set - the variable ECHO to all. + If you want to see the lines on the screen as they are read you + must set the variable ECHO to + all. @@ -672,12 +709,12 @@ pipe (|). See also \pset for a generic way \l (or \list) - List all the databases in the server as well as their owners. Append a - + to the command name to see any descriptions - for the databases as well. If your PostgreSQL - installation was - compiled with multibyte encoding support, the encoding scheme of each - database is shown as well. + List all the databases in the server as well as their owners. + Append a + to the command name to see any + descriptions for the databases as well. If your + PostgreSQL installation was compiled + with multibyte encoding support, the encoding scheme of each + database is shown as well. @@ -688,21 +725,26 @@ pipe (|). See also \pset for a generic way - Reads the large object with OID loid - from the database and writes it to filename. - Note that this is subtly different from the server function lo_export, - which acts with the permissions of the user that the database server runs as and - on the server's file system. + Reads the large object with OID loid from the database and + writes it to filename. Note that this is + subtly different from the server function + lo_export, which acts with the permissions + of the user that the database server runs as and on the server's + file system. - Use \lo_list to find out the large object's OID. + Use \lo_list to find out the large object's + OID. - See the description of the LO_TRANSACTION variable for - important information concerning all large object operations. + See the description of the LO_TRANSACTION + variable for important information concerning all large object + operations. @@ -714,29 +756,32 @@ pipe (|). See also \pset for a generic way - Stores the file into a PostgreSQL large object. - Optionally, it associates the given comment with the object. Example: + Stores the file into a PostgreSQL + large object. Optionally, it associates the given + comment with the object. Example: foo=> \lo_import '/home/peter/pictures/photo.xcf' 'a picture of me' lo_import 152801 - The response indicates that the large object received object id 152801 - which one ought to remember if one wants to access the object ever again. - For that reason it is recommended to always associate a human-readable - comment with every object. Those can then be seen with the - \lo_list command. + The response indicates that the large object received object id + 152801 which one ought to remember if one wants to access the + object ever again. For that reason it is recommended to always + associate a human-readable comment with every object. Those can + then be seen with the \lo_list command. - Note that this command is subtly different from the server-side lo_import - because it acts as the local user on the local file system, rather than the server's - user and file system. + Note that this command is subtly different from the server-side + lo_import because it acts as the local user + on the local file system, rather than the server's user and file + system. - See the description of the LO_TRANSACTION variable for - important information concerning all large object operations. + See the description of the LO_TRANSACTION + variable for important information concerning all large object + operations. @@ -746,9 +791,9 @@ lo_import 152801 \lo_list - Shows a list of all PostgreSQL large - objects currently stored in the database, along with any - comments provided for them. + Shows a list of all PostgreSQL + large objects currently stored in the database, + along with any comments provided for them. @@ -758,19 +803,22 @@ lo_import 152801 - Deletes the large object with OID loid - from the database. + Deletes the large object with OID + loid from the + database. - Use \lo_list to find out the large object's OID. + Use \lo_list to find out the large object's + OID. - See the description of the LO_TRANSACTION variable for - important information concerning all large object operations. + See the description of the LO_TRANSACTION + variable for important information concerning all large object + operations. @@ -782,25 +830,26 @@ lo_import 152801 - Saves future query results to the file - filename or pipes future - results into a separate Unix shell to execute - command. - If no arguments are specified, the query output will be reset to + Saves future query results to the file filename or pipes future results + into a separate Unix shell to execute command. If no arguments are + specified, the query output will be reset to stdout. - Query results includes all tables, command responses, - and notices obtained - from the database server, as well as output of various backslash - commands that query the database (such as \d), - but not error messages. + Query results includes all tables, command + responses, and notices obtained from the database server, as + well as output of various backslash commands that query the + database (such as \d), but not error + messages. - To intersperse text output in between query results, use \qecho. + To intersperse text output in between query results, use + \qecho. @@ -822,10 +871,11 @@ lo_import 152801 - This command sets options affecting the output of query result tables. - parameter describes which option - is to be set. The semantics of value - depend thereon. + This command sets options affecting the output of query result + tables. parameter + describes which option is to be set. The semantics of + value depend + thereon. @@ -836,21 +886,23 @@ lo_import 152801 Sets the output format to one of unaligned, - aligned, html, or latex. - Unique abbreviations are allowed. (That would mean one letter is enough.) + aligned, html, or + latex. Unique abbreviations are allowed. + (That would mean one letter is enough.) - Unaligned writes all fields of a tuple on a line, separated - by the currently active field separator. This is intended to create output - that might be intended to be read in by other programs (tab-separated, - comma-separated). - Aligned mode is the - standard, human-readable, nicely formatted text output that is default. - The HTML and LaTeX modes - put out tables that are intended to be included in documents using the - respective mark-up language. They are not complete documents! (This might - not be so dramatic in HTML, but in LaTeX you must + Unaligned writes all fields of a tuple on a + line, separated by the currently active field separator. This + is intended to create output that might be intended to be read + in by other programs (tab-separated, comma-separated). + Aligned mode is the standard, human-readable, + nicely formatted text output that is default. The + HTML and + LaTeX modes put out tables that are intended to + be included in documents using the respective mark-up + language. They are not complete documents! (This might not be + so dramatic in HTML, but in LaTeX you must have a complete document wrapper.) @@ -860,12 +912,13 @@ lo_import 152801 border - The second argument must be a number. In general, the higher the number - the more borders and lines the tables will have, but this depends on - the particular format. In HTML mode, this will - translate directly into the border=... attribute, in - the others only values 0 (no border), 1 (internal dividing lines), and 2 - (table frame) make sense. + The second argument must be a number. In general, the higher + the number the more borders and lines the tables will have, + but this depends on the particular format. In + HTML mode, this will translate directly + into the border=... attribute, in the + others only values 0 (no border), 1 (internal dividing lines), + and 2 (table frame) make sense. @@ -874,10 +927,11 @@ lo_import 152801 expanded (or x) - Toggles between regular and expanded format. When expanded format is - enabled, all output has two columns with the field name on the left - and the data on the right. This mode is useful if the data wouldn't - fit on the screen in the normal horizontal mode. + Toggles between regular and expanded format. When expanded + format is enabled, all output has two columns with the field + name on the left and the data on the right. This mode is + useful if the data wouldn't fit on the screen in the normal + horizontal mode. @@ -890,10 +944,11 @@ lo_import 152801 null - The second argument is a string that should be printed whenever a field - is null. The default is not to print anything, which can easily be mistaken - for, say, an empty string. Thus, one might choose to write - \pset null '(null)'. + The second argument is a string that should be printed + whenever a field is null. The default is not to print + anything, which can easily be mistaken for, say, an empty + string. Thus, one might choose to write \pset null + '(null)'. @@ -902,10 +957,11 @@ lo_import 152801 fieldsep - Specifies the field separator to be used in unaligned output mode. That way - one can create, for example, tab- or comma-separated output, which other - programs might prefer. To set a tab as field separator, type - \pset fieldsep '\t'. The default field separator is + Specifies the field separator to be used in unaligned output + mode. That way one can create, for example, tab- or + comma-separated output, which other programs might prefer. To + set a tab as field separator, type \pset fieldsep + '\t'. The default field separator is '|' (a pipe symbol). @@ -915,7 +971,8 @@ lo_import 152801 footer - Toggles the display of the default footer (x rows). + Toggles the display of the default footer (x + rows). @@ -924,8 +981,8 @@ lo_import 152801 recordsep - Specifies the record (line) separator to use in unaligned output mode. The default - is a newline character. + Specifies the record (line) separator to use in unaligned + output mode. The default is a newline character. @@ -934,9 +991,10 @@ lo_import 152801 tuples_only (or t) - Toggles between tuples only and full display. Full display may show - extra information such as column headers, titles, and various footers. - In tuples only mode, only actual table data is shown. + Toggles between tuples only and full display. Full display may + show extra information such as column headers, titles, and + various footers. In tuples only mode, only actual table data + is shown. @@ -945,15 +1003,15 @@ lo_import 152801 title [ text ] - Sets the table title for any subsequently printed tables. This can be - used to give your output descriptive tags. If no argument is given, - the title is unset. + Sets the table title for any subsequently printed tables. This + can be used to give your output descriptive tags. If no + argument is given, the title is unset. - This formerly only affected HTML mode. You can now - set titles in any output format. + This formerly only affected HTML mode. You + can now set titles in any output format. @@ -963,11 +1021,12 @@ lo_import 152801 tableattr (or T) [ text ] - Allows you to specify any attributes to be placed inside the HTML - table tag. This could for example be - cellpadding or bgcolor. Note that you - probably don't want to specify border here, as - that is already taken care of by \pset border. + Allows you to specify any attributes to be placed inside the + HTML table tag. This + could for example be cellpadding or + bgcolor. Note that you probably don't want + to specify border here, as that is already + taken care of by \pset border. @@ -977,25 +1036,28 @@ lo_import 152801 pager - Toggles the list of a pager to do table output. If the environment variable - PAGER is set, the output is piped to the specified program. - Otherwise more is used. + Toggles the list of a pager to do table output. If the + environment variable PAGER is set, the output + is piped to the specified program. Otherwise + more is used. - In any case, psql only uses the pager if it - seems appropriate. That means among other things that the output is to - a terminal and that the table would normally not fit on the screen. - Because of the modular nature of the printing routines it is not always - possible to predict the number of lines that will actually be printed. - For that reason psql might not appear very + In any case, psql only uses the + pager if it seems appropriate. That means among other things + that the output is to a terminal and that the table would + normally not fit on the screen. Because of the modular nature + of the printing routines it is not always possible to predict + the number of lines that will actually be printed. For that + reason psql might not appear very discriminating about when to use the pager and when not to. Illustrations on how these different formats look can be seen in - the section. + the section. @@ -1008,8 +1070,9 @@ lo_import 152801 - It is an error to call \pset without arguments. In the future - this call might show the current status of all printing options. + It is an error to call \pset without + arguments. In the future this call might show the current status + of all printing options. @@ -1031,9 +1094,9 @@ lo_import 152801 \qecho text [ ... ] - This command is identical to \echo except that - all output will be written to the query output channel, as set by - \o. + This command is identical to \echo except + that all output will be written to the query output channel, as + set by \o. @@ -1053,21 +1116,20 @@ lo_import 152801 \s [ filename ] - Print or save the command line history to - filename. - If filename is omitted, - the history is written to the standard output. - This option is only available if psql is - configured to use the GNU history library. + Print or save the command line history to filename. If filename is omitted, the history + is written to the standard output. This option is only available + if psql is configured to use the + GNU history library. - In the current version, it is no longer - necessary to save the command history, since that will be done - automatically on program termination. The history is - also loaded automatically every time psql - starts up. + In the current version, it is no longer necessary to save the + command history, since that will be done automatically on + program termination. The history is also loaded automatically + every time psql starts up. @@ -1079,28 +1141,30 @@ lo_import 152801 - Sets the internal variable name - to value or, if more than one - value is given, to the concatenation of all of them. If no second argument - is given, the variable is just set with no value. To unset a variable, use - the \unset command. + Sets the internal variable name to value or, if more than one value + is given, to the concatenation of all of them. If no second + argument is given, the variable is just set with no value. To + unset a variable, use the \unset command. - Valid variable names can contain characters, digits, and underscores. - See the section about psql variables for details. + Valid variable names can contain characters, digits, and + underscores. See the section about + psql variables for details. - Although you are welcome to set any variable to anything you want, - psql treats several variables as special. - They are documented in the section about variables. + Although you are welcome to set any variable to anything you + want, psql treats several variables + as special. They are documented in the section about variables. - This command is totally separate from the SQL command - . + This command is totally separate from the SQL + command . @@ -1111,9 +1175,9 @@ lo_import 152801 \t - Toggles the display of output column name headings and row count footer. - This command is equivalent to \pset tuples_only and - is provided for convenience. + Toggles the display of output column name headings and row count + footer. This command is equivalent to \pset + tuples_only and is provided for convenience. @@ -1123,9 +1187,11 @@ lo_import 152801 \T table_options - Allows you to specify options to be placed within the table - tag in HTML tabular output mode. This command is - equivalent to \pset tableattr table_options. + Allows you to specify options to be placed within the + table tag in HTML tabular + output mode. This command is equivalent to \pset + tableattr table_options. @@ -1145,8 +1211,9 @@ lo_import 152801 \w {filename | |command} - Outputs the current query buffer to the file filename - or pipes it to the Unix command command. + Outputs the current query buffer to the file filename or pipes it to the Unix + command command. @@ -1167,9 +1234,10 @@ lo_import 152801 \z [ pattern ] - Produces a list of all tables in the database with their appropriate - access permissions listed. If an argument is given it is taken as a regular - expression which limits the listing to those tables which match it. + Produces a list of all tables in the database with their + appropriate access permissions listed. If an argument is given + it is taken as a regular expression which limits the listing to + those tables which match it. @@ -1187,23 +1255,25 @@ Access permissions for database "test" "=r": PUBLIC has read - (SELECT) permission on the table. + (SELECT) permission on the table. - "joe=arwR": User joe has read, - write (UPDATE, DELETE), - append (INSERT) permissions, - and permission to create rules on the table. + "joe=arwR": User joe has + read, write (UPDATE, + DELETE), append + (INSERT) permissions, and permission to + create rules on the table. - "group staff=ar": Group staff - has SELECT and INSERT permission. + "group staff=ar": Group + staff has SELECT and + INSERT permission. @@ -1224,8 +1294,9 @@ Access permissions for database "test" Escapes to a separate Unix shell or executes the Unix command - command. The arguments - are not further interpreted, the shell will see them as is. + command. The + arguments are not further interpreted, the shell will see them + as is. @@ -1235,7 +1306,8 @@ Access permissions for database "test" \? - Get help information about the backslash (\) commands. + Get help information about the backslash (\) + commands. @@ -1254,9 +1326,9 @@ Access permissions for database "test" Command-line Options - If so configured, psql understands both standard - Unix short options, and GNU-style long options. The latter - are not available on all systems. + If so configured, psql understands both + standard Unix short options, and GNU-style long + options. The latter are not available on all systems. @@ -1265,9 +1337,10 @@ Access permissions for database "test" -a, --echo-all - Print all the lines to the screen as they are read. This is more useful for - script processing rather than interactive mode. - This is equivalent to setting the variable ECHO to all. + Print all the lines to the screen as they are read. This is more + useful for script processing rather than interactive mode. This is + equivalent to setting the variable ECHO to + all. @@ -1277,8 +1350,8 @@ Access permissions for database "test" -A, --no-align - Switches to unaligned output mode. (The default output mode is otherwise - aligned.) + Switches to unaligned output mode. (The default output mode is + otherwise aligned.) @@ -1288,18 +1361,19 @@ Access permissions for database "test" -c, --command query - Specifies that psql - is to execute one query string, query, - and then exit. This is useful in shell scripts. + Specifies that psql is to execute one + query string, query, + and then exit. This is useful in shell scripts. - query must be either a query string - that is completely parseable by the backend (i.e., it contains no psql - specific features), or it is a single backslash command. Thus - you cannot mix SQL and psql + query must be either + a query string that is completely parseable by the backend (i.e., + it contains no psql specific features), + or it is a single backslash command. Thus you cannot mix + SQL and psql meta-commands. To achieve that, you could pipe the string into - psql, like this: - echo "\x \\ select * from foo;" | psql. + psql, like this: echo "\x \\ + select * from foo;" | psql. @@ -1309,8 +1383,9 @@ Access permissions for database "test" -d, --dbname dbname - Specifies the name of the database to connect to. This is equivalent to specifying - dbname as the first non-option + Specifies the name of the database to connect to. This is + equivalent to specifying dbname as the first non-option argument on the command line. @@ -1321,9 +1396,9 @@ Access permissions for database "test" -e, --echo-queries - Show all queries that are sent to the backend. - This is equivalent to setting the variable ECHO - to queries. + Show all queries that are sent to the backend. This is equivalent + to setting the variable ECHO to + queries. @@ -1333,10 +1408,11 @@ Access permissions for database "test" -E, --echo-hidden - Echoes the actual queries generated by \d and other backslash commands. - You can use this if you wish to include similar functionality into - your own programs. This is equivalent to setting the variable - ECHO_HIDDEN from within psql. + Echoes the actual queries generated by \d and other backslash + commands. You can use this if you wish to include similar + functionality into your own programs. This is equivalent to + setting the variable ECHO_HIDDEN from within + psql. @@ -1348,8 +1424,9 @@ Access permissions for database "test" Use the file filename as the source of queries instead of reading queries interactively. - After the file is processed, psql terminates. - This is in many ways equivalent to the internal command \i. + After the file is processed, psql + terminates. This is in many ways equivalent to the internal + command \i. @@ -1358,14 +1435,16 @@ Access permissions for database "test" - Using this option is subtly different from writing - psql < filename. - In general, both will do what you expect, but using -f - enables some nice features such as error messages with line numbers. - There is also a slight chance that using this option will reduce - the start-up overhead. On the other hand, the variant using the shell's - input redirection is (in theory) guaranteed to yield exactly the same - output that you would have gotten had you entered everything by hand. + Using this option is subtly different from writing psql + < filename. In general, + both will do what you expect, but using -f + enables some nice features such as error messages with line + numbers. There is also a slight chance that using this option will + reduce the start-up overhead. On the other hand, the variant using + the shell's input redirection is (in theory) guaranteed to yield + exactly the same output that you would have gotten had you entered + everything by hand. @@ -1375,8 +1454,9 @@ Access permissions for database "test" -F, --field-separator separator - Use separator as the field separator. - This is equivalent to \pset fieldsep or \f. + Use separator as the + field separator. This is equivalent to \pset + fieldsep or \f. @@ -1387,9 +1467,9 @@ Access permissions for database "test" Specifies the host name of the machine on which the - postmaster is running. - If host begins with a slash, it is used - as the directory for the unix domain socket. + postmaster is running. If host begins + with a slash, it is used as the directory for the unix domain + socket. @@ -1399,9 +1479,9 @@ Access permissions for database "test" -H, --html - Turns on HTML tabular output. This is equivalent - to \pset format html or the \H - command. + Turns on HTML tabular output. This is + equivalent to \pset format html or the + \H command. @@ -1411,8 +1491,9 @@ Access permissions for database "test" -l, --list - Lists all available databases, then exits. Other non-connection options - are ignored. This is similar to the internal command \list. + Lists all available databases, then exits. Other non-connection + options are ignored. This is similar to the internal command + \list. @@ -1422,8 +1503,9 @@ Access permissions for database "test" -o, --output filename - Put all query output into file filename. - This is equivalent to the command \o. + Put all query output into file filename. This is equivalent to + the command \o. @@ -1433,11 +1515,12 @@ Access permissions for database "test" -p, --port port - Specifies the TCP/IP port or, by omission, the local Unix domain socket file - extension on which the postmaster - is listening for connections. Defaults to the value of the - PGPORT environment variable or, if not set, to the port - specified at compile time, usually 5432. + Specifies the TCP/IP port or, by omission, the local Unix domain + socket file extension on which the + postmaster is listening for + connections. Defaults to the value of the PGPORT + environment variable or, if not set, to the port specified at + compile time, usually 5432. @@ -1447,10 +1530,11 @@ Access permissions for database "test" -P, --pset assignment - Allows you to specify printing options in the style of \pset - on the command line. Note that here you have to separate name and value with - an equal sign instead of a space. Thus to set the output format to LaTeX, you - could write -P format=latex. + Allows you to specify printing options in the style of + \pset on the command line. Note that here you + have to separate name and value with an equal sign instead of a + space. Thus to set the output format to LaTeX, you could write + -P format=latex. @@ -1460,11 +1544,12 @@ Access permissions for database "test" -q - Specifies that psql should do its work quietly. - By default, it prints welcome messages and various informational output. - If this option is used, none of this happens. This is useful with the - option. Within psql you can - also set the QUIET variable to achieve the same effect. + Specifies that psql should do its work + quietly. By default, it prints welcome messages and various + informational output. If this option is used, none of this + happens. This is useful with the option. + Within psql you can also set the + QUIET variable to achieve the same effect. @@ -1474,8 +1559,9 @@ Access permissions for database "test" -R, --record-separator separator - Use separator as the record separator. - This is equivalent to the \pset recordsep command. + Use separator as the + record separator. This is equivalent to the \pset + recordsep command. @@ -1485,9 +1571,9 @@ Access permissions for database "test" -s, --single-step - Run in single-step mode. That means the user is prompted before each query - is sent to the backend, with the option to cancel execution as well. - Use this to debug scripts. + Run in single-step mode. That means the user is prompted before + each query is sent to the backend, with the option to cancel + execution as well. Use this to debug scripts. @@ -1497,15 +1583,16 @@ Access permissions for database "test" -S, --single-line - Runs in single-line mode where a newline terminates a query, as a semicolon does. + Runs in single-line mode where a newline terminates a query, as a + semicolon does. - This mode is provided for those who insist on it, but you are not necessarily - encouraged to use it. In particular, if you mix SQL and - meta-commands on a line the order of execution might not always be clear to - the inexperienced user. + This mode is provided for those who insist on it, but you are not + necessarily encouraged to use it. In particular, if you mix + SQL and meta-commands on a line the order of + execution might not always be clear to the inexperienced user. @@ -1516,8 +1603,9 @@ Access permissions for database "test" -t, --tuples-only - Turn off printing of column names and result row count footers, etc. - It is completely equivalent to the \t meta-command. + Turn off printing of column names and result row count footers, + etc. It is completely equivalent to the \t + meta-command. @@ -1527,8 +1615,9 @@ Access permissions for database "test" -T, --table-attr table_options - Allows you to specify options to be placed within the HTML - table tag. See \pset for details. + Allows you to specify options to be placed within the + HTML table tag. See + \pset for details. @@ -1538,15 +1627,16 @@ Access permissions for database "test" -u - Makes psql prompt for the user name and password - before connecting to the database. + Makes psql prompt for the user name and + password before connecting to the database. - This option is deprecated, as it is conceptually flawed. (Prompting for - a non-default user name and prompting for a password because the - backend requires it are really two different things.) You are encouraged - to look at the and options instead. + This option is deprecated, as it is conceptually flawed. + (Prompting for a non-default user name and prompting for a + password because the backend requires it are really two different + things.) You are encouraged to look at the and + options instead. @@ -1556,8 +1646,9 @@ Access permissions for database "test" -U, --username username - Connects to the database as the user username - instead of the default. (You must have permission to do so, of course.) + Connects to the database as the user username instead of the default. + (You must have permission to do so, of course.) @@ -1568,13 +1659,12 @@ Access permissions for database "test" Performs a variable assignment, like the \set - internal command. Note that you must separate name and value, - if any, by an equal sign on the command line. To unset a - variable, leave off the equal sign. To just set a variable - without a value, use the equal sign but leave off the value. - These assignments are done during a very early stage of - start-up, so variables reserved for internal purposes might get - overwritten later. + internal command. Note that you must separate name and value, if + any, by an equal sign on the command line. To unset a variable, + leave off the equal sign. To just set a variable without a value, + use the equal sign but leave off the value. These assignments are + done during a very early stage of start-up, so variables reserved + for internal purposes might get overwritten later. @@ -1594,19 +1684,20 @@ Access permissions for database "test" -W, --password - Requests that psql should prompt for a password - before connecting to a database. This will remain set for the entire - session, even if you change the database connection with the meta-command - \connect. + Requests that psql should prompt for a + password before connecting to a database. This will remain set for + the entire session, even if you change the database connection + with the meta-command \connect. - In the current version, psql automatically issues a - password prompt whenever the backend requests password authentication. - Because this is currently based on a hack, the automatic - recognition might mysteriously fail, hence this option to force a prompt. - If no password prompt is issued and the backend requires password authentication - the connection attempt will fail. + In the current version, psql + automatically issues a password prompt whenever the backend + requests password authentication. Because this is currently based + on a hack, the automatic recognition might mysteriously fail, + hence this option to force a prompt. If no password prompt is + issued and the backend requires password authentication the + connection attempt will fail. @@ -1616,8 +1707,8 @@ Access permissions for database "test" -x, --expanded - Turns on extended row format mode. This is equivalent to the command - \x. + Turns on extended row format mode. This is equivalent to the + command \x. @@ -1637,7 +1728,8 @@ Access permissions for database "test" -?, --help - Shows help about psql command line arguments. + Shows help about psql command line + arguments. @@ -1659,18 +1751,20 @@ Access permissions for database "test" Variables - psql provides variable substitution features - similar to common Unix command shells. This feature is new and not very - sophisticated, yet, but there are plans to expand it in the future. - Variables are simply name/value - pairs, where the value can be any string of any length. To set variables, - use the psql meta-command \set: + psql provides variable substitution + features similar to common Unix command shells. This feature is new + and not very sophisticated, yet, but there are plans to expand it in + the future. Variables are simply name/value pairs, where the value + can be any string of any length. To set variables, use the + psql meta-command + \set: testdb=> \set foo bar - sets the variable foo to the value bar. To retrieve - the content of the variable, precede the name with a colon and use it - as the argument of any slash command: + sets the variable foo to the value + bar. To retrieve the content of the variable, precede + the name with a colon and use it as the argument of any slash + command: testdb=> \echo :foo bar @@ -1679,44 +1773,46 @@ bar - The arguments of \set are subject to the same substitution - rules as with other commands. Thus you can construct interesting references - such as \set :foo 'something' and get soft - links or variable variables of Perl - or PHP fame, respectively. - Unfortunately (or fortunately?), there is no way to do anything useful - with these constructs. On the - other hand, \set bar :foo is a perfectly valid way to copy - a variable. + The arguments of \set are subject to the same + substitution rules as with other commands. Thus you can construct + interesting references such as \set :foo + 'something' and get soft links or + variable variables of Perl + or PHP fame, + respectively. Unfortunately (or fortunately?), there is no way to do + anything useful with these constructs. On the other hand, + \set bar :foo is a perfectly valid way to copy a + variable. - If you call \set without a second argument, the variable is simply - set, but has no value. To unset (or delete) a variable, use the command - \unset. + If you call \set without a second argument, the + variable is simply set, but has no value. To unset (or delete) a + variable, use the command \unset. - psql's internal variable names can consist of - letters, numbers, and underscores in any order and any number of them. - A number of regular variables are treated specially by psql. - They indicate certain option settings that can be changed at run time - by altering the value of the variable or represent some state of the application. - Although you can use these - variables for any other purpose, this is not recommended, as the - program behavior might grow really strange really quickly. - By convention, all specially treated variables consist of all upper-case letters - (and possibly numbers and underscores). To ensure maximum compatibility in the - future, avoid such variables. - A list of all specially treated variables follows. + psql's internal variable names can + consist of letters, numbers, and underscores in any order and any + number of them. A number of regular variables are treated specially + by psql. They indicate certain option + settings that can be changed at run time by altering the value of + the variable or represent some state of the application. Although + you can use these variables for any other purpose, this is not + recommended, as the program behavior might grow really strange + really quickly. By convention, all specially treated variables + consist of all upper-case letters (and possibly numbers and + underscores). To ensure maximum compatibility in the future, avoid + such variables. A list of all specially treated variables follows. DBNAME - The name of the database you are currently connected to. This is set every time - you connect to a database (including program start-up), but can be unset. + The name of the database you are currently connected to. This is + set every time you connect to a database (including program + start-up), but can be unset. @@ -1725,12 +1821,14 @@ bar ECHO - If set to all, all lines entered or from a script - are written to the standard output before they - are parsed or executed. To specify this on program start-up, use the switch - . If set to queries, - psql merely prints all queries as they are sent to the - backend. The option for this is . + If set to all, all lines + entered or from a script are written to the standard output + before they are parsed or executed. To specify this on program + start-up, use the switch . If set to + queries, + psql merely prints all queries as + they are sent to the backend. The option for this is + . @@ -1739,11 +1837,13 @@ bar ECHO_HIDDEN - When this variable is set and a backslash command queries the database, the query - is first shown. This way you can study the PostgreSQL - internals and provide similar functionality in your own programs. If you set the - variable to the value noexec, the queries are just shown but are - not actually sent to the backend and executed. + When this variable is set and a backslash command queries the + database, the query is first shown. This way you can study the + PostgreSQL internals and provide + similar functionality in your own programs. If you set the + variable to the value noexec, the queries are + just shown but are not actually sent to the backend and + executed. @@ -1752,8 +1852,8 @@ bar ENCODING - The current client multibyte encoding. If you are not set up to use - multibyte characters, this variable will always contain + The current client multibyte encoding. If you are not set up to + use multibyte characters, this variable will always contain SQL_ASCII. @@ -1763,16 +1863,18 @@ bar HISTCONTROL - If this variable is set to ignorespace, lines which begin with a - space are not entered into the history list. If set to a value of - ignoredups, lines matching the previous history line are not - entered. A value of ignoreboth combines the two - options. If unset, or if set to any other value than those above, all lines read - in interactive mode are saved on the history list. + If this variable is set to ignorespace, + lines which begin with a space are not entered into the history + list. If set to a value of ignoredups, lines + matching the previous history line are not entered. A value of + ignoreboth combines the two options. If + unset, or if set to any other value than those above, all lines + read in interactive mode are saved on the history list. - This feature was shamelessly plagiarized from bash. + This feature was shamelessly plagiarized from + bash. @@ -1782,12 +1884,13 @@ bar HISTSIZE - The number of commands to store in the command history. - The default value is 500. + The number of commands to store in the command history. The + default value is 500. - This feature was shamelessly plagiarized from bash. + This feature was shamelessly plagiarized from + bash. @@ -1797,8 +1900,9 @@ bar HOST - The database server host you are currently connected to. This is set every time - you connect to a database (including program start-up), but can be unset. + The database server host you are currently connected to. This is + set every time you connect to a database (including program + start-up), but can be unset. @@ -1807,14 +1911,17 @@ bar IGNOREEOF - If unset, sending an EOF character (usually Control-D) to an interactive session of - psql will terminate the application. - If set to a numeric value, that many EOF characters are ignored before the application - terminates. If the variable is set but has no numeric value, the default is 10. + If unset, sending an EOF character (usually Control-D) to an + interactive session of psql will + terminate the application. If set to a numeric value, that many + EOF characters are ignored before the application terminates. + If the variable is set but has no numeric value, the default is + 10. - This feature was shamelessly plagiarized from bash. + This feature was shamelessly plagiarized from + bash. @@ -1824,10 +1931,11 @@ bar LASTOID - The value of the last affected oid, as returned from an INSERT - or lo_insert command. This variable is only guaranteed to be - valid until after the result of the next SQL command has been - displayed. + The value of the last affected oid, as returned from an + INSERT or lo_insert + command. This variable is only guaranteed to be valid until + after the result of the next SQL command has + been displayed. @@ -1836,29 +1944,32 @@ bar LO_TRANSACTION - If you use the PostgreSQL large object - interface to specially store data that does not fit into one tuple, - all the operations must be contained in a transaction block. (See the - documentation of the large object interface for more information.) Since - psql has no way to tell if you already - have a transaction in progress when you call one of its internal - commands (\lo_export, \lo_import, - \lo_unlink) it must take some arbitrary action. This - action could either be to roll back any transaction that might already - be in progress, or to commit any such transaction, or to do nothing at - all. In the last case you must provide your own - BEGIN TRANSACTION/COMMIT block or - the results will be unpredictable (usually resulting in the desired - action's not being performed in any case). + If you use the PostgreSQL large + object interface to specially store data that does not fit into + one tuple, all the operations must be contained in a transaction + block. (See the documentation of the large object interface for + more information.) Since psql has no + way to tell if you already have a transaction in progress when + you call one of its internal commands + (\lo_export, \lo_import, + \lo_unlink) it must take some arbitrary + action. This action could either be to roll back any transaction + that might already be in progress, or to commit any such + transaction, or to do nothing at all. In the last case you must + provide your own BEGIN + TRANSACTION/COMMIT block or the + results will be unpredictable (usually resulting in the desired + action's not being performed in any case). To choose what you want to do you set this variable to one of - rollback, commit, or nothing. - The default is to roll back the transaction. If you just want to load one - or a few objects this is fine. However, if you intend to transfer many - large objects, it might be advisable to provide one explicit transaction - block around all commands. + rollback, commit, or + nothing. The default is to roll back the + transaction. If you just want to load one or a few objects this + is fine. However, if you intend to transfer many large objects, + it might be advisable to provide one explicit transaction block + around all commands. @@ -1867,16 +1978,18 @@ bar ON_ERROR_STOP - By default, if non-interactive scripts encounter an error, such as a - malformed SQL query or internal meta-command, - processing continues. This has been the traditional behavior of - psql but it is sometimes not desirable. If this variable - is set, script processing will immediately terminate. If the script was - called from another script it will terminate in the same fashion. - If the outermost script was not called from an interactive psql - session but rather using the option, psql - will return error code 3, to distinguish this case from fatal - error conditions (error code 1). + By default, if non-interactive scripts encounter an error, such + as a malformed SQL query or internal + meta-command, processing continues. This has been the + traditional behavior of psql but it + is sometimes not desirable. If this variable is set, script + processing will immediately terminate. If the script was called + from another script it will terminate in the same fashion. If + the outermost script was not called from an interactive + psql session but rather using the + option, psql will + return error code 3, to distinguish this case from fatal error + conditions (error code 1). @@ -1885,8 +1998,9 @@ bar PORT - The database server port to which you are currently connected. This is set every time - you connect to a database (including program start-up), but can be unset. + The database server port to which you are currently connected. + This is set every time you connect to a database (including + program start-up), but can be unset. @@ -1895,10 +2009,10 @@ bar PROMPT1, PROMPT2, PROMPT3 - These specify what the prompt psql issues is - supposed to look like. See - - below. + These specify what the prompt psql + issues is supposed to look like. See below. @@ -1907,8 +2021,9 @@ bar QUIET - This variable is equivalent to the command line option . - It is probably not too useful in interactive mode. + This variable is equivalent to the command line option + . It is probably not too useful in + interactive mode. @@ -1917,8 +2032,8 @@ bar SINGLELINE - This variable is set by the command line option . You - can unset or reset it at run time. + This variable is set by the command line option + . You can unset or reset it at run time. @@ -1927,7 +2042,8 @@ bar SINGLESTEP - This variable is equivalent to the command line option . + This variable is equivalent to the command line option + . @@ -1936,8 +2052,9 @@ bar USER - The database user you are currently connected as. This is set every time - you connect to a database (including program start-up), but can be unset. + The database user you are currently connected as. This is set + every time you connect to a database (including program + start-up), but can be unset. @@ -1953,27 +2070,28 @@ bar <acronym>SQL</acronym> Interpolation - An additional useful feature of psql variables - is that you can substitute (interpolate) them into - regular SQL statements. The syntax for this is again to prepend - the variable name with a colon (:). + An additional useful feature of psql + variables is that you can substitute (interpolate) + them into regular SQL statements. The syntax for + this is again to prepend the variable name with a colon + (:). testdb=> \set foo 'my_table' testdb=> SELECT * FROM :foo; - would then query the table my_table. The value of the - variable is copied literally, so it can even contain unbalanced quotes or - backslash commands. You must make sure that it makes sense where you put it. - Variable interpolation will not be performed into quoted SQL - entities. + would then query the table my_table. The value of + the variable is copied literally, so it can even contain unbalanced + quotes or backslash commands. You must make sure that it makes sense + where you put it. Variable interpolation will not be performed into + quoted SQL entities. - A popular application of this facility is to refer to the last inserted - OID in subsequent statements to build a foreign key - scenario. - Another possible use of this mechanism is to copy the contents of a file - into a field. First load the file into a variable and then proceed as above. + A popular application of this facility is to refer to the last + inserted OID in subsequent statements to build a + foreign key scenario. Another possible use of this mechanism is to + copy the contents of a file into a field. First load the file into a + variable and then proceed as above. testdb=> \set content '\'' `cat my_file.txt` '\'' testdb=> INSERT INTO my_table VALUES (:content); @@ -1985,28 +2103,32 @@ testdb=> INSERT INTO my_table VALUES (:content); testdb=> \set content '\'' `sed -e "s/'/\\\\\\'/g" < my_file.txt` '\'' - Observe the correct number of backslashes (6)! You can resolve it this way: After - psql has parsed this line, it passes - sed -e "s/'/\\\'/g" < my_file.txt to the shell. The shell - will do its own thing inside the double quotes and execute sed - with the arguments -e and s/'/\\'/g. - When sed parses this it will replace the two - backslashes with a single one and then do the substitution. Perhaps at - one point you thought it was great that all Unix commands use the same - escape character. And this is ignoring the fact that you might have to - escape all backslashes as well because SQL text constants - are also subject to certain interpretations. In that case you might - be better off preparing the file externally. + Observe the correct number of backslashes (6)! You can resolve it + this way: After psql has parsed this + line, it passes sed -e "s/'/\\\'/g" < my_file.txt + to the shell. The shell will do its own thing inside the double + quotes and execute sed with the arguments + -e and s/'/\\'/g. When + sed parses this it will replace the two + backslashes with a single one and then do the substitution. Perhaps + at one point you thought it was great that all Unix commands use the + same escape character. And this is ignoring the fact that you might + have to escape all backslashes as well because + SQL text constants are also subject to certain + interpretations. In that case you might be better off preparing the + file externally. - Since colons may legally appear in queries, the following rule applies: If the variable - is not set, the character sequence colon+name is not changed. In any - case you can escape a colon with a backslash to protect it from interpretation. - (The colon syntax for variables is standard SQL for embedded - query languages, such as ecpg. The colon syntax for - array slices and type casts are PostgreSQL extensions, - hence the conflict.) + Since colons may legally appear in queries, the following rule + applies: If the variable is not set, the character sequence + colon+name is not changed. In any case you can escape + a colon with a backslash to protect it from interpretation. (The + colon syntax for variables is standard SQL for + embedded query languages, such as ecpg. + The colon syntax for array slices and type casts are + PostgreSQL extensions, hence the + conflict.) @@ -2016,21 +2138,24 @@ testdb=> \set content '\'' `sed -e "s/'/\\\\\\'/g" < my_file.txt` '\' Prompting - The prompts psql issues can be customized to - your preference. The three variables PROMPT1, PROMPT2, - and PROMPT3 contain strings and special escape sequences - that describe the appearance of the prompt. Prompt 1 is the normal prompt - that is issued when psql requests a new query. - Prompt 2 is issued when more input is expected during query input because - the query was not terminated with a semicolon or a quote was not closed. - Prompt 3 is issued when you run an SQL COPY - command and you are expected to type in the tuples on the terminal. + The prompts psql issues can be customized + to your preference. The three variables PROMPT1, + PROMPT2, and PROMPT3 contain strings + and special escape sequences that describe the appearance of the + prompt. Prompt 1 is the normal prompt that is issued when + psql requests a new query. Prompt 2 is + issued when more input is expected during query input because the + query was not terminated with a semicolon or a quote was not closed. + Prompt 3 is issued when you run an SQL + COPY command and you are expected to type in the + tuples on the terminal. - The value of the respective prompt variable is printed literally, except where - a percent sign (%) is encountered. Depending on the next - character, certain other text is substituted instead. Defined substitutions are: + The value of the respective prompt variable is printed literally, + except where a percent sign (%) is encountered. + Depending on the next character, certain other text is substituted + instead. Defined substitutions are: @@ -2038,10 +2163,10 @@ testdb=> \set content '\'' `sed -e "s/'/\\\\\\'/g" < my_file.txt` '\' The full host name (with domain name) of the database server, - or [local] if the connection is over a - Unix domain socket, or - [local:/dir/name], - if the Unix domain socket is not at the compiled in default + or [local] if the connection is over a Unix + domain socket, or + [local:/dir/name], if the Unix domain socket is not at the compiled in default location. @@ -2052,8 +2177,8 @@ testdb=> \set content '\'' `sed -e "s/'/\\\\\\'/g" < my_file.txt` '\' The host name of the database server, truncated after the - first dot, or [local] if the connection - is over a Unix domain socket. + first dot, or [local] if the connection is + over a Unix domain socket. @@ -2088,58 +2213,72 @@ testdb=> \set content '\'' `sed -e "s/'/\\\\\\'/g" < my_file.txt` '\' %R - - In prompt 1 normally =, but ^ if in single-line - mode, and ! if the session is disconnected from the database - (which can happen if \connect fails). In prompt 2 the - sequence is replaced by -, *, a single quote, - or a double quote, depending on whether psql - expects more input because the query wasn't terminated yet, because you are - inside a /* ... */ comment, or because you are inside - a quote. In prompt 3 the sequence doesn't resolve to anything. + + + In prompt 1 normally =, but ^ if + in single-line mode, and ! if the session is + disconnected from the database (which can happen if + \connect fails). In prompt 2 the sequence is + replaced by -, *, a single quote, + or a double quote, depending on whether + psql expects more input because the + query wasn't terminated yet, because you are inside a + /* ... */ comment, or because you are inside + a quote. In prompt 3 the sequence doesn't resolve to anything. + %digits - - If digits starts with - 0x the rest of the characters are interpreted as a - hexadecimal digit and the character with the corresponding code is - substituted. If the first digit is 0 the characters are - interpreted as on octal number and the corresponding character is - substituted. Otherwise a decimal number is assumed. + + + If digits starts + with 0x the rest of the characters are + interpreted as a hexadecimal digit and the character with the + corresponding code is substituted. If the first digit is + 0 the characters are interpreted as on octal + number and the corresponding character is substituted. Otherwise + a decimal number is assumed. + %:name: - - The value of the psql, variable name. See the section - - for details. + + + The value of the psql, variable + name. See the + section for details. + %`command` - - The output of command, similar to - ordinary back-tick substitution. + + + The output of command, similar to ordinary + back-tick substitution. + - To insert a percent sign into your prompt, write %%. The - default prompts are equivalent to '%/%R%# ' for prompts 1 - and 2, and '>> ' for prompt 3. + To insert a percent sign into your prompt, write + %%. The default prompts are equivalent to + '%/%R%# ' for prompts 1 and 2, and + '>> ' for prompt 3. - This feature was shamelessly plagiarized from tcsh. + This feature was shamelessly plagiarized from + tcsh. @@ -2149,18 +2288,19 @@ testdb=> \set content '\'' `sed -e "s/'/\\\\\\'/g" < my_file.txt` '\' Miscellaneous - psql returns 0 to the shell if it finished normally, - 1 if a fatal error of its own (out of memory, file not found) occurs, 2 if the - connection to the backend went bad and the session is not interactive, and 3 if - an error occurred in a script and the variable ON_ERROR_STOP was - set. + psql returns 0 to the shell if it + finished normally, 1 if a fatal error of its own (out of memory, + file not found) occurs, 2 if the connection to the backend went bad + and the session is not interactive, and 3 if an error occurred in a + script and the variable ON_ERROR_STOP was set. - Before starting up, psql attempts - to read and execute commands from the file $HOME/.psqlrc. It - could be used to set up the client or the server to taste (using the \set - and SET commands). + Before starting up, psql attempts to read + and execute commands from the file + $HOME/.psqlrc. It could be used to set up the + client or the server to taste (using the \set + and SET commands). @@ -2169,46 +2309,52 @@ testdb=> \set content '\'' `sed -e "s/'/\\\\\\'/g" < my_file.txt` '\' <acronym>GNU</acronym> readline - psql supports the readline and history libraries for - convenient line editing and retrieval. The command history is stored in a file - named .psql_history in your home directory and is reloaded when - psql starts up. - Tab-completion is also supported, although - the completion logic makes no claim to be an SQL parser. - When available, psql is automatically built to use these - features. If for some reason you do not like the tab completion, you can turn if off - by putting this in a file named .inputrc in your - home directory: + psql supports the readline and history + libraries for convenient line editing and retrieval. The command + history is stored in a file named .psql_history + in your home directory and is reloaded when + psql starts up. Tab-completion is also + supported, although the completion logic makes no claim to be an + SQL parser. When available, + psql is automatically built to use these + features. If for some reason you do not like the tab completion, you + can turn if off by putting this in a file named + .inputrc in your home directory: $if psql set disable-completion on $endif - (This is not a psql but a readline - feature. Read its documentation for further details.) + (This is not a psql but a + readline feature. Read its documentation + for further details.) - If you have the readline library installed but psql - does not seem to use it, you must make sure that PostgreSQL's - top-level configure script finds it. configure - needs to find both the library libreadline.a - (or a shared library equivalent) - and the header files readline.h and - history.h (or readline/readline.h and - readline/history.h) in appropriate directories. If - you have the library and header files installed in an obscure place you - must tell configure about them, for example: + If you have the readline library installed but + psql does not seem to use it, you must + make sure that PostgreSQL's top-level + configure script finds it. + configure needs to find both the library + libreadline.a (or a shared library equivalent) + and the header files + readline.h and history.h + (or readline/readline.h and + readline/history.h) in appropriate directories. + If you have the library and header files installed in an obscure + place you must tell configure about them, for + example: $ ./configure --with-includes=/opt/gnu/include --with-libs=/opt/gnu/lib ... - Then you have to recompile psql (not necessarily - the entire code tree). + Then you have to recompile psql (not + necessarily the entire code tree). - The GNU readline library can be obtained from the GNU - project's FTP server at ftp://ftp.gnu.org. + The GNU readline library can be obtained from the + GNU project's FTP server at + ftp://ftp.gnu.org. @@ -2222,16 +2368,17 @@ $ ./configure --with-includes=/opt/gnu/include --with-libs=/opt/gnu/lib ... - This section only shows a few examples specific to psql. - If you want to learn SQL or get familiar with - PostgreSQL, you might wish to read the Tutorial that - is included in the distribution. + This section only shows a few examples specific to + psql. If you want to learn + SQL or get familiar with + PostgreSQL, you might wish to read the + Tutorial that is included in the distribution. - The first example shows how to spread a query over several lines of input. - Notice the changing prompt: + The first example shows how to spread a query over several lines of + input. Notice the changing prompt: testdb=> CREATE TABLE my_table ( testdb(> first integer not null default 0, @@ -2255,7 +2402,8 @@ testdb=> \d my_table testdb=> \set PROMPT1 '%n@%m %~%R%# ' peter@localhost testdb=> - Let's assume you have filled the table with data and want to take a look at it: + Let's assume you have filled the table with data and want to take a + look at it: peter@localhost testdb=> SELECT * FROM my_table; first | second @@ -2267,8 +2415,8 @@ peter@localhost testdb=> SELECT * FROM my_table; (4 rows) - You can make this table look differently by using the \pset - command: + You can make this table look differently by using the + \pset command: peter@localhost testdb=> \pset border 2 Border style is 2. @@ -2346,11 +2494,12 @@ second | four - In some earlier life psql allowed the first - argument to start directly after the (single-letter) command. For - compatibility this is still supported to some extent but I am not - going to explain the details here as this use is discouraged. But - if you get strange messages, keep this in mind. For example + In some earlier life psql allowed the + first argument to start directly after the (single-letter) + command. For compatibility this is still supported to some extent + but I am not going to explain the details here as this use is + discouraged. But if you get strange messages, keep this in mind. + For example testdb=> \foo Field separator is "oo", @@ -2361,18 +2510,20 @@ Field separator is "oo", - psql only works smoothly with servers of the - same version. That does not mean other combinations will fail outright, - but subtle and not-so-subtle problems might come up. + psql only works smoothly with servers + of the same version. That does not mean other combinations will + fail outright, but subtle and not-so-subtle problems might come + up. - Pressing Control-C during a copy in (data sent to the - server) doesn't show the most ideal of behaviors. If you get a message - such as COPY state must be terminated first, - simply reset the connection by entering \c - -. + Pressing Control-C during a copy in (data sent to + the server) doesn't show the most ideal of behaviors. If you get a + message such as COPY state must be terminated + first, simply reset the connection by entering \c + - -. diff --git a/src/backend/commands/copy.c b/src/backend/commands/copy.c index 16c23d320e..cfc8a5c070 100644 --- a/src/backend/commands/copy.c +++ b/src/backend/commands/copy.c @@ -7,7 +7,7 @@ * * * IDENTIFICATION - * $Header: /cvsroot/pgsql/src/backend/commands/copy.c,v 1.156 2002/05/21 22:59:00 tgl Exp $ + * $Header: /cvsroot/pgsql/src/backend/commands/copy.c,v 1.157 2002/06/20 16:00:43 momjian Exp $ * *------------------------------------------------------------------------- */ @@ -261,18 +261,79 @@ CopyDonePeek(FILE *fp, int c, bool pickup) * the table. */ void -DoCopy(const RangeVar *relation, bool binary, bool oids, bool from, bool pipe, - char *filename, char *delim, char *null_print) +DoCopy(const CopyStmt *stmt) { + RangeVar *relation = stmt->relation; + char *filename = stmt->filename; + bool is_from = stmt->is_from; + bool pipe = (stmt->filename == NULL); + List *option; + DefElem *dbinary = NULL; + DefElem *doids = NULL; + DefElem *ddelim = NULL; + DefElem *dnull = NULL; + bool binary = false; + bool oids = false; + char *delim = "\t"; + char *null_print = "\\N"; FILE *fp; Relation rel; - AclMode required_access = (from ? ACL_INSERT : ACL_SELECT); + AclMode required_access = (is_from ? ACL_INSERT : ACL_SELECT); AclResult aclresult; + /* Extract options from the statement node tree */ + foreach(option, stmt->options) + { + DefElem *defel = (DefElem *) lfirst(option); + + if (strcmp(defel->defname, "binary") == 0) + { + if (dbinary) + elog(ERROR, "COPY: conflicting options"); + dbinary = defel; + } + else if (strcmp(defel->defname, "oids") == 0) + { + if (doids) + elog(ERROR, "COPY: conflicting options"); + doids = defel; + } + else if (strcmp(defel->defname, "delimiter") == 0) + { + if (ddelim) + elog(ERROR, "COPY: conflicting options"); + ddelim = defel; + } + else if (strcmp(defel->defname, "null") == 0) + { + if (dnull) + elog(ERROR, "COPY: conflicting options"); + dnull = defel; + } + else + elog(ERROR, "COPY: option \"%s\" not recognized", + defel->defname); + } + + if (dbinary) + binary = intVal(dbinary->arg); + if (doids) + oids = intVal(doids->arg); + if (ddelim) + delim = strVal(ddelim->arg); + if (dnull) + null_print = strVal(dnull->arg); + + if (binary && ddelim) + elog(ERROR, "You can not specify the DELIMITER in BINARY mode."); + + if (binary && dnull) + elog(ERROR, "You can not specify NULL in BINARY mode."); + /* * Open and lock the relation, using the appropriate lock type. */ - rel = heap_openrv(relation, (from ? RowExclusiveLock : AccessShareLock)); + rel = heap_openrv(relation, (is_from ? RowExclusiveLock : AccessShareLock)); /* Check permissions. */ aclresult = pg_class_aclcheck(RelationGetRelid(rel), GetUserId(), @@ -306,7 +367,7 @@ DoCopy(const RangeVar *relation, bool binary, bool oids, bool from, bool pipe, server_encoding = GetDatabaseEncoding(); #endif - if (from) + if (is_from) { /* copy from file to database */ if (rel->rd_rel->relkind != RELKIND_RELATION) { @@ -410,7 +471,7 @@ DoCopy(const RangeVar *relation, bool binary, bool oids, bool from, bool pipe, if (!pipe) FreeFile(fp); - else if (!from) + else if (!is_from) { if (!binary) CopySendData("\\.\n", 3, fp); @@ -425,7 +486,7 @@ DoCopy(const RangeVar *relation, bool binary, bool oids, bool from, bool pipe, * transaction to ensure that updates will be committed before lock is * released. */ - heap_close(rel, (from ? NoLock : AccessShareLock)); + heap_close(rel, (is_from ? NoLock : AccessShareLock)); } diff --git a/src/backend/commands/dbcommands.c b/src/backend/commands/dbcommands.c index ae4e52f42a..4e9fe90e36 100644 --- a/src/backend/commands/dbcommands.c +++ b/src/backend/commands/dbcommands.c @@ -9,7 +9,7 @@ * * * IDENTIFICATION - * $Header: /cvsroot/pgsql/src/backend/commands/dbcommands.c,v 1.93 2002/06/18 17:27:57 momjian Exp $ + * $Header: /cvsroot/pgsql/src/backend/commands/dbcommands.c,v 1.94 2002/06/20 16:00:43 momjian Exp $ * *------------------------------------------------------------------------- */ @@ -58,7 +58,7 @@ static bool remove_dbdirs(const char *real_loc, const char *altloc); */ void -createdb(CreatedbStmt *stmt) +createdb(const CreatedbStmt *stmt) { char *nominal_loc; char *alt_loc; diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c index d2a3f40a34..37da8612a4 100644 --- a/src/backend/nodes/copyfuncs.c +++ b/src/backend/nodes/copyfuncs.c @@ -15,7 +15,7 @@ * Portions Copyright (c) 1994, Regents of the University of California * * IDENTIFICATION - * $Header: /cvsroot/pgsql/src/backend/nodes/copyfuncs.c,v 1.189 2002/06/18 17:27:57 momjian Exp $ + * $Header: /cvsroot/pgsql/src/backend/nodes/copyfuncs.c,v 1.190 2002/06/20 16:00:43 momjian Exp $ * *------------------------------------------------------------------------- */ @@ -1992,16 +1992,11 @@ _copyCopyStmt(CopyStmt *from) { CopyStmt *newnode = makeNode(CopyStmt); - newnode->binary = from->binary; Node_Copy(from, newnode, relation); - newnode->oids = from->oids; - newnode->direction = from->direction; + newnode->is_from = from->is_from; if (from->filename) newnode->filename = pstrdup(from->filename); - if (from->delimiter) - newnode->delimiter = pstrdup(from->delimiter); - if (from->null_print) - newnode->null_print = pstrdup(from->null_print); + Node_Copy(from, newnode, options); return newnode; } diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c index 85ff384479..55f0be0558 100644 --- a/src/backend/nodes/equalfuncs.c +++ b/src/backend/nodes/equalfuncs.c @@ -20,7 +20,7 @@ * Portions Copyright (c) 1994, Regents of the University of California * * IDENTIFICATION - * $Header: /cvsroot/pgsql/src/backend/nodes/equalfuncs.c,v 1.136 2002/06/18 17:27:57 momjian Exp $ + * $Header: /cvsroot/pgsql/src/backend/nodes/equalfuncs.c,v 1.137 2002/06/20 16:00:43 momjian Exp $ * *------------------------------------------------------------------------- */ @@ -799,19 +799,13 @@ _equalClusterStmt(ClusterStmt *a, ClusterStmt *b) static bool _equalCopyStmt(CopyStmt *a, CopyStmt *b) { - if (a->binary != b->binary) - return false; if (!equal(a->relation, b->relation)) return false; - if (a->oids != b->oids) - return false; - if (a->direction != b->direction) + if (a->is_from != b->is_from) return false; if (!equalstr(a->filename, b->filename)) return false; - if (!equalstr(a->delimiter, b->delimiter)) - return false; - if (!equalstr(a->null_print, b->null_print)) + if (!equal(a->options, b->options)) return false; return true; diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index 1c499f417d..b7b2f6a693 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -11,7 +11,7 @@ * * * IDENTIFICATION - * $Header: /cvsroot/pgsql/src/backend/parser/gram.y,v 2.331 2002/06/19 15:40:58 momjian Exp $ + * $Header: /cvsroot/pgsql/src/backend/parser/gram.y,v 2.332 2002/06/20 16:00:43 momjian Exp $ * * HISTORY * AUTHOR DATE MAJOR EVENT @@ -159,8 +159,8 @@ static void doNegateFloat(Value *v); %type alter_column_default %type add_drop, drop_behavior, opt_drop_behavior -%type createdb_opt_list -%type createdb_opt_item +%type createdb_opt_list, copy_opt_list +%type createdb_opt_item, copy_opt_item %type opt_lock, lock_type %type opt_force, opt_or_replace @@ -182,7 +182,7 @@ static void doNegateFloat(Value *v); %type TriggerEvents %type TriggerFuncArg -%type relation_name, copy_file_name, copy_delimiter, copy_null, +%type relation_name, copy_file_name, database_name, access_method_clause, access_method, attr_name, index_name, name, function_name, file_name @@ -236,11 +236,14 @@ static void doNegateFloat(Value *v); %type opt_interval %type overlay_placing, substr_from, substr_for -%type opt_binary, opt_instead, opt_cursor -%type opt_with_copy, index_opt_unique, opt_verbose, opt_full +%type opt_instead, opt_cursor +%type index_opt_unique, opt_verbose, opt_full %type opt_freeze +%type opt_binary, opt_oids, copy_delimiter -%type copy_dirn, direction, reindex_type, drop_type, +%type copy_from + +%type direction, reindex_type, drop_type, opt_column, event, comment_type %type fetch_how_many @@ -330,8 +333,8 @@ static void doNegateFloat(Value *v); CURRENT_TIMESTAMP, CURRENT_USER, CURSOR, CYCLE, DATABASE, DAY_P, DEC, DECIMAL, DECLARE, DEFAULT, - DEFERRABLE, DEFERRED, DEFINER, DELETE_P, DELIMITERS, DESC, - DISTINCT, DO, DOMAIN_P, DOUBLE, DROP, + DEFERRABLE, DEFERRED, DEFINER, DELETE_P, DELIMITER, DELIMITERS, + DESC, DISTINCT, DO, DOMAIN_P, DOUBLE, DROP, EACH, ELSE, ENCODING, ENCRYPTED, END_TRANS, ESCAPE, EXCEPT, EXCLUSIVE, EXECUTE, EXISTS, EXPLAIN, EXTERNAL, EXTRACT, @@ -1293,27 +1296,38 @@ opt_id: ColId { $$ = $1; } /***************************************************************************** * * QUERY : - * COPY [BINARY] FROM/TO - * [USING DELIMITERS ] + * COPY FROM/TO [WITH options] + * + * BINARY, OIDS, and DELIMITERS kept in old locations + * for backward compatibility. 2002-06-18 * *****************************************************************************/ -CopyStmt: COPY opt_binary qualified_name opt_with_copy copy_dirn copy_file_name copy_delimiter copy_null +CopyStmt: COPY opt_binary qualified_name opt_oids copy_from + copy_file_name copy_delimiter opt_with copy_opt_list { CopyStmt *n = makeNode(CopyStmt); - n->binary = $2; n->relation = $3; - n->oids = $4; - n->direction = $5; + n->is_from = $5; n->filename = $6; - n->delimiter = $7; - n->null_print = $8; + + n->options = NIL; + /* Concatenate user-supplied flags */ + if ($2) + n->options = lappend(n->options, $2); + if ($4) + n->options = lappend(n->options, $4); + if ($7) + n->options = lappend(n->options, $7); + if ($9) + n->options = nconc(n->options, $9); $$ = (Node *)n; } ; -copy_dirn: TO { $$ = TO; } - | FROM { $$ = FROM; } +copy_from: + FROM { $$ = TRUE; } + | TO { $$ = FALSE; } ; /* @@ -1327,30 +1341,79 @@ copy_file_name: | STDOUT { $$ = NULL; } ; -opt_binary: BINARY { $$ = TRUE; } - | /*EMPTY*/ { $$ = FALSE; } + + +copy_opt_list: + copy_opt_list copy_opt_item { $$ = lappend($1, $2); } + | /* EMPTY */ { $$ = NIL; } ; -opt_with_copy: - WITH OIDS { $$ = TRUE; } - | /*EMPTY*/ { $$ = FALSE; } + +copy_opt_item: + BINARY + { + $$ = makeNode(DefElem); + $$->defname = "binary"; + $$->arg = (Node *)makeInteger(TRUE); + } + | OIDS + { + $$ = makeNode(DefElem); + $$->defname = "oids"; + $$->arg = (Node *)makeInteger(TRUE); + } + | DELIMITER opt_as Sconst + { + $$ = makeNode(DefElem); + $$->defname = "delimiter"; + $$->arg = (Node *)makeString($3); + } + | NULL_P opt_as Sconst + { + $$ = makeNode(DefElem); + $$->defname = "null"; + $$->arg = (Node *)makeString($3); + } + ; + +/* The following exist for backward compatibility */ + +opt_binary: + BINARY + { + $$ = makeNode(DefElem); + $$->defname = "binary"; + $$->arg = (Node *)makeInteger(TRUE); + } + | /*EMPTY*/ { $$ = NULL; } + ; + +opt_oids: + WITH OIDS + { + $$ = makeNode(DefElem); + $$->defname = "oids"; + $$->arg = (Node *)makeInteger(TRUE); + } + | /*EMPTY*/ { $$ = NULL; } ; -/* - * the default copy delimiter is tab but the user can configure it - */ copy_delimiter: - opt_using DELIMITERS Sconst { $$ = $3; } - | /*EMPTY*/ { $$ = "\t"; } + /* USING DELIMITERS kept for backward compatibility. 2002-06-15 */ + opt_using DELIMITERS Sconst + { + $$ = makeNode(DefElem); + $$->defname = "delimiter"; + $$->arg = (Node *)makeString($3); + } + | /*EMPTY*/ { $$ = NULL; } ; -opt_using: USING {} +opt_using: + USING {} | /*EMPTY*/ {} ; -copy_null: WITH NULL_P AS Sconst { $$ = $4; } - | /*EMPTY*/ { $$ = "\\N"; } - ; /***************************************************************************** * @@ -3422,10 +3485,6 @@ createdb_opt_list: | /* EMPTY */ { $$ = NIL; } ; -/* - * createdb_opt_item returns 2-element lists, with the first element - * being an integer code to indicate which item was specified. - */ createdb_opt_item: LOCATION opt_equal Sconst { @@ -6529,6 +6588,7 @@ unreserved_keyword: | DEFERRED | DEFINER | DELETE_P + | DELIMITER | DELIMITERS | DOMAIN_P | DOUBLE diff --git a/src/backend/parser/keywords.c b/src/backend/parser/keywords.c index 0f441290ea..c125cc79fb 100644 --- a/src/backend/parser/keywords.c +++ b/src/backend/parser/keywords.c @@ -8,7 +8,7 @@ * * * IDENTIFICATION - * $Header: /cvsroot/pgsql/src/backend/parser/keywords.c,v 1.114 2002/06/15 03:00:03 thomas Exp $ + * $Header: /cvsroot/pgsql/src/backend/parser/keywords.c,v 1.115 2002/06/20 16:00:43 momjian Exp $ * *------------------------------------------------------------------------- */ @@ -98,6 +98,7 @@ static const ScanKeyword ScanKeywords[] = { {"deferred", DEFERRED}, {"definer", DEFINER}, {"delete", DELETE_P}, + {"delimiter", DELIMITER}, {"delimiters", DELIMITERS}, {"desc", DESC}, {"distinct", DISTINCT}, diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.c index 4e23a6d919..c0f480085c 100644 --- a/src/backend/tcop/utility.c +++ b/src/backend/tcop/utility.c @@ -10,7 +10,7 @@ * * * IDENTIFICATION - * $Header: /cvsroot/pgsql/src/backend/tcop/utility.c,v 1.157 2002/06/18 17:27:58 momjian Exp $ + * $Header: /cvsroot/pgsql/src/backend/tcop/utility.c,v 1.158 2002/06/20 16:00:43 momjian Exp $ * *------------------------------------------------------------------------- */ @@ -347,22 +347,10 @@ ProcessUtility(Node *parsetree, { CopyStmt *stmt = (CopyStmt *) parsetree; - if (stmt->direction != FROM) + if (!stmt->is_from) SetQuerySnapshot(); - DoCopy(stmt->relation, - stmt->binary, - stmt->oids, - (bool) (stmt->direction == FROM), - (bool) (stmt->filename == NULL), - - /* - * null filename means copy to/from stdout/stdin, rather - * than to/from a file. - */ - stmt->filename, - stmt->delimiter, - stmt->null_print); + DoCopy(stmt); } break; diff --git a/src/bin/pgaccess/doc/html/tutorial/tut_edit.html b/src/bin/pgaccess/doc/html/tutorial/tut_edit.html index 8282fb9a42..a4e75b1779 100644 --- a/src/bin/pgaccess/doc/html/tutorial/tut_edit.html +++ b/src/bin/pgaccess/doc/html/tutorial/tut_edit.html @@ -29,7 +29,7 @@ a text file named newref.txt that starts like this:

Notice that there are two consecutive tildes to allow for the fact that this particular entry doesn't have anything in the Editor field. You can then perform a Query as follows:

-COPY psyref FROM '/home/jim/newref.txt' USING DELIMITERS +COPY psyref FROM '/home/jim/newref.txt' WITH DELIMITER '~';

This will read the records from newref.txt and insert them into the table psyref. See the PostgreSQL documentation under the headings diff --git a/src/bin/pgaccess/lib/help/copy.hlp b/src/bin/pgaccess/lib/help/copy.hlp index eaf77a3cca..88dd77e255 100644 --- a/src/bin/pgaccess/lib/help/copy.hlp +++ b/src/bin/pgaccess/lib/help/copy.hlp @@ -5,10 +5,10 @@ and the name must be specified from the viewpoint of the backend. If stdin or st " {} " COPY \[ BINARY \] table \[ WITH OIDS \] FROM { 'filename' | stdin } - \[ USING DELIMITERS 'delimiter' \] + \[ WITH DELIMITER 'delimiter' \] COPY \[ BINARY \] table \[ WITH OIDS \] TO { 'filename' | stdout } - \[ USING DELIMITERS 'delimiter' \] + \[ WITH DELIMITER 'delimiter' \] " {code} "Inputs" {bold} " @@ -44,7 +44,7 @@ COPY \[ BINARY \] table \[ WITH OIDS \] " {} "Usage" {bold} " The following example copies a table to standard output, using a vertical bar \(\"|\"\) as the field delimiter: -COPY country TO stdout USING DELIMITERS '|'; +COPY country TO stdout WITH DELIMITER '|'; To copy data from a Unix file into a table \"country\": COPY country FROM '/usr1/proj/bray/sql/country_data'; @@ -93,13 +93,12 @@ The format for each instance in the file is as follows. Note that this format mu The " {} "BINARY" {bold} " keyword will force all data to be stored/read as binary objects rather than as text. It is somewhat faster than the normal copy command, but is not generally portable, and the files \ generated are somewhat larger, although this factor is highly dependent on the data itself. By default, a text copy uses a tab \ -\(\"\\t\"\) character as a delimiter. The delimiter may also be changed to any other single character with the keyword phrase USING DELIMITERS. Characters in data fields which happen to match the delimiter character will be quoted. +\(\"\\t\"\) character as a delimiter. The delimiter may also be changed to any other single character with the keyword phrase WITH DELIMITER. Characters in data fields which happen to match the delimiter character will be quoted. You must have select access on any table whose values are read by " {} "COPY" {bold} ", and either insert or update access to a table into which values are being inserted by \ " {} "COPY" {bold} ". The backend also needs appropriate Unix permissions for any file read or written by \ " {} "COPY" {bold} ". -The keyword phrase " {} "USING DELIMITERS" {bold} " specifies a single character to be used for all delimiters between columns. If multiple characters are specified in the delimiter string, only the first \ -character is used. +The keyword phrase " {} "WITH DELIMITER" {bold} " specifies a single character to be used for all delimiters between columns. Tip: Do not confuse " {} "COPY" {bold} " with the psql instruction \\copy. " diff --git a/src/bin/pgaccess/lib/mainlib.tcl b/src/bin/pgaccess/lib/mainlib.tcl index 781989cc7b..5021d021db 100644 --- a/src/bin/pgaccess/lib/mainlib.tcl +++ b/src/bin/pgaccess/lib/mainlib.tcl @@ -636,6 +636,8 @@ proc vTclWindow.pgaw:ImportExport {base} { if {$PgAcVar(impexp,delimiter)==""} { set sup "" } else { + # now we use WITH DELIMITER, but keep old syntax for + # backward compatibility. 2002-06-15 set sup " USING DELIMITERS '$PgAcVar(impexp,delimiter)'" } if {[.pgaw:ImportExport.expbtn cget -text]=="Import"} { diff --git a/src/bin/psql/copy.c b/src/bin/psql/copy.c index fb07b87279..32f875db84 100644 --- a/src/bin/psql/copy.c +++ b/src/bin/psql/copy.c @@ -3,7 +3,7 @@ * * Copyright 2000 by PostgreSQL Global Development Group * - * $Header: /cvsroot/pgsql/src/bin/psql/copy.c,v 1.22 2002/04/24 21:00:10 petere Exp $ + * $Header: /cvsroot/pgsql/src/bin/psql/copy.c,v 1.23 2002/06/20 16:00:44 momjian Exp $ */ #include "postgres_fe.h" #include "copy.h" @@ -36,9 +36,12 @@ bool copy_in_state; * parse_slash_copy * -- parses \copy command line * - * Accepted syntax: \copy [binary] table|"table" [with oids] from|to filename|'filename' [ using delimiters ''] [ with null as 'string' ] + * Accepted syntax: \copy table|"table" [with oids] from|to filename|'filename' [with ] [ oids ] [ delimiter ''] [ null as 'string' ] * (binary is not here yet) * + * Old syntax for backward compatibility: (2002-06-19): + * \copy table|"table" [with oids] from|to filename|'filename' [ using delimiters ''] [ with null as 'string' ] + * * returns a malloc'ed structure with the options, or NULL on parsing error */ @@ -120,6 +123,7 @@ parse_slash_copy(const char *args) error = true; else { + /* Allows old COPY syntax for backward compatibility 2002-06-19 */ if (strcasecmp(token, "with") == 0) { token = strtokx(NULL, " \t\n\r", NULL, '\\', NULL, NULL, pset.encoding); @@ -161,12 +165,11 @@ parse_slash_copy(const char *args) token = strtokx(NULL, " \t\n\r", NULL, '\\', NULL, NULL, pset.encoding); if (token) { + /* Allows old COPY syntax for backward compatibility 2002-06-19 */ if (strcasecmp(token, "using") == 0) { token = strtokx(NULL, " \t\n\r", NULL, '\\', NULL, NULL, pset.encoding); - if (!token || strcasecmp(token, "delimiters") != 0) - error = true; - else + if (token && strcasecmp(token, "delimiters") == 0) { token = strtokx(NULL, " \t\n\r", "'", '\\', NULL, NULL, pset.encoding); if (token) @@ -177,32 +180,42 @@ parse_slash_copy(const char *args) else error = true; } + else + error = true; } + } + } - if (!error && token) + if (!error && token) + { + if (strcasecmp(token, "with") == 0) + { + while (!error && (token = strtokx(NULL, " \t\n\r", NULL, '\\', NULL, NULL, pset.encoding))) { - if (strcasecmp(token, "with") == 0) + if (strcasecmp(token, "delimiter") == 0) { - token = strtokx(NULL, " \t\n\r", NULL, '\\', NULL, NULL, pset.encoding); - if (!token || strcasecmp(token, "null") != 0) + token = strtokx(NULL, " \t\n\r", "'", '\\', NULL, NULL, pset.encoding); + if (token && strcasecmp(token, "as") == 0) + token = strtokx(NULL, " \t\n\r", "'", '\\', NULL, NULL, pset.encoding); + if (token) + result->delim = xstrdup(token); + else error = true; + } + else if (strcasecmp(token, "null") == 0) + { + token = strtokx(NULL, " \t\n\r", "'", '\\', NULL, NULL, pset.encoding); + if (token && strcasecmp(token, "as") == 0) + token = strtokx(NULL, " \t\n\r", "'", '\\', NULL, NULL, pset.encoding); + if (token) + result->null = xstrdup(token); else - { - token = strtokx(NULL, " \t\n\r", NULL, '\\', NULL, NULL, pset.encoding); - if (!token || strcasecmp(token, "as") != 0) - error = true; - else - { - token = strtokx(NULL, " \t\n\r", "'", '\\', NULL, NULL, pset.encoding); - if (token) - result->null = xstrdup(token); - } - } + error = true; } - else - error = true; + else error = true; } } + else error = true; } free(line); @@ -250,6 +263,7 @@ do_copy(const char *args) appendPQExpBuffer(&query, "BINARY "); appendPQExpBuffer(&query, "\"%s\" ", options->table); + /* Uses old COPY syntax for backward compatibility 2002-06-19 */ if (options->oids) appendPQExpBuffer(&query, "WITH OIDS "); @@ -259,6 +273,7 @@ do_copy(const char *args) appendPQExpBuffer(&query, "TO STDOUT"); + /* Uses old COPY syntax for backward compatibility 2002-06-19 */ if (options->delim) appendPQExpBuffer(&query, " USING DELIMITERS '%s'", options->delim); @@ -298,7 +313,7 @@ do_copy(const char *args) free_copy_options(options); return false; } - + result = PSQLexec(query.data); termPQExpBuffer(&query); diff --git a/src/include/commands/copy.h b/src/include/commands/copy.h index e0a4ecdcd4..40cbb35195 100644 --- a/src/include/commands/copy.h +++ b/src/include/commands/copy.h @@ -7,19 +7,18 @@ * Portions Copyright (c) 1996-2001, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $Id: copy.h,v 1.17 2002/03/29 19:06:21 tgl Exp $ + * $Id: copy.h,v 1.18 2002/06/20 16:00:44 momjian Exp $ * *------------------------------------------------------------------------- */ #ifndef COPY_H #define COPY_H +#include "nodes/parsenodes.h" #include "nodes/primnodes.h" extern int copy_lineno; -void DoCopy(const RangeVar *relation, bool binary, bool oids, - bool from, bool pipe, - char *filename, char *delim, char *null_print); +void DoCopy(const CopyStmt *stmt); #endif /* COPY_H */ diff --git a/src/include/commands/dbcommands.h b/src/include/commands/dbcommands.h index 1a99da6d4d..c4c9f56eec 100644 --- a/src/include/commands/dbcommands.h +++ b/src/include/commands/dbcommands.h @@ -7,7 +7,7 @@ * Portions Copyright (c) 1996-2001, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $Id: dbcommands.h,v 1.22 2002/06/18 17:27:58 momjian Exp $ + * $Id: dbcommands.h,v 1.23 2002/06/20 16:00:44 momjian Exp $ * *------------------------------------------------------------------------- */ @@ -16,7 +16,7 @@ #include -extern void createdb(CreatedbStmt *stmt); +extern void createdb(const CreatedbStmt *stmt); extern void dropdb(const char *dbname); extern void AlterDatabaseSet(AlterDatabaseSetStmt *stmt); diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index 01b90ce028..627c30c644 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -7,7 +7,7 @@ * Portions Copyright (c) 1996-2001, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $Id: parsenodes.h,v 1.180 2002/06/18 17:27:58 momjian Exp $ + * $Id: parsenodes.h,v 1.181 2002/06/20 16:00:44 momjian Exp $ * *------------------------------------------------------------------------- */ @@ -851,13 +851,10 @@ typedef struct ClosePortalStmt typedef struct CopyStmt { NodeTag type; - bool binary; /* is a binary copy? */ RangeVar *relation; /* the relation to copy */ - bool oids; /* copy oid's? */ - int direction; /* TO or FROM */ + bool is_from; /* TO or FROM */ char *filename; /* if NULL, use stdin/stdout */ - char *delimiter; /* delimiter character, \t by default */ - char *null_print; /* how to print NULLs, `\N' by default */ + List *options; /* List of DefElem nodes */ } CopyStmt; /* ---------------------- diff --git a/src/interfaces/jdbc/org/postgresql/jdbc1/DatabaseMetaData.java b/src/interfaces/jdbc/org/postgresql/jdbc1/DatabaseMetaData.java index 3c447a88b4..5fba1d68c0 100644 --- a/src/interfaces/jdbc/org/postgresql/jdbc1/DatabaseMetaData.java +++ b/src/interfaces/jdbc/org/postgresql/jdbc1/DatabaseMetaData.java @@ -13,7 +13,7 @@ import org.postgresql.util.PSQLException; /* * This class provides information about the database as a whole. * - * $Id: DatabaseMetaData.java,v 1.46 2002/06/11 02:55:16 barry Exp $ + * $Id: DatabaseMetaData.java,v 1.47 2002/06/20 16:00:44 momjian Exp $ * *

Many of the methods here return lists of information in ResultSets. You * can use the normal ResultSet methods such as getString and getInt to @@ -370,7 +370,7 @@ public class DatabaseMetaData implements java.sql.DatabaseMetaData */ public String getSQLKeywords() throws SQLException { - return "abort,acl,add,aggregate,append,archive,arch_store,backward,binary,change,cluster,copy,database,delimiters,do,extend,explain,forward,heavy,index,inherits,isnull,light,listen,load,merge,nothing,notify,notnull,oids,purge,rename,replace,retrieve,returns,rule,recipe,setof,stdin,stdout,store,vacuum,verbose,version"; + return "abort,acl,add,aggregate,append,archive,arch_store,backward,binary,change,cluster,copy,database,delimiter,delimiters,do,extend,explain,forward,heavy,index,inherits,isnull,light,listen,load,merge,nothing,notify,notnull,oids,purge,rename,replace,retrieve,returns,rule,recipe,setof,stdin,stdout,store,vacuum,verbose,version"; } public String getNumericFunctions() throws SQLException diff --git a/src/interfaces/jdbc/org/postgresql/jdbc2/DatabaseMetaData.java b/src/interfaces/jdbc/org/postgresql/jdbc2/DatabaseMetaData.java index a6130720dd..e38f4c18e5 100644 --- a/src/interfaces/jdbc/org/postgresql/jdbc2/DatabaseMetaData.java +++ b/src/interfaces/jdbc/org/postgresql/jdbc2/DatabaseMetaData.java @@ -15,7 +15,7 @@ import org.postgresql.util.PSQLException; /* * This class provides information about the database as a whole. * - * $Id: DatabaseMetaData.java,v 1.55 2002/06/11 02:55:16 barry Exp $ + * $Id: DatabaseMetaData.java,v 1.56 2002/06/20 16:00:44 momjian Exp $ * *

Many of the methods here return lists of information in ResultSets. You * can use the normal ResultSet methods such as getString and getInt to @@ -409,7 +409,7 @@ public class DatabaseMetaData implements java.sql.DatabaseMetaData public String getSQLKeywords() throws SQLException { if (Driver.logDebug) Driver.debug("getSQLKeyWords"); - return "abort,acl,add,aggregate,append,archive,arch_store,backward,binary,change,cluster,copy,database,delimiters,do,extend,explain,forward,heavy,index,inherits,isnull,light,listen,load,merge,nothing,notify,notnull,oids,purge,rename,replace,retrieve,returns,rule,recipe,setof,stdin,stdout,store,vacuum,verbose,version"; + return "abort,acl,add,aggregate,append,archive,arch_store,backward,binary,change,cluster,copy,database,delimiter,delimiters,do,extend,explain,forward,heavy,index,inherits,isnull,light,listen,load,merge,nothing,notify,notnull,oids,purge,rename,replace,retrieve,returns,rule,recipe,setof,stdin,stdout,store,vacuum,verbose,version"; } public String getNumericFunctions() throws SQLException diff --git a/src/test/locale/de_DE.ISO8859-1/test-de-upper.sql.in b/src/test/locale/de_DE.ISO8859-1/test-de-upper.sql.in index 5b2b32750e..5149f8cd67 100644 --- a/src/test/locale/de_DE.ISO8859-1/test-de-upper.sql.in +++ b/src/test/locale/de_DE.ISO8859-1/test-de-upper.sql.in @@ -1,4 +1,4 @@ -COPY wordlist FROM stdin USING DELIMITERS '|'; +COPY wordlist FROM stdin WITH DELIMITER '|'; AAA |ÄÄÄ AAAAa |ÄÄÄÄä BBBB |BBBB diff --git a/src/test/locale/de_DE.ISO8859-1/test-de.sql.in b/src/test/locale/de_DE.ISO8859-1/test-de.sql.in index c0b0dbd495..7108d0290e 100644 --- a/src/test/locale/de_DE.ISO8859-1/test-de.sql.in +++ b/src/test/locale/de_DE.ISO8859-1/test-de.sql.in @@ -1,4 +1,4 @@ -COPY wordlist FROM stdin USING DELIMITERS '|'; +COPY wordlist FROM stdin WITH DELIMITER '|'; AAA |ÄÄÄ AAAAa |ÄÄÄÄä BBBB |BBBB diff --git a/src/test/locale/gr_GR.ISO8859-7/test-gr.sql.in b/src/test/locale/gr_GR.ISO8859-7/test-gr.sql.in index 8cb638882e..781f4efcad 100644 --- a/src/test/locale/gr_GR.ISO8859-7/test-gr.sql.in +++ b/src/test/locale/gr_GR.ISO8859-7/test-gr.sql.in @@ -1,4 +1,4 @@ -COPY usastates FROM stdin USING DELIMITERS '|'; +COPY usastates FROM stdin WITH DELIMITER '|'; AK|Alaska |ÁëÜóêá WA|Washington |Ãïõüóéíãêôïí OR|Oregon |Ïñåãêïí diff --git a/src/test/locale/koi8-r/test-koi8.sql.in b/src/test/locale/koi8-r/test-koi8.sql.in index 784d440d2f..6be83923be 100644 --- a/src/test/locale/koi8-r/test-koi8.sql.in +++ b/src/test/locale/koi8-r/test-koi8.sql.in @@ -1,4 +1,4 @@ -COPY usastates FROM stdin USING DELIMITERS '|'; +COPY usastates FROM stdin WITH DELIMITER '|'; AK|Alaska |áÌÑÓËÁ WA|Washington |÷ÁÛÉÎÇÔÏÎ OR|Oregon |ïÒÅÇÏÎ diff --git a/src/test/locale/koi8-to-win1251/test-koi8.sql.in b/src/test/locale/koi8-to-win1251/test-koi8.sql.in index 2a5e8175f5..244e495176 100644 --- a/src/test/locale/koi8-to-win1251/test-koi8.sql.in +++ b/src/test/locale/koi8-to-win1251/test-koi8.sql.in @@ -1,4 +1,4 @@ -COPY usastates FROM stdin USING DELIMITERS '|'; +COPY usastates FROM stdin WITH DELIMITER '|'; AK|Alaska |Àëÿñêà WA|Washington |Âàøèíãòîí OR|Oregon |Îðåãîí -- 2.40.0