POSTGRESQL Archives - icodebroker


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

List of roles

Role name | Attributes | Member of

-----------+------------------------------------------------------------+-----------

postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

testuser1 | Superuser, Create DB | {}

testuser2 | | {}

[DATABASE/POSTGRESQL] 사용자 권한 조회하기 (테이블 수준)

■ 사용자 권한을 조회하는 방법을 보여준다. (테이블 수준) ▶ 예제 코드 (SQL)


SELECT
    TABLE_CATALOG,
    TABLE_SCHEMA,
    TABLE_NAME,
    PRIVILEGE_TYPE
FROM  information_schema.role_table_grants
WHERE GRANTEE = 'testuser1';

SELECT

TABLE_CATALOG,

TABLE_SCHEMA,

TABLE_NAME,

PRIVILEGE_TYPE

FROM information_schema.role_table_grants

WHERE GRANTEE = 'testuser1';

※ 해당 뷰 명칭은 소문자로 입력한다. ※ testuser1 :

[DATABASE/POSTGRESQL] 사용자 권한 조회하기 (스키마 수준)

■ 사용자 권한 조회하기 (스키마 수준) ▶ 예제 코드 (SQL)


SELECT
    N.NSPNAME                                            AS SCHEMA,
    U.USENAME                                            AS USER,
    HAS_SCHEMA_PRIVILEGE(U.USENAME, N.NSPNAME, 'USAGE' ) AS CAN_USE,
    HAS_SCHEMA_PRIVILEGE(U.USENAME, N.NSPNAME, 'CREATE') AS CAN_CREATE
FROM       PG_NAMESPACE N
CROSS JOIN PG_USER U
WHERE U.USENAME = 'testuser1';

SELECT

N.NSPNAME AS SCHEMA,

U.USENAME AS USER,

HAS_SCHEMA_PRIVILEGE(U.USENAME, N.NSPNAME, 'USAGE' ) AS CAN_USE,

HAS_SCHEMA_PRIVILEGE(U.USENAME, N.NSPNAME, 'CREATE') AS CAN_CREATE

FROM PG_NAMESPACE N

CROSS JOIN PG_USER U

WHERE U.USENAME = 'testuser1';

▶ 실행 결과


       schema       |   user    | can_use | can_create
--------------------+-----------+---------+------------
 pg_toast           | testuser1 | t       | t
 pg_temp_1          | testuser1 | t       | t
 pg_toast_temp_1    | testuser1 | t       | t
 pg_catalog         | testuser1 | t       | t
 public             | testuser1 | t       | t
 information_schema | testuser1 | t       | t
(6 rows)

schema | user | can_use | can_create

--------------------+-----------+---------+------------

pg_toast | testuser1 | t | t

pg_temp_1 | testuser1 | t | t

pg_toast_temp_1 | testuser1 | t | t

pg_catalog | testuser1 | t | t

public | testuser1 | t | t

information_schema | testuser1 | t | t

(6 rows)

[DATABASE/POSTGRESQL] 사용자 권한 조회하기 (데이터베이스 수준)

■ 사용자 권한을 조회하는 방법을 보여준다. (데이터베이스 수준) ▶ 예제 코드 (SQL)


SELECT
    D.DATNAME AS DATABASE,
    U.USENAME AS USER,
    HAS_DATABASE_PRIVILEGE(U.USENAME, D.DATNAME, 'CONNECT') AS CAN_CONNECT,
    HAS_DATABASE_PRIVILEGE(U.USENAME, D.DATNAME, 'CREATE' ) AS CAN_CREATE
FROM       PG_DATABASE D
CROSS JOIN PG_USER     U
WHERE U.USENAME = 'testuser1';

SELECT

D.DATNAME AS DATABASE,

U.USENAME AS USER,

HAS_DATABASE_PRIVILEGE(U.USENAME, D.DATNAME, 'CONNECT') AS CAN_CONNECT,

HAS_DATABASE_PRIVILEGE(U.USENAME, D.DATNAME, 'CREATE' ) AS CAN_CREATE

FROM PG_DATABASE D

CROSS JOIN PG_USER U

WHERE U.USENAME = 'testuser1';

※ testuser1 : 사용자명, 대소문자 구분한다. ▶ 실행 결과

[DATABASE/POSTGRESQL] CREATE ROLE … WITH LOGIN PASSWORD … 명령 : 사용자 생성하기

■ CREATE ROLE … WITH LOGIN PASSWORD … 명령을 사용해 사용자를 생성하는 방법을 보여준다. ▶ 예제 코드 (SQL)


CREATE ROLE user1 WITH LOGIN PASSWORD '1234';

CREATE ROLE user1 WITH LOGIN PASSWORD '1234';

※ user1 :

[DATABASE/POSTGRESQL] 데이터베이스 목록 조회하기 (PGSQL)

■ 데이터베이스 목록을 조회하는 방법을 보여준다. (PGSQL) ▶ 실행 명령


postgres=# \l

또는

postgres=# \list

postgres=# \l

또는

postgres=# \list

▶ 실행 결과


                                       List of databases
   Name    |  Owner   | Encoding |     Collate      |      Ctype       |   Access privileges
-----------+----------+----------+------------------+------------------+-----------------------
 postgres  | postgres | UTF8     | Korean_Korea.949 | Korean_Korea.949 |
 stat      | postgres | UTF8     | Korean_Korea.949 | Korean_Korea.949 |
 template0 | postgres | UTF8     | Korean_Korea.949 | Korean_Korea.949 | =c/postgres          +
           |          |          |                  |                  | postgres=CTc/postgres
 template1 | postgres | UTF8     | Korean_Korea.949 | Korean_Korea.949 | =c/postgres          +
           |          |          |                  |                  | postgres=CTc/postgres
(4 rows)

List of databases

-----------+----------+----------+------------------+------------------+-----------------------

(4 rows)

[DATABASE/POSTGRESQL] 데이터베이스 목록 조회하기

■ 데이터베이스 목록을 조회하는 방법을 보여준다. ▶ 예제 코드 (SQL)


SELECT DATNAME FROM PG_DATABASE;

SELECT DATNAME FROM PG_DATABASE;

[DATABASE/POSTGRESQL] ALTER USER … WITH … 명령 : 사용자 권한 설정하기

■ ALTER USER … WITH … 명령을 사용해 사용자 권한을 설정하는 방법을 보여준다. ▶ 예제 코드 (SQL)


ALTER USER user1 WITH CREATEDB;
ALTER USER user1 WITH SUPERUSER;

ALTER USER user1 WITH CREATEDB;

ALTER USER user1 WITH SUPERUSER;

[DATABASE/POSTGRESQL] CREATE USER … WITH PASSWORD … 명령 : 사용자 생성하기

■ CREATE USER … WITH PASSWORD … 명령을 사용해 사용자를 생성하는 방법을 보여준다. ▶ 예제 코드 (SQL)


CREATE USER user1 WITH PASSWORD '1234';

CREATE USER user1 WITH PASSWORD '1234';

※ user1 : 사용자명

[DATABASE/POSTGRESQL] 우분투에서 도커로 PostgreSQL 데이터베이스 설치하기

■ 우분투에서 도커로 PostgreSQL 데이터베이스를 설치하는 방법을 보여준다. 1. CTRL + ALT + T 키를 눌러서 [터미널]을 실행한다. 2. [터미널]에서 아래 명령을

[DATABASE/POSTGRESQL] VACUUM FULL … 명령 : 테이블 데이터 정리하기

■ VACUUM FULL … 명령을 사용해 테이블 데이터를 정리하는 방법을 보여준다. ▶ 예제 코드 (SQL)


VACUUM FULL LIBRARY_DATA;

※ LIBRARY_DATA : 대상 테이블명

VACUUM FULL LIBRARY_DATA;

※ LIBRARY_DATA : 대상 테이블명

※ Vaccum은 PostgreSQL의 MVCC 구현 방법에서

[DATABASE/POSTGRESQL] – 연산자 : 두 날짜 사이 일수 계산하기

■ – 연산자를 사용해 두 날짜 사이의 일수를 계산하는 방법을 보여준다. ▶ 예제 코드 (SQL)


SELECT '2023-10-18'::DATE - '2023-01-01'::DATE;

SELECT '2023-10-18'::DATE - '2023-01-01'::DATE;

[DATABASE/POSTGRESQL] EXTRACT 함수 : 날짜 값에서 요일 구하기 (월요일-일요일)

■ EXTRACT 함수를 사용해 날짜 값에서 요일을 구하는 방법을 보여준다. (월요일-일요일) ▶ 예제 코드 (SQL)


SELECT
    CASE EXTRACT(ISODOW FROM DATE '2023-02-20')
	     WHEN 1 THEN '월'
	     WHEN 2 THEN '화'
	     WHEN 3 THEN '수'
	     WHEN 4 THEN '목'
	     WHEN 5 THEN '금'
	     WHEN 6 THEN '토'
	     WHEN 7 THEN '일'
    END AS WEEK_DAY;

SELECT

CASE EXTRACT(ISODOW FROM DATE '2023-02-20')

WHEN 1 THEN '월'

WHEN 2 THEN '화'

WHEN 3 THEN '수'

WHEN 4 THEN '목'

WHEN 5 THEN '금'

WHEN 6 THEN '토'

WHEN 7 THEN '일'

END AS WEEK_DAY;

[DATABASE/POSTGRESQL] NOW 함수 : 특정 시간대의 현재 시간 구하기

■ NOW 함수를 사용해 특정 시간대의 현재 시간을 구하는 방법을 보여준다. ▶ 예제 코드 (SQL)


SELECT NOW() AT TIME ZONE 'Asia/Seoul';

/*
2023-06-30 23:33:22.146391
*/

SELECT NOW() AT TIME ZONE 'Asia/Seoul';

2023-06-30 23:33:22.146391

[DATABASE/POSTGRESQL] DATE_TRUNC 함수 : 날짜 값에서 특정 시간 부분까지 구하기

■ DATE_TRUNC 함수를 사용해 날짜 값에서 특정 시간 부분까지 구하는 방법을 보여준다. ▶ 예제 코드 (SQL)


SELECT DATE_TRUNC('day', NOW());

/*
2023-06-30 00:00:00+09 -- 날짜 단위 부분까지만
*/

SELECT DATE_TRUNC('hour', NOW());

/*
2023-06-30 23:00:00+09 -- 시간 단위 부분까지만
*/

SELECT DATE_TRUNC('minute', NOW());

/*
2023-06-30 23:29:00+09 -- 분 단위 부분까지만
*/

SELECT DATE_TRUNC('day', NOW());

2023-06-30 00:00:00+09 -- 날짜 단위 부분까지만

SELECT DATE_TRUNC('hour', NOW());

2023-06-30 23:00:00+09 -- 시간 단위 부분까지만

SELECT DATE_TRUNC('minute', NOW());

2023-06-30 23:29:00+09 -- 분 단위 부분까지만

[DATABASE/POSTGRESQL] SHOW TIMEZONE 명령 : 시간대 구하기

■ SHOW TIMEZONE 명령을 사용해 시간대를 구하는 방법을 보여준다. ▶ 예제 코드 (SQL)


SHOW TIMEZONE;

/*
Asia/Seoul
*/

SHOW TIMEZONE;

Asia/Seoul

[DATABASE/POSTGRESQL] EXTRACT 함수 : 날짜 값에서 연도/월/일/주차 수/날짜 수/유닉스 시간 구하기

■ EXTRACT 함수를 사용헤 날짜 값에서 연도/월/일/주차 수/날짜 수/유닉스 시간을 구하는 방법을 보여준다. ▶ 예제 코드 (SQL)


SELECT EXTRACT(YEAR FROM NOW());

/*
2023 -- 연도
*/

SELECT EXTRACT(MONTH FROM NOW());

/*
6 -- 월
*/

SELECT EXTRACT(DAY FROM NOW());

/*
30 -- 일
*/

SELECT EXTRACT(DOW FROM NOW());

/*
5 -- 금요일
*/

SELECT EXTRACT(WEEK FROM NOW());

/*
26 -- 주차 수
*/

SELECT EXTRACT(DOY FROM NOW());

/*
181 - 날짜 수
*/

SELECT EXTRACT(EPOCH FROM NOW());

/*
1688135124.14553 - 유닉스 시간
*/

SELECT EXTRACT(YEAR FROM NOW());

2023 -- 연도

SELECT EXTRACT(MONTH FROM NOW());

6 -- 월

SELECT EXTRACT(DAY FROM NOW());

30 -- 일

SELECT EXTRACT(DOW FROM NOW());

5 -- 금요일

SELECT EXTRACT(WEEK FROM NOW());

26 -- 주차 수

SELECT EXTRACT(DOY FROM NOW());

181 - 날짜 수

SELECT EXTRACT(EPOCH FROM NOW());

1688135124.14553 - 유닉스 시간

[DATABASE/POSTGRESQL] INTERVAL 키워드 : 일정 간격 더하기/빼기

■ INTERVAL 키워드를 사용해 일정 간격을 더하거나 빼는 방법을 보여준다. ▶ 예제 코드 (SQL)


SELECT NOW() + INTERVAL '10 year';

/*
2033-06-30 23:17:28.468788+09
*/

SELECT NOW() - INTERVAL '10 day';

/*
2023-06-20 23:17:09.008891+09
*/

SELECT NOW() + INTERVAL '10 year';

2033-06-30 23:17:28.468788+09

SELECT NOW() - INTERVAL '10 day';

2023-06-20 23:17:09.008891+09

[DATABASE/POSTGRESQL] TO_TIMESTAMP 함수 : 유닉스 시간에서 타임스탬프 값 구하기

■ TO_TIMESTAMP 함수를 사용해 유닉스 시간에서 타임스탬프 값을 구하는 방법을 보여준다. ▶ 예제 코드 (SQL)


SELECT TO_TIMESTAMP(1688134012.82159);

/*
2023-06-30 23:06:52.82159+09
*/

SELECT TO_TIMESTAMP(1688134012.82159);

2023-06-30 23:06:52.82159+09

[DATABASE/POSTGRESQL] INTERVAL 키워드 : 1일 간격 구하기

■ INTERVAL 키워드를 사용해 1일 간격을 구하는 방법을 보여준다. ▶ 예제 코드 (SQL)


SELECT INTERVAL '1 day';

/*
1 day
*/

SELECT INTERVAL '1 day';

1 day

[DATABASE/POSTGRESQL] CURRENT_DATE 키워드 : 현재 날짜 구하기

■ CURRENT_DATE 키워드를 사용해 현재 날짜를 구하는 방법을 보여준다. ▶ 예제 코드 (SQL)


SELECT CURRENT_DATE;

/*
2023-06-30
*/

SELECT CURRENT_DATE;

2023-06-30