PostgreSQL 캐스팅 사용 방법 유용한 예제(숫자, 문자, 날짜 변환)

CAST 함수

-- 숫자 -> 문자
SELECT 'The number is ' || CAST(42 AS text);

-- 문자 -> 숫자
SELECT CAST('42' AS integer);

-- 문자 -> date
SELECT CAST('2022-07-23' AS date);

-- 문자 -> timestamp
SELECT CAST('2022-07-23 12:34:56' AS timestamp);

-- date -> 문자
SELECT CAST(date '2022-07-23' AS text);

-- timestamp -> 문자
SELECT CAST(timestamp '2022-07-23 12:34:56' AS text);

:: 오퍼레이터

:: 오퍼레이터를 사용하여 변환

SELECT text('42')::integer;

SELECT date('2022-07-23')::date;

SELECT to_timestamp('2022-07-23 12:34:56', 'YYYY-MM-DD HH24:MI:SS')::timestamp;

아래와 같이 간략히 할 수 있습니다.

SELECT '42'::integer;

SELECT '2022-07-23'::date;

SELECT '20220723 123456'::timestamp;
-- or
SELECT '2022-07-23 123456'::timestamp;
-- or
SELECT '2022-07-23 12:34:56'::timestamp;
-- 에러
SELECT '2022-07-2312:34:56'::timestamp;

 

예제

-- 문자열 숫자 변환 후 사칙연산
SELECT '10'::integer + '20'::integer; -- Result: 30

SELECT '2022-07-23'::date || ' is a great day!'; -- Result: '2022-07-23 is a great day!'

SELECT floor(35.12::numeric)::integer -- Result: 35

-- timestamp를 date로 변환 후 group by
SELECT date_trunc('day', created_at) as date, count(*)
FROM cars
GROUP BY date;

0, 1 숫자 데이터를 boolean으로 캐스팅하여 조건

with temp as(
select 'Car1' as car_name, 1 as is_sold
union all
select 'Car2' as car_name, 0 as is_sold
union all
select 'Car3' as car_name, 1 as is_sold
)
select * from temp where is_sold::boolean

-- Result
car_name|is_sold|
--------+-------+
Car1    |      1|
Car3    |      1|

timestamp를 Unix epoch 로 변환

SELECT extract(epoch from '2022-07-21 12:34:56'::timestamp)::bigint

-- Result
date_part |
----------+
1658406896|