GPDB 기본 명령어 [greenplum database]

안녕하세요.

GPDB 기본 명령어를 살펴볼까요?

특별히 쿼리를 하지 않더라도

다양한 정보들은 간편한 명령어로 확인 할 수 있습니다.

\da [aggregate_pattern]

Lists all available aggregate functions, together with the data types they operate on. If a pattern is specified, only aggregates whose names match the pattern are shown.

\db [tablespace_pattern] | \db+ [tablespace_pattern]

Lists all available tablespaces and their corresponding filespace locations. If pattern is specified, only tablespaces whose names match the pattern are shown. If + is appended to the command name, each object is listed with its associated permissions.

\dc [conversion_pattern]

Lists all available conversions between character-set encodings. If pattern is specified, only conversions whose names match the pattern are listed.

\dC

Lists all available type casts.

\dd [object_pattern]

Lists all available objects. If pattern is specified, only matching objects are shown.

\dD [domain_pattern]

Lists all available domains. If pattern is specified, only matching domains are shown.

\df [function_pattern] | \df+ [function_pattern ]

Lists available functions, together with their argument and return types. If pattern is specified, only functions whose names match the pattern are shown. If the form \df+ is used, additional information about each function, including language and description, is shown. To reduce clutter, \df does not show data type I/O functions. This is implemented by ignoring functions that accept or return type cstring.

\dg [role_pattern]

Lists all database roles. If pattern is specified, only those roles whose names match the pattern are listed.

\distPvxS [index | sequence | table | parent table | view | external_table | system_object]

This is not the actual command name: the letters i, s, t, P, v, x, S stand for index, sequence, table, parent table, view, external table, and system table, respectively. You can specify any or all of these letters, in any order, to obtain a listing of all the matching objects. The letter Srestricts the listing to system objects; without S, only non-system objects are shown. If + is appended to the command name, each object is listed with its associated description, if any. If a pattern is specified, only objects whose names match the pattern are listed.

\dl

This is an alias for \lo_list, which shows a list of large objects.

\dn [schema_pattern] | \dn+ [schema_pattern]

Lists all available schemas (namespaces). If pattern is specified, only schemas whose names match the pattern are listed. Non-local temporary schemas are suppressed. If + is appended to the command name, each object is listed with its associated permissions and description, if any.

\do [operator_pattern]

Lists available operators with their operand and return types. If pattern is specified, only operators whose names match the pattern are listed.

\dp [relation_pattern_to_show_privileges]

Produces a list of all available tables, views and sequences with their associated access privileges. If pattern is specified, only tables, views and sequences whose names match the pattern are listed. The GRANT and REVOKE commands are used to set access privileges.

\dT [datatype_pattern] | \dT+ [datatype_pattern]

Lists all data types or only those that match pattern. The command form \dT+ shows extra information.

\du [role_pattern]

Lists all database roles, or only those that match pattern.

\e | \edit [filename]

If a file name 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.

psql searches the environment variables PSQL_EDITOR, EDITOR, and VISUAL (in that order) for an editor to use. If all of them are unset, vi is used on UNIX systems, notepad.exe on Windows systems.

\echotext [ ... ]

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.

If you use the \o command to redirect your query output you may wish to use 'echo instead of this command.

\encoding [encoding]

Sets the client character set encoding. Without an argument, this command shows the current encoding.

\f [field_separator_string]

Sets the field separator for unaligned query output. The default is the vertical bar (|). See also \pset for a generic way of setting output options.

\g [{filename | |command }]

Sends the current query input buffer to the server and optionally stores the query's output in a file or pipes the output into a separate UNIX shell executing command. A bare \g is virtually equivalent to a semicolon. A \g with argument is a one-shot alternative to the \o command.

\h | \help [sql_command]

Gives syntax help on the specified SQL command. If a command is not specified, then psqlwill list all the commands for which syntax help is available. Use an asterisk (*) to show syntax help on all SQL commands. To simplify typing, commands that consists of several words do not have to be quoted.

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

\i input_filename

Reads input from a file 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.

\l | \list | \l+ | \list+

List the names, owners, and character set encodings of all the databases in the server. If + is appended to the command name, database descriptions are also displayed.

\lo_export loid filename

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.

\lo_import large_object_filename [comment]

Stores the file into a large object. Optionally, it associates the given comment with the object. Example:

mydb=> \lo_import '/home/gpadmin/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. 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.

\lo_list

Shows a list of all large objects currently stored in the database, along with any comments provided for them.

\lo_unlink largeobject_oid

Deletes the large object of the specified OID from the database. Use \lo_list to find out the large object's OID.

\o [ {query_result_filename | |command} ]

Saves future query results to a file or pipes them into a UNIX shell command. If no arguments are specified, the query output will be reset to the standard output. Query results include 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 'echo.

\p

Print the current query buffer to the standard output.

\password [username]

Changes the password of the specified user (by default, the current user). This command prompts for the new password, encrypts it, and sends it to the server as an ALTER ROLEcommand. This makes sure that the new password does not appear in cleartext in the command history, the server log, or elsewhere.

\prompt [ text ] name

Prompts the user to set a variable name. Optionally, you can specify a prompt. Enclose prompts longer than one word in single quotes.

By default, \prompt uses the terminal for input and output. However, use the -f command line switch to specify standard input and standard output.

\pset print_option [value]

This command sets options affecting the output of query result tables. print_optiondescribes which option is to be set. Adjustable printing options are:

• format – Sets the output format to one of unaligned, aligned, html, latex, troff-ms, or wrapped. First letter abbreviations are allowed. Unaligned writes all columns of a row 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. 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.)

  The wrapped option sets the output format like the aligned parameter , but wraps wide data values across lines to make the output fit in the target column width. The target width is set with the columns option. To specify the column width and select the wrapped format, use two \pset commands; for example, to set the with to 72 columns and specify wrapped format, use the commands \pset columns 72 and then \pset format wrapped.

  Note: Since psql does not attempt to wrap column header titles, the wrapped format behaves the same as aligned if the total width needed for column headers exceeds the target.
• 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.
• columns – Sets the target width for the wrapped format, and also the width limit for determining whether output is wide enough to require the pager. The default is zero. Zero causes the target width to be controlled by the environment variable COLUMNS, or the detected screen width if COLUMNS is not set. In addition, if columns is zero then the wrapped format affects screen output only. If columns is nonzero then file and pipe output is wrapped to that width as well.

  After setting the target width, use the command \pset format wrapped to enable the wrapped format.

• expanded | x) – Toggles between regular and expanded format. When expanded format is enabled, query results are displayed in two columns, with the column name on the left and the data on the right. This mode is useful if the data would not fit on the screen in the normal horizontal mode. Expanded mode is supported by all four output formats.
• linestyle [unicode | ascii | old-ascii] – Sets the border line drawing style to one of unicode, ascii, or old-ascii. Unique abbreviations, including one letter, are allowed for the three styles. The default setting is ascii. This option only affects the aligned and wrapped output formats.

  ascii – uses plain ASCII characters. Newlines in data are shown using a + symbol in the right-hand margin. When the wrapped format wraps data from one line to the next without a newline character, a dot (.) is shown in the right-hand margin of the first line, and again in the left-hand margin of the following line.

  old-ascii – style uses plain ASCII characters, using the formatting style used in PostgreSQL 8.4 and earlier. Newlines in data are shown using a : symbol in place of the left-hand column separator. When the data is wrapped from one line to the next without a newline character, a ; symbol is used in place of the left-hand column separator.

  unicode – style uses Unicode box-drawing characters. Newlines in data are shown using a carriage return symbol in the right-hand margin. When the data is wrapped from one line to the next without a newline character, an ellipsis symbol is shown in the right-hand margin of the first line, and again in the left-hand margin of the following line.

  When the border setting is greater than zero, this option also determines the characters with which the border lines are drawn. Plain ASCII characters work everywhere, but Unicode characters look nicer on displays that recognize them.

• null 'string' – The second argument is a string to print whenever a column is null. The default is not to print anything, which can easily be mistaken for an empty string. For example, the command \psetnull '(empty)' displays (empty) in null columns.
• 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 '|' (a vertical bar).
• footer – Toggles the display of the default footer (x rows).
• numericlocale – Toggles the display of a locale-aware character to separate groups of digits to the left of the decimal marker. It also enables a locale-aware decimal marker.
• recordsep – Specifies the record (line) separator to use in unaligned output mode. The default is a newline character.
• 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.
• tableattr | 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.
• tuples_only | t [novalue | on | off] – The \pset tuples_only command by itselt toggles between tuples only and full display. The values on and off set the tuples display, regardless of the current setting. Full display may show extra information such as column headers, titles, and various footers. In tuples only mode, only actual table data is shown The \t command is equivalent to \psettuples_only and is provided for convenience.
• pager – Controls the use of a pager for query and psql help output. When on, if the environment variable PAGER is set, the output is piped to the specified program. Otherwise a platform-dependent default (such as more) is used. When off, the pager is not used. When on, the pager is used only when appropriate. Pager can also be set to always, which causes the pager to be always used.

\q

Quits the psql program.

\qechotext [ ... ]

This command is identical to \echo except that the output will be written to the query output channel, as set by \o.

\r

Resets (clears) the query buffer.

\s [history_filename]

Print or save the command line history to filename. If filename is omitted, the history is written to the standard output.

\set [name [value [ ... ]]]

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 "Variables" in Advanced Features. Variable names are case-sensitive.

Although you are welcome to set any variable to anything you want, psql treats several variables as special. They are documented in the topic about variables.

This command is totally separate from the SQL command SET.

\t [novalue | on | off]

The \t command by itself toggles a display of output column name headings and row count footer. The values on and off set the tuples display, regardless of the current setting. This command is equivalent to \pset tuples_only and is provided for convenience.

\T table_options

Allows you to specify attributes to be placed within the table tag in HTML tabular output mode.

\timing [novalue | on | off]

The \timing command by itself toggles a display of how long each SQL statement takes, in milliseconds. The values on and off set the time display, regardless of the current setting.

\w {filename | |command}

Outputs the current query buffer to a file or pipes it to a UNIX command.

\x

Toggles expanded table formatting mode.

\z [relation_to_show_privileges]

Produces a list of all available tables, views and sequences with their associated access privileges. If a pattern is specified, only tables, views and sequences whose names match the pattern are listed. This is an alias for \dp.

\! [command]

Escapes to a separate UNIX shell or executes the UNIX command. The arguments are not further interpreted, the shell will see them as is.

\?

Shows help information about the psql backslash commands.

By. stricky