TWpower Reading time ~1 minute

쉘 스크립트(bash)에서 대문자 소문자 간 변환하는 방법에 대한 정리


환경

  • Shell Script
  • Bash


변환

대문자 -> 소문자

  • POSIX standard
test="THIS is TeSt"
echo "$test" | tr '[:upper:]' '[:lower:]'
  • Bash over 4.0
test="THIS is TeSt"
echo "${test,,}"

소문자 -> 대문자

  • POSIX standard
test="THIS is TeSt"
echo "$test" | tr '[:lower:]' '[:upper:]'
  • Bash over 4.0
test="THIS is TeSt"
echo "${test^^}"


참고자료

TWpower Reading time ~1 minute

Post about converting between upper case letter and lower case letter in shell script(bash)


Environment and Prerequisite

  • Shell Script
  • Bash


Convert

Upper case -> Lower case

  • POSIX standard
test="THIS is TeSt"
echo "$test" | tr '[:upper:]' '[:lower:]'
  • Bash over 4.0
test="THIS is TeSt"
echo "${test,,}"

Lower case -> Upper case

  • POSIX standard
test="THIS is TeSt"
echo "$test" | tr '[:lower:]' '[:upper:]'
  • Bash over 4.0
test="THIS is TeSt"
echo "${test^^}"


Reference

TWpower Reading time ~1 minute

PostgreSQL에서 테이블의 행(Row) 개수와 테이블 크기(Size) 구해보자


환경

  • PostgreSQL


행(Row) 개수

  • 쿼리에서 COUNT(*)를 이용
select count(*) from {table_name};
  • pg_class를 통해 어림잡은 값 가져오기
select relname, reltuples from pg_class where relname='{table_name}';

select n.nspname as table_schema, c.relname as table_name, c.reltuples as rows
from pg_class c join pg_namespace n on n.oid = c.relnamespace
where c.relname='{table_name}' and c.relkind = 'r' and n.nspname not in ('information_schema','pg_catalog')
order by c.reltuples desc;


크기(Size)

  • pg_total_relation_size() 함수를 이용
select pg_size_pretty(pg_total_relation_size('{table_name}'));


하나의 쿼리로 행(Row) 개수와 크기(Size) 구하기

create or replace function count_rows_of_table(table_schema text, table_name text)
returns numeric
language plpgsql
as
$$
declare
 count numeric;
begin
 execute format('select count(*) from %s.%s', table_schema, table_name)
 into count;
 return count;
end;
$$;

select n.nspname as table_schema, c.relname as table_name, c.reltuples as estimated_row_count, count_rows_of_table(n.nspname, c.relname) as exact_row_count
from pg_class c join pg_namespace n on n.oid = c.relnamespace
where c.relkind = 'r' and n.nspname not in ('information_schema','pg_catalog')
order by c.reltuples desc;


참고자료

TWpower Reading time ~1 minute

Get table’s row count and size in PostgreSQL


Environment and Prerequisite

  • PostgreSQL


Row Count

  • Use COUNT(*) in query
select count(*) from {table_name};
  • Get estimated value using pg_class
select relname, reltuples from pg_class where relname='{table_name}';

select n.nspname as table_schema, c.relname as table_name, c.reltuples as rows
from pg_class c join pg_namespace n on n.oid = c.relnamespace
where c.relname='{table_name}' and c.relkind = 'r' and n.nspname not in ('information_schema','pg_catalog')
order by c.reltuples desc;


Size

  • Use pg_total_relation_size() function
select pg_size_pretty(pg_total_relation_size('{table_name}'));


Get row count and size using one query

create or replace function count_rows_of_table(table_schema text, table_name text)
returns numeric
language plpgsql
as
$$
declare
 count numeric;
begin
 execute format('select count(*) from %s.%s', table_schema, table_name)
 into count;
 return count;
end;
$$;

select n.nspname as table_schema, c.relname as table_name, c.reltuples as estimated_row_count, count_rows_of_table(n.nspname, c.relname) as exact_row_count
from pg_class c join pg_namespace n on n.oid = c.relnamespace
where c.relkind = 'r' and n.nspname not in ('information_schema','pg_catalog')
order by c.reltuples desc;


Reference

TWpower Reading time ~1 minute

두 datetime 사이의 날짜들을 가져오자


환경

  • Python


예제

  • 두 datetime 사이의 날짜들 가져오기
import datetime
start_date = datetime.datetime(2021, 11, 15)
end_date = datetime.datetime(2021, 11, 21)

dates = [(start_date + datetime.timedelta(days=day_delta)) for day_delta in range((end_date - start_date).days + 1)]

for date in dates:
    print(date)

2021-11-15 00:00:00
2021-11-16 00:00:00
2021-11-17 00:00:00
2021-11-18 00:00:00
2021-11-19 00:00:00
2021-11-20 00:00:00
2021-11-21 00:00:00
  • 두 datetime 사이의 날짜들을 포맷 변경해서 가져오기
import datetime
start_date = datetime.datetime(2021, 11, 15)
end_date = datetime.datetime(2021, 11, 21)

dates = [(start_date + datetime.timedelta(days=day_delta)).strftime("%Y/%m/%d") for day_delta in range((end_date - start_date).days + 1)]

for date in dates:
    print(date)

2021/11/15
2021/11/16
2021/11/17
2021/11/18
2021/11/19
2021/11/20
2021/11/21


참고자료