PostgreSQL built-in functions are ready-to-use operations for working with values and database information. They cover tasks such as changing text, calculating dates, handling missing values, summarizing rows, and processing JSON; you call them in SQL expressions, often without installing anything.
What PostgreSQL built-in functions are
A function takes input, performs an operation, and returns a result. For example, lower('PostgreSQL') returns postgresql. Built-in functions are supplied by PostgreSQL itself and are available in ordinary queries. They are distinct from functions that a developer creates for an application and from functions supplied by optional extensions.
PostgreSQL has a large catalog of functions, and the exact catalog can vary between major versions. Function names, argument types, return types, and behavior are documented by version in the PostgreSQL manual. A function may accept more than one data type or have multiple signatures, a feature called overloading. PostgreSQL uses the arguments and surrounding expression to decide which matching version to call.
Functions are only one part of SQL’s built-in capabilities. Operators such as + and =, data types, and special SQL expressions also provide useful behavior. A familiar-looking expression is not always an ordinary function call: for example, CURRENT_DATE is a SQL value expression, not a function that you call as CURRENT_DATE().
Main categories of built-in functions
Built-ins are easier to navigate when grouped by what they do. The main categories include:
- Scalar functions: Take values as input and return a value, such as
upper,abs, ordate_trunc. - Aggregate functions: Combine values from multiple rows into a result, such as
count,sum, andavg. - Window functions: Calculate results across related rows while keeping each row in the output, such as
row_numberandrank. - Set-returning functions: Return a set of rows rather than one value, such as
generate_series. - System-information functions: Return information about the current session, database objects, or server, such as
current_database().
These categories describe how a function behaves in a query, not necessarily a strict division in the documentation. For example, JSON functions can return a single JSON value or expand JSON data into rows.
Everyday scalar functions
Scalar functions are useful in a SELECT list, a WHERE condition, a join condition, or an expression that defines a calculated column. Here are some common examples:
SELECT upper('hello');returnsHELLO.SELECT abs(-12);returns12.SELECT round(12.345, 2);returns12.35.SELECT length('database');returns the number of characters in the text value.
PostgreSQL also provides concat and concat_ws for joining text. The separator in concat_ws is placed between non-null arguments, and null arguments after the separator are skipped. For example, SELECT concat_ws('-', 'db', NULL, '01'); returns db-01. This behavior can be helpful when assembling labels from optional fields.
To avoid errors or unwanted results when values may be absent, use COALESCE to return the first non-null expression. For example, COALESCE(nickname, full_name, 'Unknown') chooses a nickname when present, otherwise a full name, otherwise the fallback text. NULLIF(a, b) does the reverse kind of check: it returns null when its two arguments are equal, and otherwise returns the first argument.
Aggregates, grouping, and windows
Aggregate functions summarize rows. A query such as SELECT count(*) FROM orders; returns the number of rows in the table. count(*) counts rows, while count(column_name) counts only rows where that column is not null. This distinction often explains why a count is lower than the total number of records.
Other common aggregates include sum, avg, min, and max. Use GROUP BY to calculate a result for each group. For instance, SELECT customer_id, count(*) FROM orders GROUP BY customer_id; returns one count per customer. An aggregate can also accept an ordering clause: string_agg(product_name, ', ' ORDER BY product_name) joins names into a consistently ordered text value for each group.
Window functions solve a related but different problem. They calculate across a set of rows without collapsing each group into a single output row. For example, row_number() OVER (PARTITION BY customer_id ORDER BY order_date) numbers each customer’s orders in date order. The OVER clause defines the window; PARTITION BY divides rows into groups, and ORDER BY determines their sequence. When ordering values can tie, add a unique column such as an order ID to make the result deterministic.
Date, time, null, and JSON operations
Date and time functions are useful for reporting and time-based filtering. date_trunc('month', created_at) returns a timestamp truncated to the start of its month, which can be used to group events by month. EXTRACT(YEAR FROM created_at) obtains a date or time field such as the year. Be aware that now() and CURRENT_TIMESTAMP represent the start time of the current transaction in PostgreSQL. If an expression needs the actual changing wall-clock time, PostgreSQL also provides clock_timestamp().
Null handling deserves special attention because null means an unknown or absent value, not zero or an empty string. Comparisons such as value = NULL do not test for null; use value IS NULL or value IS NOT NULL. Many ordinary functions return null when given a null input, though behavior depends on the particular function. Check the documentation when a null argument could affect a calculation.
For JSON data, PostgreSQL offers functions and operators for constructing, inspecting, and transforming values. For example, jsonb_build_object('id', id, 'name', name) constructs a JSONB object from SQL values. JSON operators such as -> and ->> extract JSON values, with ->> returning text. Functions such as jsonb_each can expand an object into rows. Choose JSON or JSONB based on the storage and query requirements; they are related types, not interchangeable names for a single type.
Set-returning functions and practical query patterns
A set-returning function can produce several rows from one call. generate_series is a handy built-in for generating a sequence of numbers or timestamps. For example, SELECT generate_series(1, 5); returns five rows containing the integers from 1 through 5. A timestamp series can be used to create a sequence of dates or times, subject to the function’s supported argument types and interval rules.
Set-returning functions are often used in the FROM clause, where their output can be joined with table data. Suppose a report needs one row for each day in a date range, including days with no recorded sales. A generated date series can serve as the list of days, then be left-joined to daily sales totals. This pattern helps make missing dates visible instead of omitting them from a report.
Some functions expand structured data as well. A JSON expansion function in the FROM clause can turn object entries into rows for filtering or joining. When using any set-returning function, check how its output columns are named and typed, and give it an alias when that makes the query clearer.
Finding the right function and checking its behavior
The PostgreSQL documentation is the best reference for a function’s supported signatures, return type, null behavior, and version availability. Its functions-and-operators chapters are organized by subject, including mathematical, string, date/time, JSON, aggregate, and window operations. Search using the function name together with the PostgreSQL major version in use; this avoids relying on features that may not exist on an older server.
You can also inspect the database catalog. For example, SELECT to_regprocedure('lower(text)'); checks whether a function with that signature can be resolved and returns its identity when found, or null otherwise. pg_typeof(expression) can show the data type PostgreSQL assigns to an expression. These are useful checks when overloaded functions or implicit type conversions make a query’s behavior unclear.
Function names are normally written in lowercase, and unquoted names are treated as lowercase by PostgreSQL. Built-in objects are generally available through the pg_catalog schema, which PostgreSQL searches automatically. Schema qualification, such as pg_catalog.lower(value), can make the target explicit, though most everyday queries do not need it.
Choosing functions safely and avoiding common mistakes
Choose a function based on both the desired result and the type of data it operates on. A date, timestamp, timestamp with time zone, text value, and JSON value have different semantics. If a call fails with a function-does-not-exist error, check the argument types and casts before assuming the function is unavailable. A function may exist but not accept the types supplied by the query.
Other frequent mistakes include confusing aggregates with window functions, forgetting that count(column) ignores nulls, and assuming every date/time expression uses the current wall clock. Also take care when using functions in filters on indexed columns. For example, wrapping an indexed timestamp column in a transformation may prevent a straightforward index condition from being used. A range condition on the original column is often a better starting point, but query plans and indexing choices depend on the actual schema and data. Use EXPLAIN to inspect how PostgreSQL plans a query rather than assuming a function call is always fast or slow.
Finally, not every useful function belongs to the PostgreSQL core. Some functions are provided by extensions that must be installed and enabled for a database. If a function is missing, verify whether it is built in, whether the server version supports it, and whether the relevant extension is available. Do not treat a function found in one hosted database as guaranteed to exist in every PostgreSQL installation.
Frequently Asked Questions
Are PostgreSQL built-in functions available without installation?
Core built-in functions are supplied with PostgreSQL and are generally ready to use. Functions from optional extensions are different: an extension may need to be installed on the server and enabled in the database.
How can I list PostgreSQL functions?
Use the PostgreSQL documentation or inspect system catalogs such as pg_proc, joined with namespace and type information when needed. Catalog queries can be detailed, so database tools that display function signatures may be more convenient for a quick search.
What is the difference between an aggregate and a window function?
An aggregate such as sum combines rows into a result for a group. A window calculation such as sum(amount) OVER (PARTITION BY customer_id) computes across related rows while retaining the individual rows in the output.
Is CURRENT_DATE a PostgreSQL function?CURRENT_DATE is a special SQL value expression, not a normal function call. Use it without parentheses. By contrast, many built-ins, including now() and lower(text), are called with parentheses.
Why does PostgreSQL say a function does not exist?
The requested signature may not match the argument types, a needed cast may be missing, or the function may depend on an extension or newer PostgreSQL version. Check the exact function signature and the server’s version before changing the query.
Related
- How to Encrypt an Excel File With a Password
- Encrypt an Excel File: Steps, Limits, and Safer Sharing
- Encrypt an Excel Spreadsheet: Steps and Security Tips
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- COUNTIF Formula for Attendance in ExcelTo count attendance marks in Excel, use =COUNTIF(B2:U2,"P"). If a student’s 20 session entries are in B2:U2 and…
- Filter Excel by Highlighted CellsYes. In Excel for Microsoft 365 on Windows, open the filter arrow in the column containing the highlighted…
- Working Hours Calculator in Excel: Formulas and ExamplesTo calculate a shift in decimal hours, enter the start time in A2, end time in B2, and…
- Excel INDIRECT Drop-Down Lists: Setup and FixesTo make a dependent drop-down list in Excel, create a named range for each set of choices, then…
- Excel Formula to Calculate Age From a BirthdayIn Excel for Microsoft 365, use =DATEDIF(A2,TODAY(),"Y") to calculate someone’s age in completed years, assuming the birthday is…
- Excel Filter Between Text Ranges: Steps and LimitsYes. In Excel for Microsoft 365 on Windows, open the column’s filter menu, choose Text Filters > Custom…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.