DB/PostgreSQL
-
[PostgreSQL] WITH ... AS, INSERT INTO .... ON CONFLICT .. DO UPDATE SET ..DB/PostgreSQL 2016. 12. 9. 21:04
여기서는 WITH ... AS 문을 사용하여 SELECT 결과가 없을 시 INSERT하는 쿼리, 로우가 1개 이상일 경우 UPDATE를 하고 그렇지 않으면 INSERT를 하는 쿼리 등을 설명합니다. 그리고 INSERT INTO .... ON CONFLICT .. DO UPDATE SET .. 문을 사용하여 보다 간편하게 로우가 없으면 INSERT, 존재하면 UPDATE를 하는 기능을 설명합니다.WITH ... AS여기서는 WITH ... AS 문에 대해 설명하기 보다 이를 사용해 다른 데이터베이스에 존재하는 upsert기능이나 select 결과가 없으면 insert를 하는 쿼리 등을 만들어 설명하겠습니다.upsert란 로우의 갯수가 1개 이상일 시, 사용자가 명시한 쿼리대로 레코드의 값을 update하고..
-
[PostgreSQL] 전체 데이터베이스, 스키마, 테이블 목록 조회DB/PostgreSQL 2016. 12. 8. 17:47
SELECT datname FROM pg_database -- 전체 데이터베이스 조회 SELECT datname FROM pg_database WHERE datistemplate = false -- 사용자가 생성한 데이터베이스만 조회 select nspname from pg_catalog.pg_namespace -- 현재 db의 전체 스키마 조회select tablename from pg_tables -- 전체 테이블 조회
-
[PostgreSQL] 컬럼 타입 변경시 에러 수정 방법 (text -> smallint)DB/PostgreSQL 2016. 11. 18. 09:21
만약 text 타입의 a라는 컬럼을 smallint로 타입을 변경한다고 할 때 아래와 같은 에러가 납니다.alter table test alter column a type smallint; -- 에러 발생 You might need to specify "USING a::smallint 텍스트 또는 varchar에서 정수로의 자동 변환을 지원하지 않습니다. (즉, 정수로 예상하는 함수에 varchar를 전달하거나 varchar 필드를 정수로 할당 할 수 없습니다.) 따라서 ALTER TABLE ...을 사용하여 형변환에 대해 명시적으로 지정해야합니다.alter table test alter column a type smallint using a::smallint;
-
[PostgreSQL] first(), last()DB/PostgreSQL 2016. 11. 16. 13:54
기타 sql에는 first()와 last() aggregate function을 제공하지만 postgresql에서는 직접 제공해주지 않습니다. 따라서 다른 sql에서 사용하는 first()와 last()를 사용하기 위해선 아래의 프로시저 함수를 추가해야 동일한 기능을 사용가능합니다.-- Drop a function if exists DROP FUNCTION IF EXISTS first_agg(anyelement, anyelement) CASCADE; -- Create a function that always returns the first non-NULL item CREATE OR REPLACE FUNCTION first_agg ( anyelement, anyelement ) RETURNS anyelem..
-
[PostgreSQL] ALTERDB/PostgreSQL 2016. 11. 3. 13:21
컬럼추가ALTER TABLE books ADD publication date;컬럼삭제ALTER TABLE books DROP publication;컬럼 default값 추가ALTER TABLE books ALTER COLUMN id SET DEFAULT nextval('books_idx');컬럼 default값 제거ALTER TABLE books ALTER id DROP DEFAULT;컬럼 NOT NULL 세팅ALTER TABLE books ALTER COLUMN id SET NOT NULL;컬럼 NOT NULL 제거ALTER TABLE books ALTER COLUMN id DROP NOT NULL;테이블 이름 변경ALTER TABLE books RENAME TO literature;컬럼명 변경ALTE..
-
[PostgreSQL] pg_hba.conf 설명DB/PostgreSQL 2016. 10. 28. 15:49
PostgreSQL의 pg_hba.conf 파일을 통해 외부접근에 대한 처리는 되도록 배재하는것이 좋습니다. 외부접근제어는 시스템 OS Kernel Level의 Packet Filter( iptable, ipchain...)를 이용하는 것이 PostgreSQL로의 불법로그인으로 인한PostgreSQL 인증체크처리 부하라던지 전체적인 성능에 이득이 됩니다. 즉, Kernel단에서 해당 IP나 IP Block에 대해서 접근제어를 해주는 것이 전체적인 성능에 좋습니다.pg_hba.conf는 PostgreSQL의 인증시스템관련 정보를 담고 있는 설정파일로 설정방법이 간단한편으로 실제적인 계정에 대한 정보는PostgreSQL의 카탈로그 테이블인 pg_user에서 관리하고 접근 Host나 Host의 데이터 전송방식..
-
[PostgreSQL] 대량의 Insert를 가장 빠르게 하는 방법DB/PostgreSQL 2016. 10. 24. 16:47
데이터를 insert한다고 할 때, 단순한 insert의 경우는 속도를 고려해야할 필요가 거의 없습니다. 하지만 10만, 100만, 혹은 억단위의 데이터를 postgresql에 insert한다고 하면 퍼포먼스를 고려해야 되는 이슈가 생깁니다.이러한 해당 이슈에 대한 많은 해결책이 있습니다.이상적인 해결책은 인덱스없이 로깅되지 않은 테이블을 import한 후 이 로깅을 변경하고 인덱스를 추가하는 것입니다. 아쉽게도 PostgreSQL의 9.4에서 로깅되지 않은 테이블을 로깅으로 변경하는 것을 지원하지 않습니다. 9.5 버전에서는 ALTER TABLE ... SET LOGGED와 같은 명령어를 추가해 사용자가 변경할 수 있도록 추가하였습니다.PostgreSQL Tuning Point만약 사용자가 bulk i..
-
[PostgreSQL] COPYDB/PostgreSQL 2016. 10. 21. 16:47
COPY -- copy data between a file and a tablecopy란 말 그대로 파일과테이블 사이 간 데이터를 복사를 해주는 function입니다.시놉시스COPY table_name [ ( column [, ...] ) ] FROM { 'filename' | STDIN } [ [ WITH ] ( option [, ...] ) ] COPY { table_name [ ( column [, ...] ) ] | ( query ) } TO { 'filename' | STDOUT } [ [ WITH ] ( option [, ...] ) ] where option can be one of: FORMAT format_name OIDS [ boolean ] DELIMITER 'delimiter_cha..
-
[PostgreSQL]postgres_fdwDB/PostgreSQL 2016. 9. 27. 17:23
dblink와 마찬가지로 물리적으로 떨어져 있는 원격 테이블에 접속하기 위해 사용합니다. 차이점은 아래에서 설명하겠습니다.CREATE EXTENSIONCREATE EXTENSION [ IF NOT EXISTS ] extension_name [ WITH ] [ SCHEMA schema_name ] [ VERSION version ] [ FROM old_version ]간단하게 설치하는 방법은 CREATE EXTENSION postgres_fdw 입니다. 또한 스키마를 지정하지 않으면 default로 public에 설치가 됩니다.여기서 유의해야 할 것은 dblink extension이 데이터베이스 내에 설치가 되면 (사용자가 정한 디비 스키마라던지, default인 public 스키마 라던지..) 지우지 않..