Category: SQL

Schema와 Table 이름 확인하기
PostgreSQL에서 Table 이름을 확인하려면 \dt 명령어를 입력하라는 것을 쉽게 찾을 수 있다. 그런데 뭐가 문제인지 relation을 찾을 수 없다는 메시지만 나온다. 이럴 경우에 확인할 수 있는 방법이다.
```
SELECT * FROM pg_catalog.pg_tables WHERE schemaname != 'pg_catalog' AND schemaname != 'information_schema';
```
2022-10-14
partition by
특정 값을 기준으로 그룹으로 나눈 후 최소값이나 최대값 혹은 평균값 등을 구한다고 생각해보자. 아마 group by 로 구하면 되지 않을까 생각이 들것이다. 그런데 group by 로 하면 생각보다 문제가 풀리지 않는다. 이와 관련된 해결책을 찾다가 stackoverflow 에서 다음의 글을 찾았다. 여기에는 4가지 방법을 제시하고 있는데, 나는 이 중에서 group by 를 이용하지 않는 첫 번째 방법을 이용했다. 첫 번째를 선택한 이유는 코드가 짧기 때문이다. 😉

https://stackoverflow.com/questions/13325583/postgresql-max-and-group-by
```
medein=# select datetime, rain_type from (select datetime, rain_type, input_time, max(input_time) over (partition by datetime) from work.weather_long where rain_type is not null) as b where input_time = max order by datetime;
```
2022-06-23
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