Category: SQL

PostgreSQL, 테이블 이름 변경
이 뻔할 것 같은 것을 기록으로 남기는 이유는 명령어를 올바르게 입력하기 위해서이다. 통상적으로 insert, delete, select 같은 경우 스키마.테이블 이런 식으로 꼬박꼬박 써주고 있다. 하지만 이름을 변경할 때에는 한 번만 써주어야 한다.
```
ALTER TABLE cdm.measurement2 RENAME TO measurement
```
2020-11-24
일정한 패턴을 가진 항목 찾기
쉽게 말하면 ‘> 125 (150)’ 이런 식으로 구성되어 있는 것을 찾는 것이다.
```
WHERE 변수 LIKE '%>%(%)%'
```
저런 형식으로 입력해서 찾으면 된다.

그 중에서 괄호 안의 값을 찾는 방법이 필요했다. 내가 시도한 방법은 ‘(‘를 기준으로 문자열을 분리 후 2번째 항목을 선택한다. 그럼 ‘150)’ 형식으로 된다. 그 다음에는 ‘)’을 ”으로 REPLACE 시켜준다.
```
REPLACE(split_part(변수, '(', 2), ')', '')
```
일부가 3.56E-1 이런 형식을 하고 있었다. 불행 중 다행이라면 여백이 없었다는 것이다. 다음과 같이 실수형태로 변환시켰다.
```
CAST(SPLIT_PART(변수, 'E', 1) AS NUMERIC)*10^CAST(SPLIT_PART(변수, 'E', 2)
```
127.7. 과 같은 형식도 있었다. PostgreSQL에서는 뒤쪽에서 부터 단어를 선택하는 함수는 없는 것 같다. 하지만, 뒤집는 것은 있다. 일단 해당 값에서 ‘.’이 2개가 있는 것을 확인하는 것이 필요하다. 전체 길이에서 찾고자 하는 글자를 삭제한 후 글자 길이가 2개만큼 줄어드는 것을 찾으면 된다.
```
WHERE (LENGTH(변수)-LENGTH(REPLACE(변수, '.', ''))) = 2
```
‘.’이 2개인 것을 찾았으면 뒤집는다. 그럼 ‘.7.721’이다. 2번째 글자부터 끝까지 단어를 선택한 다음 다시 뒤집으면 된다.
```
REVERSE(SUBSTR(REVERSE(변수), 2, CHAR_LENGTH(변수)))
```
여백을 없애야 했다. 여백은 TRIM으로 없애는 것이 기본이나 별의 별 형식으로 공간을 잡아먹는 녀석들이 있다. 따라서 정규식이나 escape 문자를 이용한다.
```
REGEXP_REPLACE(변수, '\s+', '')
```
이것은 정규식 형태이다. 빈 줄은 다음과 같은 식으로 제거한다.
```
REGEXP_REPLACE(변수, E'[\n\r]+', '')
```
이렇게 실수형태가 아닌 것들을 다 제외시킨다. 나의 경우에는 이제 실수형태가 아닌 것들만 남아 있었다. 이를 확인하는 것은 매우 어려웠다. 이 정규식을 인터넷에 공개해주신 분(http://mwultong.blogspot.com/2006/09/regex-signed-float.html)께 대단히 감사. 추가로 자료를 찾아본 결과 정규식 표현에서 ~는 like 의미로 쓰인다고 한다. 그렇다고 like를 쓰면 동작하지는 않는다.
```
WHERE 변수 ~ '^[+-]?\d*(\.?\d*)$'
```
이렇게 하면 실수 형태인 자료들을 찾을 수 있다. 그런데 이런 값들은 정리해줄 필요가 없다.
```
select regexp_replace(변수, '^\D+', '') from ... 
  where 변수 ~ '^\D+[+-]?\d*(\.?\d*)$';
```
이렇게 하면 변수에 실수 앞 부분에 숫자 이외의 문자가 하나 이상 포함되어 있고 그 뒷 부분은 실수형태인 것들을 찾을 수 있다. 대표적이라면 > 10 같은 것이다. 이러면 실수형태 앞부분을 삭제할 수 있다.
```
select regexp_replace(변수, '\D+$', '') from ... 
  where 변수 ~ '^[+-]?\d*(\.?\d*)\D+$';
```
이러면 실수형태 숫자 뒤쪽에 문자가 있는 것들을 삭제할 수 있다. ’20 이하’ 같은 것 말이다.
- 여기까지 해보니 다음과 같은 순서대로 하면 될 것 같다. >100(125) 같은 형태의 자료는 우선적으로 처리해 준다.
- 쉼표를 제거한다.
- ‘..’을 처리한다.
- 공백과 빈 줄을 제거한다.
- 빈 칸이 입력된 것은 NULL로 바꾸어 준다.
- 앞에 숫자 아닌 거 들어간 것을 없애주고, 뒤에 숫자 아닌거 들어간 것을 없애준다.
- 실수 형태가 아닌 자료들을 확인해서 NULL로 바꾼다.
```
UPDATE .. SET 변수 = REGEXP_REPLACE(변수, '^\D+', '') ;
UPDATE .. SET 변수 = REGEXP_REPLACE(변수, '\D+$', '') ;
```
2020-11-07
Database 복사하기
떨어진 컴퓨터로 DB를 복사하는 방법은 pg_dump이지만, 같인 PC에서 복사할 때에는 명령어로 만들 수 있다.
```
CREATE DATABASE targetdb WITH TEMPLATE sourcedb;
```
2020-11-05
Session 종료하기
database의 이름을 바꾸는 작업을 하기 위해서는 연결되어 있는 세션을 모두 종료시켜야 한다. 컴퓨터를 재부팅하는 방법도 있지만, 그건 좋은 방법이 아니니 정식으로 하는 방법을 알아야 한다. 세션 종료는 PID 번호를 알아야 한다. database와 연관된 것을 알아야 하기 때문에 다음과 같이 검색한다.
```
SELECT pid, datname FROM pg_stat_activity;

  pid  |  datname
-------+-----------
   948 |
   946 |
  8890 | postgres
  8891 | cdm_build
 12702 | postgres
   944 |
   943 |
   945 |
```
이렇게 실행시키면 PID와 database 이름을 알 수 있다.
```
SELECT pg_terminate_backend(8891);
```
이렇게 하면 세션을 종료 시킬 수 있다.
2020-10-20