[大数据][2020-3-16]Postgresql 调用脚本方法

postgresql数据库执行脚本常用的两种方式

1、在psql中通过\i 或者\ir的方式执行脚本，语法如下：

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)

例子如下：

[postgres@postgres ~]$ cat 1.sql
\set id 1
insert into t values (:id);
[postgres@postgres ~]$ psql
psql (12.10)
Type "help" for help.

postgres=# \i 1.sql
INSERT 0 1
postgres=# 

2、通过psql直接调用-f 参数调用脚本，语法如下：

[postgres@postgres ~]$ 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

例子

[postgres@postgres ~]$ psql -h 127.0.0.1 -d postgres -U postgres -f 1.sql
INSERT 0 1
[postgres@postgres ~]$