00 psql
1. psql의 정의 및 사용방법
- PostgreSQL interactive terminal
- psql is a terminal-based front-end to PostgreSQL.
- enables you to type in queries interactively, issue them to PostgreSQL, and see the query results.
- input can be from a file or from command line arguments.
- provides a number of meta-commands and various shell-like features to facilitate writing scripts and automating a wide variety of tasks.
2. Linux 터미널에서 접속하기
psql [option...] [dbname [username]]
psql -U [DB사용자계정] [데이터베이스명]
3. 옵션
- -c command 또는 --command=command
- Specifies that psql is to execute the given command string, command.
- can be repeated and combined in any order with the -f option
- When either -c or -f is specified, psql does not read commands from standard input; instead it terminates after processing all the -c and -f options in sequence
- 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.
- cannot mix SQL and psql meta-commands within a -c option; To achieve that, you could use repeated -c options or pipe the string into psql
- 예 ((\\ is the separator meta-command.))
-
$ psql -c '\x' -c 'SELECT * FROM foo;' $ echo '\x \\ SELECT * FROM foo;' | psql # 또는 - Each SQL command string passed to -c is sent to the server as a single request.
- Because of this, the server executes it as a single transaction even if the string contains multiple SQL commands, unless there are explicit BEGIN/COMMIT commands included in the string to divide it into multiple transactions.
- --csv # 또는 \pset format csv
- -d dbname 또는 --dbname=dbname
- Specifies the name of the database to connect to
- The dbname can be a connection string. If so, connection string parameters will override any conflicting command line options.
- -U username 또는 --username=username
- Connect to the database as the user username instead of the default.
- (You must have permission to do so, of course.)
- -w 또는 --no-password
- Never issue a password prompt.
- If the server requires password authentication and a password is not available from other sources such as a .pgpass file, the connection attempt will fail.
- This option can be useful in batch jobs and scripts where no user is present to enter a password.
- Note that this option will remain set for the entire session, and so it affects uses of the meta-command \connect as well as the initial connection attempt.
- -W 또는 --password
- Force psql to prompt for a password before connecting to a database, even if the password will not be used.
- If the server requires password authentication and a password is not available from other sources such as a .pgpass file, psql will prompt for a password in any case.
- However, psql will waste a connection attempt finding out that the server wants a password.
- In some cases it is worth typing -W to avoid the extra connection attempt.
- Note that this option will remain set for the entire session, and so it affects uses of the meta-command \connect as well as the initial connection attempt.
01 psql 메타 명령어 (Meta-Commands)
- 문법
- backslash (\), followed immediately by a command verb, then any arguments
- The arguments are separated from the command verb and each other by any number of whitespace characters
- 몇가지 기본 메타 명령어
| 설명 | 명령어 |
| DB 출력 | \l 또는 \list |
| DB 선택 | \c 데이터베이스명 |
| 테이블 출력 | \dt |
| 테이블 구조 조회 | \d+ 테이블명 |
| 해당 테이블 정보 출력 | \d 테이블명 |
| 사용자 권한 정보 | \du |
| 출력 변경 | \x |
| 쿼리 결과값 파일로 저장 (쿼리 결과값 미출력, 파일에만 저장) | \o 파일경로 select문 명령어 |
| 나가기 (종료) | \q |