Database/RDBMS

[PostgreSQL] 읽기 전용 유저 생성

특정 데이터베이스의 특정 스키마에 대한 읽기 전용 유저 생성 방법 1. PostgreSQL 접속 $ psql -U postgres 2. 유저 생성 create user with password ''; 3. 해당 데이터베이스 접속 \c db_1; 4. 해당 스키마에 대한 USAGE 권한을 부여 GRANT USAGE ON SCHEMA schema_1 TO ; 5. 해당 스키마의 모든 테이블에 대한 SELECT 권한을 부여 GRANT SELECT ON ALL TABLES IN SCHEMA schema_1 TO ; 5-1. 필요한 경우 시퀀스에 대한 SELECT 권한을 부여 GRANT SELECT ON ALL SEQUENCES IN SCHEMA schema_1 TO ; 6. 권한 조회 테이블 권한 조회 sele..

[PostgreSQL] 중복데이터 삭제 방법(중복데이터 하나만 남기고 제거)

테이블에 중복된 데이터가 있을 때 중복된 데이터에서 한개만 남기고 나머지를 제거해야 하는 이슈가 있었다. 여기서 중복이라는 것은 유일할 것이라고 생각하는 컬럼의 데이터가 여러개 있을 경우를 말한다. (애초에 pk 또는 unique key를 잡았다면 문제가 없었을 것이다.) DELETE FROM table_name where ctid in ( SELECT A.ctid FROM ( SELECT ctid, ROW_NUMBER() over (PARTITION BY column_name ORDER BY column_name desc) AS num FROM table_name ) A WHERE A.num > 1 ) 중복을 제거할 테이블에서 중복 체크할 컬럼을 PARTITION으로 묶어 ORDER BY 순으로 번호를..

[PostgreSQL] VIEW vs MVIEW(Materialized View)

Meterialized View(MVIEW) 란? MVIEW는 말 그대로 VIEW입니다. 하지만 일반 VIEW는 가상으로 존재하는 논리적 테이블이고, MVIEW는 실제 존재하는 TABLE처럼 물리적으로 존재하는 테이블입니다. 데이터가 물리적으로 존재하기 때문에 쿼리 실행 시간의 수행속도를 향상 시킬 수 있습니다. 그리고 SUM, MIN, MAX, AVG, COUNT등 그룹 함수를 미리 계산해 놓을 때 많이 사용하고, INDEX를 사용할 수 있다는 것도 장점이 됩니다. VIEW와 Materialized View의 차이점 VIEW Materialized View disk에 저장되지 않는 가상 테이블 기초 테이블을 물리적으로 복사를 하며 디스크에 저장 기초 테이블이 업데이트 될 때마다 VIEW도 업데이트 됨 ..

[PostgreSQL] JSON 컬럼에 특정 값만 추출해서 Index를 걸어보자

JSON 데이터를 PostgreSql에 저장할 때 data type을 json으로 하여 저장을 합니다. 프로젝트를 진행 하던 중 json 데이터의 특정 값만 찾아서 Index를 걸어야 하는 경우가 발생하여 처리 방법을 작성하고 성능까지 비교해보겠습니다. JSON 연산자 PostgreSql에서 data type이 json인 컬럼에 where절을 사용할 때 아래와 같이 쿼리를 사용합니다. -- info column(json data) : { "customer": "Lily Bush", "items": {"product": "Diaper","qty": 24}} -- json data에서 items의 product가 Diaper인 데이터 조회 쿼리 SELECT info ->> 'customer' AS custo..