Today we will learn about most common string and date functions in postgresql.
String functions
Like we have several methods and functions in Javascript for manipulating strings we do have equivalent of them in postgres. Let's explore them.
1. CONCAT
CONCAT for building strings.
select
CONCAT(first_name, ' ', last_name) as full_name
from
users
where
first_name is not null
and last_name is not null;
2. LENGTH
LENGTH for getting the length of strings.
select
username,
LENGTH(username) as username_length
from
users
where
LENGTH(username) < 8
order by
LENGTH(username) asc;
3. LOWER
LOWER works like .toLowerCase() method in js.
select
email,
LOWER(email) as clean_email
from
users
where
email != LOWER(email);
4. INITCAP
INITCAP -> jaMeS -> James
select
first_name,
INITCAP(first_name) as clean_first_name
from
users;
4. TRIM
TRIM -> ' jaMeS' -> 'jaMeS'
select
first_name,
TRIM(first_name) as clean_first_name
from
users;
5. SUBSTRING and SPLIT_PART
select
email,
SPLIT_PART(email, '@', 2) as email_domain,
SUBSTRING(username from 1 for 3) as username_prefix
from
users;
-- Jacube@obilet.com obilet.com jau
Date functions
1. Current date/time functions
select
CURRENT_DATE as today,
CURRENT_TIMESTAMP as now,
NOW() as also_now;
-- today | now | also_now
-- ------------+-------------------------------+-------------------------------
-- 2025-08-07 | 2025-08-07 18:49:01.617884+00 | 2025-08-07 18:49:01.617884+00
2. Age calculations
select
username,
created_at,
AGE(CURRENT_DATE, created_at) as account_age
from
users;
-- username | created_at | account_age
-- ----------+---------------------+-------------------------
-- jaubry3q | 2024-12-23 11:29:46 | 7 mons 14 days 12:30:14
3. Date formatting for display
select
username,
TO_CHAR(created_at, 'Month DD, YYYY') as formatted_date,
TO_CHAR(created_at, 'Day') as signup_day
from
users;
-- jaubry3q | December 23, 2024 | Monday
4. Date arithmetic
select
username,
created_at,
created_at + interval '1 year' as anniversary_date,
created_at + interval '30 days' as thirty_days_later
from
users limit 1;
-- username | created_at | anniversary_date | thirty_days_later
-- ----------+---------------------+---------------------+---------------------
-- jaubry3q | 2024-12-23 11:29:46 | 2025-12-23 11:29:46 | 2025-01-22 11:29:46
End of Day 3
Today we learned about the most popular string and date functions.
Tomorrow we will learn about joining tables.
