Re: Index corruption issue after migration from RHEL 7 to RHEL 9 (PostgreSQL 11 streaming replication)

On Thu, Oct 23, 2025 at 4:24 PM Scot Kreienkamp < [email protected]> wrote: > > I always assumed streaming would “just work” as long as it’s the same > major PG version and Linux-to-Linux regardless of OS/glibc versionIt > never occurred to me that there could be an OS influencing f

Bitnami deprecation

Not sure where to post this but the Postgres team should consider pulling the promotion for Bitnami on the downloads page. See: https://github.com/bitnami/charts/issues/35164

Re: Connection to 127.0.0.1 refused in a Dockerfile based on postgres:17-alpine3.20

> > > Is anybody able spotting, what am I doing wrong here? Thank you > Unless I'm mistaken, it seems like you are trying to run jetty and postgres in the same container. You should really use two containers and run them both with docker compose. Docker is very much designed to run a single ser

Re: Reset sequence to current maximum value of rows

> Aha! So it's likely that by listing the PK column name in the list of > columns to be inserted was what caused the problem? No need to specify > DEFAULT for it? > There's no need to specify the column if it has a default value, but specifying it did not cause the issue that you saw.

Re: Poor performance after restoring database from snapshot on AWS RDS

On Wed, Jun 5, 2024 at 4:23 AM Sam Kidman wrote: > We get very poor performance in the staging environment after this > restore takes place - after some usage it seems to get better perhaps > because of caching. > This is due to the way that RDS restores snapshots. >From the docs >(https://doc

Re: How to update upper-bound of tstzrange ?

Thanks all for your answers ! Much appreciated. Sent with Proton Mail secure email. On Tuesday, 21 May 2024 at 11:02, Laurenz Albe wrote: > On Mon, 2024-05-20 at 13:56 +0200, Erik Wienhold wrote: > > > On 2024-05-20 12:30 +0200, Laura Smith wrote: > > > > > C

How to update upper-bound of tstzrange ?

Could someone kindly help me out with the correct syntax ? My first thought was the below but that doesn't work: update foo set upper(bar_times)=upper(bar_times)+interval '1' hour where bar_id='abc'; ERROR: syntax error at or near "(" LINE 1: update event_sessions set upper(bar_times)=upper(bar

Obsolete Linux downloads (Debian) instructions

Hi Who do I have to badger to get the obsolete and frankly dangerous Debian repo instructions fixed @ https://www.postgresql.org/download/linux/debian/ ?  The manner proposed is really "not the done thing" in 2024 and it has been explicitly obsoleted by Debian so the project really should not b

Re: array_to_json/array_agg question

> You almost got the subrecord ("value_1" and "value_2") right. You need > to use json_build_object() (or even the new json_object() function added > in pg16) instead of row_to_json() to just include "value_1" and > "value_2". Then GROUP BY "key" and aggregate the subrecords with > json_agg(). T

array_to_json/array_agg question

Hi Before I go down the road of taking care of this in the front-end through iterations, I thought I would ask the pgsql if there was a clever query I could do on postgres that would take care of it for me instead. In essence, I would like to consolidate values from the same key as a json arra

Re: Function inserting into tstzrange ? (syntax error at or near...)

> > There's not bespoke SQL syntax for constructing a range. You must > use a function, something like > > VALUES(p_event_id, tstzrange(p_start_time,p_end_time,'[)')) ... Thanks all for your swift replies. Serves me right for assuming I could use variable substitution where text would norma

Function inserting into tstzrange ? (syntax error at or near...)

I'm sure I'm doing something stupid here, but I think I've got the syntax right ? The error I'm seeing: psql:event_session_funcs.sql:26: ERROR:  syntax error at or near "[" LINE 11:         VALUES(p_event_id,[p_start_time,p_end_time)) RETURNI... The function: CREATE OR REPLACE FUNCTION new_even

Re: Scriptable way to validate a pg_dump restore ?

On Monday, 29 January 2024 at 09:06, Ron Johnson wrote: > > That's kinda like being asked to prove that rocks always fall when you drop > them. Either you trust physics, because physics has always worked, or you > must watch every rock, because next time it might not fall. The analogy is >

Scriptable way to validate a pg_dump restore ?

Hi Let's say I've got a scenario where I'm doing a pg_dump replication rather than online streaming, e.g. due to air-gap or whatever. Is there a scriptable way to validate the restore ?  e.g. using doing something clever with ctid or something to ensure both the schema and all its rows were re

Re: Postgresql HA cluster

--- Original Message --- On Friday, October 13th, 2023 at 14:10, Jehan-Guillaume de Rorthais wrote: > But really, double check first why a simple primary-standby architecture > doesn't > meet your needs. The simpler the architecture is, the better. Even from the > application point of

RE: Help with PostgreSQL Upgrade

ember 2023 16:25 To: SMITH Matt ; [email protected] Subject: [EXTERNAL EMAIL] Re: Help with PostgreSQL Upgrade On 9/14/23 03:50, SMITH Matt wrote: > Classified as: {OPEN} > > > Hi, > > We currently have a database hosted in pg 13.1 (with PostGIS 3.1.1) > and we ar

Help with PostgreSQL Upgrade

Classified as: {OPEN} Hi, We currently have a database hosted in pg 13.1 (with PostGIS 3.1.1) and we are keen to move to pg 15.3 (with PostGIS 3.3.3) all running on Windows 10. I installed pg15 without issue. The pgAdmin 4 utility (bundled with postgres 15) connects okay. Then I used the pg_u

Re: Maintaining accents with "COPY" ?

> Looks like an encoding issue and a mismatch between database encoding and > client > encoding. You can check both with: > > SHOW server_encoding; > SHOW client_encoding; > > Then either set the client encoding or use COPY's encoding option to match the > database encoding (I assume utf8 in t

Maintaining accents with "COPY" ?

Hi I'm currently doing a CSV export using COPY: COPY (select * from foo where bar='foo') TO '/tmp/bar.csv' DELIMITER ',' CSV HEADER; This works great apart from accents are not preserved in the output, for example é gets converted to random characters, e.g. √© or similar. How can I preserve

Re: Adding SHOW CREATE TABLE

On Sat, May 13, 2023, 3:25 AM Kirk Wolak wrote: > Does this imply SQL SYNTAX like: > > SHOW CREATE TABLE > [ INCLUDING { ALL | INDEXES | SEQUENCES | ??? }] > [EXCLUDING { PK | FK | COMMENTS | STORAGE | } ] > [FOR {V11 | V12 | V13 | V14 | V15 }] ?? > ? > Personally, I would expect a funct

Re: What happened to the tip "It is good practice to create a role that has the CREATEDB and CREATEROLE privileges..."

On Wed, Apr 19, 2023 at 2:19 PM Bryn Llewellyn wrote: > This tip > > « > It is good practice to create a role that has the CREATEDB and CREATEROLE > privileges, but is not a superuser, and then use this role for all routine > management of databases and roles. This approach avoids the dangers of

Re: Patroni, slots, and expiring WALs

On Tue, Mar 28, 2023 at 10:55 AM Peter J. Holzer wrote: > > > The configuration includes `use_slots: true` and I can see a slot in > pg_replication_slots on the leader. > > I was under the impression that this would be sufficient to prevent WALs > from being deleted on the leader before they are

Re: Tools for moving normalized data around

> > > How to do this with two tables connected via one foreign key, that is > explained a dozen times in Stackoverflow. But what if the tree is 50 > tables and 120 foreign key columns? > It can be done. But probably not manually. > > So, as this seems a very usual use-case for normalized data, is t

Re: Stored procedure code no longer stored in v14 and v15, changed behaviour

> > From a user point of view, can also be seen as a "regression", > when an observable property of the system changes to a new > different / incompatible way, to some extent. I'm not saying it is, > still it is a change one discovers too late, creates pain to some, > and is both worth reporting an

Re: "peer" authentication: cannot make "pg_ident.conf" work as I believe that the doc says that it should

> > > > *sudo systemctl stop postgresql**sudo * > *systemctl start postgresql**pg_ctl reload -D /etc/postgresql/11/main/* > > (I know that I could've used "systemctl restart ".) Like I said elsewhere, > the "reload" seems to be superfluous. But it costs nothing to do it. > > Actually, you only n

Re: Seeking the correct term of art for the (unique) role that is usually called "postgres"—and the mental model that underlies it all

> > I can now characterize what I'd observed more clearly, thus: only a > bootstrap super user (as defined above) can start a session without > mentioning the name of the database to which to connect and the name of the > within-cluster role to connect as—and without supplying a password. And it >

pg RLS suggestions needed

I've got a table with an RLS policy on it: Policies:    POLICY "app_users_policy"       USING ((app_id = CURRENT_USER)) Is there a way I'm not aware of (e.g. via Pl/PGSQL) that would allow "migration" of data from one RLS owner to another ? At the moment, the only option I can think of is for t

Re: Is it possible to stop sessions killing eachother when they all authorize as the same role?

I say this only to emphasize that there are always things that are critical > that are elided in a testcase that tries to be minimal. > > So it seems that there's something critical about my env that I'm failing > to tell you all. But what can it be? > > Removing permissions also works for me. In

Re: ***SPAM*** Re: WARNING: could not open statistics file "pg_stat_tmp/global.stat": Operation not permitted

> On Sep 8, 2022, at 10:43 PM, Tom Lane wrote: > > Perry Smith writes: >> From within the container, files which I assume are created by >> PostgreSQL are ending up being owned by root rather than Postgres. > > If it looks that way from *inside* the container, tha

WARNING: could not open statistics file "pg_stat_tmp/global.stat": Operation not permitted

This is an issue when PostgreSQL is running inside a container. In my quest to find an answer, I’ve discovered three instances that it has come up and various people have talked about fixes but no one seemed to notice what I found. I opened an issue here[1]. From within the container, files wh

Re: ***SPAM*** Re: With Recursive / Recursive View question

> On Aug 20, 2022, at 19:38, Christophe Pettus wrote: > > >> On Aug 20, 2022, at 15:42, Perry Smith wrote: >> >> To rephrase, is it possible to write a view that would work from the child >> terms out towards the ancestors? > > Assuming that the c

Re: ***SPAM*** Re: With Recursive / Recursive View question

> On Aug 20, 2022, at 19:38, Christophe Pettus wrote: > > > >> On Aug 20, 2022, at 15:42, Perry Smith wrote: >> >> To rephrase, is it possible to write a view that would work from the child >> terms out towards the ancestors? > > Assuming that th

With Recursive / Recursive View question

This select is almost instant: WITH RECURSIVE pathname(id, parent_id, basename) AS ( SELECT child.id, child.parent_id, child.basename FROM dirents child WHERE basename = '10732.emlx' UNION ALL SELECT parent.id, parent.parent_id, CONCAT(parent.basename, '/', child.basename) F

Re: ***SPAM*** Re: Can I get the number of results plus the results with a single query?

> On Aug 15, 2022, at 08:55, David G. Johnston > wrote: > > On Monday, August 15, 2022, Perry Smith <mailto:[email protected]>> wrote: > I’ve been toying with row_number() and then sort by row_number descending and > pick off the first row as the total number.

Can I get the number of results plus the results with a single query?

I like to have what I call “baby sitting” messages such as “Completed 15 out of 1023”. To do this, I need the number of results a query returns but I also need the results. Currently I’m doing this with two queries such as: SELECT COUNT(*) FROM table WHERE …. expression … SELECT * FROM

Re: Oddity that I don't understand

I see why… The select of the view is picking d.ino which is null because there is no match in the dirents table. Thanks guys! > On Aug 11, 2022, at 08:23, Perry Smith wrote: > > I’m tempted to ask “Is this a bug” but I predict there is an explanation. > > I have a view: &g

Oddity that I don't understand

I’m tempted to ask “Is this a bug” but I predict there is an explanation. I have a view: find_dups=# \sv+ dateien 1 CREATE OR REPLACE VIEW public.dateien AS 2SELECT d.id, 3 d.basename, 4 d.parent_id, 5 d.ino, 6 d.ext, 7 i.ftype, 8

Re: Fwd: Modelling a web CMS in Postgres ... a little advice needed

Sent with Proton Mail secure email. --- Original Message --- On Thursday, August 11th, 2022 at 13:11, Ray O'Donnell wrote: > On 11/08/2022 11:00, Laura Smith wrote: > > > Hi Tony > > > > The reason I'm looking to do it from scratch is that its

Re: Fwd: Modelling a web CMS in Postgres ... a little advice needed

in Postgres ... a little advice needed > To: Laura Smith > > > > On Thu, 11 Aug 2022 at 09:35, Laura Smith > wrote: > > > Hi > > > > I'm looking at using pgsql as a backend to a web CMS but could do with a > > little advice from the crowd on the

Modelling a web CMS in Postgres ... a little advice needed

Hi I'm looking at using pgsql as a backend to a web CMS but could do with a little advice from the crowd on the wiseness of my schema thinking. TL;DR the design is centered around two tables "pages" and "page_content", where "pages" has a jsonb column that refers to "page_content" in a key-valu

Re: Creating A GIN index on JSONB column (large database)

ld help as its likely largest partition will either be the current month or the previous month so the problem would still persist. On Tue, Aug 9, 2022 at 3:50 PM Rob Sargent wrote: > > > > On Aug 9, 2022, at 7:45 AM, Taylor Smith > wrote: > > > > Thanks Rob, > >

Re: Creating A GIN index on JSONB column (large database)

Thanks Rob, This is what I was thinking. Would you say it presents any risk then having a database unable to rebuild its own indexes if needed? On Tue, Aug 9, 2022 at 3:39 PM Rob Sargent wrote: > > > > On Aug 9, 2022, at 7:04 AM, Taylor Smith > wrote: > > > &g

Creating A GIN index on JSONB column (large database)

to mitigate or a necessary evil. The only solution we have so far is scale up our RDS instance. Kind regards, Taylor Smith

Re: Issues with upserts

- New versions are created for all rows, even if the data is identical. > This quickly fills up the WAL and puts unnecessary load on the tablespace > drives. > > - If the conflict target is not the serial column, the sequence backing > the serial column gets incremented for every row. This quickly

Re: Postgres NOT IN vs NOT EXISTS optimization

I think this explains the situation well: https://wiki.postgresql.org/wiki/Don't_Do_This#Don.27t_use_NOT_IN On Tue, Jun 14, 2022 at 11:59 AM Dirschel, Steve < [email protected]> wrote: > We are in the process of migrating from Oracle to Postgres and the > following query does muc

Re: Help with large delete

Thank you TOM!!! So… I did: create index parent_id_index on dateien(parent_id); And now things are going much faster. As you can see, I had an index kinda sorta on the parent id but I guess the way I did it prevented Postgres from using it. > On Apr 17, 2022, at 06:58, Perry Smith wr

Re: Help with large delete

I’m sending this again. I don’t see that it made it to the list but there is also new info here. > On Apr 16, 2022, at 10:33, Tom Lane <mailto:[email protected]>> wrote: > > Perry Smith mailto:[email protected]>> writes: >> Currently I have one table th

Re: Help with large delete

> On Apr 16, 2022, at 13:56, Rob Sargent wrote: > > > >> On Apr 16, 2022, at 12:24 PM, Perry Smith wrote: >> >>  >> >>> On Apr 16, 2022, at 12:57, Jan Wieck >> <mailto:[email protected]>> wrote: >>> >>> Make

Re: Help with large delete

> On Apr 16, 2022, at 12:57, Jan Wieck wrote: > > Make your connection immune to disconnects by using something like the screen > utility. Exactly… I’m using emacs in a server (daemon) mode so it stays alive. Then I do “shell” within it. > On Sat, Apr 16, 2022, 09

Re: ***SPAM*** Re: Help with large delete

> On Apr 16, 2022, at 10:33, Tom Lane wrote: > > Perry Smith writes: >> Currently I have one table that mimics a file system. Each entry has a >> parent_id and a base name where parent_id is an id in the table that must >> exist in the table or be null with

Help with large delete

Currently I have one table that mimics a file system. Each entry has a parent_id and a base name where parent_id is an id in the table that must exist in the table or be null with cascade on delete. I’ve started a delete of a root entry with about 300,000 descendants. The table currently has

Constraint ordering

I think (hope) I’ve made a bad assumption. I have my DB with one table with two constraint on new entries. The “first” is for the parent and basename be unique. The “second” is that the devno and inode are unique if it is a directory. When I was doing my early testing, the parent+basename co

Re: What have I done!?!?!? :-)

> On Apr 8, 2022, at 08:10, Magnus Hagander wrote: > > > > On Fri, Apr 8, 2022 at 3:07 PM Jan Wieck > wrote: > On 4/8/22 08:58, Magnus Hagander wrote: > > A side-note on this, which of course won't help the OP at this point, > > but if the general best practice of not

Re: What have I done!?!?!? :-)

> On Apr 8, 2022, at 07:47, Jan Wieck wrote: > > On 4/8/22 01:57, Nikolay Samokhvalov wrote: >> On Thu, Apr 7, 2022 at 8:10 AM Jan Wieck > > wrote: >>So **IF** Active Record is using that feature, then it can dump any >>amount of garbage into your PostgreSQL data

Re: What have I done!?!?!? :-)

> On Apr 6, 2022, at 18:05, Lionel Bouton wrote: > > Hi Perry, > > Le 07/04/2022 à 00:25, Perry Smith a écrit : >> [...] > I'd say the later : in your case I would use a NULL parent_id for root(s). > Your way leads you to bend PostgreSQL until its back

What have I done!?!?!? :-)

disable_referential_integrity which takes a block of code. When the block of code exists, the constraints are put back. At least, that is what I thought. I’m wondering if the disabled constraints are still disabled somehow. If so, how would I check for that and how would I turn them back on

Re: Problem with PG 11 database on OrangePi3 (ARMBIAN, ARM64) after disk disrupion (problem with USB disk connection)

On Wednesday, April 6th, 2022 at 17:41, W.P. wrote: > That was not a "command". PG was working ok, DB on encrypted (LUKS on > LVM2, ext4) USB drive. > I mean, why ? If there was ever a "setting yourself up to fail" scenario, it would be this. Just because you can do it, doesn't mean you sho

Re: Select .... where id not in (....) returns 0 incorrectly

> > Doesn't this usually happen if q.id contains NULL. That is as per ANSI > standard. > > Yes, there's a good description of this here: https://wiki.postgresql.org/wiki/Don't_Do_This#Don.27t_use_NOT_IN It would be better to use NOT EXISTS: select count(*) from snapshotlist where NOT EXISTS (SELE

Re: Open SSL Version Query

Hi Sahaj AFAIK this is a question for you to ask your chosen OS provider. Postgres will be compiled against the system library (dynamic linking) therefore whether your version of OpenSSL has been patched against the vulnerability is a question for your OS provider, not Postgres. Unless of cour

Re: UUID type question

Sent with ProtonMail Secure Email. --- Original Message --- On Friday, February 18th, 2022 at 14:25, Dominique Devienne wrote: > > You might also be interested in KSUID, e.g. https://github.com/segmentio/ksuid I love KSUID. I use it in all new projects. ;-)

UUID type question

I'm *sure* I've seen this discussed on here before - infact I'm worried it might even have been me who asked the question ! But I've searched the archives without luck, so here goes : Is there anything inherently "special" about the UUID type ? i.e. if I store a UUID in a text is it "worse" ?

Re: pg_basebackup with hostssl ?

Sent with ProtonMail Secure Email. ‐‐‐ Original Message ‐‐‐ On Wednesday, February 2nd, 2022 at 17:20, Adrian Klaver wrote: > > Before you do that I would establish that you are connecting to the > > correct Postgres instance. > > Good news, all up and running ! The new "postgresq

Re: pg_basebackup with hostssl ?

Sent with ProtonMail Secure Email. ‐‐‐ Original Message ‐‐‐ On Wednesday, February 2nd, 2022 at 16:50, Adrian Klaver wrote: > Not completely: > > https://www.postgresql.org/docs/current/app-pgbasebackup.html > > -d connstr > > --dbname=connstr > > Specifies parameters used to connec

Re: pg_basebackup with hostssl ?

Forgot to add that I also have : "hostsslallall10.0.0.0/8md5"

Re: pg_basebackup with hostssl ?

Sent with ProtonMail Secure Email. ‐‐‐ Original Message ‐‐‐ On Wednesday, February 2nd, 2022 at 16:30, Adrian Klaver wrote: > I am not following. pg_basebackup is a stand alone client that is not > > involved in replication other then establishing a starting point from > > which to

pg_basebackup with hostssl ?

I've had a quick glance through the man page for pg_basebackup but can't see any flags to set ssl. Long story short, I've got the following in pg_hba.conf of the master: hostsslreplicationall10.0.0.0/8md5 But the slave is complaining: FATAL: no pg_hba.conf entry for replication

Re: Counting the number of repeated phrases in a column

‐‐‐ Original Message ‐‐‐ On Tuesday, January 25th, 2022 at 17:10, Shaozhong SHI wrote: > 'The City of London, London' also has no occurrences of any repeated phrase. Not sure the City would be particularly happy with that attribution. ;-) Its it sits on its own. Its own local authori

Re: ZFS filesystem - supported ?

Sent with ProtonMail Secure Email. ‐‐‐ Original Message ‐‐‐ On Tuesday, October 26th, 2021 at 01:18, Benedict Holland wrote: > In my opinion, ext4 will solve any and all problems without a very deep > understanding of file system architecture. In short, i would stick with ext4 > unle

Re: ZFS filesystem - supported ?

‐‐‐ Original Message ‐‐‐ On Saturday, October 23rd, 2021 at 18:48, Mladen Gogala wrote: > On 10/23/21 09:37, Laura Smith wrote: > > > Hi Mladen, > > > > Yes indeed, snapshots is the primary reason, closely followed by > > zfssend/receive. > > > &

Re: ZFS filesystem - supported ?

On Saturday, October 23rd, 2021 at 14:03, Mladen Gogala wrote: > On 10/23/21 07:29, Laura Smith wrote: > > > Hi, > > > > Given an upcoming server upgrade, I'm contemplating moving away from XFS to > > ZFS (specifically the ZoL flavour via Debian 11). BTRFS

ZFS filesystem - supported ?

Hi, Given an upcoming server upgrade, I'm contemplating moving away from XFS to ZFS (specifically the ZoL flavour via Debian 11). BTRFS seems to be falling away (e.g. with Redhat deprecating it etc.), hence my preference for ZFS. However, somewhere in the back of my mind I seem to have a recoll

Debian APT repo instructions need updating

Not sure if this is the right place to post it, but I wanted to higlight that the Debian repo instructions (https://www.postgresql.org/download/linux/debian/) need updating to bring them inline with Debian best practices. As per https://wiki.debian.org/DebianRepository/UseThirdParty: "The key

RE: Multi-master replication

‐‐‐ Original Message ‐‐‐ On Friday, August 13th, 2021 at 12:35 PM, Zahir Lalani wrote: > Confidential > > Thx Laura > > So here is the dilemma - everything in the cloud world tends toward > horizontal scaling. We do that with PG using single master and multiple > slaves. But we are wr

Re: Multi-master replication

‐‐‐ Original Message ‐‐‐ On Friday, August 13th, 2021 at 9:00 AM, Zahir Lalani wrote: > Confidential > > Hello all > > Does anyone on the list have production experience of using PG in a > multi-master setup – if so using which tools? > > Thx > > Zahir I have sometimes considered it

PostgreSQL Apt Repository instructions need updating

Re: https://www.postgresql.org/download/linux/debian/ The instructions presented for key handling are not inline with Debian best-practices  As per https://wiki.debian.org/DebianRepository/UseThirdParty: "The key MUST be downloaded over a secure mechanism like HTTPS to a location only writable

Re: returning setof from insert ?

‐‐‐ Original Message ‐‐‐ On Wednesday, July 14th, 2021 at 1:14 PM, Thomas Kellerer wrote: > Laura Smith schrieb am 14.07.2021 um 13:22: > > > A bit of pl/pgsql writer's block going on here ... > > > > Postgres complains "RETURN cannot have a parame

returning setof from insert ?

Hi, A bit of pl/pgsql writer's block going on here ... Postgres complains "RETURN cannot have a parameter in function returning set" in relation to the below. I don't really want to have to "RETURNS TABLE" because that means I have to enumerate all the table columns. I'm sure I'm missing somet

Re: Ideas for building a system that parses medical research publications/articles

Sent with ProtonMail Secure Email. ‐‐‐ Original Message ‐‐‐ On Saturday, 5 June 2021 12:14, Achilleas Mantzios wrote: > > I know its a huge work, but you are missing a point. Nobody wishes to > compete with anyone. This is a about a project, a parent-advocacy > non-profit that ONLY

Re: Ideas for building a system that parses medical research publications/articles

‐‐‐ Original Message ‐‐‐ On Saturday, 5 June 2021 10:49, Achilleas Mantzios wrote: > Hello > > I am imagining a system that can parse papers from various sources > (web/files/etc) and in various formats (text, pdf, etc) and can store > metadata for this paper ,some kind of global ID if

Re: Struggling with EXCLUDE USING gist

Sent with ProtonMail Secure Email. ‐‐‐ Original Message ‐‐‐ On Friday, 4 June 2021 18:45, Mark Dilger wrote: > > On Jun 4, 2021, at 9:47 AM, Laura Smith [email protected] > > wrote: > > CREATE TABLE test ( > > t_val text not null, > >

Re: Struggling with EXCLUDE USING gist

Sent with ProtonMail Secure Email. ‐‐‐ Original Message ‐‐‐ On Friday, 4 June 2021 18:45, Mark Dilger wrote: > > On Jun 4, 2021, at 9:47 AM, Laura Smith [email protected] > > wrote: > > CREATE TABLE test ( > > t_val text not null, > >

Re: Struggling with EXCLUDE USING gist

Sent with ProtonMail Secure Email. ‐‐‐ Original Message ‐‐‐ On Friday, 4 June 2021 18:11, Julien Rouhaud wrote: > On Sat, Jun 5, 2021 at 12:48 AM Laura Smith > [email protected] wrote: > > > All the examples I've seen around the internet mak

Re: Struggling with EXCLUDE USING gist

‐‐‐ Original Message ‐‐‐ On Friday, 4 June 2021 18:07, Adrian Klaver wrote: > On 6/4/21 9:47 AM, Laura Smith wrote: > > > All the examples I've seen around the internet make this sound so easy. > > But I seem to be missing some important step because all I'

Struggling with EXCLUDE USING gist

All the examples I've seen around the internet make this sound so easy. But I seem to be missing some important step because all I'm getting are messages such as "DETAIL: Key (t_val, t_version)=(def, [-infinity,infinity)) conflicts with existing key (t_val, t_version)=(def, [-infinity,"2021-06-

Re: EXCLUDE USING and tstzrange

Sent with ProtonMail Secure Email. ‐‐‐ Original Message ‐‐‐ On Friday, 4 June 2021 16:20, Joe Conway wrote: > On 6/4/21 10:58 AM, Laura Smith wrote: > > > One other question, what's the syntax for manipulating only the upper > > bound of a range. > > S

Re: EXCLUDE USING and tstzrange

Sent with ProtonMail Secure Email. ‐‐‐ Original Message ‐‐‐ On Friday, 4 June 2021 15:44, Adrian Klaver wrote: > On 6/4/21 7:32 AM, Laura Smith wrote: > > > Hi, > > I'm having difficulty finding the right part of the docs for this one. > > Could someo

EXCLUDE USING and tstzrange

Hi, I'm having difficulty finding the right part of the docs for this one. Could someone kindly clarify: create table test ( test_id text, test_range tstzrange); Will "EXCLUDE USING gist (test_id WITH =, test_range WITH && )" work as expected or do I need to use "EXCLUDE USING gist (test_id WI

Re: Internationalisation (i18n) with Postgres as backend

On Wednesday, 2 June 2021 00:42, Rob Sargent wrote: > On 6/1/21 5:00 PM, Laura Smith wrote: > > > > What is your notion of "object".  I first assumed it was akin to > > > "document" but then pages have objects. > > > > I think my terminolog

Re: Internationalisation (i18n) with Postgres as backend

rray['de', > 'en']); >  langtestfunc > -- >  de for abc > (1 row) > b2bc_owner@b2bcreditonline=# select langtestfunc('zz', 'def', array['de', > 'en']); >  langtestfunc > -- >  en for def

Re: Internationalisation (i18n) with Postgres as backend

> What is your notion of "object".  I first assumed it was akin to > "document" but then pages have objects. I think my terminology is a bit off. A document/page has object(s) on it. Or, perhaps better expressed, think of document/page as the template and object(s) is what fills the gaps in t

Internationalisation (i18n) with Postgres as backend

Hi, I'm creating a Postgres backend for an internal tool which is essentially a very simple implementation of multi-lingual CMS. So far my thoughts are along the lines of the below, but I would appreciate a second (or more !) pair of eyes from some Postgresql gurus. I am especially interested

Re: Overriding natural order of query results for a subset

Sent with ProtonMail Secure Email. ‐‐‐ Original Message ‐‐‐ On Saturday, 29 May 2021 17:55, Tom Lane wrote: > Michael Nolan [email protected] writes: > > > You probably need some kind order by case when else end clause, > > where the else clause deals with the non-VIPs, prob

Re: Overriding natural order of query results for a subset

‐‐‐ Original Message ‐‐‐ On Saturday, 29 May 2021 17:06, Adrian Klaver wrote: > On 5/29/21 9:00 AM, Laura Smith wrote: > > > I did try "nulls last" but will give it another go, maybe I messed up on > > the ordering of clauses. > > Unless the fields

Re: Overriding natural order of query results for a subset

uot; option in order by > (https://www.postgresql.org/docs/13/queries-order.html)? > > Alternatively, you could write your own type, with its own ordering primitive > 😉 > > On Sun, 30 May 2021, 12:15 am Laura Smith, > wrote: > > > Hi > > > > I

Overriding natural order of query results for a subset

Hi I've got a bit of a puzzle that I'm not quite sure how to approach. Let's say I've got a table of bios, so : create table bios ( first_name text not null, last_name text not null, person_title text, person_short_bio text ); Now, the "natural order" would be a standard "select * from bios ord

Re: Modelling versioning in Postgres

objectID uuid, >   versionID uuid, >   validRange tsrange, >   objectData text, >   EXCLUDE USING GIST(objectID WITH =, validRange WITH &&) > ); > > On Fri, May 28, 2021 at 8:20 PM Laura Smith > wrote: > > > Hi > > > > I was wondering what the c

Modelling versioning in Postgres

Hi I was wondering what the current thinking is on ways to model versioning in Postgres. The overall premise is that the latest version is the current version unless a rollback has occurred, in which case versions get tracked from the rollback point (forking ?). My initial naïve starting poin

Re: Logical Replication: SELECT pg_catalog.set_config Statement

> > We found out because we are monitoring long running queries, and saw it > had been running for a month before the restart yesterday. > I just queried pg_stat_activity and it seems to be running since then. > > taimusz=# SELECT pid, query_start, usename, left(query,70) > FROM pg_stat_activity >

Re: Foreign table performance issue / PostgreSQK vs. ORACLE

> > > Could there be some tuning option in PostgreSQL to make queries via > foreign tables faster (e.g. I heard about option fetch_size)? > fetch_size can make a difference, but it won't change a query that takes hours into a query that takes seconds. The default is likely too low, though. Have

Re: How to keep format of views source code as entered?

On Sat, Jan 9, 2021 at 9:22 AM Markhof, Ingolf < [email protected]> wrote: > Tom, all, > > when I change a tables column name in Oracle SQL , SQLdeveloper (a SQL > client) marks views that refer to the table using the old column name as > erroneous. So, I can easily identify these case

  1   2   >