Category: SQL

DEFAULT CURRENT_TIMESTAMP
시간을 입력받는 컬럼의 속성에서 DEFAULT CURRENT_TIMESTAMP 를 추가해 주면 자료가 입력되면서, 시간이 입력되는 구조로 동작한다. 굳이 쿼리에 넣어줄 필요가 없었던 부분이다.
```
# ADD COLUMN time2 timestamp without time zone DEFAULT CURRENT_TIMESTAMP;
```
2021-07-01
RESTful API를 이용하여 PostgreSQL 자료 입력
psycopg2 를 이용하면 편하게 PostgreSQL에 접근하여 자료를 다룰 수 있다. 편하게 이용할 수 있는 방법인 반면에 DB 접속 권한이 노출된다는 단점이 있다.

따라서, PostgreSQL이 설치된 서버에 RESTful API를 이용하여 자료를 전달하도록 한다. 보안 접속은 차차 구현해 보기로 하고, 기본형으로 시작한다.

찾아보면 더 좋은게 있을지도 모르겠다. 최근까지 업데이트 흔적이 있고, 스폰서가 있는 PostgREST를 이용해 보기로 했다. 예제는 다음을 참고했다.

https://postgrest.org/en/stable/tutorials/tut0.html

설치 파일을 다운 받는다.

https://github.com/PostgREST/postgrest/releases/tag/v7.0.1

tar.xz 파일이 이상하게도 압축이 안풀려서 Windows에서 풀어서 설치 파일을 복사해서 사용했다. 환경 설정 파일 없이 실행하면 친절하게 최소한의 옵션에 따라서 환경 설정 파일을 만들라고 한다. 사용자 계정, 계정의 비밀번호, DB 이름, 스키마 이름, role을 지정한다.
```
$ nano tutorial.conf
db-uri = "postgres://user:pass@localhost:5432/dbname"
db-schema = "public" 
db-anon-role = "postgres"
```
작성 후 실행한다. 적절하게 만들어졌다면 다음과 같이 실행된다. 기본적으로 3000 포트를 이용한다.
```
$ ./postgrest tutorial.conf
Attempting to connect to the database...
Listening on port 3000
Connection successful
```
curl을 이용해서 테이블을 조회하여 본다. tablename이라는 테이블 내용을 조회하고 싶다면 다음과 같이 실행한다.
```
$ curl http://server:3000/tablename
```
JSON 포맷으로 tablename 테이블에 자료를 입력(POST)하고 싶다면 다음과 같이 입력한다.
```
$ curl http://server:3000/tablename -X POST \
-H "Content-Type: application/json" \
-d '{"source_id": "200",
     "person_id": "15258756"}'
```
테이블 이름이 tablename이고, col1의 값이 1234인 열의 자료를 조회하고 싶다면 다음과 같이 한다. 연산자는 다음의 링크를 참조한다.

https://postgrest.org/en/v7.0.0/api.html
```
$ curl http://server:3000/tablename?col1=eq.1234
```
파이썬에서는 다음과 같이 한다. requests를 이용한다. POST는 다음과 같이 하면 된다.
```
>>> import requests
>>> requests.post('http://server:3000/tablename ', data={'col1': '1234'})
```
GET을 하는 방법은 2가지 방법이 있는 것 같은데 일단은 curl을 시도한 것과 유사한 형식으로 하면 된다. JSON 포맷으로 받으려면 다음과 같이 한다.
```
>>> tmp = requests.get('http://server:3000/tablename?col1=eq.1234').json()
>>> tmp[0]['col1'] 
```
2021-06-30
psycopg2 – DB 연결 및 INSERT 쿼리 보내기
내가 필요한 부분은 psycopg2 로 해결이 가능할 것 같아서 확인해 보기로 했다.

Python과 PostgreSQL을 연결하는 패키지는 여러가지가 있다고 하지만, 가장 최근까지 지원이 되고 있는 psycopg2 (https://pypi.org/project/psycopg2/)를 이용하는 것이 일반적이다고 한다.

PIP를 통해서 설치한다.
```
> pip3 install psycopg2
```
R에서와 비슷한 포맷으로 DB를 연결하면 된다.
```
>>> db=psycopg2.connect(host='ADDRESS', dbname='DBNAME', user='postgres', password='password', port=5432)
```
PostgreSQL은 auto-commit=on 이지만, psycopg2 의 경우 이 설정이 적용이 되지 않는다. 내가 구현해야 할 부분에서는 auto-commit이 더 편하므로 auto-commit을 적용하도록 한다.
```
>>> db.autocommit=True
```
psycopg2에서는 cursor라는 개념이 있고, cursor를 이용하여 SQL쿼리를 수행한다고 보면 되는 것 같다. Python에서는 문자와 숫자가 구별되어 있으나 psycopg2에서는 아니다. 숫자를 입력하고 싶어도 %s를 이용한다.
```
db.cursor().execute("INSERT INTO TABLE (col1 , col2) values (%s,  CURRENT_TIMESTAMP)", [110])
```
2021-06-07
모두를 위한 PostgreSQL
CDM이 담겨 있는 PostgreSQL을 제대로 알아보기 위하여 도서관에서 책을 빌렸다. 전반적으로 훑어보았는데, 이 책은 표지에 쓰여 있듯이 누구나 이해할 수 있는에 맞추어 내용이 구성되어 있었다.

내가 이용해 볼 수 있을 것 같은 부분만 골라서 기록해 둔다.

BETWEEN
```
a >= 1 and a <= 10 은 BETWEEN 1 and 10 으로 이용할 수 있다.
```
CASE WHEN THEN ELSE END. 쉽게 말해 ifelse를 사용할 경우에 이용한다.
```
CASE
 WHEN 조건1 THEN 결과1
 WHEN 조건2 THEN 결과2
 ELSE 결과3
END
```
SIMILAR TO는 표준 SQL을 따르며, POSIX 메타 문자(|, *, +, ?)를 이용할 수 있다.
LIKE는 %(문자열), _(문자 한 글자) 형식으로 이용할 수 있으며, ILIKE는 대소문자 구별이 없다.
LIKE는 ~~, NOT LIKE는 !~~, ILIKE는 ~~*, NOT ILIKE는 !~~*로 이용할 수 있다.

WHERE은 조건 집계 전에 적용하고, HAVING은 조건 집계 후에 이용한다.

우선 순위가 있으며, 높은 우선 순위 순서대로 다음과 같다.
FROM, WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY, LIMIT

UNION, INTERSECT, EXCEPT도 괜찮지만, 중복을 제거하지 않고, 일단 모아도 된다면 UNION ALL, INTERSECT ALL, EXCEPT ALL를 이용하는 것이 속도가 빠르다.

함수와 트리거를 이용해보자.
2021-05-25