psql的使用

最小化安装

brew install libpq

ln -s /usr/local/Cellar/libpq/xxxxxx/bin/psql /usr/local/bin/psql

psql -h 172.0.0.1 -p 5432 -U postgres -d public

pgcli

brew install pgcli  # Only on macOS
pgcli postgresql://[user[:password]@][netloc][:port][/dbname][?extra=value[&other=other-value]]
pgcli -h localhost -p 5432 -U username postgres

连接

psql -h <hostname or ip> -p <端口> [数据库名称] [用户名称]

常用命令


\l - Display database
\c - Connect to database

SHOW search_path;
SET search_path TO myschema,public;

psql 'postgresql://myUser@myHost:myPort/myDb?options=--search_path%3dmyschema'

\dn - List schemas
\dt - List tables inside public schemas

\d tablename|view
\dt tablename
\di indexname
\ds sequename
\dv viewname
\df functioname

OPERATOR(schema.operator) - OPERATOR(myschema.%) 

\dx - SELECT * FROM pg_extension; SELECT * FROM pg_available_extensions;

\encoding [utf-8]
show [client_encoding|server_encoding]
\q

select * from pg_tables;
select tablename from pg_tables where schemaname='public';


\set PROMPT1 '%001%[%033[1;32;40m%]%002%n@%/%R%001%[%033[0m%]%002%# '
\set PROMPT1 '%001%[%033[1;33;40m%]%002%n@%/%R%001%[%033[0m%]%002%# '
\set PROMPT1 '%001%[%033[1;32;40m%]%002%M_%/%R%001%[%033[0m%]%002%# '

来源

  1. psql
  2. psql-PROMPTING
  3. pgcli
  4. correct-way-to-install-psql-without-full-postgres-on-macos
  5. how-to-select-a-schema-in-postgres-when-using-psql
  6. dt-says-did-not-find-any-relations
  7. how-to-customize-the-postgresql-prompt
  8. how-to-set-a-search-path-default-on-a-psql-cmd-execution