PostgreSQL: Using ‘SELECT’ without ‘FROM’

Introduction

Using SELECT without FROM in PostgreSQL can be powerful for generating values, performing calculations, or retrieving server configurations without querying actual tables.

Basic Usage of SELECT without FROM

To begin exploring the use of SELECT without FROM, let us start with a simple value retrieval.

SELECT 'Hello World';

This query will return ‘Hello World’ as a row without consulting any underlying table data.

Performing Calculations

Beyond text, the SELECT statement without FROM can be used for performing calculations.

SELECT 2 + 2;

This simple arithmetic will result in 4 without the need for any table.

Using Built-in Functions

PostgreSQL has numerous built-in functions that you can use without a FROM clause.

SELECT CURRENT_DATE;

This will return the current date from the system running the database.

Generating Series

With the generate_series function, PostgreSQL can create a sequence of numbers in the absence of tables.

SELECT generate_series(1, 5);

The above returns a set of numbers from 1 to 5.

Combining Constants and Functions

You can combine constants and functions in a single query:

SELECT 'Today is ', CURRENT_DATE;

This returns a phrase that includes the current date.

Retrieving Server Configuration

To check configuration parameters, use SHOW:

SELECT SHOW 'server_version';

This will show the version of PostgreSQL that is currently running.

Conditional Expressions

Conditional logic using CASE can also be employed:

SELECT CASE WHEN 1 < 0 THEN 'False' ELSE 'True' END;

This statement evaluates the condition and returns ‘True’.

Using VALUES to Create Rows

The VALUES keyword can be utilized to construct row literals:

SELECT * FROM (VALUES (1, 'One'), (2, 'Two')) AS t(col_number, col_name);

The above creates a temporary table with two rows and reads from it without a physical table being present.

Advanced Calculations

More complex calculations can also be executed:

SELECT sqrt(pi());

Calculating the square root of π, the result is returned immediately, showcasing the power of mathematical functions in SQL.

Combining with UNION

The UNION command combines result sets from multiple SELECT statements.

SELECT 'Set 1' AS setname, 1 AS number
UNION ALL
SELECT 'Set 2', 2;

This example creates a combined result set from two different SELECT statements without a FROM clause.

Creating Complex Outputs

You can merge multiple function calls to create a complex output:

SELECT 'The circle radius is: ', r, ' and its area is: ', pi() * r^2 FROM (SELECT 5 AS r) circle_data;

This generates custom-tailored outputs with ease, again, without needing to reference any actual table.

Conclusion

In conclusion, PostgreSQL’s ability to perform SELECT without FROM opens the door to a multitude of possibilities ranging from simple data retrieval and calculations to complex function executions and server configurations. By mastering these queries, you enrich your SQL toolkit with flexible and powerful solutions for scenarios where table data isn’t necessary or available.