PostgreSQL Built-in Functions: A Detailed Guide

Q: What's the difference between AGE and subtraction for date differences?

DATE1 - DATE2 returns an integer number of days; AGE(DATE1, DATE2) returns an INTERVAL including years, months, and days (e.g., 1 year 2 mons 3 days)—more human-friendly, but less precise than subtraction.

1. What You'll Learn

Math functions: ABS / ROUND / CEIL / FLOOR / MOD / POWER / SQRT
String functions: LENGTH / CONCAT / LOWER / UPPER / TRIM / SUBSTRING / REPLACE / SPLIT_PART / LEFT / RIGHT
Date functions: NOW / CURRENT_DATE / AGE / EXTRACT / DATE_TRUNC / TO_CHAR / TO_DATE
Conditional functions: COALESCE / NULLIF / GREATEST / LEAST
Sequence functions: NEXTVAL / CURRVAL / SETVAL
Techniques for combining functions

2. The Story

Alice is an e-commerce data analyst who needs to generate a monthly sales report at month-end. The report must:

Format order_date as 2025-June-30
Concatenate the customer name + order number into a report row title
Handle NULL: missing discount values default to 0
Money calculation: precise total with tax and net after discount
Sequence use: generate sequential numbers for report rows

Alice needs to combine PostgreSQL's built-in functions to produce the report.

3. Concept: Math Functions

(1) Basic Math Functions

Function	Meaning	Example	Result
`ABS(x)`	Absolute value	`ABS(-15)`	`15`
`ROUND(x, n)`	Round to n decimals	`ROUND(3.1415, 2)`	`3.14`
`CEIL(x)`	Round up	`CEIL(3.2)`	`4`
`FLOOR(x)`	Round down	`FLOOR(3.8)`	`3`
`MOD(x, y)`	Modulo	`MOD(10, 3)`	`1`
`POWER(x, y)`	Power	`POWER(2, 10)`	`1024`
`SQRT(x)`	Square root	`SQRT(144)`	`12`
`SIGN(x)`	Sign	`SIGN(-5)`	`-1`
`TRUNC(x, n)`	Truncate	`TRUNC(3.1415, 2)`	`3.14`

▶ Example: ROUND to Fixed Decimals

SQL

SELECT
  unit_price,
  ROUND(unit_price, 0) AS rounded_0,
  ROUND(unit_price, 2) AS rounded_2
FROM products
WHERE category = 'Electronics';

TEXT

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

▶ Example: CEIL / FLOOR Rounding

SQL

SELECT
  total_amount,
  CEIL(total_amount) AS ceil_value,
  FLOOR(total_amount) AS floor_value,
  total_amount - FLOOR(total_amount) AS decimal_part
FROM orders
WHERE order_status = 'completed';

TEXT

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

(2) ROUND vs TRUNC vs CEIL/FLOOR

Function	Behavior	`3.5` result	`3.2` result	`-3.5` result
`ROUND(x)`	Round to nearest integer	4	3	-4
`TRUNC(x)`	Truncate decimals	3	3	-3
`CEIL(x)`	Round up	4	4	-3
`FLOOR(x)`	Round down	3	3	-4

▶ Example: MOD Remainder

SQL

SELECT
  order_id,
  total_amount,
  MOD(order_id, 10) AS partition_key,
  total_amount * POWER(1.08, 1) AS with_annual_tax
FROM orders
WHERE order_status = 'completed';

TEXT

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

▶ Example: SQRT and POWER Combined

SQL

SELECT
  unit_price,
  SQRT(unit_price) AS price_sqrt,
  POWER(unit_price, 1.0 / 3) AS price_cbrt
FROM products
WHERE unit_price > 0
ORDER BY unit_price DESC;

TEXT

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

4. Concept: String Functions

(1) Case and Length

Function	Meaning	Example	Result
`LOWER(s)`	To lowercase	`LOWER('Hello')`	`hello`
`UPPER(s)`	To uppercase	`UPPER('Hello')`	`HELLO`
`INITCAP(s)`	Capitalize first letter	`INITCAP('hello world')`	`Hello World`
`LENGTH(s)`	Character count	`LENGTH('PostgreSQL')`	`10`
`BIT_LENGTH(s)`	Bit length	`BIT_LENGTH('A')`	`8`
`CHAR_LENGTH(s)`	Same as LENGTH	`CHAR_LENGTH('ABC')`	`3`

▶ Example: Case Conversion

SQL

SELECT
  product_name,
  LOWER(product_name) AS lower_name,
  UPPER(product_name) AS upper_name,
  INITCAP(LOWER(product_name)) AS title_name
FROM products
WHERE is_active = true;

TEXT

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

(2) Concatenation and Substring

Function	Meaning	Example	Result
`CONCAT(s1, s2, ...)`	Concatenate (NULL-safe)	`CONCAT('A', NULL, 'B')`	`AB`
`CONCAT_WS(sep, s1, s2)`	Concatenate with separator	`CONCAT_WS('-', 'A','B')`	`A-B`
`s1		s2`	Concatenate (NULL-unsafe)
`SUBSTRING(s FROM n FOR len)`	Substring	`SUBSTRING('Hello' FROM 2 FOR 3)`	`ell`
`LEFT(s, n)`	Left n chars	`LEFT('Hello', 3)`	`Hel`
`RIGHT(s, n)`	Right n chars	`RIGHT('Hello', 2)`	`lo`

▶ Example: CONCAT vs `||`

SQL

SELECT
  product_name,
  CONCAT(product_name, ' - $', unit_price) AS safe_concat,
  product_name || ' - $' || unit_price AS unsafe_concat,
  CONCAT_WS(' | ', product_name, category, unit_price::TEXT) AS ws_concat
FROM products;

TEXT

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

(3) Find and Replace

Function	Meaning	Example	Result
`POSITION(sub IN s)`	Find position	`POSITION('sql' IN 'postgresql')`	`8`
`STRPOS(s, sub)`	Same (args reversed)	`STRPOS('postgresql', 'sql')`	`8`
`REPLACE(s, from, to)`	Replace	`REPLACE('ABC', 'B', 'X')`	`AXC`
`OVERLAY(s PLACING t FROM n)`	Overwrite	`OVERLAY('XXXX' PLACING 'AB' FROM 2)`	`XABX`
`SPLIT_PART(s, delim, n)`	Split by delimiter, take n-th	`SPLIT_PART('A-B-C', '-', 2)`	`B`
`TRIM(s)`	Trim surrounding spaces	`TRIM(' Hi ')`	`Hi`
`LTRIM(s)`	Trim left spaces	`LTRIM(' Hi ')`	`Hi`
`RTRIM(s)`	Trim right spaces	`RTRIM(' Hi ')`	`Hi`
`RPAD(s, len, fill)`	Right-pad	`RPAD('Hi', 5, 'x')`	`Hixxx`
`LPAD(s, len, fill)`	Left-pad	`LPAD('42', 5, '0')`	`00042`
`REVERSE(s)`	Reverse	`REVERSE('ABC')`	`CBA`

▶ Example: SPLIT_PART to Parse CSV Fields

SQL

SELECT
  product_name,
  SPLIT_PART(category_path, '/', 1) AS top_category,
  SPLIT_PART(category_path, '/', 2) AS sub_category
FROM products
WHERE category_path LIKE '%/%';

TEXT

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

▶ Example: REPLACE and TRIM Combined

SQL

SELECT
  TRIM(product_name) AS clean_name,
  REPLACE(product_name, '  ', ' ') AS no_double_spaces
FROM products
WHERE is_active = true;

TEXT

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

▶ Example: LPAD to Format Numbers

SQL

SELECT
  LPAD(product_id::TEXT, 8, '0') AS formatted_id,
  product_name,
  unit_price
FROM products
ORDER BY product_id;

TEXT

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

5. Concept: Date Functions

(1) Getting the Current Time

Function	Return type	Description
`CURRENT_DATE`	DATE	Current date
`CURRENT_TIME`	TIME WITH TZ	Current time (with time zone)
`CURRENT_TIMESTAMP`	TIMESTAMP WITH TZ	Current date and time
`NOW()`	TIMESTAMP WITH TZ	Same as CURRENT_TIMESTAMP
`LOCALTIMESTAMP`	TIMESTAMP	Current time (without time zone)
`CLOCK_TIMESTAMP()`	TIMESTAMP WITH TZ	Real-time clock (changes per call)

▶ Example: Getting the Current Time

SQL

SELECT
  CURRENT_DATE AS today,
  CURRENT_TIMESTAMP AS now_ts,
  NOW() AS now_func,
  LOCALTIMESTAMP AS local_ts;

TEXT

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

(2) Date Arithmetic

Expression	Meaning	Result example
`DATE '2025-01-15' + 7`	Add days	`2025-01-22`
`DATE '2025-01-15' - DATE '2025-01-01'`	Day difference	`14`
`NOW() + INTERVAL '30 days'`	Add interval	30 days later
`NOW() - INTERVAL '1 year'`	Subtract interval	1 year ago
`AGE(end, start)`	Interval description	`1 year 2 mons 3 days`
`AGE(timestamp)`	Interval since now	—

▶ Example: Date Arithmetic

SQL

SELECT
  order_date,
  order_date + 7 AS due_date,
  CURRENT_DATE - order_date AS days_ago,
  AGE(CURRENT_DATE, order_date) AS age_since_order
FROM orders
WHERE order_date >= CURRENT_DATE - INTERVAL '90 days';

TEXT

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

(3) EXTRACT and DATE_TRUNC

Usage	Meaning	Example	Result
`EXTRACT(YEAR FROM ts)`	Extract year	`EXTRACT(YEAR FROM NOW())`	`2025`
`EXTRACT(MONTH FROM ts)`	Extract month	`EXTRACT(MONTH FROM NOW())`	`6`
`EXTRACT(DAY FROM ts)`	Extract day	—	—
`EXTRACT(DOW FROM ts)`	Day of week (0=Sunday)	—	`1` (Monday)
`EXTRACT(QUARTER FROM ts)`	Quarter	—	`2`
`EXTRACT(EPOCH FROM ts)`	Unix timestamp	—	`1719500400`
`DATE_TRUNC('month', ts)`	Truncate to month start	`DATE_TRUNC('month', '2025-06-15')`	`2025-06-01`
`DATE_TRUNC('year', ts)`	Truncate to year start	—	`2025-01-01`

▶ Example: EXTRACT to Get Date Parts

SQL

SELECT
  order_date,
  EXTRACT(YEAR FROM order_date) AS order_year,
  EXTRACT(MONTH FROM order_date) AS order_month,
  EXTRACT(QUARTER FROM order_date) AS order_quarter,
  EXTRACT(DOW FROM order_date) AS weekday
FROM orders
WHERE order_status = 'completed';

TEXT

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

▶ Example: DATE_TRUNC Monthly Aggregation

SQL

SELECT
  DATE_TRUNC('month', order_date) AS month_start,
  COUNT(*) AS order_count,
  SUM(total_amount) AS monthly_revenue
FROM orders
WHERE order_status = 'completed'
  AND order_date >= DATE_TRUNC('year', CURRENT_DATE)
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY month_start;

TEXT

 count 
-------
     5
(1 row)

(4) TO_CHAR and TO_DATE

Function	Purpose	Example
`TO_CHAR(ts, fmt)`	Time → string	`TO_CHAR(NOW(), 'YYYY-Month-DD')`
`TO_DATE(s, fmt)`	String → date	`TO_DATE('2025-06-15', 'YYYY-MM-DD')`
`TO_TIMESTAMP(s, fmt)`	String → timestamp	`TO_TIMESTAMP('2025-06-15 14:30', 'YYYY-MM-DD HH24:MI')`

Common format templates:

Template	Meaning	Example output
`YYYY`	4-digit year	`2025`
`YY`	2-digit year	`25`
`Month`	Full month name	`June`
`MON`	Abbreviated month	`Jun`
`MM`	2-digit month	`06`
`DD`	2-digit day	`15`
`HH24`	24-hour	`14`
`MI`	Minute	`30`
`SS`	Second	`45`

▶ Example: TO_CHAR Date Formatting

SQL

SELECT
  order_id,
  TO_CHAR(order_date, 'YYYY-Month-DD') AS formatted_date,
  TO_CHAR(order_date, 'YYYY-MM-DD HH24:MI:SS') AS full_datetime,
  TO_CHAR(total_amount, 'FM$999,999.00') AS formatted_amount
FROM orders
WHERE order_status = 'completed';

TEXT

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

▶ Example: TO_DATE String to Date

SQL

SELECT
  TO_DATE('2025-06-15', 'YYYY-MM-DD') AS parsed_date,
  TO_DATE('15/06/2025', 'DD/MM/YYYY') AS eu_date,
  TO_DATE('Jun 15 2025', 'Mon DD YYYY') AS us_date;

TEXT

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

6. Concept: Conditional Functions

(1) COALESCE

COALESCE(val1, val2, ...) returns the first non-NULL argument and is the most common NULL-handling function.

Expression	Result
`COALESCE(NULL, NULL, 'default')`	`default`
`COALESCE(100, 0)`	`100`
`COALESCE(NULL, 0)`	`0`

▶ Example: COALESCE for Missing Discounts

SQL

SELECT
  product_name,
  unit_price,
  discount_rate,
  COALESCE(discount_rate, 0) AS safe_discount,
  unit_price * (1 - COALESCE(discount_rate, 0)) AS net_price
FROM products;

TEXT

 count 
-------
     5
(1 row)

(2) NULLIF

NULLIF(val1, val2) returns NULL when the two values are equal, otherwise returns val1. Commonly used to avoid divide-by-zero errors.

Expression	Result
`NULLIF(100, 100)`	NULL
`NULLIF(100, 0)`	100
`100 / NULLIF(0, 0)`	NULL (instead of error)

▶ Example: NULLIF to Prevent Divide-by-Zero

SQL

SELECT
  product_name,
  unit_price,
  total_sold,
  unit_price / NULLIF(total_sold, 0) AS revenue_per_unit
FROM products
WHERE is_active = true;

TEXT

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

(3) GREATEST / LEAST

Function	Meaning	Example	Result
`GREATEST(a, b, c)`	Return the maximum	`GREATEST(10, 20, 5)`	`20`
`LEAST(a, b, c)`	Return the minimum	`LEAST(10, 20, 5)`	`5`

▶ Example: GREATEST / LEAST

SQL

SELECT
  product_name,
  unit_price,
  COALESCE(discount_rate, 0) AS discount,
  unit_price * (1 - GREATEST(COALESCE(discount_rate, 0), 0.5)) AS capped_net_price,
  LEAST(unit_price, 999.99) AS price_cap
FROM products
WHERE is_active = true;

TEXT

 count 
-------
     5
(1 row)

(4) Conditional Function Comparison

Function	Scenario	NULL handling
`COALESCE(a, b)`	Provide a default	Returns first non-NULL
`NULLIF(a, b)`	NULL when values equal	Does not handle NULL
`GREATEST(a, b)`	Take the maximum	Returns NULL if any is NULL
`LEAST(a, b)`	Take the minimum	Returns NULL if any is NULL

7. Concept: Sequence Functions

(1) Sequence Operation Functions

Function	Meaning	Example
`NEXTVAL('seq_name')`	Increment and return next value	`NEXTVAL('order_id_seq')` → 1001
`CURRVAL('seq_name')`	Return current session's last NEXTVAL	`CURRVAL('order_id_seq')` → 1001
`SETVAL('seq_name', n)`	Set the sequence's current value	`SETVAL('order_id_seq', 5000)`
`SETVAL('seq_name', n, false)`	Set value; next NEXTVAL returns n	`SETVAL('order_id_seq', 5000, false)`

▶ Example: NEXTVAL to Generate Numbers

SQL

INSERT INTO orders (order_id, customer_id, total_amount, order_date)
VALUES (NEXTVAL('order_id_seq'), 1001, 299.99, CURRENT_DATE);

TEXT

INSERT 0 1

▶ Example: CURRVAL to View Current Value

SQL

SELECT
  NEXTVAL('report_seq') AS report_number,
  CURRVAL('report_seq') AS current_seq;

TEXT

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

(2) SETVAL to Reset a Sequence

Call	Next NEXTVAL returns
`SETVAL('seq', 100)`	101
`SETVAL('seq', 100, false)`	100
`SETVAL('seq', 1, false)`	1 (reset to start)

▶ Example: SETVAL to Reset a Sequence

SQL

-- Reset sequence to start from 1 again
SELECT SETVAL('order_id_seq', 1, false);

-- Set sequence to a specific high value (e.g., after data migration)
SELECT SETVAL('order_id_seq', (SELECT MAX(order_id) FROM orders));

TEXT

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

8. Flowchart: Choosing a Function

flowchart TD
    A[What operation do you need?] --> B{Data type?}
    B -->|Numeric| C{What do you need?}
    B -->|String| D{What do you need?}
    B -->|Date| E{What do you need?}
    B -->|NULL handling| F[COALESCE / NULLIF]
    C -->|Round| G[ROUND / TRUNC]
    C -->|Round to integer| H[CEIL / FLOOR]
    C -->|Compute| I[ABS / MOD / POWER / SQRT]
    D -->|Concatenate| J[CONCAT / ||]
    D -->|Substring| K[SUBSTRING / LEFT / RIGHT]
    D -->|Replace| L[REPLACE / SPLIT_PART]
    D -->|Case| M[LOWER / UPPER / INITCAP]
    D -->|Trim| N[TRIM / LTRIM / RTRIM]
    E -->|Current time| O[NOW / CURRENT_TIMESTAMP]
    E -->|Extract part| P[EXTRACT]
    E -->|Truncate| Q[DATE_TRUNC]
    E -->|Format| R[TO_CHAR]
    E -->|Parse| S[TO_DATE / TO_TIMESTAMP]

    style A fill:#e1f5fe
    style F fill:#c8e6c9

9. Comprehensive Example

Alice produced a complete monthly sales report.

SQL

-- Step 1: Monthly revenue report with date formatting
SELECT
  TO_CHAR(DATE_TRUNC('month', order_date), 'YYYY-Month') AS report_month,
  COUNT(*) AS order_count,
  SUM(total_amount)::NUMERIC(12,2) AS gross_revenue,
  SUM(total_amount * 0.08)::NUMERIC(12,2) AS tax_collected,
  SUM(total_amount * 1.08)::NUMERIC(12,2) AS total_with_tax
FROM orders
WHERE order_status = 'completed'
  AND order_date >= DATE_TRUNC('year', CURRENT_DATE)
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY report_month;

-- Step 2: Product summary with string formatting and NULL handling
SELECT
  LPAD(p.product_id::TEXT, 6, '0') AS product_code,
  INITCAP(LOWER(p.product_name)) AS display_name,
  CONCAT('$', ROUND(p.unit_price, 2)) AS price_label,
  COALESCE(p.discount_rate, 0) AS discount,
  ROUND(p.unit_price * (1 - COALESCE(p.discount_rate, 0)), 2) AS net_price,
  LEAST(ROUND(p.unit_price * (1 - COALESCE(p.discount_rate, 0)), 2), 999.99) AS capped_price
FROM products p
WHERE p.is_active = true
  AND p.unit_price > NULLIF(p.cost_price, 0)
ORDER BY net_price DESC;

-- Step 3: Customer order summary with age calculation
SELECT
  CONCAT(c.first_name, ' ', c.last_name) AS customer_name,
  COUNT(o.order_id) AS total_orders,
  SUM(o.total_amount)::NUMERIC(12,2) AS lifetime_value,
  AGE(MAX(o.order_date), MIN(o.order_date)) AS customer_span,
  EXTRACT(YEAR FROM AGE(CURRENT_DATE, c.register_date)) AS years_since_join
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
WHERE o.order_status = 'completed'
GROUP BY c.customer_id, c.first_name, c.last_name, c.register_date
ORDER BY lifetime_value DESC
FETCH FIRST 25 ROWS ONLY;

❓ FAQ

Q What's the difference between CONCAT and ||?

A CONCAT ignores NULL (it concatenates the non-NULL parts), while || returns NULL for the whole result if any operand is NULL. For NULL-safe concatenation, use CONCAT or CONCAT_WS.

Q Is ROUND(2.5) equal to 2 or 3?

A PostgreSQL's ROUND uses "banker's rounding" (round half to even), so ROUND(2.5) = 2 and ROUND(3.5) = 4. This differs from the rounding in some languages.

Q Does EXTRACT(DOW FROM date) return 0 or 7 for Sunday?

A DOW returns 0–6, where 0 is Sunday, 1 is Monday, and 6 is Saturday. For the ISO standard (1=Monday, 7=Sunday), use EXTRACT(ISODOW FROM date).

Q Can DATE_TRUNC truncate to the hour?

A Yes. DATE_TRUNC supports precision levels including microsecond, millisecond, second, minute, hour, day, week, month, quarter, year, decade, century, and millennium.

Q Can COALESCE accept different types?

A Yes, but all arguments must be implicitly convertible to the same type. For example, COALESCE(NULL, 0, 0.5) converts all values to NUMERIC.

Q Can CURRVAL be used before calling NEXTVAL?

A No. CURRVAL requires the current session to have already called NEXTVAL on that sequence, otherwise it errors with "currval of sequence is not yet defined in this session".

Q What does the FM prefix mean when TO_CHAR formats numbers?

A FM (Fill Mode) removes the padding of leading zeros and trailing spaces. TO_CHAR(42, 'FM00000') = '42' (no leading zeros), while TO_CHAR(42, '00000') = '00042'.

Q What's the difference between AGE and subtraction for date differences?

A DATE1 - DATE2 returns an integer number of days; AGE(DATE1, DATE2) returns an INTERVAL including years, months, and days (e.g., 1 year 2 mons 3 days)—more human-friendly, but less precise than subtraction.

📖 Summary

Math functions: ROUND/CEIL/FLOOR for rounding, ABS/MOD/POWER/SQRT for calculation; mind integer division and precision
String functions: CONCAT (NULL-safe) / || (NULL-unsafe), SUBSTRING/LEFT/RIGHT for slicing, REPLACE/SPLIT_PART for replace/split
Date functions: NOW/CURRENT_TIMESTAMP for current time, EXTRACT to pull parts, DATE_TRUNC to truncate, TO_CHAR/TO_DATE to format and parse
Conditional functions: COALESCE returns the first non-NULL, NULLIF returns NULL on equality to prevent divide-by-zero, GREATEST/LEAST take extremes
Sequence functions: NEXTVAL increments and returns, CURRVAL reads the current value, SETVAL resets
Combining functions is a core real-world skill—use nesting and type casts well

📝 Exercises

⭐ Write a query that selects product_name and unit_price from products, uses CONCAT to build a price label (format $99.99), uses ROUND to keep two decimals, and orders by price descending.
⭐⭐ Write a query that generates a 2025 monthly sales report: group by month with DATE_TRUNC('month', order_date), format the month with TO_CHAR as YYYY-MM, compute the monthly order count and total amount (NUMERIC(12,2)), and use COALESCE to handle possible NULL discounts.
⭐⭐⭐ Write a query that generates a report row per customer: join the name with CONCAT_WS, compute the customer's registration duration with AGE, extract the registration year with EXTRACT, cap the discount at 50% with GREATEST, prevent divide-by-zero when computing average order value with NULLIF, format the customer ID as a 6-digit number with LPAD, and finally generate the report row sequence with NEXTVAL('report_seq').

Web-Tutorial Tech Team

A team of developers maintaining programming tutorials. Each tutorial is written and reviewed by developers with expertise in that field. We work to keep our content accurate and reliable — if you spot an issue, please let us know.

About Us

404 Not Found

PostgreSQL Built-in Functions: A Detailed Guide

1. What You'll Learn

2. The Story

3. Concept: Math Functions

(1) Basic Math Functions

▶ Example: ROUND to Fixed Decimals

▶ Example: CEIL / FLOOR Rounding

(2) ROUND vs TRUNC vs CEIL/FLOOR

▶ Example: MOD Remainder

▶ Example: SQRT and POWER Combined

4. Concept: String Functions

(1) Case and Length

▶ Example: Case Conversion

(2) Concatenation and Substring

▶ Example: CONCAT vs ||

(3) Find and Replace

▶ Example: SPLIT_PART to Parse CSV Fields

▶ Example: REPLACE and TRIM Combined

▶ Example: LPAD to Format Numbers

5. Concept: Date Functions

(1) Getting the Current Time

▶ Example: Getting the Current Time

(2) Date Arithmetic

▶ Example: Date Arithmetic

(3) EXTRACT and DATE_TRUNC

▶ Example: EXTRACT to Get Date Parts

▶ Example: DATE_TRUNC Monthly Aggregation

(4) TO_CHAR and TO_DATE

▶ Example: TO_CHAR Date Formatting

▶ Example: TO_DATE String to Date

6. Concept: Conditional Functions

(1) COALESCE

▶ Example: COALESCE for Missing Discounts

(2) NULLIF

▶ Example: NULLIF to Prevent Divide-by-Zero

(3) GREATEST / LEAST

▶ Example: GREATEST / LEAST

(4) Conditional Function Comparison

7. Concept: Sequence Functions

(1) Sequence Operation Functions

▶ Example: NEXTVAL to Generate Numbers

▶ Example: CURRVAL to View Current Value

(2) SETVAL to Reset a Sequence

▶ Example: SETVAL to Reset a Sequence

8. Flowchart: Choosing a Function

9. Comprehensive Example

❓ FAQ

📖 Summary

📝 Exercises

🙏 帮我们做得更好

▶ Example: CONCAT vs `||`