PostgreSQL 실전 노트

함수가 딱 없어서 자기 필드에서 날짜를 싹 빼버리고, 그 부분을 다른 필드 값을 더해주는 형태로 구현했다. 짱똘을 좀 굴려야 😂

UPDATE public.testxapi_result SET starttime = starttime - date_trunc('hour', starttime) + date_trunc('hour', created_at), endtime = endtime - date_trunc('hour', endtime) + date_trunc('hour', created_at)where serial='00:08:DC:5E:B6:9C';-- orderitem_id= 200SELECT * from public.testxapi_result where serial='00:08:DC:5E:B6:9C';

결과는,

<figure class=“kg-card kg-image-card”></figure>## 참고 사이트

<figure class=“kg-card kg-bookmark-card”><div class=“kg-bookmark-content”><div class=“kg-bookmark-title”>9.9. Date/Time Functions and Operators</div><div class=“kg-bookmark-description”>9.9.&nbsp;Date/Time Functions and Operators 9.9.1. EXTRACT, date_part 9.9.2. date_trunc 9.9.3. date_bin 9.9.4. AT TIME ZONE 9.9.5. Current Date/Time 9.9.6. Delaying …</div><div class=“kg-bookmark-metadata”><span class=“kg-bookmark-author”>PostgreSQL Documentation</span>``</div>``</div><div class=“kg-bookmark-thumbnail”></div></figure>> timestamp - interval → timestamp

Subtract an interval from a timestamp
timestamp '2001-09-28 23:00' - interval '23 hours' → 2001-09-28 00:00:00

PostgreSQL – WHERE 필터 in 사용법

PostgreSQL 조회 업데이트 시 filtering in 사용하는 방법을 살펴보자.

같은 것을 비교할 때는 그냥 = 키워드를 쓰면 되니, 고민할 것이 없는데 복수 개의 키워드 값과 동일한지 비교하려면 in 을 사용하는 것이 좋다.

볼 것도 없게 쉽게 예제로 파악할 수 있다. in('키워드', '키', ... ) 이렇게 사용하면 된다.

UPDATE public.testxapi_result 
SET is_deleted = '2'::integer 
WHERE serial in ('00:08:DC:75:5F:3F','00:08:DC:75:46:3A','00:08:DC:75:5C:B0');

간단하게 정리 끝.

참고 페이지

PostgreSQL 필터링 조회 (IN 연산자)

PostgreSQL JSONField 값 입력 방법

그냥 텍스트가 아니라 Query Editor 에서 넣으려고 하는데 “” 이렇게 주니깐 에러 발생

::jsonb 라는 키워드를 붙여줘야 한다.

UPDATE django.app_test_data_manager_testlog SET option_field = '{}'::jsonb;

이 예제의 option_field 의 선언은 다음과 같이 되어 있다.

<figure class=“wp-block-image size-full”></figure>

psycopg2 TextField 값 파일 저장 예제

Python psycopg2를 사용해서 TextField 값을 파일로 저장하는 방법을 알아보겠습니다. TextField는 PostgreSQL에서 긴 텍스트를 저장하는 데 사용하는 데이터 형식입니다. 예를 들어, 블로그 게시물의 내용이나 제품 설명을 저장할 수 있습니다.

Python 코드:

Python

import psycopg2

# PostgreSQL 연결 설정
conn = psycopg2.connect(
    host="localhost",
    port=5432,
    database="mydb",
    user="myuser",
    password="mypassword",
)

# 테이블에서 데이터 가져오기
cur = conn.cursor()
cur.execute("SELECT text FROM mytable")

# TextField 값 뽑기
for row in cur:
    text = row[0]

    # 파일에 저장
    with open("output.txt", "w") as f:
        f.write(text)

# 연결 종료
conn.close()

코드를 사용할 때는 주의하시기 바랍니다. 자세히 알아보기content_copy

설명:

이 예제에서는 다음과 같은 작업을 수행합니다.

1. PostgreSQL 연결을 설정합니다.
2. 테이블에서 데이터를 가져옵니다.
3. TextField 값을 뽑습니다.
4. 값을 파일에 저장합니다.
5. 연결을 종료합니다.

TextField 값 뽑는 방법:

TextField 값을 뽑으려면 다음 코드를 사용합니다.

Python

text = row[0]

코드를 사용할 때는 주의하시기 바랍니다. 자세히 알아보기content_copy

이 코드는 mytable 테이블의 text 열에서 값을 가져옵니다.

파일에 값 저장하는 방법:

파일에 값을 저장하려면 다음 코드를 사용합니다.

Python

with open("output.txt", "w") as f:
    f.write(text)

코드를 사용할 때는 주의하시기 바랍니다. 자세히 알아보기content_copy

이 코드는 output.txt 파일에 text 값을 씁니다.

예시:

다음은 mytable 테이블의 내용입니다.

text |
---- |
This is a blog post. |
This is a product description. |
... |

이 예시에서 mytable 테이블에는 두 개의 행이 있습니다. 첫 번째 행에는 블로그 게시물의 내용이 있고, 두 번째 행에는 제품 설명이 있습니다.

확장:

이 예제를 확장하여 다음과 같은 작업을 수행할 수 있습니다.

• TextField 값을 필터링하거나 정렬할 수 있습니다.
• TextField 값을 JSON 또는 CSV 형식으로 저장할 수 있습니다.
• TextField 값을 여러 파일에 저장할 수 있습니다.

PostgreSQL – 조건에 IN 활용

Where 구문에 같은 것 말고 리스트에 포함된 걸로 조사할 때 사용하는 IN 이 있다.

용례가 갑자기 생각이 안나서 찾아봄

PostgreSQL IN operator examples

https://www.postgresqltutorial.com/postgresql-tutorial/postgresql-in/“

Suppose you want to know the rental information of customer id 1 and 2, you can use the IN operator in the WHERE clause as follows:

`SELECT customer_id, rental_id, return_date `
`FROM rental `
`WHERE customer_id IN (1, 2) `
`ORDER BY return_date DESC;`

PostgreSQL – 필드 내 중복 값 제외 distinct

아래 글 참고

https://dog-developers.tistory.com/130

COLUMN_1의 값이 중복 값 존재 시 중복 값을 제거

SELECT 
	DISTINCT COLUMN_1 
FROM TABLE_NAME;

COLUMN_1 + COLUMN_2의 값이 중복 값 존재 시 중복 값을 제거

SELECT 
	DISTINCT COLUMN_1 , COLUMN_2
FROM TABLE_NAME;

추가 참고

https://redk.tistory.com/37 – Django ORM distinct()

PostgreSQL – 시간 차이를 초로 변환 SQL

PostgreSQL – 시간 차이를 초로 변환하는 구문이 필요하다.

필드 두개를 빼서, 그것을 초로 변환하면 되는데 PostgreSQL 구문으로 간단하게 처리하고 싶어서 검색

잘 안나오는데,

SELECT date_done, date_start, round(extract(epoch from (date_done::timestamp -date_start::timestamp ))::numeric) as diff
FROM TABLE
where id in ('201','200')
ORDER BY id ASC ;

epoch 함수, round 함수, extract 함수

초로 계산한 결과가 잘 나온다.

참고한 링크는 여기 https://tjdguqdl.tistory.com/38

ORM 중복 제거 – distinct

https://django-orm-cookbook-ko.readthedocs.io/en/latest/duplicate.html

>>> duplicates = User.objects.values(
    'first_name'
    ).annotate(name_count=Count('first_name')).filter(name_count__gt=1)
>>> duplicates
<QuerySet [{'first_name': 'John', 'name_count': 3}]>

여기 링크 글이 좋음 – https://unocoings.tistory.com/12