Category: SQL

pgAdmin Master Password 없애기

www.pgadmin.org/docs/pgadmin4/development/master_password.html

일단 홈페이지 방법대로 하면 된다. 그런데 위치가 틀렸다.

나의 경우에는 C:\Program Files\pgAdmin 4\v4\web 에 있는 config.py파일을 수정했다.

2020-12-14
PostgreSQL DATABASE DROP 하기
현재 접속중인 사용자가 있더라도, 강제적으로 DATABASE를 DROP 시키는 방법이다. 다음 내용을 참고했다. pgAdmin으로 접속해 있으면, 이것이 계속 세션을 유지시키고 있으므로 꼭 종료시키고 실행한다.

How to drop a PostgreSQL database if there are active connections to it? – Stack Overflow
```
SELECT pg_terminate_backend(pg_stat_activity.pid) 
FROM pg_stat_activity 
WHERE pg_stat_activity.datname = 'cdm_build' 
AND pid <> pg_backend_pid();
```
2020-11-24
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