When people start learning PostgreSQL, they usually learn about the SELECT statement as a way to get information from a table. The SELECT statement can do a lot more than that.
Postgres has a lot of built-in functions that you can use with the SELECT statement. The SELECT statement and these functions let you look at information about the server, see who the current user is, check what is happening in the database, work with dates and times, do math, change strings, make JSON create lists, and do other things.
Knowing the SELECT statement and these built-in functions makes working with databases every day a lot easier. If you are trying to fix a problem with the server making an application or just learning about PostgreSQL, the SELECT statement and these functions give you a quick way to get to useful information without having to write complicated SQL code.
1. Get the users
SELECT current_user;
Result:
current_user
--------------
postgres
(1 row)
In PostgreSQL, there are two types of users. One is the currently logged-in user, and the other is the session user.
SELECT session_user;
Result:
session_user
--------------
postgres
(1 row)
2. Get current date and time
SELECT now();
Result:
now
----------------------------------
2026-07-29 19:12:59.158511+05:30
(1 row)
This will show the current date and time.
SELECT current_date;
Result:
current_date
--------------
2026-07-29
(1 row)
This will show the current date only.
SELECT current_time;
Result:
current_time
-----------------------
19:15:01.907671+05:30
(1 row)
This will show the current time in 24 hrs format.
SELECT current_timestamp;
Result:
current_timestamp
----------------------------------
2026-07-29 19:15:05.126034+05:30
(1 row)
This will show the current timestamp.
SELECT localtimestamp;
Result:
localtimestamp
----------------------------
2026-07-29 19:15:07.715013
(1 row)
This will show the local timestamp.CURRENT_TIMESTAMP - returns the current date and time with time zone information (timestamp with time zone).
LOCALTIMESTAMP - returns the current date and time without time zone information (timestamp without time zone).
SELECT statement_timestamp();
Result:
statement_timestamp
----------------------------------
2026-07-29 19:15:10.515748+05:30
(1 row)
This returns the timestamp when the current SQL statement started execution.
SELECT transaction_timestamp();
Result:
transaction_timestamp
----------------------------------
2026-07-29 19:15:12.961439+05:30
(1 row)
This returns the timestamp when the current transaction started.
SELECT clock_timestamp();
Result:
clock_timestamp
----------------------------------
2026-07-29 19:15:17.270056+05:30
(1 row)
This returns the actual current system time at the exact moment the function is called.
3. Database information
SELECT current_database();
Result:
current_database
------------------
postgres
(1 row)
This will show the name of the current database in PostgreSQL where we connected.
SELECT version();
Result:
version
-------------------------------------------------------------------------------------------------------------------------------------
PostgreSQL 18.4 (Ubuntu 18.4-1.pgdg22.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.4.0-1ubuntu1~22.04.3) 11.4.0, 64-bit
(1 row)
This will show the current PostgreSQL version.
SELECT current_schema();
Result:
current_schema
----------------
public
(1 row)
This will show the name of the scheme were currently using.
SELECT pg_backend_pid();
Result:
pg_backend_pid
----------------
452615
(1 row)
This will show the current backend ID of this psql session, and we can terminate or cancel this backend through the functions named pg_terminate_backend() and pg_cancel_backend() based on the process ID.
4. User and connection information
SELECT current_role;
Result:
current_role
--------------
postgres
(1 row)
This will show the current role of PostgreSQL.
5. Mathematical expressions
In PostgreSQL, we can also get the result of mathematical expressions like this.
SELECT 5 + 10;
Result:
?column?
----------
15
(1 row)
SELECT 25 * 4;
Result:
?column?
----------
100
(1 row)
SELECT sqrt(81);
Result:
sqrt
------
9
(1 row)
SELECT power(2, 10);
Result:
power
-------
1024
(1 row)
SELECT pi();
Result:
pi
-------------------
3.141592653589793
(1 row)
6. String functions
SELECT upper('postgres');Result:
upper
----------
POSTGRES
(1 row)
In PostgreSQL, we have the upper() function to convert lowercase letters to uppercase letters.
SELECT lower('HELLO');Result:
lower
-------
hello
(1 row)
We can also use lower() to convert uppercase letters to lowercase.
SELECT length('PostgreSQL');Result:
length
--------
10
(1 row)
The length() function is used to determine the length of the string we passed in.
SELECT concat('Hello', ' ', 'World');Result:
concat
-------------
Hello World
(1 row)
The concat() function is used to join two words and returns the combined word to the user.
SELECT substring('PostgreSQL', 1, 8);Result:
substring
-----------
PostgreS
(1 row)
The substring() is used to return a substring of a string. The first parameter is the string we passed, the second parameter is the starting position, and the third parameter is the ending position.
7. Boolean expressions
In PostgreSQL, we can also get the result based on boolean expressions
SELECT 10 > 5;
Result:
?column?
----------
t
(1 row)
SELECT 5 = 10;
Result:
?column?
----------
f
(1 row)
SELECT 5 <> 10;
Result:
?column?
----------
t
(1 row)
SELECT 3 BETWEEN 1 AND 5;
Result:
?column?
----------
t
(1 row)
8. Random values
In PostgreSQL, we have the random() to generate random numbers with a range between 0 and 1.
SELECT random();
Result:
random
--------------------
0.9066967845047638
(1 row)
SELECT random() * 100;
Result:
?column?
-------------------
50.06089528634483
(1 row)
The random() * 100 is commonly used to generate a random number between 0 and 100.
9. Generate rows
SELECT generate_series(1,10);
Result:
generate_series
-----------------
1
2
3
4
5
6
7
8
9
10
(10 rows)
We can use the generate_series() to generate numbers between the starting and ending numbers we passed into the function.
10. Uuid generation
SELECT gen_random_uuid();
Result:
gen_random_uuid
--------------------------------------
49074e74-b38f-4f25-9554-3ab3d5e09103
(1 row)
This function is used to generate a random Universally Unique Identifier (UUID), which is a 128-bit value used to uniquely identify records.
11. sleep
SELECT pg_sleep(5);
Result:
pg_sleep
----------
(1 row)
This function is mainly used to hold the current process for some time based on the seconds we passed into the function.
12. Current settings of PostgreSQL
SELECT current_setting('max_connections');Result:
current_setting
-----------------
100
(1 row)
We can get the current value of the postgres configuration parameter by using the current_setting() by passing the name of the parameter.
13. Server statistics
SELECT pg_postmaster_start_time();
Result:
pg_postmaster_start_time
----------------------------------
2026-07-29 09:33:52.461579+05:30
(1 row)
This returns the date and time when the PostgreSQL server (postmaster) was last started.
SELECT pg_is_in_recovery();
Result:
pg_is_in_recovery
-------------------
f
(1 row)
This function is mainly used to check whether the postgres server is currently running in recovery mode (standby).
SELECT pg_current_wal_lsn();
Result:
pg_current_wal_lsn
--------------------
17/DF556368
(1 row)
This function is mainly used to display the current WAL file’s least sequence number.
14. Arrays
SELECT ARRAY[1,2,3,4];
Result:
array
-----------
{1,2,3,4}
(1 row)
We can use the ARRAY[] to display the numbers in array format.
SELECT array_length(ARRAY[1,2,3],1);
Result:
array_length
--------------
3
(1 row)
This function is mainly used to return the number of elements in the specified dimension of an array.
15. JSON
SELECT json_build_object(
'name', 'Alice',
'age', 25
);
Result:
json_build_object
--------------------------------
{"name" : "Alice", "age" : 25}
(1 row)
The json_build_object() is used to return the contents in JSON format as key value pairs.
16. Conditional expressions
SELECT CASE
WHEN 10 > 5 THEN 'Yes'
ELSE 'No'
END;
Result:
case
------
Yes
(1 row)
We can also use the ‘case’ statement inside a select query to check conditions.
17. Useful Functions in PostgreSQL
SELECT pg_size_pretty(pg_database_size(current_database()));
Result:
pg_size_pretty
----------------
625 MB
(1 row)
This returns the size of the current database.
SELECT pg_size_pretty(pg_relation_size('sale_order'));Result:
pg_size_pretty
----------------
100 MB
(1 row)
The SELECT statement is really useful for getting data from tables. It also helps you use the features that come with PostgreSQL. You can use it to look at information about the server, get configuration settings, do math work with dates and words, make JSON files, create lists, check what is happening with WAL, and see how storage is being used. You can do all of this with one SQL command.
As you get to know these things, you will not have to spend as much time looking through configuration files or writing SQL code that you do not need. You will have time to understand what is going on with your database. If you keep these queries handy, it will make your daily work with the database a lot easier. You will be able to manage and fix problems with the database quickly.
If you are just starting to use PostgreSQL or if you are already working with databases that are being used by people learning about these SELECT queries is a way to get better at using the database. Mastering these SELECT queries will help you work with the database effectively. Using the SELECT statement and these queries will make you more efficient when you are working with PostgreSQL.