Perl::DBI and TYPE of column

Hello, We unload Sybase and Oracle data to migrate the database to PostgreSQL. The loading is done very fast with PostgreSQL's COPY command. During unload trailing blanks in all columns are discarded, because they would cause problems during loading for INT and DATE columns. The discarding is

Re: Perl::DBI and TYPE of column

Matthias Apitz writes: > During unload trailing blanks in all columns are discarded, because they > would cause problems during loading for INT and DATE columns. Really? regression=# select '123 '::int; int4 -- 123 (1 row) regression=# select '12-02-2019 '::date; date

Examing cotets of NEW & OLD in a function programed in perl

I need to write a generic function to process data before allowing the insert or update to continue. To do this, I need to be able to examine the NEW, and OLD structures without prior knowledge of the structure of the table that fired the trigger. Can someone show me an example of how to get

Re: Detecting which columns a query will modify in a function called by a trigger

On Mon, Mar 02, 2020 at 01:44:52PM -0700, David G. Johnston wrote: > On Mon, Mar 2, 2020 at 1:28 PM stan wrote: > > > Envision a table with a good many columns. This table represents the "life > > history" of a part on a project. Some of the columns need to be > > created/modified by the

Re: Perl::DBI and TYPE of column

## Matthias Apitz (g...@unixarea.de): > My question here is: How I could get a copy of the document > ftp://sqlstandards.org/SC32/SQL_Registry/ Methinks that the most interesting constants of that are already in DBI (export tag sql_types) - man DBI, /sql_types. Is that the data you're looking

Re: Detecting which columns a query will modify in a function called by a trigger

On 3/3/20 9:42 AM, stan wrote: On Mon, Mar 02, 2020 at 01:44:52PM -0700, David G. Johnston wrote: On Mon, Mar 2, 2020 at 1:28 PM stan wrote: Envision a table with a good many columns. This table represents the "life history" of a part on a project. Some of the columns need to be

Exportacion por lotes

Estimados antes que todo me presento, mi nombre es Hernan Gonzalez, me acabo de inscribir en esta lista de correo y desde ya me disculpo si en este mensaje violo alguna normativa de la lista de correo pero necesito ayuda que me apura mucho. Quien me podria ayudar con información respecto de como

Re: Examing cotets of NEW & OLD in a function programed in perl

stan writes: > I need to write a generic function to process data before allowing the > insert or update to continue. > To do this, I need to be able to examine the NEW, and OLD structures > without prior knowledge of the structure of the table that fired the > trigger. AFAIR, they're just

Re: Perl::DBI and TYPE of column

El día Dienstag, März 03, 2020 a las 09:36:32 -0500, Tom Lane escribió: > Matthias Apitz writes: > > During unload trailing blanks in all columns are discarded, because they > > would cause problems during loading for INT and DATE columns. > > Really? > > regression=# select '123 '::int; >

Re: Perl::DBI and TYPE of column

On 3/3/20 6:02 AM, Matthias Apitz wrote: Hello, We unload Sybase and Oracle data to migrate the database to PostgreSQL. The loading is done very fast with PostgreSQL's COPY command. During unload trailing blanks in all columns are discarded, because they would cause problems during loading for

Re: Detecting which columns a query will modify in a function called by a trigger

hello By means of json you can detect what change between NEW and OLD example: select b.* from (values (now(),123456,'pepito perez',false)) as old(dato1,dato2,dato3,dato4),json_each_text(row_to_json(old)) as b(text1,text2) except select b.* from (values

Re: Exportacion por lotes

On 2020-Mar-03, Hernan Jesus Gonzalez Carmona wrote: > Estimados antes que todo me presento, mi nombre es Hernan Gonzalez, me > acabo de inscribir en esta lista de correo y desde ya me disculpo si en > este mensaje violo alguna normativa de la lista de correo pero necesito > ayuda que me apura

Graphical Query Builder

Is the Graphical Query Builder supported in pgAdmin 4? I reviewed the archives and found a message from 2017 that pgAdmin 4 did not support the Graphical Query Builder. Is the Graphical Query Builder still not supported by pgAdmin 4? If it isn't supported will it ever be? What are the reasons for

Re: Detecting which columns a query will modify in a function called by a trigger

On 3/3/20 1:32 PM, stan wrote: Please reply to list also. Ccing list On Tue, Mar 03, 2020 at 10:48:29AM -0800, Adrian Klaver wrote: On 3/3/20 9:42 AM, stan wrote: On Mon, Mar 02, 2020 at 01:44:52PM -0700, David G. Johnston wrote: On Mon, Mar 2, 2020 at 1:28 PM stan wrote: Envision a table

Re: Detecting which columns a query will modify in a function called by a trigger

On 3/3/20 3:06 PM, David G. Johnston wrote: On Tue, Mar 3, 2020 at 3:48 PM Adrian Klaver > wrote: The link was for automatically updateable views. If you want to do something more involved then see:

Re: Graphical Query Builder

On 3/3/20 2:05 PM, Patrick Olson wrote: Is the Graphical Query Builder supported in pgAdmin 4? I reviewed the archives and found a message from 2017 that pgAdmin 4 did not support the Graphical Query Builder. Is the Graphical Query Builder still not supported by pgAdmin 4? If it isn't

Re: Detecting which columns a query will modify in a function called by a trigger

On Tue, Mar 3, 2020 at 4:11 PM Adrian Klaver wrote: > On 3/3/20 3:06 PM, David G. Johnston wrote: > > On Tue, Mar 3, 2020 at 3:48 PM Adrian Klaver > > wrote: > > > > The link was for automatically updateable views. If you want to do > > something more

Re: Examing cotets of NEW & OLD in a function programed in perl

> On Mar 3, 2020, at 13:26, stan wrote: > So, they should just be visible as OLD, and NEW as hasshes? They're documented here: https://www.postgresql.org/docs/current/plperl-triggers.html A global hash variable $_TD is available in the trigger function with all sorts of

elog() plperl function

I did a Google search for using the RAISE functionality in plperl, and all the answers I see point to the elog built in function. Looking at this it appears to me, I can do things like RAISE NOTICE. But what if I want to do, say, a RAISE EXCEPTION? Or do I need to handle that with the rerun AND an

Poor plan choice with partial unique indexes on jsonb column and simple RLS policy (with test script)

Hi all, I have recently encountered a strange poor query plan choice after implementing RLS. My table has a number of partial indexes on a jsonb column and the query went from low number of milliseconds to several seconds as the planner chose a different index. Simply stated, in the jsonb

Re: Exportacion por lotes

Hola --- Charles Clavadetscher Spitzackerstrasse 9 CH - 8057 Zürich Tel: +41-79-345 18 88 - > On 03.03.2020, at 20:21, Hernan Jesus Gonzalez Carmona > wrote: > > Estimados antes que todo me presento, mi nombre es Hernan

Re: Examing cotets of NEW & OLD in a function programed in perl

On Tue, Mar 03, 2020 at 12:59:00PM -0500, Tom Lane wrote: > stan writes: > > I need to write a generic function to process data before allowing the > > insert or update to continue. > > To do this, I need to be able to examine the NEW, and OLD structures > > without prior knowledge of the

Re: elog() plperl function

On Tue, Mar 03, 2020 at 05:31:32PM -0500, stan wrote: > I did a Google search for using the RAISE functionality in plperl, and all > the answers I see point to the elog built in function. Looking at this it > appears to me, I can do things like RAISE NOTICE. But what if I want to do, > say, a

Re: Detecting which columns a query will modify in a function called by a trigger

On Tue, Mar 3, 2020 at 3:48 PM Adrian Klaver wrote: > The link was for automatically updateable views. If you want to do > something more involved then see: > > https://www.postgresql.org/docs/12/rules-views.html#RULES-VIEWS-UPDATE CREATE TRIGGER works with views; I usually see recommendations

Re: Poor plan choice with partial unique indexes on jsonb column and simple RLS policy (with test script)

Alastair McKinley writes: > I have recently encountered a strange poor query plan choice after > implementing RLS. > My table has a number of partial indexes on a jsonb column and the query went > from low number of milliseconds to several seconds as the planner chose a > different index. >

Re: Poor plan choice with partial unique indexes on jsonb column and simple RLS policy (with test script)

Hi Tom, Thank you for having a look at this. In the interim I discovered that I could trigger the issue by creating a security barrier view, whereas a regular view worked fine, so I think that also points to your conclusion about leakyness? I attempted to workaround the issue with a leakproof

Re: Poor plan choice with partial unique indexes on jsonb column and simple RLS policy (with test script)

Alastair McKinley writes: > Thank you for having a look at this. In the interim I discovered that I > could trigger the issue by creating a security barrier view, whereas a > regular view worked fine, so I think that also points to your conclusion > about leakyness? > I attempted to

Postgres on macOS 10

Hi, We just noticed something strange with our Postgres server. We have Postgres 11 and 12 running on macOS 10.14 (Mojave), installed with the EDB installer. Whenever the machine is restarted, the Postgres service cannot be launched until a macOS user logs in. We have "automatic login"