case and accent insensitive

Hi, I need to reproduced the following Oracle functionality in PostgreSQL: CREATE INDEX index_ciudad ON world.ciudad ((NLSSORT(NOMBRE29,'nls_sort=''GENERIC_BASELETTER'''))); I don't know how i could reproduced case insensitive and accent insensitive. Is there any collate? Thanks.

Re: pg_stat_statements: password in command is not obfuscated

On 24 March 2018 at 10:30, legrand legrand wrote: > It seems that passwords used in commands are not removed when caught by > pg_stat_statements > (they are not "normalized" being utility statements) > > exemple: > alter role tt with password '123'; > > select query

Re: FDW Foreign Table Access: strange LOG message

Albrecht =?iso-8859-1?b?RHJl3w==?= writes: > A different, confusing point (which is closer to a “bug” IMHO) is that > connections to localhost are actually encrypted by default. This is > basically useless and just a waste of cpu cycles – if a malicious user may >

pg_stat_statements: password in command is not obfuscated

Hello, It seems that passwords used in commands are not removed when caught by pg_stat_statements (they are not "normalized" being utility statements) exemple: alter role tt with password '123'; select query from public.pg_stat_statements where query like '%password%'; query

Re: FDW Foreign Table Access: strange LOG message

Hi Tom: Am 22.03.18 23:55 schrieb(en) Tom Lane: It finally occurred to me what is a likely explanation for why you're seeing these messages but I don't when I test postgres_fdw locally: probably, you are encrypting connections to the foreign server with SSL. I can confirm that the confusing

Re: Postgresql 10.3 , query never completes if LIMIT clause is specified and paralle processing is on

PS , in the meanwhile I discovered a 2nd workaround(beside disabling parallel processing) . I added offset 0 to the subquery , and, according to the documentation, “OFFSET 0 is the same as omitting the OFFSET clause” - https://www.postgresql.org/docs/current/static/queries-limit.html cmd3dev=#

Re: postgresql-10.3 on unbuntu-17.10 - how??

Re: Stuart McGraw 2018-03-23 > So what I was thinking of was rebuilding the Pgdg Ubuntu source > package (I'm assuming one is available somewhere). I have had > good results on Fedora backporting current versions of Postgresql > from later

Re: JDBC connectivity issue

Cool, thanks. I'd love to see this work Dave Cramer da...@postgresintl.com www.postgresintl.com On 23 March 2018 at 10:24, JCMS22 wrote: > I forgot to say I am trying to investigate how to make it work as we need > specific stuff from the DataSource object. > > It

RE: Foreign Key locking / deadlock issue.... v2

Rob, thanks for looking. The "pause" is only to not-do-the-commit yet, so that the child process can then try and access the record - I've not left anything out. This code is my own demo, not a cut from our production code. Did you run this as the 'postgres' superuser? That would bypass

Re: JDBC connectivity issue

I forgot to say I am trying to investigate how to make it work as we need specific stuff from the DataSource object. It might work if you use setServerName rather than setUrl. setServerName will append itself the jdbc:postgresql:// so you only give it the server name (i.e

Re: JDBC connectivity issue

I am surprised this was not answered correct yet. I am in the same bucket as you and here is the problem. A quick look at the code and you find the answer. The BaseDataSource class (from which PGSimpleDataSource inherits) does NOT support failover addresses. The underlying driver used to parse

RE: Use pgloader with FDW

We plan to build a Postgresql database connected to our mysql database to plug BI tools. We will build materialized views in Postgresql. The datetime conversion logic will be seen in these views, I think. Thanks for your advise. -Message d'origine- De : Alban Hertroys

Re: Use pgloader with FDW

I suppose MySQL has the concept of views. Perhaps you can create a view over the table that translates those 0-datetimes to NULL and have the FDW connect to the view instead? On 23 March 2018 at 14:27, Patricia DECHANDOL wrote: > Thanks a lot Adrian. > We can't

RE: Use pgloader with FDW

Thanks a lot Adrian. We can't update the mysql columns values, so I will adopt the datetime -> text solution. Thanks again -Message d'origine- De : Adrian Klaver Envoyé : vendredi 23 mars 2018 14:25 À : Patricia DECHANDOL ;

Re: Use pgloader with FDW

On 03/23/2018 06:07 AM, Patricia DECHANDOL wrote: Hello Adrian, So, if I well understand, the only solution is to wrap the mysql datetime columns to "text" columns in my foreign tables. And then use a cast function to convert from text to date in Postgre when I want to use these columns ?

RE: Use pgloader with FDW

Hello Adrian, So, if I well understand, the only solution is to wrap the mysql datetime columns to "text" columns in my foreign tables. And then use a cast function to convert from text to date in Postgre when I want to use these columns ? No other way. The pgloader can't be used by the FDW to

Re: Use pgloader with FDW

On 03/23/2018 03:55 AM, Patricia DECHANDOL wrote: Hello, I’m new to Postgresql and try to use the FDW with mysql database. Everything is OK to create my FDW and foreign tables, but I meet a problem when I try to do a select on a FOREIGN table containing datetime column which contains the

Re: Foreign Key locking / deadlock issue.... v2

Hello Phil, I've run your sample script on 9.6.5 and 10.3. The only thing that I added was a commit; after the initial inserts just to ensure the rows were saved. No errors were reported for either version. The output of \dp after running was:- Access privileges

Use pgloader with FDW

Hello, I'm new to Postgresql and try to use the FDW with mysql database. Everything is OK to create my FDW and foreign tables, but I meet a problem when I try to do a select on a FOREIGN table containing datetime column which contains the value "-00-00 00:00:00" in mysql. Select on the

Re: primary key and unique index

On 23/03/2018 09:55, Thomas Poty wrote: Hi all, I am migrating fromMySQL to Postgresql 9.6. In MySQL a  "show create table" gives me :  ...   PRIMARY KEY (`ID`,`CountryCode`,`LanguageCode`),   UNIQUE KEY `unique_my table_4` (`ID`,`CountryCode`,`LanguageCode`), ... So, In PostgreSQL, does it

Re: primary key and unique index

On 23 March 2018 at 20:55, Thomas Poty wrote: > In MySQL a "show create table" gives me : > ... > PRIMARY KEY (`ID`,`CountryCode`,`LanguageCode`), > UNIQUE KEY `unique_my table_4` (`ID`,`CountryCode`,`LanguageCode`), > ... > > So, In PostgreSQL, does it make sense to