Re: how can I fix my accent issues?

[Adrian Klaver]

On 12/12/23 16:09, Igniris Valdivia Baez wrote:

Hello to all, to clarify the data is moving this way:
1. The data is extracted from a database in postgres using Pentaho(Kettle)
2. Here is there is a bifurcation some data is loaded into the destiny 
database and behaves fine the other scenario the data is saved in xls 
files to be reviewed


How is saved to xls files?

3. After the revision the data is loaded to the destiny database and 
here is were I believe the issue is, because the data is reviewed in 
Windows and somehow Pentaho is not understanding correctly the 
interaction between both operating systems.


Defined reviewed, on particular is the data changed?

How is transferred from xls to to the database?

Is the data reviewed in Excel only on one machine or many?

What the locales/encodings/character sets involved?



PD: when the hole operation is executed in Windows it never fails


Define what you mean by whole operation done in Windows.


Thank you all



--
Adrian Klaver
adrian.kla...@aklaver.com

Re: how can I fix my accent issues?

[Igniris Valdivia Baez]

Hello to all, to clarify the data is moving this way:
1. The data is extracted from a database in postgres using Pentaho(Kettle)
2. Here is there is a bifurcation some data is loaded into the destiny
database and behaves fine the other scenario the data is saved in xls files
to be reviewed
3. After the revision the data is loaded to the destiny database and here
is were I believe the issue is, because the data is reviewed in Windows and
somehow Pentaho is not understanding correctly the interaction between both
operating systems.

PD: when the hole operation is executed in Windows it never fails
Thank you all

El mar., 12 de diciembre de 2023 7:00 p. m., Adrian Klaver <
adrian.kla...@aklaver.com> escribió:

> On 12/12/23 15:54, Igniris Valdivia Baez wrote:
>
> Please use Reply All to reply to list also
> Ccing list
>
> > PgAdmin 4 but it looks the same in the console and from postman.
> > I believe that the problem is the xls that is generated from a postgres
> > database opened in Windows to fulfill a review requirement and imported
> > again using Pentaho, because I'm moving another data using the same
> > environment and it's fine the difference is the review xls
>
> Huh, where did that come from?
>
> At no point previously have you indicated xls(Excel?) was involved.
>
> Provide a more detailed explanation that the route the data is taking to
> get to the database.
>
>
> > Thank you
> >
> > El mar., 12 de diciembre de 2023 6:04 p. m., Adrian Klaver
> > mailto:adrian.kla...@aklaver.com>> escribió:
> >
> > On 12/12/23 12:44, Igniris Valdivia Baez wrote:
> >  > this is the result I got, now I have to figure it out how to
> > solve it,
> >  > thank you so much
> >
> > In what client are you viewing the data?
> >
> >
> > --
> > Adrian Klaver
> > adrian.kla...@aklaver.com 
> >
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>
>

Re: how can I fix my accent issues?

[Adrian Klaver]

On 12/12/23 15:54, Igniris Valdivia Baez wrote:

Please use Reply All to reply to list also
Ccing list


PgAdmin 4 but it looks the same in the console and from postman.
I believe that the problem is the xls that is generated from a postgres 
database opened in Windows to fulfill a review requirement and imported 
again using Pentaho, because I'm moving another data using the same 
environment and it's fine the difference is the review xls


Huh, where did that come from?

At no point previously have you indicated xls(Excel?) was involved.

Provide a more detailed explanation that the route the data is taking to 
get to the database.




Thank you

El mar., 12 de diciembre de 2023 6:04 p. m., Adrian Klaver 
mailto:adrian.kla...@aklaver.com>> escribió:


On 12/12/23 12:44, Igniris Valdivia Baez wrote:
 > this is the result I got, now I have to figure it out how to
solve it,
 > thank you so much

In what client are you viewing the data?


-- 
Adrian Klaver

adrian.kla...@aklaver.com 



--
Adrian Klaver
adrian.kla...@aklaver.com

Re: how can I fix my accent issues?

[Adrian Klaver]

On 12/11/23 10:54, Igniris Valdivia Baez wrote:

hello to all, thanks for your answers i've changed the encoding using this:
ALTER DATABASE testdb
SET client_encoding = WIN1252;

now when we try to select data from a table we get this error:

ERROR: character with byte sequence 0xe2 0x80 0x8b in encoding "UTF8"
has no equivalent in encoding "WIN1252" SQL state: 22P05ERROR:
character with byte sequence 0xe2 0x80 0x8b in encoding "UTF8" has no
equivalent in encoding "WIN1252" SQL state: 22P05

i want to clarify that the postgres on dev is in a docker environment
that already have databases in it so we can't change encoding for the
hole container


You don't have to:

https://www.postgresql.org/docs/current/manage-ag-templatedbs.html

Another common reason for copying template0 instead of template1 is that 
new encoding and locale settings can be specified when copying 
template0, whereas a copy of template1 must use the same settings it 
does. This is because template1 might contain encoding-specific or 
locale-specific data, while template0 is known not to.




thanks in advance

El sáb, 9 dic 2023 a las 1:01, Laurenz Albe
() escribió:


On Fri, 2023-12-08 at 23:58 -0500, Igniris Valdivia Baez wrote:

hello, I have an ETL process collecting data from a postgresql
database and xls files and inserting in a postgresql database that
process occurs great in a local DB in postgres 14 with UTF8
codification and Spanish_Cuba.1952 collation but when I execute that
process in dev which is in postgres 15 and UTF8 with collation
en_US.utf8 the words with accents and ñ looks like an interrogation
symbol, what can I do to fix this?


If the data you are sending are encoded in WINDOWS-1252 (I assume that
"1952" is just a typo), you should set the client encoding to WIN1252,
so that PostgreSQL knows how to convert the data correctly.

You can do that in several ways; the simplest might be to set the
environment variable PGCLIENTENCODING to WIN1252.

Yours,
Laurenz Albe





--
Adrian Klaver
adrian.kla...@aklaver.com

Re: how can I fix my accent issues?

[Adrian Klaver]

On 12/12/23 12:44, Igniris Valdivia Baez wrote:

this is the result I got, now I have to figure it out how to solve it,
thank you so much


In what client are you viewing the data?


--
Adrian Klaver
adrian.kla...@aklaver.com

Re: how can I fix my accent issues?

[Laurenz Albe]

On Tue, 2023-12-12 at 15:44 -0500, Igniris Valdivia Baez wrote:
> this is the result I got, now I have to figure it out how to solve it,

Since you already have a replacement character in the database, the
software that stores the data in the database must be responsible.
PostgreSQL doesn't convert characters to replacement characters.

Yours,
Laurenz Albe

Re: Check column result size in functions

[David G. Johnston]

On Tue, Dec 12, 2023 at 3:13 PM Marcos Pegoraro  wrote:

> My functions should return varchar(5) or should return an exception, but
> sometimes they return CHARACTER VARYING. Why ?
>
> thanks for any explanation
>

The observed behavior is documented.

https://www.postgresql.org/docs/current/sql-createfunction.html  (see notes)

David J.

Check column result size in functions

[Marcos Pegoraro]

My functions should return varchar(5) or should return an exception, but
sometimes they return CHARACTER VARYING. Why ?

create or replace function f_sql() returns varchar(5) language sql as
$$select '0123456789'$$;
--works, but shouldn't
select * from f_sql();

create domain T5 as varchar(5);
create or replace function f_sql_domain() returns T5 language sql as
$$select '0123456789'$$;
--exception because of domain
--ERROR: value too long for type character varying(5)
select * from f_sql_domain();

create or replace function f_sql_table() returns table (V5 varchar(5))
language sql as
$$select '0123456789'$$;
--works, but shouldn't
select * from f_sql_table();

create or replace function f_plpgsql() returns varchar(5) language plpgsql
as
$$begin return '0123456789';end;$$;
--works, but shouldn't
select * from f_plpgsql();

create or replace function f_plpgsql_table() returns table(V5 varchar(5))
language plpgsql as
$$begin return query select '0123456789';end;$$;
--Shouldn't be this exception the response for all other calls ?
--ERROR: structure of query does not match function result type
select * from f_plpgsql_table();

I've created some functions using LANGUAGE SQL but strangely all varchar
results did not respect I've declared.
To be sure column result size is fine I have to use a DOMAIN ? Because even
on PLPGSQL, column size are checked only if RETURN TABLE.

thanks for any explanation

Re: how can I fix my accent issues?

[Igniris Valdivia Baez]

this is the result I got, now I have to figure it out how to solve it,
thank you so much

El mar, 12 dic 2023 a las 14:42, Daniel Verite
() escribió:
>
> Igniris Valdivia Baez wrote:
>
> > hello, thank you for answering, it's not a typo, in the attachments
> > you can see that this is actually my collation, algo a pic of the
> > problem for more clarification,
>
> This character is meant to replace undisplayable characters:
>
> From https://en.wikipedia.org/wiki/Specials_(Unicode_block):
>
>   U+FFFD � REPLACEMENT CHARACTER used to replace an unknown,
>   unrecognised, or unrepresentable character
>
> It would useful to know whether:
>
> - this code point U+FFFD is in the database contents in places
> where accented characters should be. In this case the SQL client is
> just faithfully displaying it and the problem is not on its side.
>
> - or whether the database contains the accented characters normally
> encoded in UTF8. In this case there's a configuration mismatch on the
> SQL client side when reading.
>
> To break down a string into code points to examine it, a query like
> the following can be used, where you replace SELECT 'somefield'
> with a query that selects a suspicious string from your actual table:
>
> WITH string(x) AS (
>SELECT 'somefield'
> )
> SELECT
>   c,
>   to_hex(ascii(c)) AS codepoint
> FROM
>   string CROSS JOIN LATERAL regexp_split_to_table(x, '') AS c
> ;
>
>
> Best regards,
> --
> Daniel Vérité
> https://postgresql.verite.pro/
> Twitter: @DanielVerite

Re: how can I fix my accent issues?

[Daniel Verite]

Igniris Valdivia Baez wrote:

> hello, thank you for answering, it's not a typo, in the attachments
> you can see that this is actually my collation, algo a pic of the
> problem for more clarification,

This character is meant to replace undisplayable characters:

From https://en.wikipedia.org/wiki/Specials_(Unicode_block):

  U+FFFD � REPLACEMENT CHARACTER used to replace an unknown,
  unrecognised, or unrepresentable character

It would useful to know whether:

- this code point U+FFFD is in the database contents in places
where accented characters should be. In this case the SQL client is
just faithfully displaying it and the problem is not on its side.

- or whether the database contains the accented characters normally
encoded in UTF8. In this case there's a configuration mismatch on the
SQL client side when reading.

To break down a string into code points to examine it, a query like
the following can be used, where you replace SELECT 'somefield'
with a query that selects a suspicious string from your actual table:

WITH string(x) AS (
   SELECT 'somefield'
)
SELECT
  c,
  to_hex(ascii(c)) AS codepoint
FROM
  string CROSS JOIN LATERAL regexp_split_to_table(x, '') AS c
;


Best regards,
-- 
Daniel Vérité
https://postgresql.verite.pro/
Twitter: @DanielVerite

Re: Assistance Needed: Error during PostgreSQL Configuration

[Ayush Vatsa]

Hi all,
@Ron Johnson, I am building from the source because I wanted to contribute
to the open-source community, and for that, I want the source files. I had
a few things in mind currently for the same :).
@Tom Lane , Thanks I am using a directory path with
spaces in it, and removing them solved my issue. Next time I will use '_'
in paths to be on the safer side.

Thanks and regards
Ayush Vatsa
SDE Amazon

On Tue, 12 Dec 2023 at 22:10, Tom Lane  wrote:

> Ayush Vatsa  writes:
> > Sorry, I should have included the required information initially itself.
> I
> > am new to the database field so please pardon my mistakes :)
>
> You still didn't mention the platform/environment, but I guess from
> the reference to -isysroot that it must be macOS (Darwin).  I further
> guess that you're using Homebrew or MacPorts, because bare macOS
> doesn't supply GNU sed.  That doesn't get us much further though;
> plenty of Postgres developers use one or the other of those setups
> without difficulty.
>
> One idea that comes to mind is that you might be trying to build in
> a directory path that contains spaces or other odd characters.
> That's generally not well supported by Unix-based tooling.
> However, I'm not sure how that'd lead to this particular failure.
>
> The only other idea I have is that maybe you have some weird
> Homebrew or MacPorts package installed that changes the behavior
> of your shell.  I have no idea what that would be though.
>
> FWIW, on my own Mac laptop, line 486 in config.status in a
> current build looks like
>
> *\'*) ac_optarg=`$as_echo "$ac_optarg" | sed "s/'/'''/g"` ;;
>
> which seems identical to what you reported.  So that takes some
> steam out of the idea that the file was generated incorrectly
> in your build, pointing more to the idea that your shell is not
> reading it as-expected.
>
> regards, tom lane
>

Re: Assistance Needed: Error during PostgreSQL Configuration

[Tom Lane]

Ayush Vatsa  writes:
> Sorry, I should have included the required information initially itself. I
> am new to the database field so please pardon my mistakes :)

You still didn't mention the platform/environment, but I guess from
the reference to -isysroot that it must be macOS (Darwin).  I further
guess that you're using Homebrew or MacPorts, because bare macOS
doesn't supply GNU sed.  That doesn't get us much further though;
plenty of Postgres developers use one or the other of those setups
without difficulty.

One idea that comes to mind is that you might be trying to build in
a directory path that contains spaces or other odd characters.
That's generally not well supported by Unix-based tooling.
However, I'm not sure how that'd lead to this particular failure.

The only other idea I have is that maybe you have some weird
Homebrew or MacPorts package installed that changes the behavior
of your shell.  I have no idea what that would be though.

FWIW, on my own Mac laptop, line 486 in config.status in a
current build looks like

*\'*) ac_optarg=`$as_echo "$ac_optarg" | sed "s/'/'''/g"` ;;

which seems identical to what you reported.  So that takes some
steam out of the idea that the file was generated incorrectly
in your build, pointing more to the idea that your shell is not
reading it as-expected.

regards, tom lane

Re: Assistance Needed: Error during PostgreSQL Configuration

[Ron Johnson]

On Tue, Dec 12, 2023 at 11:02 AM Ayush Vatsa 
wrote:

> Hi,
> Sorry, I should have included the required information initially itself. I
> am new to the database field so please pardon my mistakes
>

Why are you building from source instead of using a packaged solution?
https://www.postgresql.org/download/macosx/

Re: Assistance Needed: Error during PostgreSQL Configuration

[Ayush Vatsa]

Hi,
Sorry, I should have included the required information initially itself. I
am new to the database field so please pardon my mistakes :)
What I have done till now is clone the source code files from Gihub in my
Mac laptop and then try to follow this documentation
, then I got an
error posted in the mail in the very first step itself i.e. ./configure
command.

The PostgreSQL version is the latest (i.e. 16) as I have cloned the source
code from GitHub.
I have tried using both shells including zsh and bash here but same response
For sed, I am using GNU sed and also included this GNU sed in the PATH
variable

ayuvatsa@bcd0745f2da7 ~ % sed --version

sed (GNU sed) 4.9


The configure script runs fine till the step given below:

configure: using CPPFLAGS=-isysroot $(PG_SYSROOT)

configure: using LDFLAGS=-isysroot $(PG_SYSROOT)   -Wl,-dead_strip_dylibs

configure: creating ./config.status

Thanks
Ayush Vatsa

On Mon, 11 Dec 2023 at 20:20, Tom Lane  wrote:

> Ian Lawrence Barwick  writes:
> > 2023年12月11日(月) 18:09 Ayush Vatsa :
> >> ./config.status: line 486: syntax error near unexpected token `)'
> >> ./config.status: line 486: ` *\'*) ac_optarg=`$as_echo "$ac_optarg" |
> sed "s/'/'''/g"` ;;'
>
> > You should provide, at the very least, the following information:
> > - the PostgreSQL version you are trying to build
> > - the environment you are trying to build it in
>
> +1
>
> > A very hand-wavy guess, but possibly your local "sed" installation is
> > not GNU sed?
>
> I'm also wondering if the shell in use is nonstandard.  The configure
> script would likely already have failed if you tried to run it with,
> say, csh; but maybe more-subtly-incompatible shells could give rise
> to this symptom.
>
> regards, tom lane
>

Re: how can I fix my accent issues?

[Laurenz Albe]

On Mon, 2023-12-11 at 13:54 -0500, Igniris Valdivia Baez wrote:
> El sáb, 9 dic 2023 a las 1:01, Laurenz Albe () 
> escribió:
> > 
> > On Fri, 2023-12-08 at 23:58 -0500, Igniris Valdivia Baez wrote:
> > > hello, I have an ETL process collecting data from a postgresql
> > > database and xls files and inserting in a postgresql database that
> > > process occurs great in a local DB in postgres 14 with UTF8
> > > codification and Spanish_Cuba.1952 collation but when I execute that
> > > process in dev which is in postgres 15 and UTF8 with collation
> > > en_US.utf8 the words with accents and ñ looks like an interrogation
> > > symbol, what can I do to fix this?
> > 
> > If the data you are sending are encoded in WINDOWS-1252 (I assume that
> > "1952" is just a typo), you should set the client encoding to WIN1252,
> > so that PostgreSQL knows how to convert the data correctly.
> > 
> > You can do that in several ways; the simplest might be to set the
> > environment variable PGCLIENTENCODING to WIN1252.
>
> hello to all, thanks for your answers i've changed the encoding using this:
> ALTER DATABASE testdb
> SET client_encoding = WIN1252;
>
> now when we try to select data from a table we get this error:
>
> ERROR: character with byte sequence 0xe2 0x80 0x8b in encoding "UTF8"
> has no equivalent in encoding "WIN1252" SQL state: 22P05ERROR:
> character with byte sequence 0xe2 0x80 0x8b in encoding "UTF8" has no
> equivalent in encoding "WIN1252" SQL state: 22P05

So that was not the correct encoding.

Unfortunately your problem description lacks the precision required
to give a certain answer.  You'll have to figure out what encoding the
application data have and how the client encoding is set in the case
where the non-ASCII characters look right and when the don't.

You should also investigate what bytes are actually stored in the database
in both cases.

Yours,
Laurenz Albe