[PostgreSQL] psql 기본 명령어

postgresql 설치하면 기본적으로 postgres라는 사용자가 생성된다.

postgresql 접속

root에서 postgres 사용자로 postgresql db 접속

root@ psql -U postgres

postgres=#

DB 접속 정보 확인

postgres=# \conninfo 

> You are connected to database "postgres" as user "postgres" via socket in "/var/run/postgresql" at port "5432".

• db,user, socket, port 정보 확인

postgresql 나가기

postgres=# \q

---

User

User 생성

postgres=# CREATE USER [USER_NAME] [OPTION];

[OPTION]    [역할]
SUPERUSER   수퍼 유저 여부
CREATEDB    db 생성 권한 여부
CREATEUSER  user 생성 권한 여부
PASSWORD    패스워드 설정

\

postgres=# CREATE USER kyuyeon PASSWORD '0429' SUPERUSER;

> CREATE ROLE

사용자 정보 확인

postgres=# SELECT * FROM PG_USER;

 usename  | usesysid | usecreatedb | usesuper | userepl | usebypassrls |  passwd  | valuntil | useconfig 
----------+----------+-------------+----------+---------+--------------+----------+----------+-----------
 postgres |       10 | t           | t        | t       | t            | ******** |          | 
 kyuyeon  |    16388 | f           | t        | f       | f            | ******** |          | 
(2 rows)

사용자 정보 확인, 커맨드라인 명령어

postgres=# \du

\

postgres=# \du

                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 kyuyeon   | Superuser                                                  | {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

사용자 삭제

postgres=# DROP USER [USER_NAME];

\

postgres=# DROP USER kyuyeon;

> DROP ROLE

---

DB

DB 생성

postgres=# CREATE DATABASE [DATABASE_NAME] OWNER [USER_NAME] [OPTION];

[OPTION]    [역할]
OWNER       DB owner, 다른 계정들은 db 역할 제한 있음
TEMPLATE    DB template에 의해 생성될 때 template 이름, 기본값 template1.
ENCODING    data 인코딩 방식
...

\

postgres=# CREATE DATABASE kyuyeon_database OWNER kyuyeon;

> CREATE DATABASE

DB 목록 확인

postgres=# \l

\

                                                    List of databases
       Name       |  Owner   | Encoding |  Collate   |   Ctype    | ICU Locale | Locale Provider |   Access privileges   
------------------+----------+----------+------------+------------+------------+-----------------+-----------------------
 kyuyeon_database | kyuyeon  | UTF8     | en_US.utf8 | en_US.utf8 |            | libc            | 
 postgres         | postgres | UTF8     | en_US.utf8 | en_US.utf8 |            | libc            | 
 template0        | postgres | UTF8     | en_US.utf8 | en_US.utf8 |            | libc            | =c/postgres          +
                  |          |          |            |            |            |                 | postgres=CTc/postgres
 template1        | postgres | UTF8     | en_US.utf8 | en_US.utf8 |            | libc            | =c/postgres          +
                  |          |          |            |            |            |                 | postgres=CTc/postgres
(4 rows)

DB 삭제

DROP DATABASE [DATABASE_NAME]

\

DROP DATABASE kyuyeon_database;

> DROP DATABASE

---

Table

Table 생성

postgres=# CREATE TABLE [TABLE_NAME0 (
...
);

\

CREATE TABLE mloops (
  id serial PRIMARY KEY,
  semester integer NOT NULL,
  part varchar NOT NULL,
  name varchar NOT NULL
);

> CREATE TABLE

• serial 타입은 자동으로 증가하는 숫자

Table 확인

postgres=# \d

\

              List of relations
 Schema |     Name      |   Type   |  Owner   
--------+---------------+----------+----------
 public | mloops        | table    | postgres
 public | mloops_id_seq | sequence | postgres
(2 rows)

• table에 serial 타입의 데이터가 존재하는 경우 serial 데이터를 저장하기 위해 따로 또 serquence 타입의 relation이 생성됨

postgres=# \dt

• table 타입의 relation들만 가져오기

       List of relations

Schema | Name | Type | Owner
-------+--------+-------+----------
public | mloops | table | postgres  
(1 row)

Table 삭제

postgres=# DELETE TABLE [TABLE_NAME];

\

postgres=# DROP TABLE mloops;

> DROP TABLE

postgres=# \d

> Did not find any relations.

• sequnce reltaion도 같이 사라진다.

---

데이터 입출력

insert

INSERT INTO [TABLE_NAME] ([FIELD_NAME],...) VALUES ([VALUE],...);

\

INSERT INTO mloops (semester, part, name) VALUES (18, '분석', '박규연');
INSERT INTO mloops (semester, part, name) VALUES (19, '엔지니어링', '김지원');

> INSERT 0 1
> INSERT 0 1

select

SELECT [열, 행, field...] FROM [TABLE_NAME];

\

SELECT * FROM mloops;

 id | semester |    part    |  name  
----+----------+------------+--------
  1 |       18 | 분석       | 박규연
  2 |       19 | 엔지니어링 | 김지원
(2 rows)

where

SELECT [열, 행, field...] FROM [TABLE_NAME] WHERE [CONDITION];

\

SELECT * FROM mloops WHERE semester='18';

 id | semester | part |  name  
----+----------+------+--------
  1 |       18 | 분석 | 박규연
(1 row)

delete

DELETE FROM [TABLE_NAME] WHERE [CONDITION];

\

DELETE FROM mloops where name='김지원';

> DELETE 1

SELECT * FROM mloops;

 id | semester | part |  name  
----+----------+------+--------
  1 |       18 | 분석 | 박규연
(1 row)

---

References

https://medium.com/coding-blocks/creating-user-database-and-adding-access-on-postgresql-8bfcd2f4a91e
https://dejavuqa.tistory.com/16
https://browndwarf.tistory.com/3