Transition tables for column-specific UPDATE triggers

Hello, The manual says: https://www.postgresql.org/docs/current/sql-createtrigger.html A column list cannot be specified when requesting transition relations. And (I think the same point): The standard allows transition tables to be used with column-specific |UPDATE| triggers, but

Re: Case Insensitive Comparison with Postgres 12

As I understand it, custom collation are not applied globally. Meaning, you have to associate a collation with a column or en expression with COLLATE.

Case Insensitive Comparison with Postgres 12

I am trying to test a simple case insensitive comparison. Most likely the collation that I chose is wrong, but I'm not sure how to choose the correct one (for English/US?). Here is my snippet: create collation case_insensitive( provider=icu, locale='en-US-x-icu', deterministic=false );

Re: Declarative Range Partitioning Postgres 11

On 10/8/19 12:33 PM, Michael Lewis wrote: On Tue, Oct 8, 2019 at 8:00 AM Shatamjeev Dewan > wrote: Hi Michael, In this case , I always need to include partition key(date)  in primary key ( if I have a primary key defined on non partition key column e.g

RE: Declarative Range Partitioning Postgres 11

Thanks a lot Michael for invaluable advise . Appreciate your great help and support. From: Michael Lewis Sent: October-08-19 1:33 PM To: Shatamjeev Dewan Cc: pgsql-general Subject: Re: Declarative Range Partitioning Postgres 11 On Tue, Oct 8, 2019 at 8:00 AM Shatamjeev Dewan

Re: Segmentation fault with PG-12

På tirsdag 08. oktober 2019 kl. 17:24:21, skrev Tom Lane mailto:t...@sss.pgh.pa.us>>: Andreas Joseph Krogh writes: > Will running a debug-enabled build slow things noticably down? gcc promises that the generated code is the same with or without debug. I think clang does too. With other

Re: Declarative Range Partitioning Postgres 11

On Tue, Oct 8, 2019 at 8:00 AM Shatamjeev Dewan wrote: > Hi Michael, > > > > In this case , I always need to include partition key(date) in primary > key ( if I have a primary key defined on non partition key column e.g id > (in my case), to make it a composite primary key (id, date). This

Re: Declarative Range Partitioning Postgres 11

On Mon, Oct 7, 2019 at 5:56 PM Ron wrote: > On 10/7/19 6:17 PM, Michael Lewis wrote: > > No, what you want is not possible and probably won't ever be I would > expect. > > Sure it is. Maybe not the (weird) way that Postgres does partitioning, > but > the legacy RDBMS that I still occasionally

Re: temporary files

thank you very much you take for your time We raised the work_mem to 130 mb and there was no more problem! Now we are seeing to improve the query, it is complicated because it is generated by a product that we have installed in production! -- Sent from:

Re: Segmentation fault with PG-12

Andreas Joseph Krogh writes: > Will running a debug-enabled build slow things noticably down? gcc promises that the generated code is the same with or without debug. I think clang does too. With other compilers you may pay some penalty. > Is there a way > to make it dump a stack-trace (or

Segmentation fault with PG-12

In our production-environment we get sig11 every now and then after upgrading to PG-12: 2019-10-08 15:45:29.654 CEST [8829-76] LOG: server process (PID 20631) was terminated bysignal 11: Segmentation fault 2019-10-08 15:45:29.654 CEST [8829-77] DETAIL: Failed process was running: COMMIT

Re: Table locking during backup

=?utf-8?Q?Artur_Zaj=C4=85c?= writes: > First session: > BEGIN; > set transaction isolation level repeatable read, read only; > lock TABLE gm.tableabc IN access share mode; > Second session: > BEGIN; > CREATE TEMP TABLE IF NOT EXISTS tableabc (Id BIGINT DEFAULT random()) > INHERITS

Re: psql \copy hanging

Le 08/10/2019 à 16:55, Daniel Verite a écrit : Arnaud L. wrote: Anyway, it hung using this syntax during last night's run. I'll give it another try tonight just to be sure. When psql.exe is hanging, maybe you could use a tool like Process Monitor [1] or Process Explorer [2] to get

Re: psql \copy hanging

Arnaud L. wrote: > Anyway, it hung using this syntax during last night's run. > I'll give it another try tonight just to be sure. When psql.exe is hanging, maybe you could use a tool like Process Monitor [1] or Process Explorer [2] to get insights about what it's stuck on or what it's

Re: psql \copy hanging

Le 08/10/2019 à 16:03, Adrian Klaver a écrit : This is going to be hard to troubleshoot if you change your commands. Previously you had: psql -h %MYPGSERVER% -a -f myscript.sql %MYPGDB% Changing more then one thing at a time makes it that much more difficult to isolate the issue. Yes, true.

RE: Declarative Range Partitioning Postgres 11

Hi Michael, In this case , I always need to include partition key(date) in primary key ( if I have a primary key defined on non partition key column e.g id (in my case), to make it a composite primary key (id, date). This would allow duplicate id with different date,which is not desirable .

RE: Declarative Range Partitioning Postgres 11

Thanks Michael. From: Michael Lewis Sent: October-07-19 7:18 PM To: Shatamjeev Dewan Cc: pgsql-general Subject: Re: Declarative Range Partitioning Postgres 11 No, what you want is not possible and probably won't ever be I would expect. Scanning every partition to validate the primary key

Re: psql \copy hanging

On 10/8/19 12:06 AM, Arnaud L. wrote: Le 07/10/2019 à 16:36, Adrian Klaver a écrit : So you are saying that you have not run the problematic line by itself? It hung during last night's run. I had modified my batch script to run the \copy commands separately, i.e. it now reads as : psql -h

Re: psql \copy hanging

Le 08/10/2019 à 12:55, Daniel Verite a écrit : Testing this with 11.5, it works for me. Make sure you're running the latest minor release (on the client side in this case), because a related fix was issued last February. For the 11 branch it was in version 11.2. OK, my bad, backslashes in a

Re: GPS coordinates problem

You can use the postgis extension: create extension postgis; Then you can create a geography coulmn location geography( point, 4326) and insert a lat /long as a point like this: ST_GeographyFromText('SRID=4326;POINT(%(longitude)s %(latitude)s)'), On Tue, Oct 8, 2019 at 1:30 PM Andreas

Re: GPS coordinates problem

Am 08.10.19 um 12:50 schrieb Timmy Siu: Now, I need Global Position System coordinates as a data type. How do I define it in Postgresql 11 or 12? consider PostGIS. Regards, Andreas -- 2ndQuadrant - The PostgreSQL Support Company. www.2ndQuadrant.com

Re: psql \copy hanging

Le 08/10/2019 à 12:55, Daniel Verite a écrit : Arnaud L. wrote: As a side note, COPY (...) TO STDOUT \g 'somefile' does not work in a script file (i.e. it does not work if the command is passed in a file via the -f argument). The command runs fine, no error is raised either by the

Re: psql \copy hanging

Arnaud L. wrote: > As a side note, COPY (...) TO STDOUT \g 'somefile' does not work in a > script file (i.e. it does not work if the command is passed in a file > via the -f argument). > The command runs fine, no error is raised either by the client or the > server, but no file is

GPS coordinates problem

Dear All Postgre Users, I want to develop a new project on Lavarel 5 and Postgresql 11. Now, I need Global Position System coordinates as a data type. How do I define it in Postgresql 11 or 12? Thanks for your help. Timmy

Re: Performance on JSONB select

Sorry, I told you that the status was always populated but not, I need that filter for the index because not all records are using that json structure. When json is not null then yes, Status is always there. I have maybe 20 or 25% of records having json populated, so, I really need that filter.

Re: psql \copy hanging

Le 08/10/2019 à 09:28, Pavel Stehule a écrit : you can write simple C application with COPY API https://www.postgresql.org/docs/12/libpq-copy.html Unfortunately, I don't know C. Then you can eliminate or ensure locality of problem. more, you can use server side copy. Superuser can read data

Re: psql \copy hanging

út 8. 10. 2019 v 9:06 odesílatel Arnaud L. napsal: > Le 07/10/2019 à 16:36, Adrian Klaver a écrit : > > So you are saying that you have not run the problematic line by itself? > > It hung during last night's run. > > I had modified my batch script to run the \copy commands separately, > i.e. it

Re: psql \copy hanging

Le 07/10/2019 à 16:36, Adrian Klaver a écrit : So you are saying that you have not run the problematic line by itself? It hung during last night's run. I had modified my batch script to run the \copy commands separately, i.e. it now reads as : psql -h myserver -a mydb <

RE: Table locking during backup

> Not sure why would it matter that the pg_dump connection is read-only, this > is about locking because > pg_dump needs to ensure the schema does not change while it's running. > pg_dump does not do > > LOCK TABLE gm.tableabc; > > but > > LOCK TABLE gm.tableabc IN ACCESS SHARE MODE; > >