PostgreSQL FORMAT

Introduction to PostgreSQL FORMAT

The PostgreSQL provides us with the FORMAT() function which givens the formatted output based on the format string. The PostgreSQL FORMAT function is the same as the sprintf() function provided in the C programming language. The PostgreSQL FORMAT is an extension of the built-in function provided by PostgreSQL for formatting. The PostgreSQL FORMAT() functions help us to build the dynamic SQL statements or get the formatted result which contains various operations provided on string input like concatenation etc. The input array values are considered as ordinary arguments to the FORMAT() function.

Syntax of PostgreSQL FORMAT

FORMAT(format_string [, format_arg [, ...] ])

Explanation:

The PostgreSQL FORMAT() function takes multiple numbers of arguments which are termed variadic functions. We can pass the arguments to the PostgreSQL FORMAT() function by using the VARIADIC keyword.

1. format_string

This string defines the required format of the resultant string. It consists of format and text specifiers. The format acts as a placeholder for the input arguments which we are inserting into the output string and the text is placed as it is in the output string.

Understand the format specifier syntax by using the following statement:

%[position][flags][width]type

Explanation:

The percentage(%) character is the starting character of the format specifier.

The format specifier consists of the following optional components:

• Position:

By using the position defined we can decide the argument to be added in the output string. We use the n$ form to define the position.
n: index of the argument.  The first index of the argument is 1.

We can skip the position as by default the PostgreSQL considers the next argument.

• flags:

The can be used to give the instruction to the format specifier to treat the output as left-justified when the flag is minus(-).

It is required to define the width to use the flags component.

• width:

The width field is optional and it is used to define the minimum characters required to use to display the format specifier’s result. In order to fill the width, the output string can get padded with the space characters in the left or right sides. The output is shown as it is by skipping truncation if the width is too small.

The width can have any one of the following values:

1. non-negative integer value.
2. In order to use the width as the next function argument, it will be an asterisk (*).
3. In order to use the width as an nth function argument, it will be in the form *n$.

• Type:
  

The optional component type can be converting an argument value as:

s: For formatting as a string.

I: For treating an SQL identifier.

L: For quoting an SQL literal.

In order to build the dynamic SQL statements we generally use I and L. Also we need to use double percentages %% if we want % to be in the output string.

2. Format_arg argument

As we have seen PostgreSQL FORMAT() function we have multiple arguments.

Output value :

We get the formatted string as a result of The PostgreSQL FORMAT() function.

How does PostgreSQL FORMAT function work?

Consider the following example to understand the working of the PostgreSQL format function. We will create a student table by using the CREATE TABLE statement as follows:

CREATE TABLE student
(
stud_id serial PRIMARY KEY,
stud_fname varchar(80),
stud_lname varchar(80)
);

Now we will insert some data in the ‘student’ table by using the INSERT INTO statement as follows:

INSERT INTO student (stud_fname, stud_lname)
values
('Oliver','Jake'),
('Jack','Connor'),
('Harry','Callum'),
('Jacob','John'),
('Thomas','David');

Illustrate the result of the above statement by using the following snapshot and SQL statement.

select * from student;

 Now we will use the FORMAT() function for building full names of the student from stud_lname, stud_fname columns:

SELECT FORMAT('%s, %s',stud_lname, stud_fname) AS full_name
FROM  Student;

Illustrate the result of the above statement by using the following snapshot

In the above example, we have used %s %s which gets replaced by values in the stud_lname, stud_fname columns.

Examples to Implement FORMAT Function in PostgreSQL

We will go through the following example for understanding the PostgreSQL FORMAT() function.

1. Simple format

SELECT FORMAT('Welcome, %s','EduCBA');

Illustrate the result of the above statement by using the following snapshot :

In the above example, the %s is replaced by the ‘EduCBA’ string argument.

2. Component – flags

Let’s see the statement defined below to understand the usage of the flag.

SELECT FORMAT('|%20s|', 'ten');

The resultant string will be right-aligned and left-padded with white spaces, as we have defined the flag with no sign.
Illustrate the result of the above statement by using the following snapshot

In order to make the result left-aligned we will use the minus sign( – ) in the flag:

SELECT FORMAT('|%-20s|', 'ten');

Illustrate the result of the above statement by using the following snapshot

3. Component – position

Let’s see the statement defined below to understand the usage of the position

SELECT FORMAT('%1$s House, %2$s Villa, %1$s Flat', '2BHK', '4BHK');

 Illustrate the result of the above statement by using the following snapshot

In the above example, we have defined arguments 2BHK and 4BHK strings.  1$ and 2$  are the positions that give instruction to the FORMAT() function to use the (2BHK ) and (4BHK ) as first and second arguments respectively and place them into the respective location.

The position 1$ is placed twice in the format string which results in the first argument 2BHK  to insert twice.

Advantages of using FORMAT Function in PostgreSQL

1. The PostgreSQL format function is used to build the dynamic SQL statement.
2. We can use the optional parameter in the SQL statement by using the FORMAT function.
3. We can Format the result by using the FORMAT function.
4. Column values of the table can be combined by using the FORMAT statement.

Conclusion

We hope from the above article you have understood how to use the PostgreSQL  FORMAT() function and how the PostgreSQL FORMAT() function works. Also, we have added some examples of PostgreSQL FORMAT() function to understand it in detail.

Recommended Articles

This is a guide to PostgreSQL FORMAT. Here we discuss the Examples to implement format function in PostgreSQL along with the Advantages. You may also have a look at the following articles to learn more –

1. PostgreSQL ROLLUP
2. PostgreSQL LAG()
3. SQL RANK()
4. MySQL Constraints