交互工具psql的使用简述

最新推荐文章于 2023-11-14 16:55:20 发布

Franklin_H

最新推荐文章于 2023-11-14 16:55:20 发布

阅读量615

点赞数 1

文章标签： postgresql

本文链接：https://blog.csdn.net/sinat_29742125/article/details/74059000

版权

psql介绍

1. 通过 man psql 或者 psql --help 这两个linux 命令可以了解 psql 的用法。

postgres@pgdb-> psql --help

psql is the PostgreSQL interactive terminal.

Usage:
  psql [OPTION]... [DBNAME [USERNAME]]

General options:
常用连接
  -c, --command=COMMAND    run only single command (SQL or internal) and exit
  -d, --dbname=DBNAME      database name to connect to (default: "postgres")
  -f, --file=FILENAME      execute commands from file, then exit
  -l, --list               list available databases, then exit
  -v, --set=, --variable=NAME=VALUE
                           set psql variable NAME to VALUE
                           (e.g., -v ON_ERROR_STOP=1)
  -V, --version            output version information, then exit
  -X, --no-psqlrc          do not read startup file (~/.psqlrc)
  -1 ("one"), --single-transaction
                           execute as a single transaction (if non-interactive)
  -?, --help[=options]     show this help, then exit
      --help=commands      list backslash commands, then exit
      --help=variables     list special variables, then exit

//输入输出选项
Input and output options:
  -a, --echo-all           echo all input from script
  -b, --echo-errors        echo failed commands
  -e, --echo-queries       echo commands sent to server
  -E, --echo-hidden        display queries that internal commands generate
  -L, --log-file=FILENAME  send session log to file
  -n, --no-readline        disable enhanced command line editing (readline)
  -o, --output=FILENAME    send query results to file (or |pipe)
  -q, --quiet              run quietly (no messages, only query output)
  -s, --single-step        single-step mode (confirm each query)
  -S, --single-line        single-line mode (end of line terminates SQL command)

//输入格式
Output format options:
  -A, --no-align           unaligned table output mode
  -F, --field-separator=STRING
                           field separator for unaligned output (default: "|")
  -H, --html               HTML table output mode
  -P, --pset=VAR[=ARG]     set printing option VAR to ARG (see \pset command)
  -R, --record-separator=STRING
                           record separator for unaligned output (default: newline)
  -t, --tuples-only        print rows only
  -T, --table-attr=TEXT    set HTML table tag attributes (e.g., width, border)
  -x, --expanded           turn on expanded table output
  -z, --field-separator-zero
                           set field separator for unaligned output to zero byte
  -0, --record-separator-zero
                           set record separator for unaligned output to zero byte

//连接选项
Connection options:
  -h, --host=HOSTNAME      database server host or socket directory (default: "/home/postgres/pgdata")
  -p, --port=PORT          database server port (default: "1921")
  -U, --username=USERNAME  database user name (default: "postgres")
  -w, --no-password        never prompt for password
  -W, --password           force password prompt (should happen automatically)

For more information, type "\?" (for internal commands) or "\help" (for SQL
commands) from within psql, or consult the psql section in the PostgreSQL
documentation.

Report bugs to <pgsql-bugs@postgresql.org>.

也可以用man psql 命令：

ostgres@pgdb-> man psql
PSQL(1)                 PostgreSQL 9.5.3 Documentation                 PSQL(1)

NAME
       psql - PostgreSQL interactive terminal

SYNOPSIS
       psql [option...] [dbname [username]]

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

OPTIONS
       -a
       --echo-all
           Print all nonempty input lines to standard output as they are read. (This does not apply to lines
           read interactively.) This is equivalent to setting the variable ECHO to all.

       -A
       --no-align
           Switches to unaligned output mode. (The default output mode is otherwise aligned.)

       -b
       --echo-errors
           Print failed SQL commands to standard error output. This is equivalent to setting the variable
           ECHO to errors.

       -c command
       --command=command
           Specifies that psql is to execute one command string, command, and then exit. This is useful in
           shell scripts. Start-up files (psqlrc and ~/.psqlrc) are ignored with this option.

           command must be either a command string that is completely parsable by the server (i.e., it
           contains no psql-specific features), or a single backslash command. Thus you cannot mix SQL and
           psql meta-commands with this option. To achieve that, you could pipe the string into psql, for
           example: echo '\x \\ SELECT * FROM foo;' | psql. (\\ is the separator meta-command.)

进入到psql后的常用技巧

无论是通过 unix-socket-domain 还是TCP-IP方式连接进入psql后，就意味着可以使用sql语句操作数据库了。

按tab键自动补齐

postgres=# create 
//此时通过使用tab键，就可以补齐命令，如下
AGGREGATE             EXTENSION             MATERIALIZED VIEW     SERVER                UNIQUE
CAST                  FOREIGN DATA WRAPPER  OPERATOR              TABLE                 UNLOGGED
COLLATION             FOREIGN TABLE         POLICY                TABLESPACE            USER
CONVERSION            FUNCTION              ROLE                  TEMP                  USER MAPPING FOR
DATABASE              GROUP                 RULE                  TEXT SEARCH           VIEW
DOMAIN                INDEX                 SCHEMA                TRIGGER               
EVENT TRIGGER         LANGUAGE              SEQUENCE              TYPE                  
postgres=# create tab
按“tab”键也可以自动补齐喔-
postgres=# create tab

帮助命令：

// 通过 \h 可以显示 create table 的语法
postgres=# \h create table


Command:     CREATE TABLE
Description: define a new table
Syntax:
CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name ( [
  { column_name data_type [ COLLATE collation ] [ column_constraint [ ... ] ]
    | table_constraint
    | LIKE source_table [ like_option ... ] }
    [, ... ]
] )
[ INHERITS ( parent_table [, ... ] ) ]
[ WITH ( storage_parameter [= value] [, ... ] ) | WITH OIDS | WITHOUT OIDS ]
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
[ TABLESPACE tablespace_name ]

CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name
    OF type_name [ (
  { column_name WITH OPTIONS [ column_constraint [ ... ] ]
    | table_constraint }
    [, ... ]
) ]
[ WITH ( storage_parameter [= value] [, ... ] ) | WITH OIDS | WITHOUT OIDS ]
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
[ TABLESPACE tablespace_name ]

where column_constraint is:

[ CONSTRAINT constraint_name ]
{ NOT NULL |
  NULL |
  CHECK ( expression ) [ NO INHERIT ] |
  DEFAULT default_expr |
  UNIQUE index_parameters |
  PRIMARY KEY index_parameters |

//通过 \? 命令可以列出一些快捷命令：

postgres=# \?
General
  \copyright             show PostgreSQL usage and distribution terms
  \g [FILE] or ;         execute query (and send results to file or |pipe)
  \gset [PREFIX]         execute query and store results in psql variables
  \q                     quit psql
  \watch [SEC]           execute query every SEC seconds

Help
  \? [commands]          show help on backslash commands
  \? options             show help on psql command-line options
  \? variables           show help on special variables
  \h [NAME]              help on syntax of SQL commands, * for all commands

Query Buffer
  \e [FILE] [LINE]       edit the query buffer (or file) with external editor
  \ef [FUNCNAME [LINE]]  edit function definition with external editor
  \p                     show the contents of the query buffer
  \r                     reset (clear) the query buffer
  \s [FILE]              display history or save it to file
  \w FILE                write query buffer to file

Input/Output
  \copy ...              perform SQL COPY with data stream to the client host
  \echo [STRING]         write string to standard output
  \i FILE                execute commands from file
  \ir FILE               as \i, but relative to location of current script
  \o [FILE]              send all query results to file or |pipe
  \qecho [STRING]        write string to query output stream (see \o)

Informational  用法
  (options: S = show system objects, + = additional detail)
  \d[S+]                 list tables, views, and sequences
  \d[S+]  NAME           describe table, view, sequence, or index
  \da[S]  [PATTERN]      list aggregates
  \db[+]  [PATTERN]      list tablespaces
  \dc[S+] [PATTERN]      list conversions
  \dC[+]  [PATTERN]      list casts
  \dd[S]  [PATTERN]      show object descriptions not displayed elsewhere
  \ddp    [PATTERN]      list default privi

\set VERBOSITY verbose
 设置详细的打印输出, 例如可以报出问题的代码

postgres=# \set VERBOSITY verbose
postgres=# select a ;
ERROR:  42703: column "a" does not exist
LINE 1: select a ;
             ^
//说明这个报错是通过 parse_relation.c 文件的 3090行爆出来的。
也就是errorMissingColumn函数报的错误
LOCATION:  errorMissingColumn, parse_relation.c:3090

防盗链接：http://www.jianshu.com/p/9e3775d5c5bd