On Mon, 27 Feb 2023, Tom Lane wrote:
>Well, yeah. Simplify it to
>
> SELECT array_agg(DISTINCT x ORDER BY y) FROM mytable;
That’s… a bit too simple for this case.
>If there are several rows containing the same value of x and different
>values of y, which y value are we supposed to sort the uni
On Mon, 27 Feb 2023, David G. Johnston wrote:
>Consider this then as a jumping point to a more precise query form:
[…]
>the basic concept holds - produce single rows in subqueries then join those
>various single rows together to produce your desired json output.
Ouch. I’ll have to read up and exp
On Tue, 28 Feb 2023, Alban Hertroys wrote:
>Perhaps you can use a lateral cross join to get the result of
>jsonb_build_object as a jsonb value to pass around?
I don’t see how. (But then I’ve not yet worked with lateral JOINs.)
But I c̲a̲n̲ just generate the objects first, I t̲h̲i̲n̲k̲, given one
On Fri, 3 Mar 2023, Alban Hertroys wrote:
>You can rewrite that into something like this:
>
>select jsonb_build_object('opening_times’,
> obj
> ORDER BY
> obj->>'weekday’,
> obj->>'from_hour’,
> obj->>'to_hour')
>)
>from cot
>cross join lateral jsonb_agg(j
On Sat, 4 Mar 2023, Alban Hertroys wrote:
>> But isn’t that the same as with a regular LEFT JOIN?
>
>Similar, but not the same, I’d say.
>
>I do now notice that I made some copying errors there, I was a bit
>nauseous at that time.
np, I’m under pollen attack currently so also not at my best.
>Th
On Tue, 14 Mar 2023, Celia McInnis wrote:
>uc_alphabet = lc_alphabet.replace('ß', 'ẞ').upper()
That’s probably for the best. The uppercase Eszett was only added
to Unicode under the rule that the lowercase Eszett’s case rules
are kept unchanged, and the former’s considered normally only ever
type
On Mon, 20 Mar 2023, David G. Johnston wrote:
>On Monday, March 20, 2023, Dávid Suchan
>wrote:
>
>> I installed both postgres versions on ubuntu machine with 'apt-get install
>> postgres', which installed both client and server packages. Is that where I
>> made a mistake?
>Anyway, you had to hav
Hi Markus,
>I am building a simple integration test framework for an application that I
>am building. For this project I am planning to use PostgreSQL.
check pg_virtualenv(1), which apparently is a Debian thing.
It auto-creates and auto-deletes at the end, if desired, a
cluster and runs your prog
On Wed, 29 Mar 2023, Ron wrote:
> There are 550+ tables, so something that I can do once on this end would make
> my life a lot easier.
Some quick perl or awk or shell job to batch-change the field
to an accepted syntax is probably quicker.
bye,
//mirabilos
--
15:41⎜ Somebody write a testsuite
On Fri, 7 Apr 2023, Telium Technical Support wrote:
>Notice: extra pg_ctl/postgres options given, bypassing systemctl for stop
>it might be 13. Coincidentally, there is a postmaster.pid file in a
>directory OTHER than the data directory:
>
>/var/lib/postgresql/15/main/postmaster.pid
>
>(and noti
On Fri, 7 Apr 2023, Telium Technical Support wrote:
>I’m assuming something is misconfigured on the host that’s causing this
>unusual behavior….and that’s what I need to understand
The mix between 13 and 15 here is what I’d consider a misconfiguration.
Also, please don’t top-post and full-quote.
On Tue, 11 Apr 2023, Federico wrote:
>The problem here is not having the auto increment id in a particular
The id might not even be auto-increment but UUID or something…
(I am surprised you would even try to insert multiple rows at once.)
bye,
//mirabilos
--
15:41⎜ Somebody write a testsuite fo
On Tue, 11 Apr 2023, Federico wrote:
>Of course sorting the returned ids is only viable when using a serial
Yes, which is why I pointed out it doesn’t have to be.
>or identity column, that's why in the general case I've mentioned the
>insert with sentinel column
But it was pointed out that that
On Tue, 11 Apr 2023, Federico wrote:
>I was under the impression that when using INSERT SELECT ORDER BY the sequence
>ids were generated using the select order.
But someone said that’s not guaranteed, especially when INSERT will
be parallelised later.
bye,
//mirabilos
--
15:41⎜ Somebody write a
On Tue, 11 Apr 2023, Federico wrote:
>My understanding was that they are generated in select order
But are they? (I don’t know, but I’d not assume that.)
>If my understanding is incorrect, would this alternative guarantee the above
>INSERT INTO t(id, data)
>SELECT nextval(pg_get_serial_
On Wed, 12 Apr 2023, Kirk Wolak wrote:
>The solution exists. Pre-fetch the IDs, assign them and insert them with
>the IDs. Then you have 100% control.
>SELECT NEXTVAL('tbl_seq') from GENERATE_SERIES(1, );
This would be a nice solution… but the PK could be always generated,
or not even sequentia
On Wed, 12 Apr 2023, Mike Bayer wrote:
>ascending values for "mytable_id_seq" will correspond to the ascending
>values for "num".
But, again, what if it’s uuid or something instead of a sequence?
bye,
//mirabilos
--
15:41⎜ Somebody write a testsuite for helloworld :-)
On Fri, 14 Apr 2023, Laurenz Albe wrote:
>So if your disk replaces a valid block with zeros (filesystem check
>after crash?), that could explain what you see.
Oh, I had that happen on a RAID 1 once. On of the two discs had an
intermittent error (write I guess) but didn’t fail out of the RAID,
and
On Sat, 22 Apr 2023, Tom Lane wrote:
>Wen Yi writes:
>> [ use calloc to replace zeroing fields individually ]
[…]
>People have complained about this practice off-and-on, but no one has
>provided any evidence that there's a significant performance cost.
>The maintenance benefits are real though.
On Fri, 12 May 2023, Nathaniel Sabanski wrote:
>I believe most users would anticipate a CREATE TABLE statement that aligns
>with the currently installed version- this is the practical solution for
The currently installed version of what, the server or the client?
bye,
//mirabilos
--
15:41⎜ Some
On Fri, 12 May 2023, GF wrote:
>"The SQL standard defines a RESPECT NULLS or IGNORE NULLS option for lead,
>lag, first_value, last_value, and nth_value. This is not implemented in
>PostgreSQL: the behavior is always the same as the standard's default,
>namely RESPECT NULLS".
Yeah, THAT caused no
On Thu, 18 May 2023, Tony Xu wrote:
>Our use-case is for a multi-tenancy scenario - we are considering using
>different databases to store different customer's data, however, for
Why not using multiple clusters then?
Better isolation of the customers, but still on one server.
bye,
//mirabilos
-
On Thu, 18 May 2023, Ron wrote:
>> Why not using multiple clusters then?
>
> Yet More Firewall Rules to get approved by the Security Team. And then they
> balk at port 5433 because they've never heard of it.
But mixing multiple customers on one cluster is much more of a risk.
> And from a techn
(please read http://deb.li/quote and don’t top-post)
On Mon, 22 May 2023, Tony Xu wrote:
>First question - do we, as a community, see the value of the proposal and
>do we believe that value is big enough for us to make any necessary changes
I’d rather like to see the energy, if there’s some inve
On Wed, 31 May 2023, Adrian Klaver wrote:
> Given that I would just COPY the data out as CSV.
I recently did something similar. I found the JSON functions not quite
satisfying and the extra spaces redundant, but it turns out that, for
a numerical table, exporting as CSV, loading that via AJAX the
On Sun, 6 Aug 2023, H wrote:
>I am running PostgreSQL 13.11 and tried to drop all tables in a
>database without dropping the database or schema.
See:
https://evolvis.org/plugins/scmgit/cgi-bin/gitweb.cgi?p=useful-scripts/useful-scripts.git;a=tree;f=SQL;hb=HEAD
Comments welcome (especially a fix
26 matches
Mail list logo
