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과 비슷한 개념을 가지고 있다. 참고 ...
PostgreSQL 제약조건 (Constrant)란? 데이터베이스는 데이터 타입 외에 제약조건들을 통해 데이터의 무결성을 유지한다. 제약조건에는 여러 가지 종류가 있으며 DMBS에 마다 다양하지만, 이번 포스트는 PostgreSQL의 5가지 제약 조건들을 설명하겠다. 1. Primary Keys(PK) 2. Foreign Keys(FK) 3. Check 4. Not-null 5. Unique 1. Primary Keys (PK) Primary Keys는 테이블의 각 ROW를 구분하는 유니크한 컬럼 혹은 컬럼의 조합이다. Not null, Unique Constraints의 조합이다. 테이블인 단 1개의 PK만 가질 수 있다. PK 생성 시 Postgresql은 B-tree 인덱스를 자동으로 부여한다. B-tree 인덱스를 사용하기 때문에 컬럼의 조합으로 PK를 설정 시 순서가 중요하다. (상세 내용은 다음 포스트에서 확인이 가능하다.) [PostgreSQL] B-tree 인덱스의 원리 및 특징 ...
1. 트랜잭션(Transaction)이란? 트랜잭션은 데이터베이스에서 실행되는 일련의 작업들이다. 트랜잭션은 데이터베이스의 무결성 및 작업 간 충돌방지, 데이터 검증을 위해 필수적인 요소이다. 단순한 DML 작업의 롤백 용도뿐 아니라, 대용량 데이터 처리의 무결성, 에러발생 시, 여러 유저의 동시작업 등에서 사용된다. 2. 트랜잭션 적용 트랜잭션을 사용하는 커맨드 예제이다. --COMMIT 혹은 ROLLBACK으로 트랜잭션을 종료하지 않으면, 해당 업데이트 건은 데이터베이스에 적용되지 않는다. BEGIN; UPDATE accounts SET balance = balance - 100.00 WHERE name = 'Alice'; COMMIT; 트랜잭션 COMMIT 전에 다른 유저가 동일한 row의 balance를 업데이트하려고 한다면, 그전 트랜잭션이 commit 혹은 rollback 되는 것을 대기해야하며 이를 lock 상태라고 한다. (Lock의 개념 및 상세는 다음 포스트에 상세하게 정리되어 있다.) ...