1. 실행 계획 확인 --Synopsis EXPLAIN [ ( option [, ...] ) ] 쿼리문 EXPLAIN [ ANALYZE ] [ VERBOSE ] 쿼리문 option 자리에 사용할 수 있는 것들: ANALYZE [ boolean ] VERBOSE [ boolean ] COSTS [ boolean ] BUFFERS [ boolean ] TIMING [ boolean ] FORMAT { TEXT | XML | JSON | YAML } * 이후 설명에서 사용될 테스트 데이터는 아래의 "7. 테스트 데이터 생성" 부분 쿼리 확인 ...
1. 뷰(VIEW) 테이블의 사용 (생성, 삭제, 수정) -- 기본 생성 CREATE VIEW comedies AS SELECT * FROM films WHERE kind = 'Comedy'; -- 삭제 DROP VIEW comedies --Synopsis CREATE [ OR REPLACE ] VIEW name [ ( column_name [, ...] ) ] AS query -- or CREATE VIEW name [ ( column [, ...] ) ] AS query [ WITH [ CASCADE | LOCAL ] CHECK OPTION ] 2. 뷰(VIEW)의 개념 및 특징 정의된 쿼리를 실행시켜 가상의 테이블 형태로 보여주며 테이블을 조회하는 것과 같은 방식으로 조회가 가능하다. VIEW는 물리적으로 생성되지 않는다. 복잡한 쿼리를 단순화시키거나 반복된 쿼리 작업을 효율적으로 처리할 수 있게 해 준다. VIEW에 참조된 쿼리는 호출 시 매번 새로 실행되기에 실시간 결과물을 조회할 수 있다. CREATE OR REPLACE VIEW로 VIEW를 수정할 시, 완전히 일치하는 컬럼 셋을 조회하는 쿼리로만 대체가 가능하다. (같은 컬럼명과 데이터타입) Schema 명을 명시적으로 작성하면 해당 Schema에, 아니라면 현재 Schema에 생성된다. View, Table, Sequence, Index는 한 스키마에 중복된 명칭을 가질 수 없다. VIEW 결과물은 수정이 불가능하다. 테이블의 전체 컬럼 및 정보를 직접적으로 노출시키지 않은 채로 사용이 가능하다. 3. 주의사항 3-1. READ-ONLY VIEW 자체에 insert, update, delete를 실행할 수 없다. ...
1. CREATE TABLE AS 사용 -- 기본 CREATE TABLE films_recent AS SELECT * FROM films WHERE date_prod >= '2002-01-01'; --Synopsis CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE table_name [ (column_name [, ...] ) ] [ [ WITH | WITHOUT ] OIDS ] AS query 2. CREATE TABLE AS 옵션 - TEMPORARY / TEMP 임시 테이블로 생성되며 세션이 종료될 시 삭제된다. - WITH OIDS / WITHOUT OIDS ...
1. 시퀀스(Sequence)의 사용 1-1. 생성, 삭제, 조회 -- 101부터 시작하는 기본 시퀀스 생성 CREATE SEQUENCE serial START 101; -- 시퀀스 다음값 조회 SELECT nextval('serial'); -- 시퀀스 현재값 조회 select currval('serial'); -- 시퀀스 삭제 DROP SEQUENCE serial; -- 시퀀스로 INSERT하기 INSERT INTO distributors VALUES (nextval('serial'), 'nothing'); -- COPY FROM 후에 시퀀스 시작값 변경하기 BEGIN; COPY distributors FROM 'input_file'; SELECT setval('serial', max(id)) FROM distributors; END; -- Synopsis CREATE [ TEMPORARY | TEMP ] SEQUENCE [ IF NOT EXISTS ] 이름 [ AS 자료형 ] [ INCREMENT [ BY ] 증가값 ] [ MINVALUE 최소값 | NO MINVALUE ] [ MAXVALUE 최대값 | NO MAXVALUE ] [ START [ WITH ] 시작값 ] [ CACHE 캐시 ] [ [ NO ] CYCLE ] [ OWNED BY { 테이블이름.칼럼이름 | NONE } ] 1-2. 사용 중인 시퀀스 확인 select n.nspname as sequence_schema, c.relname as sequence_name, u.usename as owner from pg_class c join pg_namespace n on n.oid = c.relnamespace join pg_user u on u.usesysid = c.relowner where c.relkind = 'S' and u.usename = current_user; 2. 시퀀스 생성시 상세 옵션 - TEMPORARY or TEMP ...
1. ROLE 1-1. ROLE 생성 -- 기본 CREATE ROLE jonathan LOGIN; -- 비밀번호 포함 CREATE USER davide WITH PASSWORD 'jw8s0F4'; -- 권한 포함 CREATE ROLE admin WITH CREATEDB CREATEROLE; -- 사용 기한 포함 CREATE ROLE miriam WITH LOGIN PASSWORD 'jw8s0F4' VALID UNTIL '2005-01-01'; -- 삭제 DELETE ROLE miriam; -- Synopsis CREATE ROLE name [ [ WITH ] option [ ... ] ] where option can be: SUPERUSER | NOSUPERUSER | CREATEDB | NOCREATEDB | CREATEROLE | NOCREATEROLE | INHERIT | NOINHERIT | LOGIN | NOLOGIN | REPLICATION | NOREPLICATION | BYPASSRLS | NOBYPASSRLS | CONNECTION LIMIT connlimit | [ ENCRYPTED ] PASSWORD 'password' | PASSWORD NULL | VALID UNTIL 'timestamp' | IN ROLE role_name [, ...] | IN GROUP role_name [, ...] | ROLE role_name [, ...] | ADMIN role_name [, ...] | USER role_name [, ...] | SYSID uid 1-2. ROLE 이란? CREATE ROLE은 PostgreSQL database cluster에 새로운 ROLE을 추가한다. ROLE은 데이터베이스 object, 권한을 가질 수 있는 엔티티이다. ROLE은 사용방법에 따라 USER, GROUP 혹은 둘다로 간주될 수 있다. CREATEROLE 권한이 있어야지만 사용 가능하다. ALTER ROLE, DELETE ROLE을 통해 권한을 수정, 삭제 가능하다. 1-3. ROLE 권한별 특징 SUPERUSER - 로그인을 제외한 모든 권한 포함 (ex. Role 생성 및 권한 부여) LOGIN - 데이터베이스에 로그인하기 위한 권한 PASSWORD - 로그인 비밀번호 설정 CREATEDB - 데이터베이스 생성 CREATEROLE - ROLE 생성/삭제/수정 REPLICATION - REPLICATION 권한 CONNECTIONLIMIT - 데이터베이스 접속 카운트 INHERIT - ROLE 권한들 상속 2. USER 2-1. USER 생성 -- 기본 CREATE USER jonathan; -- 비밀번호 추가 CREATE USER davide WITH PASSWORD 'jw8s0F4'; -- 만료기한 추가 CREATE USER miriam WITH PASSWORD 'jw8s0F4' VALID UNTIL '2005-01-01'; -- 권한 추가 CREATE USER manuel WITH PASSWORD 'jw8s0F4' CREATEDB; --Synopsis CREATE USER name [ [ WITH ] option [ ... ] ] where option can be: SYSID uid | CREATEDB | NOCREATEDB | CREATEUSER | NOCREATEUSER | IN GROUP groupname [, ...] | [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password' | VALID UNTIL 'abstime' 2-2. USER란? CREATE ROLE은 PostgreSQL database cluster에 새로운 User을 추가한다. CREATEUSER 권한이 있어야지만 사용 가능하다. 3. GROUP 3-1. GROUP 생성 --기본 CREATE GROUP staff; --유저 추가 CREATE GROUP marketing WITH USER jonathan, david; --그룹 삭제 DROP GROUP staff; -- Synopsis CREATE GROUP name [ [ WITH ] option [ ... ] ] where option can be: SYSID gid | USER username [, ...] 3-2. GROUP이란? CREATE GROUP은 USER 그룹을 생성한다. SUPERUSER 권한이 있어야지만 생성가능하다. 데이터베이스의 cluster 레벨에 접근 가능하기 위해 GROUP, USER, ROLE은 모두 cluster단에서 정의되어 있다. 4. ROLE, USER, GROUP 차이 ROLE은 Postgresql Database 관련 권한들을 모아 놓은 것으로, 8.1버전부터 USER와 GROUP의 개념이 ROLE로 통합되었다. 현재 버전에서는 USER와 ROLE의 기능은 동일하며, USER는 login 권한이 default, ROLE은 login 권한을 별도로 부여해야 하는 차이점만 있다. CREATE GROUP의 경우 PostgreSQL의 SQL 표준에는 존재하지 않으며, ROLE과 비슷한 개념을 가지고 있다. 참고 ...