PQexecPrepared() question

Igor Korot <ikorot01@gmail.com> writes:

If I want to have strings (aka char *) as parameters fr the query,
should I send UTF-8 strings or something else?

They should be in whatever is selected as the client_encoding
on your connection. That goes for query strings too, and whatever
other text a client might send.

regards, tom lane

Thx, Tom..

Is there some default value for client_encoding?

Thank you.

On Tue, 2025-12-16 at 21:53 -0800, Igor Korot wrote:

Is there some default value for client_encoding?

The default value for a client connection is whatever the parameter
"client_encoding" was set to in the PostgreSQL server configuration.

But that value can be overridden in several ways:

- explicitly by setting "client_encoding" in the connect string

- with the SQL statement SET by the client

- if the client has "client_encoding=auto", by the encoding set in
the client's environment

- by setting the PGCLIENTENCODING environment variable

Yours,
Laurenz Albe

Thank you.

On Tue, Dec 16, 2025 at 11:32 PM Laurenz Albe <laurenz.albe@cybertec.at>
wrote:

Hi,

On Tue, Dec 16, 2025 at 11:32 PM Laurenz Albe <laurenz.albe@cybertec.at>
wrote:

On Tue, 2025-12-16 at 21:53 -0800, Igor Korot wrote:

Is there some default value for client_encoding?

The default value for a client connection is whatever the parameter
"client_encoding" was set to in the PostgreSQL server configuration.

Imagine following scenario:

I have 2 machines. One is running PG server on *nix. Second is my app on
Windows.

An application starts for the first time.

What is “clientencoding in this case?

Thank you.

On Thu, Dec 18, 2025 at 8:20 AM Igor Korot <ikorot01@gmail.com> wrote:

Imagine following scenario:

I have 2 machines. One is running PG server on *nix. Second is my app on
Windows.

An application starts for the first time.

What is “clientencoding in this case?

This day in age, probably UTF-8; which is what most servers are
initialized using. If you aren't having issues with encoding I suggest you
just take for granted that the defaults work in 99% of the cases. If you
are having issues, share the details.

David J.

Hi, David,

On Thu, Dec 18, 2025 at 7:41 AM David G. Johnston
<david.g.johnston@gmail.com> wrote:

On Thu, Dec 18, 2025 at 8:20 AM Igor Korot <ikorot01@gmail.com> wrote:

Imagine following scenario:

I have 2 machines. One is running PG server on *nix. Second is my app on Windows.

An application starts for the first time.

What is “clientencoding in this case?

This day in age, probably UTF-8; which is what most servers are initialized using. If you aren't having issues with encoding I suggest you just take for granted that the defaults work in 99% of the cases. If you are having issues, share the details.

My code:

for( int i = 0; i < PQntuples( res ); i++ )
{
std::wstring cat = m_pimpl->m_myconv.from_bytes(
PQgetvalue( res, i, 0 ) );
std::wstring schema = m_pimpl->m_myconv.from_bytes(
PQgetvalue( res, i, 1 ) );
std::wstring table = m_pimpl->m_myconv.from_bytes(
PQgetvalue( res, i, 2 ) );
char *table_owner = PQgetvalue( res, i, 3 );
pimpl.m_tableDefinitions[cat].push_back( TableDefinition(
cat, schema, table ) );
count++;
paramValues = schema + L"." + table;
params[0] = new char[paramValues.length() + 2];
memset( params[0], '\0', paramValues.length() + 2 );
std::wcstombs( params[0], paramValues.c_str(),
paramValues.length() );
params[1] = new char[table.length() + 2];
memset( params[1], '\0', table.length() + 2 );
std::wcstombs( params[1], table.c_str(), table.length() );
params[2] = new char[schema.length() + 2];
memset( params[2], '\0', schema.length() + 2 );
std::wcstombs( params[2], schema.c_str(), schema.length() + 2 );
paramFormat[0] = paramFormat[1] = paramFormat[2] = 0;
paramLength[0] = paramValues.length();
paramLength[1] = table.length();
paramLength[2] = schema.length();
res8 = PQexecPrepared( m_db, "set_table_prop", 3, params,
paramLength, paramFormat, 0 );
if( PQresultStatus( res8 ) != PGRES_COMMAND_OK )
{
std::wstring err = m_pimpl->m_myconv.from_bytes(
PQerrorMessage( m_db ) );
errorMsg.push_back( L"Error executing query: " + err );
result = 1;
}
PQclear( res8 );
delete[] params[0];
params[0] = nullptr;
delete[] params[1];
params[1] = nullptr;
delete[] params[2];
params[2] = nullptr;
}

And ths s what I have n the DB:

table_catalog | table_schema | table_name | table_type |
self_referencing_column_name | reference_generation |
user_defined_type_catalog | user_defined_type_schema |
user_defined_type_name | is_insertable_into | is_typed | commit_action
---------------+--------------+------------+------------+------------------------------+----------------------+---------------------------+--------------------------+-
-----------------------+--------------------+----------+---------------
draft | public | abcß | BASE TABLE |
| | |
|
| YES | NO |

Using my setup above and assumng my Win locale is en_US.UTF8 I can
successfullly retreve
that record but the insertion fails with

Invalid byte sequence for parameter $1 in UTF8

Thank you.

On Thu, 2025-12-18 at 07:20 -0800, Igor Korot wrote:

Imagine following scenario:

I have 2 machines. One is running PG server on *nix. Second is my app on Windows.

An application starts for the first time.

What is “clientencoding in this case?

If I read the code correctly:

- if PGCLIENTENCODING is set in the environment of the client executable, that

- otherwise, if "client_encoding" is set on the server, that

- otherwise, SQL_ASCII

Yours,
Laurenz Albe

---------- Forwarded message ---------
From: Igor Korot <ikorot01@gmail.com>
Date: Fri, Dec 19, 2025 at 9:17 AM
Subject: Re: PQexecPrepared() question
To: Laurenz Albe <laurenz.albe@cybertec.at>

Hi, Lauren’s,

On Thu, Dec 18, 2025 at 11:43 PM Laurenz Albe <laurenz.albe@cybertec.at>
wrote:

On Thu, 2025-12-18 at 07:20 -0800, Igor Korot wrote:

Imagine following scenario:

I have 2 machines. One is running PG server on *nix. Second is my app on

Windows.

An application starts for the first time.

What is “clientencoding in this case?

If I read the code correctly:

- if PGCLIENTENCODING is set in the environment of the client executable,
that

No it is not.

- otherwise, if "client_encoding" is set on the server, that

I guess I is.
But what is the purpose of setting client encoding on the server? It is for
the client

Thank you.

Igor Korot <ikorot01@gmail.com> writes:

But what is the purpose of setting client encoding on the server? It is for
the client

No, it's to tell the server what encoding to transmit to the client
(as well as what encoding strings coming from the client are in).

regards, tom lane

On Fri, Dec 19, 2025 at 1:52 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

Igor Korot <ikorot01@gmail.com> writes:

But what is the purpose of setting client encoding on the server? It is

for

the client

No, it's to tell the server what encoding to transmit to the client
(as well as what encoding strings coming from the client are in).

If there's an encoding mismatch between the server and client, does
"something" (like a PQ driver) convert text in the data stream from the
source encoding to the target encoding?

--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!

Ron Johnson <ronljohnsonjr@gmail.com> writes:

If there's an encoding mismatch between the server and client, does
"something" (like a PQ driver) convert text in the data stream from the
source encoding to the target encoding?

The server does that itself.

regards, tom lane

On 12/19/25 11:12, Ron Johnson wrote:

On Fri, Dec 19, 2025 at 1:52 PM Tom Lane <tgl@sss.pgh.pa.us
<mailto:tgl@sss.pgh.pa.us>> wrote:

Igor Korot <ikorot01@gmail.com <mailto:ikorot01@gmail.com>> writes:

But what is the purpose of setting client encoding on the server?

It is for

the client

No, it's to tell the server what encoding to transmit to the client
(as well as what encoding strings coming from the client are in).

If there's an encoding mismatch between the server and client, does
"something" (like a PQ driver) convert text in the data stream from the
source encoding to the target encoding?

It is explained here:

https://www.postgresql.org/docs/current/multibyte.html#MULTIBYTE-AUTOMATIC-CONVERSION

--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!

--
Adrian Klaver
adrian.klaver@aklaver.com

Hi, ALL,

On Fri, Dec 19, 2025 at 9:17 AM Igor Korot <ikorot01@gmail.com> wrote:

Hi, Lauren’s,

On Thu, Dec 18, 2025 at 11:43 PM Laurenz Albe <laurenz.albe@cybertec.at>
wrote:

On Thu, 2025-12-18 at 07:20 -0800, Igor Korot wrote:

Imagine following scenario:

I have 2 machines. One is running PG server on *nix. Second is my app

on Windows.

An application starts for the first time.

What is “clientencoding in this case?

If I read the code correctly:

- if PGCLIENTENCODING is set in the environment of the client executable,
that

No it is not.

- otherwise, if "client_encoding" is set on the server, that

I just checked the postgres.conf.

This file does not have any client_encoding.

I guess I is.
But what is the purpose of setting client encoding on the server? It is
for the client

Thank you.

- otherwise, SQL_ASCII

Which means that this is an encoding that will be used.

But then I don’t understand anything.

The code I posted above worked fine on SELECT, but INSERT failed.

If the SQL_ASCII is the encoding used both operations should fail. Or both
succeeds.

Could someone explain what happened?

Thank you.

On Fri, 2025-12-19 at 20:10 -0800, Igor Korot wrote:

What is “clientencoding in this case?

- if PGCLIENTENCODING is set in the environment of the client executable, that

No it is not.

- otherwise, if "client_encoding" is set on the server, that

I just checked the postgres.conf.

This file does not have any client_encoding.

- otherwise, SQL_ASCII

Which means that this is an encoding that will be used.

You can verify that with the SQL statement "SHOW client_encoding"
in your sample program.

But then I don’t understand anything.

The code I posted above worked fine on SELECT, but INSERT failed.

If the SQL_ASCII is the encoding used both operations should fail. Or both succeeds.

Could someone explain what happened?

SQL_ASCII as client encoding means that no conversion will take place.

Still, the database encoding (I suspect UTF8) will govern what can be stored
in the database. Anything that is not valid UTF-8 will be rejected.

A SELECT will never cause an error - the client will just receive data
in UTF-8.

Yours,
Laurenz Albe

Hi, Lauren’z,

On Fri, Dec 19, 2025 at 10:24 PM Laurenz Albe <laurenz.albe@cybertec.at>
wrote:

On Fri, 2025-12-19 at 20:10 -0800, Igor Korot wrote:

What is “clientencoding in this case?

- if PGCLIENTENCODING is set in the environment of the client

executable, that

No it is not.

- otherwise, if "client_encoding" is set on the server, that

I just checked the postgres.conf.

This file does not have any client_encoding.

- otherwise, SQL_ASCII

Which means that this is an encoding that will be used.

You can verify that with the SQL statement "SHOW client_encoding"
in your sample program.

Thx, will check.

But then I don’t understand anything.

The code I posted above worked fine on SELECT, but INSERT failed.

If the SQL_ASCII is the encoding used both operations should fail. Or

both succeeds.

Could someone explain what happened?

SQL_ASCII as client encoding means that no conversion will take place.

Still, the database encoding (I suspect UTF8) will govern what can be
stored
in the database. Anything that is not valid UTF-8 will be rejected.

Rejected how?

A SELECT will never cause an error - the client will just receive data
in UTF-8.

And then what?

I’ll check the encoding and report back..

Thank you.

On Fri, 2025-12-19 at 23:04 -0800, Igor Korot wrote:

SQL_ASCII as client encoding means that no conversion will take place.

Still, the database encoding (I suspect UTF8) will govern what can be stored
in the database.  Anything that is not valid UTF-8 will be rejected.

Rejected how?

As you experienced: by throwing an error.

A SELECT will never cause an error - the client will just receive data
in UTF-8.

And then what?

The *client application* will receive UTF-8 data.

Yours,
Laurenz Albe

Hi, Laurenz,

On Fri, Dec 19, 2025 at 10:24 PM Laurenz Albe <laurenz.albe@cybertec.at> wrote:

On Fri, 2025-12-19 at 20:10 -0800, Igor Korot wrote:

What is “clientencoding in this case?

- if PGCLIENTENCODING is set in the environment of the client executable, that

No it is not.

- otherwise, if "client_encoding" is set on the server, that

I just checked the postgres.conf.

This file does not have any client_encoding.

- otherwise, SQL_ASCII

Which means that this is an encoding that will be used.

You can verify that with the SQL statement "SHOW client_encoding"
in your sample program.

I added the following code in my app:

res = PQexec( m_db, "SHOW client_encoding" );
auto value = PQgetvalue( res, 0, 0 );
PQclear( res );

and the value of the "value" variable is "UTF8".

But then I don’t understand anything.

The code I posted above worked fine on SELECT, but INSERT failed.

If the SQL_ASCII is the encoding used both operations should fail. Or both succeeds.

Could someone explain what happened?

SQL_ASCII as client encoding means that no conversion will take place.

Still, the database encoding (I suspect UTF8) will govern what can be stored
in the database. Anything that is not valid UTF-8 will be rejected.

A SELECT will never cause an error - the client will just receive data
in UTF-8.

The exact error message is:

ERROR: invalid byte sequence for encoding UTF8: 0xdf
CONTEXT: unnamed portal parameter $1

on the INSERT.

Thank you.

Igor Korot <ikorot01@gmail.com> writes:

I added the following code in my app:
res = PQexec( m_db, "SHOW client_encoding" );
auto value = PQgetvalue( res, 0, 0 );
PQclear( res );
and the value of the "value" variable is "UTF8".

Okay ...

The exact error message is:
ERROR: invalid byte sequence for encoding UTF8: 0xdf
CONTEXT: unnamed portal parameter $1
on the INSERT.

client_encoding governs both the encoding that the server will
send, and the encoding that it expects to receive. You are
sending a parameter string that is not valid UTF8.

regards, tom lane