In PostgreSQL, we can convert a string from any particular encoding to the ASCII encoding. This can be performed by using a string function called to_ascii(). We just need to provide the string to the function to get its ASCII encoding. A very small range of encodings (from which the string belongs) is supported by the to_ascii() function. Any encodings other than supported ones cause an error when they are provided to the to_ascii() function.

The content of this article covers the basic workings of the to_ascii() function in Postgres.

How to Use to_ascii() Function in PostgreSQL

The to_ascii() function converts the string from a specific encoding into ASCII encoding. The syntax for the to_ascii() function can be written as:

to_ascii(str [, Enc_name])

● To convert the string into ASCII we have to pass that string into the to_ascii() function.

● We can also optionally specify the name of the encoding, such as “LATIN1”, “LATIN2”, “LATIN9”, and “WIN1250”.

There is another way to do so. The user can also pass the integer values of the particular encoding to do the same. The syntax looks like this:

to_ascii(str [, Enc_Int])

We can optionally specify the integer value of the encoding instead of the encoding name.

The below table represents the encodings supported by the Postgres to_ascii() function and their integer representation.

Only these encodings are supported by the to_ascii() function, any other encoding specified in the to_ascii() function causes an error. By default, the encoding is taken as the encoding of the present/current database.

Let’s move towards some examples to better understand the to_ascii() function.

Example 1: Understanding to_ascii() Function

Consider the following simple query to see how the to_ascii() function works.

SELECT to_ascii('Command Prompt','LATIN2');

The above query says that the string “Command prompt” is from the LATIN2 encoding and it needs to be converted to ASCII. The output of the query is:

The function returns the above output. The string has been encoded to ASCII.

Example 2: Understanding to_ascii() Function Using Integer Representation

As stated earlier, we can also use the integer representation for the encodings. We’ll implement it in this example.

SELECT to_ascii('Command Prompt',16);

This query works fine and returns the following output:

The integer 16 is the representation of LATIN9 encoding. So, the function has encoded the string from the LATIN9 encoding to ASCII.

Example 3: Understanding to_ascii() Function With Specifying Encoding Type

We can also encode the string without specifying the name or the integer representation of the encoding of the string. In this case, the default string is considered to be in its default encoding. By default, the encoding considered is the encoding of your current database which is by default UTF-8.

We can see the encoding of our database by executing the ”\l” command on psql. Let’s execute the command:

\l

In my case, it is the default encoding i.e. UTF-8.

The function only supports a small range of encodings that are specified above in the table. Any string encoded in any other encoding results in an error. So, if we execute the following query, we will encounter an “encoding conversion” error:

SELECT to_ascii('Command Prompt');

It will definitely throw an error because the default UTF-8 encoding is used. So the output is:

We can see that the query has thrown an error. Because the UTF-8 encoding is not supported by the to_ascii() function.

Example 4: Using to_ascii() Function On Table’s Data

We can also perform the ASCII encoding on the table’s data. Consider the table named “passed_candidates”.

We’ll write the following query to implement the to_ascii() function on the “candidate_name” column of this table.

SELECT candidate_id,to_ascii(candidate_name,'LATIN1')
FROM passed_candidates;

The query will return all the entries of the “candidate_name” column encoded in ASCII like this:

This is how the to_ascii() function works.

Conclusion

The to_ascii() function in Postgres converts the provided string from any valid encoding to the ASCII encoding. The encoding in which the string originally is can optionally be mentioned in the function by its name or the integer representation of it. Only a small range of encodings are supported by the to_ascii() function; any string with any other encoding than these encodings throws an error. In this guide, we have discussed what the to_ascii() function does with the help of examples.