EDUCBA

EDUCBA

MENUMENU
  • Explore
    • Lifetime Membership
    • All in One Bundles
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign up
Home Data Science Data Science Tutorials PostgreSQL Tutorial PostgreSQL FORMAT

PostgreSQL FORMAT

Updated May 24, 2023

PostgreSQL FORMAT

Introduction to PostgreSQL FORMAT

The PostgreSQL provides the FORMAT() function, which gives 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. 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 ordinary FORMAT() function arguments.

ADVERTISEMENT
Popular Course in this category
POSTGRESQL Course Bundle - 5 Courses in 1 | 1 Mock Test

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

Syntax of PostgreSQL FORMAT

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

Explanation:

The PostgreSQL FORMAT() function takes multiple numbers of arguments 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 we insert 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:

Using the position defined, we can decide the argument to be added to the output string. We use the n$ form to determine the position.
n: index of the argument. The first index of the argument is 1.

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

  • flags:

This can instruct 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 defines the minimum characters required to display the format specifier’s result. To fill the width, the output string can get padded with the space characters on the left or right sides. The output is shown 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. To use the width as the next function argument, it will be an asterisk (*).
  3. 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.

To build 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

We have multiple arguments, as we have seen PostgreSQL FORMAT() function.

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;

PostgreSQL FORMAT output 1

 Now we will use the FORMAT() function for building the full names of the student from the stud_lname and 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

PostgreSQL FORMAT output 2

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

Examples to Implement FORMAT Function in PostgreSQL

We will use the following example to understand the PostgreSQL FORMAT() function.

1. Simple format

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

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

PostgreSQL FORMAT output 3

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 flag’s usage.

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

Since we have defined the flag with no sign, the resultant string will right-align and left-pad with white spaces.
Illustrate the result of the above statement by using the following snapshot

output 4

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

output 5

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.

output 6

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

Placing the position 1$ twice in the format string causes the insertion of the first argument, “2BHK”, twice.

Advantages of using FORMAT Function in PostgreSQL

  1. We use the PostgreSQL format function to construct dynamic SQL statements.
  2. 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. Combine column values of the table 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 the PostgreSQL FORMAT() function to understand it in detail.

Recommended Articles

We hope that this EDUCBA information on “PostgreSQL FORMAT” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

  1. PostgreSQL ROLLUP
  2. PostgreSQL LAG()
  3. SQL RANK()
  4. MySQL Constraints
ADVERTISEMENT
PROGRAMMING LANGUAGES Course Bundle - 54 Courses in 1 | 4 Mock Tests
338+ Hours of HD Videos
54 Courses
4 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
ADVERTISEMENT
SELENIUM Course Bundle - 15 Courses in 1 | 9 Mock Tests
39+ Hours of HD Videos
15 Courses
9 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
ADVERTISEMENT
IOT System Course Bundle - 7 Courses in 1
43+ Hours of HD Videos
7 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
ADVERTISEMENT
JENKINS Course Bundle - 6 Courses in 1
15+ Hour of HD Videos
6 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
Primary Sidebar
Footer
Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
  • Blog as Guest
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

🚀 Cyber Monday Reloaded Price Drop! All in One Universal Bundle (3700+ Courses) @ 🎁 90% OFF - Ends in ENROLL NOW