Postgresql Format + 28 Examples

In this PostgreSQL tutorial, we will study the use of the Postgresql format functions and we are going to discuss the following list of topics.

• Postgresql format function
• Postgresql format number with commas
• Postgresql formatter currency
• Postgresql formatter
• Postgresql formatter online
• Postgresql format string
• Postgresql format number decimal places
• Postgresql format number thousand separator
• Postgresql format age
• Postgresql time format am pm
• Postgresql binary format timestamp
• Postgresql format current_timestamp
• Postgresql format float
• Postgresql hour format
• Postgresql format current_date
• Postgresql format bigint
• Postgresql Avg format
• Postgresql autoformat code
• Postgresql format bytes
• Postgresql bytea format
• Postgresql format array
• Postgresql format backup
• Postgresql format char
• Postgresql binary file
• Postgresql format boolean
• Postgresql format percentage
• Postgresql format date as a string
• Postgresql timestamp to date
• Postgresql format character varying
• Postgresql format csv
• Postgresql timestamp 24 hour format

Postgresql format function

In Postgresql, FORMAT is a function that formats arguments based on a format string. This format function is quite similar to the sprintf( ) function in the C programming language. Let’s understand the syntax for the FORMAT function.

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

In the above syntax, the FORMAT function is variadic which means we can easily pass the arguments as an array with the keyword VARIADIC. The FORMAT function will analyze the array elements as normal arguments and an array of zero elements as NULL. Let’s understand this with help of an example.

SELECT FORMAT('Howdy, %s','United States');

Let’s check the output of the above example.

Read: Postgresql replace + Examples

Postgresql format number with commas

The TO_CHAR( ) function is used in Postgresql to format numbers including formatting numbers with commas in the compatible location. It supports money data type which gives an output of the value using the current locale. It also includes commas at the right place built upon the locale being used.

Let’s understand this with the help of an example to output a number with commas.

SELECT TO_CHAR(782933.63, 'fm999G999D99');

Let’s check the output of the above query.

The second argument in the output comprises a bundle of numeric template patterns that will tell us how the first argument is formatted. We have used the G template pattern in the query for a locale-aware group separator which is also referred to as a “thousands separator”.

A template pattern for a locale-aware decimal separator is used for D in the given output.
We have also used fm that means Fill Mode to suppress any trailing zeros and leading spaces that might be automatically applied to the outcome. Template pattern 9 is applicable for each digit.

Read: PostgreSQL Subquery with Examples

Postgresql format currency

PostgreSQL supports money data types that will output its values formatted in a locale-aware manner. Let’s check an example for the dollar.

SET lc_monetary = 'en_US';
SELECT CAST(243446.58 AS money);

Let’s check the output of the above query.

Read: Postgresql Average + Examples

Postgresql formatter

A formatter facility is given to the user by Postgresql. This formatter works as a console formatter which automatically detects the basic environment of the PostgreSQL statements as an output in an HTML file or text file.

The essential function of this formatter is to give a worthy representation of PostgreSQL statements that means it avoids complexity in a statement. The PostgreSQL formatter provides various options for execution such as anonymization, comma-start, comma- break, wrap- comments, comma end, and many more.

Postgresql formatter gives a suitable representation of the query to avoid the complexity of query statements. Depending on our need, we can use various options in the command. We can also use this formatter with various editor tools that mean as per our choice we can use any tool to format query statements.

PostgreSQL Formatter Works as first we need to install a pg formatter in your system. Secondly, we should have basic knowledge about PostgreSQL. Then we also require a query statement to perform the formatter. Moreover, need for basic knowledge about the pg formatter that means how it is used.

We can perform various operations on SQL statements with the help of a pg formatter. Let’s understand with the help of an example of indentation.

CREATE TABLE Student ( stu_id serial PRIMARY KEY, stuname VARCHAR ( 50 )  NOT NULL,      stuaddress VARCHAR ( 50 ) NOT NULL, email VARCHAR ( 255 ) UNIQUE NOT NULL);

We have tried to execute indention in the query statement in the above example. In this example, we have created a table name as Student with various attributes as shown in the SQL statement after applying the indentation option we represent the outcome of the above declaration by using the use of the below statement.

CREATE TABLE Student (stu_id serial PRIMARY KEY,
stuname VARCHAR (50) NOT NULL,
stuaddress VARCHAR (50) NOT NULL,
email VARCHAR (255) UNIQUE NOT NULL);

Read: Postgresql unique constraint

Postgresql formatter online

In Postgresql, PGFormatter is an open project and is a SQL and PlPgsql formatter that supports keywords from SQL-92, SQL-99, SQL-2003, SQL-2008, SQL-2011, and PostgreSQL specifics keywords. It might work with other databases also. PGFormatter is the enhanced SQL and PlPgsql code formatter and beautifier dedicated to PostgreSQL. It is provided as a CLI or a CGI program.

Postgresql format string

The format_string argument is a string that tells us how the result string is formatted. It comprises text and format specifiers. Text is used directly to the resultant string while the accessible format specifiers are parameters for the arguments to be inserted into the resultant value. The below syntax is for format specifier.

%[position][flags][width]type

A format specifier will start with a % character consisting of optional components like position, flags, width, and a required component type.

(1) The position will describe which argument will be inserted within the outcome string. The position is within the form n$ where n is the argument index. The primary argument will start from 1. If the position component is discarded, the default is the next argument in the list.

(2) The flags receive a minus sign that instructs the format specifier’s output to be left-justified. The flags component only takes effect when the width field is described.

(3) The width will explain the minimum number of characters that is used for display in the format specifier’s output. The output will be displayed without any truncation if the width is small.

The width can be one of the following values such as positive integer or an asterisk (*) to use the next function argument as the width. Also, a string of the form *n$ to use the nth function argument as the width.

(4)The type is used to describe the output string from the format specifier. It holds below values for type argument are as follows.

• s will format the argument value as a string. NULL could be treated as an empty string.
• I deal with argument value as an SQL identifier.
• L refers to the argument value as an SQL literal.
  We frequently use I and L for building dynamic SQL statements.

If we want to add % within the result string, we can use double percentages %%.

(5) In format_arg number of format arguments will be passed to the FORMAT() function. And in the return value, the FORMAT() function returns a formatted string.

Let’s check an example that uses the FORMAT() function to construct Student’s full names from first names and last names from the Student’s table. Let’s check the query first.

SELECT FORMAT('%s, %s', lastName, firstName) full_name 
FROM Students ORDER BY full_name;

Let’s check the output of the above query.

Read: PostgreSQL Length + 12 Examples

Postgresql format number decimal places

In Postgresql, to output numbers in a given format is used by the to_char function.
We will be given two arguments in which the first argument will be the number that is to be formatted. How it will be formatted is selected by another argument.

The text will be the return value. We can also use the cast function if we want to convert the number to the right data type. Let’s check some examples below.

SELECT to_char(50, '99.99');

SELECT to_char(50, '99');

Let’s check the output.

The satisfying template pattern is this character that we format that comprises of string if we format numbers. It describes a digit position in the case of 9. Moreover, it can be replaced by a space if it is a leading zero.

And in the case of trailing zero and fill mode is explained then it is discarded. In the second query, when we discard the decimal place and fractional seconds portion the result is different.

Read: Postgresql now() function

Postgresql format number thousand separator

Now we will learn how to format numbers with thousand separators and decimal separators. Let’s take a decimal number like 3498623.05. Now we will make these numbers have space as thousand separators and commas as decimal separators.

We can use to_char to format the given number. Firstly we will set lc_numeric appropriately for the decimal separator. Because the group separator for that locale is ., we use spaces explicitly. Let’s check the example below.

SET lc_numeric='de_DE';
SELECT to_char(3498623.05, '9 999 999D999');

Let’s check the output of the above query.

Read: PostgreSQL Rename Column

Postgresql format age

In Postgresql, we write the format of the age in a column which is also written as YY “Years” mm “Months” DD “Days“. Let’s check the query below for an example.

SELECT TO_CHAR(age(current_date, '2011-04-17 00:00:00.0'), 'YY "Years" mm "Months" DD "Days"') as length_of_job;

Let’s check the output of the above query.

Read: PostgreSQL Trim with Examples

Postgresql time format am pm

In PostgreSQL, the TIME data type allows us to store the time of day values. A time value has a precision of 6 digits. The precision describes the number of fractional digits placed in the second field. This data type involves 8 bytes and ranges from 00:00:00 to 24:00:00. We format the time for am or pm through the following query.

SELECT TO_CHAR(current_timestamp, 'yyyy-mm-dd hh12:mi:ss AM');

Let’s see the output of the above query.

Postgresql binary format timestamp

In Postgresql, we don’t have to unpack the value, because on our 64-bit architecture 8-byte integers can fit into a PostgreSQL. In the output below, 526726800 seconds are 526726800000000 microseconds. Let’s check the format below.

SELECT extract(epoch FROM TIMESTAMP '2019-01-08 09:00:00')
       - extract(epoch FROM TIMESTAMP '2002-05-01 00:00:00');

Let’s check the output below.

Read: PostgreSQL Like With Examples

Postgresql format current_timestamp

In Postgresql, the current_timestamp function returns the current date in a format as ‘YYYY-MM-DD HH:MM: SS.US+TZ’ format. We don’t have to put parentheses () after the current_timestamp function when the precision parameter is not explained Let’s check the command below.

SELECT TO_CHAR(now()::time, 'HH24:MI:SS') ;

Let’s check the output of the above query.

Read: PostgreSQL Delete Row

Postgresql format float

PostgreSQL has various data types. The single table comprises various columns with several data types. We need to store floating numbers that contain decimal points in the float column and values are not approximate that’s why we use float data type.

This data type holds floating-point numbers, real numbers, and numeric with 4 or 8 bytes numbers. We use bit size means the length of the string in float data type. Let’s check the format for the float data type.

SELECT round( CAST(float8 '5.1515927' as numeric), 2);

Let’s check the output below.

Postgresql hour format

In Postgresql, the pattern of an hour format is HH. An hour of the day is denoted as 01-12. Let’s check an example for a better understanding.

SELECT '2021-11-01 02:42:22 PM'::timestamp;

Let’s check the output.

Postgresql format current_date

In PostgreSQL, the CURRENT_DATE function will return the current date. The value is in the ‘YYYY-MM-DD’ format in which ‘YYYY’ is a 4-digit year, ‘MM’ is a 2-digit month, and ‘DD’ is a 2-digit day.

This format is defined as ‘YYYY’ is a 4-digit year, ‘MM’ is a 2-digit month, and ‘DD’ is a 2-digit day. The value that has returned is of date data type. Let’s check the example below.

SELECT CURRENT_DATE;

Let’s check the output of the above example.

Read: Postgresql Add Foreign Key

Postgresql format bignit

In PostgreSQL, there is a type of integer type called BIGINT. It has a storage size of 8 bytes and has the capacity of storing integers which are in the range of -9, 223, 372, 036, 854, 775, 808 to +9, 223, 372, 036, 854, 775, 807. Let’s check the example below.

select to_timestamp(20120822193532::text, 'YYYYMMDDHH24MISS');

Let’s check the output.

Postgresql Avg format

In PostgreSQL, an AVG function is used for finding out the average of a field in various records. This function in Postgresql does not define round(double precision, integer). Let’s check an example below.

 SELECT round( CAST(float8 '3.1415927' as numeric), 2);

Let’s check the output.

Read: Postgresql Joins

Postgresql autoformat code

This feature is not possible to implement in pgadmin4 which does autoformat queries instantly but this feature can be implemented in the future as developers are currently working on this.

Postgresql format bytes

In Postgresql, a function for showing sizes in bytes in a human-readable format.
pg_size_pretty() is a system function for showing sizes in bytes into human-readable format.

The size in the output with the right size unit is bytes in the format KB, MB, GB, TB, or PB. The units are powers of 2 in place of powers of 10 such as 1kB represents 1024 bytes, 1MB represents 1048576 bytes (10242), etc. Let’s check its use.

pg_size_pretty ( bigint ) → text
pg_size_pretty ( numeric ) → text

Let’s check the example.

SELECT pg_size_pretty(16384::bigint);

SELET pg_size_pretty(1024*9:: bigint);

SELECT pg_size_pretty(1024*6::bigint), pg_size_pretty(10240::bigint);

Let’s check the output.

Read: PostgreSQL group by with examples

Postgresql bytea format

In Postgresql, the bytea data type allows the storage of binary strings or which are generally raw bytes. Only hex format is used. The Hex format strings are further preceded by backslash with x (\x) and escape format strings are preceded by backslash (). Let’s check the query for it.

SELECT '\000001'::bytea AS bytea_val;


SELECT '\xDEADBEEF'::bytea AS bytea_val;

Let’s check the output.

Read: PostgreSQL add primary key

Postgresql format array

There is an array format in Postgresql, but it needs certain arguments and we will check that how many items are there in the array. Let’s check the query and then its output.

SELECT format('%s %s', 'Howdy', 'United States');

Let’s check the output.

Postgresql format backup

In Postgresql, for backing up a database pg_dump is used. It makes logical backups when the database is getting used at once. This pg_dump does no longer block different users from having access to the database.

The Dump is output in script or archive file formats. The Script dumps are plain-textual files that contain the commands required to reconstruct the database when it was saved. Let’s check the below format.

pg_dump mytable > mytable_backup.sql

pg_dump -U postgres -Fc myDB > myDB.dump


pg_restore -j 8 -U postgres -d myDB myDB.dump

Postgresql format char

In PostgreSQL, the TO_CHAR() function converts a timestamp, an interval, an integer, a double-precision, and a numeric value to a string. This function will return a string in a TEXT data type that illustrates the primary argument formatted according to the desired format. Let’s check the syntax below.

TO_CHAR(expression, format)

Let’s check examples and will learn how to use the to_char function in PostgreSQL.

SELECT to_char(3110, '9999.99');

SELECT to_char(3110.7, '9G999.99');

SELECT to_char(3110.7, 'L9G999.99');

SELECT to_char(3110.7, 'L9G999');

SELECT to_char(311, '9 9 9');

SELECT to_char(311, '00999');

Let’s check the output of the above queries.

Read: Postgresql while loop

Postgresql format binary file

In Postgresql, the bytea data type has the storage of binary strings. This data type has a storage size of 1 or 4 bytes plus the actual binary string with variable-length binary string.

The “hex” format will encode binary data as 2 hexadecimal digits per byte. The whole string is preceded by the sequence \x which is used to differentiate it from the escape format. Let’s check an example below.

SELECT E'\\xDEADBEEF';

Let’s check the output of the above query.

Postgresql format boolean

Only a single data type in boolean is provided by Postgresql. It uses one byte for storing a boolean value in the database. The BOOLEAN is abbreviated as BOOL. A Boolean value can be TRUE, FALSE, or NULL. BOOLEAN can have only three values: true, false, and NULL. Let’s understand this with the help of an example.

CREATE TABLE formatbool (
  name text,
  alive boolean
  );

INSERT INTO formatbool VALUES ('Clay Jensen', TRUE);
INSERT INTO formatbool VALUES ('Phoebe Buffay', FALSE);

SELECT name, alive FROM formatbool;

SELECT name, alive::text FROM formatbool;

SELECT name, UPPER(alive::text) AS alive FROM formatbool;

Let’s check the output of the above query.

Read: Postgresql REGEXP_REPLACE Function

Postgresql format percentage

In Postgresql, this percentage format comprises of passing the number and a numeric template pattern to the function to return the number formatted in the way described by the template pattern. For getting a percentage sign, we can have it in our template pattern. Let’s understand with the help of an example.

SELECT TO_CHAR(75, 'fm00D00%');

Let’s check the output of the above query.

In the above output, the 0 template pattern means that the digit position is always printed, although it contains leading or trailing zeros. Moreover, we have also used the fm format modifier to suppress any leading/trailing zeros or blanks.

Postgresql format date as a string

In Postgresql, there is no format for the DATE column. We are not able to specify a format for it. We can use DateStyle to control how PostgreSQL emits dates, but it’s global and a bit limited. Instead, we can use to_char to format the date when you query it or format it in the client application. Let’s check the query below.

SELECT to_char(DATE '2021-12-05', 'DD/MM/YYYY');

Let’s check the output.

Postgresql format timestamp to date

In Postgresql, the Now function is used to get the current timestamp means the current date and current time. When it is joined with the keyword date with this “::” operator, then it can be used to format the current timestamp to date. Let’s check the query below.

SELECT NOW()::date;

Let’s check the output of the above query.

Postgresql format character varying

In Postgresql, the character varying will specify that we will use column data type as character varying. Character varying will work the same as a varchar data type in PostgreSQL. We can pass the number of characters that are allowed in the column field.

Let’s check the below example to check that we have defined data type at the time of table creation. We will define character varying data types of student_name, student_test, and student_address column.

Create table studt_char(stud_id serial primary key, 
student_name character varying(100), 
student_test character varying(1000), 
student_address character varying(100), 
student_phone int, pincode int);
\d+ studt_char;

Let’s check the output now.

Postgresql format csv

In Postgresql, this technique basically runs on the remote server. We can’t write to our local PC which actually needs to be run as a Postgresql superuser called root. Let’s check PostgreSQL’s built-in COPY command.

Copy (Select * From foo) To '/tmp/test.csv' With CSV DELIMITER ',' HEADER;

The other technique is to do the file handling on the client-side which is in our application. The Postgresql server doesn’t need to know what file we are copying to, it just spits out the data and the client puts it somewhere. The psql command-line client has a meta-command also known as a copy which takes all the similar options as the “real” COPY, however, runs inside the client.

\copy (Select * From foo) To '/tmp/test.csv' With CSV

Postgresql timestamp 24 hour format

In Postgresql, we will check how to convert 24 hours timestamp format. Below is the query for the 24-hour format.

SELECT '2021-12-06 01:12:22 PM'::timestamp;

Let’s check the output of the above query.

In this tutorial, we have studied the use of the Postgresql format functions and we have discussed the following list of topics.

• Postgresql format function
• Postgresql format number with commas
• Postgresql formatter currency
• Postgresql formatter
• Postgresql formatter online
• Postgresql format string
• Postgresql format number decimal places
• Postgresql format number thousand separator
• Postgresql format age
• Postgresql time format am pm
• Postgresql binary format timestamp
• Postgresql format current_timestamp
• Postgresql format float
• Postgresql hour format
• Postgresql format current_date
• Postgresql format bigint
• Postgresql Avg format
• Postgresql autoformat code
• Postgresql format bytes
• Postgresql bytea format
• Postgresql format array
• Postgresql format backup
• Postgresql format char
• Postgresql binary file
• Postgresql format boolean
• Postgresql format percentage
• Postgresql format date as a string
• Postgresql timestamp to date
• Postgresql format character varying
• Postgresql format csv
• Postgresql timestamp 24 hour format