psql commands 사용법

개요

PostgreSQL Shell 을 psql이라고 부른다. PostgreSQL shell(psql)에서만 사용가능한 명령어들이 있는데, 이 명령어들을 psql commands라고 부른다. (SQL과는 별개)
psql 명령어는 \로 시작한다 (역슬래시)
SQL문은 세미콜론(;)으로 끝나는 반면 psql 명령어는 세미콜론을 사용하지 않는다.

자주 쓰는 명령어

commands	description
\q	psql 종료 ( Ctrl + d )
\c [db_name] [user]	[db_name] db에 [user]로 접속
\l	데이터베이스 목록
\dn	스키마 목록
\d	테이블, 인덱스, 시퀀스, 뷰 목록
\dt	테이블 목록
\d [table_name]	테이블 column 목록
\dv	뷰 목록
\du	사용자 목록

HELP 명령어

commands	description
\?	psql 명령어 전부를 보여줌
\h [SQL query]	SQL query의 용법과 옵션을 보여줌

Full Version

\? 로도 확인 가능

psql Commands

psql 명령어들

code	description
\a	make output format aligned or unaligned
\c [DBNAMM	USER]
\d
\dt	table list
\du	사용자 권한 조회
\e [FILE] [LINE]	edit the query buffer (or file) with external editor
\ef [FUNCNAME [LINE]]	edit function definition with external editor
\ev [VIEWNAME [LINE]]	edit view definition with external editor
\f	Field separator is "
\g [(OPTIONS)] [FILE]	execute query (and send results to file or
\g with no arguments is equivalent to a semicolon
\h	help on syntax of SQL commands, * for all commands
\i FILE	execute commands from file
\l	List of databases
\o [FILE]	send all query results to file or
\p	show the contents of the query buffer
\q	quit
\r	Query bufreset (clear) the query bufferfer reset (cleared)
\s [FILE]	display history or save it to file
\t	tuple only onoff

show only rows | | \w FILE | | | \x | expanded display onoff
toggle expanded output | | \z [PATTERN] | same as \dp | | \? | 도움말 |

Informational Commands

options
- 명령어 뒤에 + 를 붙이면 자세한 내용을 보여준다.

code	description
\d	list tables, views, and sequences
\d NAME	describe table, view, sequence, or index
\da [PATTERN]	list aggregates
\dA [PATTERN]	list access methods
\dAc [AMPTRN TYPERPTRN]	list operator classes
\dAf [AMPTRN TYPERPTRN]	list operator families
\dAo [AMPTRN [OPFPTRN]]	list operators of operator families
\dAp [AMPTRN [OPFPTRN]]	list support functions of operator families
\db [PATTERN]	list tablespaces
\dc [PATTERN]	list conversions
\dC [PATTERN]	list casts
\dd [PATTERN]	show object descriptions not displayed elsewhere
\dD [PATTERN]	list domains
\ddp [PATTERN]	list default privileges
\dE [PATTERN]	list foreign tables
\des [PATTERN]	list foreign servers
\det [PATTERN]	list foreign tables
\deu [PATTERN]	list user mappings
\dew [PATTERN]	list foreign-data wrappers
\df [PATTERN]	list [only agg/normal/procedure/trigger/window] functions
\dF [PATTERN]	list text search configurations
\dFd [PATTERN]	list text search dictionaries
\dFp [PATTERN]	list text search parsers
\dFt [PATTERN]	list text search templates
\dg [PATTERN]	list roles
\di [PATTERN]	list indexes
\dl	list large objects, same as \lo_list
\dL [PATTERN]	list procedural languages
\dm [PATTERN]	list materialized views
\dn [PATTERN]	list schemas
\do [OPPTRN [TYPEPTRN [TYPEPTRN]]]	list operators
\dO [PATTERN]	list collations
\dp [PATTERN]	list table, view, and sequence access privileges
\dP [PATTERN]	list [only index/table] partitioned relations [n=nested]
\drds [ROLEPTRN [DBPTRN]]	list per-database role settings
\dRp [PATTERN]	list replication publications
\dRs [PATTERN]	list replication subscriptions
\ds [PATTERN]	list sequences
\dt [PATTERN]	list tables
\dT [PATTERN]	list data types
\du [PATTERN]	list roles
\dv [PATTERN]	list views
\dx [PATTERN]	list extensions
\dX [PATTERN]	list extended statistics
\dy [PATTERN]	list event triggers
\l [PATTERN]	list databases
\sf FUNCNAME	show a function's definition
\sv VIEWNAME	show a view's definition
\z [PATTERN]	same as \dp