PostgreSQL에서 제공하는 DATE_PART 함수에 대해 알아보자.

 

▷ 구문

DATE_PART ('field', source)

- field : year, month, day 와 같은 날짜/시간 형태의 문자열이다.

 field 부분에서 사용이 가능한 값들은 아래와 같다.

  • century
  • decade
  • year
  • month
  • day
  • hour
  • minute
  • second
  • microseconds
  • milliseconds
  • dow
  • doy
  • epoch
  • isodow
  • isoyear
  • timezone
  • timezone_hour
  • timezone_minute

- soucre : 실제 날짜/시간 값이다.


▷ 예제1) DATE_PART() 함수 기본 사용법

select date_part('century', now());
select date_part('decade', now());
select date_part('year', now());
select date_part('month', now());
select date_part('day', now());
select date_part('hour', now());
select date_part('minute', now());
select date_part('second', now());
select date_part('microseconds', now());
select date_part('milliseconds', now());
select date_part('dow', now());
select date_part('doy', now());
select date_part('epoch', now());
select date_part('isodow', now());
select date_part('isoyear', now());
select date_part('timezone', now());
select date_part('timezone_hour', now());
select date_part('timezone_minute', now());

 

예제를 쉽게 작성하기 위해 now()를 사용했지만 날짜/시간 형식만 맞춰주면 뭐든 상관없다.


참고 : https://www.postgresqltutorial.com/postgresql-date_part/

반응형

PostgreSQL에서 제공하는 EXISTS 연산자에 대해 알아보자.

 

▷ 구문

EXISTS (subquery)
NOT EXISTS (subquery)

 

EXISTS 연산자는 영어 의미 그대로 해당 subquery에서 반환하는 값이 존재하는지의 여부를 판단해주는 역할을 한다.

즉, 특정 조건의 일치 여부에 대해 true/false를 반환하는 것이다.

 

이 EXISTS 연산자를 사용하는 이유는 성능이 좋기 때문이다.

예를 들어 보통 값이 하나라도 존재하는지를 확인하고자 한다면 아래와 같은 쿼리문을 사용하고는 했을것이다.

-- count(*) > 0 인지 체크
select count(*) from mine where name = 'record'

 

데이터가 적을때는 상관 없지만 만약 많은 데이터를 가진 테이블에서 count를 사용한다면 쿼리의 성능이 매우 떨어진다.

 

이때 EXISTS를 사용해주면 매우 좋은 이유가, EXISTS는 조회 되는 데이터가 여러개가 있다 하더라도 존재 여부를 판단하는 연산자 이기 때문에 해당 쿼리의 값이 하나라도 반환되면 바로 연산이 종료되어 성능상 매우 뛰어나다.

 

이제 어느정도 알아봤으니 구문을 보면 사용하는데 어렵지는 않겠지만 예제를 통해 좀 더 접해보자.


▷ 예제1) EXISTS 기본 사용법

SELECT it_name, mine_name
FROM mine m
WHERE EXISTS (
	SELECT 1
	FROM record r
	WHERE r.t_id = m.t_id
	  AND r.read > 11 )

 

사용법은 구문처럼 매우 간단하다 EXISTS 연산자 안에 서브쿼리를 사용하여 조건을 입력하면 되는데, 이 때 값이 하나라도 존재하면 연산이 종료되는 것이다.

 

이때 굳이 SELECT 1 을 해주는 이유는 사실 무슨 값을 넣어주던 상관없기때문에 그냥 형식상 1을 넣어주는것이다.

 

▷ 예제2) NOT EXISTS 기본 사용법

SELECT it_name, mine_name
FROM mine m
WHERE NOT EXISTS (
	SELECT 1
	FROM record r
	WHERE r.t_id = m.t_id
	  AND r.read < 11 )

 

EXISTS 연산자 앞에 NOT만 붙여준것으로 똑같이 작동하나 의미만 반대가 된다.

즉, 값이 존재하지 않을 경우 연산이 종료되는 것이다.


참고 : https://www.postgresql.org/docs/10/functions-subquery.html

 

9.22. Subquery Expressions

9.22. Subquery Expressions 9.22.1. EXISTS 9.22.2. IN 9.22.3. NOT IN 9.22.4. ANY/SOME 9.22.5. ALL 9.22.6. Single-row Comparison This section describes the …

www.postgresql.org

반응형

PostgreSQL 에서 따로 테이블에 ALTER 구문을 사용하여 제약조건을 추가 및 삭제하는 방법에 대해 알아보자.

 

▷ 구문

ALTER TABLE 테이블명 ADD CONSTRAINT 제약조건명 제약조건
ALTER TABLE 테이블명 DROP CONSTRAINT 제약조건명

 

예제를 통해 더 쉽게 접해보자.


▷ 예제1) 제약조건 추가하기

ALTER TABLE mine ADD CONSTRAINT mine_record_pk PRIMARY KEY(record);
ALTER TABLE it ADD CONSTRAINT it_record_fk FOREIGN KEY (record) REFERENCES mine (record);

PRIMARY KEY 와 FOREIGN KEY 제약조건 뿐만아니라 다른 제약조건도 추가할 수 있다.

 

▷ 예제2) 제약조건 삭제하기

ALTER TABLE mine DROP CONSTRAINT mine_record_pk;
ALTER TABLE it DROP CONSTRAINT it_record_fk;

여기까지 제약조건을 추가하고 삭제하는 방법에 대해 알아봤는데,

사실 제목에는 변경이라는 말을 써놓긴 했지만 변경하는 방법은 따로 존재하지않고 제약조건을 삭제하고 다시 추가하는 방법밖에 없다.

ALTER TABLE mine DROP CONSTRAINT mine_record_pk;
ALTER TABLE mine ADD CONSTRAINT mine_record_new_pk PRIMARY KEY(record);

ALTER TABLE it DROP CONSTRAINT it_record_fk;
ALTER TABLE it ADD CONSTRAINT it_record_new_fk FOREIGN KEY (record) REFERENCES mine (record);
반응형

PostgreSQL에서 제공하는 format 함수에 대해 알아보자.

 

▷ 구문

FORMAT(format_string [, format_arg [, ...] ])

- format_string

 format() 함수 결과의 형식을 지정하는 문자열이며, %[position][flags][width]type 의 형식을 가지고 있다.

 position : 숫자형식으로 표현하며 가져올 인수의 인덱스이다. 생략이 가능하며 생략 시 format_arg에 지정된 순서대로 할당된다.

 flags : width 표현에 사용된 숫자에 -를 붙일 경우 왼쪽 정렬이고, 생략 시 오른쪽 정렬이다.

 width : 문자열을 출력할 공간을 지정하며, 결과 문자열을 채우는 데 필요한 공백으로 왼쪽 또는 오른쪽을 채울 수 있다. 다음 함수 인수를 너비로 사용하려면 별표(*) 또는 n번째 함수 인수를 너비로 사용하려면 *n$ 형식으로 사용된다.

 type : 필수 값이며 출력형식 변환 유형을 지정한다.

      ●   - 인수 값을 문자열로 형식화한다. NULL은 빈 문자열로 처리된다.

      ●  - 인수 값을 SQL 식별자로 취급한다. NULL일 경우 오류가 발생한다.

      ●  - 인수 값을 SQL 리터럴로 취급한다. NULL을 그대로 출력한다.

 보통 동적 SQL문을 구성 하기 위해서 I 와 L을 자주 사용하고는 한다.

 

- format_arg

 format() 함수에 전달하는 인수들을 의미한다.


이제 구문에 대해 알아봤으니 쉽게 접근하기 위해 예제를 통해 알아보도록 하자.

 

▷ 예제1) 단순 형식의 format() 함수 기본 사용

-- type : s
-- result : Hello, mine-it-record
SELECT FORMAT('Hello, %s','mine-it-record');
SELECT FORMAT('Hello, %s-%s-%s','mine','it','record');

-- result : full_name (last_name, first_name)
SELECT FORMAT('%s, %s',last_name, first_name) full_name 
FROM customer;

구문에서 나온것 처럼 필수값인 type만 지정해두고 type은 그중에서 s를 지정해 문자열을 인수로 넘겨준것이다.

'Hello, %s' 와 'Hello, %s-%s-%s' 부분이 format_string 즉, 출력되는 영역이고

나머지 뒤에 선언해놓은 문자열들은 format_arg 즉, %s에 전달할 인수들을 의미한다.

 

-- type : I
-- ALTER sequence record.cm.seq_mine_sn OWNER TO mine;
SELECT FORMAT(
  'ALTER sequence %I.%I.%I OWNER TO %I;',
  sequence_catalog,
  sequence_schema,
  sequence_name,
  'mine'  -- or another just put it in quotes
)
FROM information_schema.sequences

사실 %s와 %I의 큰 차이는 잘 모르겠으나 단순 select 구문에서 사용할 결과값이 아닌,

insert, alter 등의 쿼리문을 생성할때는 I를 많이 사용하는것 같다.

 

-- type : L
-- result : INSERT INTO level VALUES('HARD')
SELECT format('INSERT INTO %I VALUES(%L)', 'level', 'HARD');

-- compare result
-- result : INSERT INTO level VALUES(HARD)
SELECT format('INSERT INTO %I VALUES(%s)', 'level', 'HARD');

-- result : INSERT INTO level VALUES("HARD")
SELECT format('INSERT INTO %I VALUES(%I)', 'level', 'HARD');

-- result : INSERT INTO level VALUES('HARD')
SELECT format('INSERT INTO %I VALUES(%L)', 'level', 'HARD');

-- result : INSERT INTO "Foo bar" VALUES("O'Reilly")
SELECT format('INSERT INTO %I VALUES(%I)', 'Foo bar', E'O\'Reilly');

-- result : INSERT INTO "Foo bar" VALUES('O''Reilly')
SELECT format('INSERT INTO %I VALUES(%L)', 'Foo bar', E'O\'Reilly');

확실히 결과를 보면 %s는 values안에는 사용할 수 없을것같고 I나 L을 사용하면 될것같은데

$L 과 $I 사이에서도 표현되는 부분에 대해서 확실히 다른 모습을 볼 수 있다.

 

▷ 예제2) type 속성의 width 와 -flag 옵션 사용

-- result : |       foo|
SELECT format('|%10s|', 'foo');

-- result : |foo       |
SELECT format('|%-10s|', 'foo');

-- use width option : *
-- result : |       foo|
SELECT format('|%*s|', 10, 'foo');

-- result : |foo       |
SELECT format('|%*s|', -10, 'foo');

-- result : |foo       |
SELECT format('|%-*s|', 10, 'foo');

-- result : |foo       |
SELECT format('|%-*s|', -10, 'foo');

-- use width position options
-- result : |       bar|
SELECT format('|%*2$s|', 'foo', 10, 'bar');

-- use postion and width position options
-- result : |       foo|
SELECT format('|%1$*2$s|', 'foo', 10, 'bar');

위 구문에서 설명한것처럼 -flag 옵션은 width의 숫자 앞에 - 가 붙어야하는데 width 옵션 중에서 *을 사용함으로써 인수를 받아 사용한 것이다.

그리고 width 옵션중 n번째 인수를 사용하는 방법에 대해 하단 예제에 나와있다.

 

▷ 예제3) position 옵션 사용

-- result : Testing three, two, one
SELECT format('Testing %3$s, %2$s, %1$s', 'one', 'two', 'three');

-- result : Testing three, two, three
SELECT format('Testing %3$s, %2$s, %s', 'one', 'two', 'three');

position 옵션을 사용할 경우 위치를 지정해주는데 두번째 쿼리문처럼 생략해줄 경우 그 위치에 맞는 인수가 할당된다.


여기까지 format 함수에 대해 알아봤는데, 이 format 함수는 function에서 많이 사용되고는 한다.

 

동적 쿼리를 만들어 자동으로 실행되게끔 하는 function을 만들어 사용할때 사용되는데,

사용법은 단순히 위 예제들처럼 FORMAT 함수를 사용하여 쿼리문을 만들고 앞에 EXECUTE를 붙여주기만 하면되기 때문에 아래 구문만 보고 마치도록 하겠다.

 

▷ 구문

EXECUTE FORMAT(format_string [, format_arg [, ...] ])

참고 : https://www.postgresql.org/docs/10/functions-string.html

 

9.4. String Functions and Operators

9.4. String Functions and Operators 9.4.1. format This section describes functions and operators for examining and manipulating string values. Strings in …

www.postgresql.org

반응형

PostgreSQL에서 데이터베이스 스키마별 들어있는 컬럼, 함수, 시퀀스, 테이블 스키마 등 여러 정보를 조회할 수 있는데,

이는 INFORMATION_SCHEMA를 통해 조회가 가능하다.

 

물론 저걸 그대로 사용할수는 없고 뒤에 목적에 따라 하나씩 붙여주며 사용한다.

 

하나하나 예제를 통해 알아보자.


▷ 예제1) 함수 목록 가져오기

select * from INFORMATION_SCHEMA.ROUTINES;
-- select * from INFORMATION_SCHEMA.ROUTINES where routine_type = 'FUNCTION';

이런식으로 사용을 해보면 데이터들이 조회되며 routine_type 더 확실하게 하기 위해서 function인 녀석들만 조건을 걸어서 조회하면된다.

 

결과 데이터에서 함수들이 속한 스키마와 함수이름 등의 각종 정보를 조회할 수 있다.

 

▷ 예제2) 테이블 목록 가져오기

select * from INFORMATION_SCHEMA.TABLES;

테이블의 스키마 등 테이블 목록 데이터를 조회할 수 있다.

 

▷ 예제3) 시퀀스 목록 가져오기

select * from INFORMATION_SCHEMA.SEQUENCES;

시퀀스의 카탈로그, 스키마명, 시퀀스 설정값 등의 데이터를 조회한다.

 

▷ 예제4) 컬럼 목록 가져오기

https://mine-it-record.tistory.com/379

 

[PostgreSQL] 테이블의 컬럼 정보 확인_INFORMATION_SCHEMA.COLUMNS (ft. 특정 컬럼 존재 여부 확인)

PostgreSQL에서 특정 테이블의 컬럼 목록과 특정 컬럼의 존재 여부를 확인하는 방법에 대해 알아보자. PostgreSQL의 구조를 보면 알겠지만 데이터베이스 내 schema 별로 테이블을 가지고 있는데 이 모든

mine-it-record.tistory.com


예제들을 보면 알겠지만 본문 시작부분에서 설명한 것 처럼 INFORMATION_SCHEMA 뒤에 필요한 정보가 있는 곳을 조회하면 해당 종류별 데이터들을 조회할 수 있다.

 

INFORMATION_SCHEMA 에서 조회할 수 있는 데이터가 많기 때문에 예제로 전부 다룰수는 없고 아래 문서를 참고해서 사용해보자.

 

참고 : https://www.postgresql.org/docs/10/information-schema.html

 

Chapter 36. The Information Schema

Chapter 36. The Information Schema Table of Contents 36.1. The Schema 36.2. Data Types 36.3. information_schema_catalog_name 36.4. administrable_role_authorizations 36.5. applicable_roles 36.6. attributes …

www.postgresql.org

반응형

PostgreSQL에서 배열을 다룰때 사용하는 연산자에 대해 알아보자.

특히 배열과 배열의 비교 또는 배열에 추가적인 값을 넣어줄때 많이 사용하게 될 것이다.

 

PostgreSQL Document에 자세한 설명이 나와있으니 표를 가져와 정리해 두고자 한다.

 

▷ PostgreSQL Document Array Operators

표현식 설명 예시 결과
= equal ARRAY[1.1,2.1,3.1]::int[] = ARRAY[1,2,3] t
<> not equal ARRAY[1,2,3] <> ARRAY[1,2,4] t
< less than ARRAY[1,2,3] < ARRAY[1,2,4] t
> greater than ARRAY[1,4,3] > ARRAY[1,2,4] t
<= less than or equal ARRAY[1,2,3] <= ARRAY[1,2,3] t
>= greater than or equal ARRAY[1,4,3] >= ARRAY[1,4,3] t
@> contains ARRAY[1,4,3] @> ARRAY[3,1,3] t
<@ is contained by ARRAY[2,2,7] <@ ARRAY[1,7,4,2,6] t
&& overlap (have elements in common) ARRAY[1,4,3] && ARRAY[2,1] t
|| array-to-array concatenation ARRAY[1,2,3] || ARRAY[4,5,6] {1,2,3,4,5,6}
|| array-to-array concatenation ARRAY[1,2,3] || ARRAY[[4,5,6],[7,8,9]] {{1,2,3},{4,5,6},{7,8,9}}
|| element-to-array concatenation 3 || ARRAY[4,5,6] {3,4,5,6}
|| array-to-element concatenation ARRAY[4,5,6] || 7 {4,5,6,7}

 

표현식과 설명 예시만 봐서는 사실 잘 모르는 부분이 있을수 있다.

좀 더 자세히 설명을 해보자면,

 

평등 연산자 ( = , <>) 의 경우 배열의 원소 별 비교를 진행한다.

select
array[1,2,3] = array[1,2,4] as compare1, -- false
array[1,2,3] <> array[1,2,4] as compare2; -- true

 

순서 연산자( > , < , >= , <=)의 경우 같은 순서로 배열의 각 요소를 비교한다.

크기랑은 상관없이 배열의 서로 위치한 요소 쌍을 기반으로 비교를 진행하는 것이다.

select
array[1,2,5] >= array[1,2,4] as compare1, -- true
array[1,2,5] <= array[1,2,4,5] as compare2; -- false

 

포함 연산자(@> , <@)의 경우 각각의 고유한 요소가 다른 배열에도 존재하는 경우 배열은 다른 배열에 포함된다고 표시한다.

-- This reads as array['a', 'b', 'c'] contains array['a', 'b', 'b', 'a']
select array['a', 'b', 'c'] @> array['a', 'b', 'b', 'a'] as contains; -- true

-- this reads as array[1, 1, 4] is contained by array[4, 3, 2, 1]
select array[1, 1, 4] <@ array[4, 3, 2, 1] as is_contained_by; -- true

한 배열의 요소가 하나라도 다른 배열에 포함되어있으면 true를 반환하는것 같다.

 

중첩 연산자(&&)의 경우 서로 다른 배열에 공통 요소가 있는지를 확인해준다.

공통 요소가 있는 배열을 중첩 배열 이라고한다.

select
array[1, 2] && array[2, 3] as overlap1, -- true
array[1, 2] && array[3, 4] as overlap2; -- false

뭔가 포함 연산자 두개를 합친게 중첩연산자로 보인다.

 

결합 연산자(||)의 경우 말 그대로 두 배열을 합쳐주는건데 사실 그 외에도 좀 다양하게 쓰인다.

개인적으로는 두 배열이 아니라 두 요소를 합치는거라 배열에서도 사용이 가능한것이라 생각된다.

select
array[1, 2, 3] || array[4, 5, 6] as concat1, -- {1,2,3,4,5,6}
array[1, 2, 3] || array[[4,5,6],[7,8,9]] as concat2, -- {{1,2,3},{4,5,6},{7,8,9}}
3 || array[4, 5, 6] as concat3, -- {3,4,5,6}
array[1, 2, 3] || 7 as concat4, -- {1,2,3,7}

참고 : https://www.postgresql.org/docs/10/functions-array.html

 

9.18. Array Functions and Operators

9.18. Array Functions and Operators Table 9.48 shows the operators available for array types. Table 9.48. Array Operators Operator Description Example Result = equal …

www.postgresql.org

 

참고 : https://popsql.com/learn-sql/postgresql/how-to-compare-arrays-in-postgresql

 

How to Compare Arrays in PostgreSQL - PopSQL

 

popsql.com

반응형