Hi,
I've seen various Postgres examples here and elsewhere that deal with the old
common-prefix problem (i.e. "given 1234 show me the longest match").
I'm in need of a bit of guidance on how best to implement an alternative take.
Frankly I don't quite know where to start but I'm guessing it wi
On Wednesday, August 7, 2019 2:01 AM, Andy Colson wrote:
> On 8/6/19 6:25 PM, Laura Smith wrote:
>
> > Hi,
> > I've seen various Postgres examples here and elsewhere that deal with the
> > old common-prefix problem (i.e. "given 1234 show me the longest ma
Hi,
I have some aggregated statistics which are currently being queried as follows:
create view stats_age as
SELECT a.category,
a.statcount,
b.a_desc
FROM reg_aggregated_stats a,regs_select_age b where a.category='age' and
a.datapoint::smallint=b.a_val order by a.datapoint asc;
Ho
>
> or this
> with t as
> (select (select sum(statcount) from stats_residence) as aggstat,
> statcount,short_name_en from stats_residence
> )
> select * from t where aggstat > some_number
>
> Apology if I did not understand the question correctly.
Hi Ravi,
Thanks for helping show me the way. Yo
According to the all-mighty manual
(https://www.postgresql.org/docs/current/app-pgrestore.html), life is supposed
to be as simple as:
"To drop the database and recreate it from the dump:
$ dropdb mydb
$ pg_restore -C -d postgres db.dump"
The reality seems to be somewhat different ?
sudo -u pos
Sent with ProtonMail Secure Email.
‐‐‐ Original Message ‐‐‐
On Friday, 5 June 2020 19:23, Christophe Pettus wrote:
> > On Jun 5, 2020, at 11:20, Laura Smith n5d9xq3ti233xiyif...@protonmail.ch
> > wrote:
> > sudo -u postgres pg_restore -v -C -d foobar 4_foobar_pgdu
Sent with ProtonMail Secure Email.
‐‐‐ Original Message ‐‐‐
On Friday, 5 June 2020 19:35, Tom Lane wrote:
> Laura Smith n5d9xq3ti233xiyif...@protonmail.ch writes:
>
> > But doesn't the second half of my original post demonstrate that I tried
> > that very thi
Hi,
I'm having a little trouble with RLS in Postgres 12, although first time I've
used RLS, so it might just be me !
The problem is that I am calling a function from a web-app, but the function
seems to be executing as "postgres" even thouhg the web-app logs in as a
completely different role ?
On Monday, 8 June 2020 11:25, Paul Förster wrote:
> Hi Laura,
>
> > On 08. Jun, 2020, at 12:17, Laura Smith n5d9xq3ti233xiyif...@protonmail.ch
> > $$ LANGUAGE plpgsql SECURITY DEFINER;
>
> you might want to use security invoker instead of definer.
>
> https://w
On Monday, 8 June 2020 12:42, Paul Förster wrote:
> Hi Laura,
>
> > On 08. Jun, 2020, at 12:46, Laura Smith n5d9xq3ti233xiyif...@protonmail.ch
> > I had a lightbulb moment just now and tried that, but it doesn't seem to be
> > working.
> > The app returns
Hi,
What'st the current state of play with indexes and ON CONFLICT ? The docs seem
to vaguely suggest it is possible, but this SO question
(https://stackoverflow.com/questions/38945027/) seems to suggest it is not.
I've got a unique named index on a table (i.e. "create unique index xyz...")
b
Hi,
Just curious if there is a way to switch a function from definer to invoker
without dropping ?
We're working on improving the security posture by changing functions from
definer to invoker, but I'm wondering what the best way to roll this out to
production is given that dropping and re-cre
Sent with ProtonMail Secure Email.
‐‐‐ Original Message ‐‐‐
On Thursday, 11 June 2020 08:39, Pavel Stehule wrote:
> Hi
>
> čt 11. 6. 2020 v 9:29 odesílatel Laura Smith
> napsal:
>
> > Hi,
> >
> > Just curious if there is a way to switch a
Hi,
Let's say we've got a fairly basic table :
create table networks (
lan_id text not null,
net_id text not null,
port_id text not null
);
create index net_uniq on networks(lan_id,port_id);
The query conundrum I am facing is that I need to add metadata to the output of
the query that indicates
On Wednesday, 19 August 2020 15:09, David G. Johnston
wrote:
> On Wednesday, August 19, 2020, Laura Smith
> wrote:
>
> > Hi,
> >
> > Let's say we've got a fairly basic table :
> >
> > create table networks (
> > lan_id text not nu
Hi,
How have others approached the problem of storing and querying filesystem paths
?
I'm talking about *nix/*nux style paths, e.g. /home/foo/bar
A text column with a GIN index is an obvious possibility, but I'm interested to
hear what approach others have taken ?
Laura
‐‐‐ Original Message ‐‐‐
On Monday, November 30, 2020 12:36 PM, Laurenz Albe
wrote:
> On Mon, 2020-11-30 at 12:05 +0000, Laura Smith wrote:
>
> > How have others approached the problem of storing and querying filesystem
> > paths ?
> > I'm talking abo
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
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
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
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
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
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
Hi,
Am looking to compile some software that will make use of Postgresql libraries.
However doing "apt-cache search postgresql-11-*" I can't see any libs or dev
package ?
Would installing "postgresql-client-11" be sufficient ? I don't really want
to go installing the whole shebang.
Thanks !
Sent with ProtonMail Secure Email.
‐‐‐ Original Message ‐‐‐
On Sunday, April 14, 2019 8:34 PM, Adrian Klaver
wrote:
> On 4/14/19 12:09 PM, Laura Smith wrote:
>
> > Hi,
> > Am looking to compile some software that will make use of Postgresql
> > libraries
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
> 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
--- 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
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
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
>
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
>
> 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
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
> 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
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
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
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
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
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
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
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
‐‐‐ 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
Sent with ProtonMail Secure Email.
‐‐‐ Original Message ‐‐‐
On Saturday, 29 May 2021 17:55, Tom Lane wrote:
> Michael Nolan htf...@gmail.com writes:
>
> > You probably need some kind order by case when else end clause,
> > where the else clause deals with the non-VIPs, prob
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
> 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
rray['de',
> 'en']);
> langtestfunc
> --
> de for abc
> (1 row)
> b2bc_owner@b2bcreditonline=# select langtestfunc('zz', 'def', array['de',
> 'en']);
> langtestfunc
> --
> en for def
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
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
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
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
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-
‐‐‐ 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'
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
> n5d9xq3ti233xiyif...@protonmail.ch wrote:
>
> > All the examples I've seen around the internet mak
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 n5d9xq3ti233xiyif...@protonmail.ch
> > wrote:
> > CREATE TABLE test (
> > t_val text not null,
> >
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 n5d9xq3ti233xiyif...@protonmail.ch
> > wrote:
> > CREATE TABLE test (
> > t_val text not null,
> >
‐‐‐ 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
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
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
‐‐‐ 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
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
‐‐‐ 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
‐‐‐ 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
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
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
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
‐‐‐ 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.
> >
> &
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
‐‐‐ 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
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
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
Forgot to add that I also have :
"hostsslallall10.0.0.0/8md5"
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
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
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" ?
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. ;-)
75 matches
Mail list logo
