Shaozhong SHI schrieb am 15.02.2022 um 07:23:
> There is a JSON column in a table. It contains key value pairs, just
> like a dictionary. What is the best way to turn this column into a
> data table?
jsonb_each() is one option:
select j.*
from the_table t
cross join jsonb_each(t.the_c
There is a JSON column in a table. It contains key value pairs, just like
a dictionary. What is the best way to turn this column into a data table?
Regards,
David
In pseudo code, group_index is defined as:
case when LAG(v) OVER (ORDER BY i) = v then lag(i) ELSE i END, right?
If you have that in the first cte instead of the start/end business, then
you can just select vals, group number, and row_num over that new grouping,
right?
Something like this?
WITH
I think that theoretically if the planner sees a condition like
a.column1 > constant_value, and it also has a condition like a.column1 =
b.column2 then it could autogenerate the b.column2 > constant_value
condition. And of course > could be <, <=, >= and <>
But I could be wrong, particularly with
I started using the latest postgres 14.2 and the query plans there for a
simplified query joining just 2 tables look like:
explain analyze select t.*, ta.* from team as t, team_aliases as ta where
ta.team_id = t.id and t.id > 2 order by t.id limit 1000;
QUERY PLAN
-
Hi!
In the following query I ended up using unnest(array(subquery)) in order to
pair up the rows with starting indices and the rows with ending indices.
This is exactly what "FROM ROWS FROM (function)" would do but alas I have a
subquery. In the target list I have to use scalar subqueries so doin
On Tue, 2022-02-15 at 08:58 +1300, Glen Eustace wrote:
>
> But upgrading that way takes too long for the master so I build a
> new
> server instead. So, if I shutdown both postgresql instances old and
> new,
> rsync the data directory and restart on the new. I should be OK ?
>
Should be, yeah.
Radoslav Nedyalkov writes:
> If the target is upgraded to pg13 then the initial copy goes fine.
> Unfortunately it is a 40T db in a sunset, so we'd rather won't upgrade.
Yeah, after looking at the code, pre-v13 versions simply don't know
how to do initial sync from anything except a plain table.
I have an embedded tomcat talking directly to postgres server via sql
generated by jOOQ. By default jOOQ creates a prepared statement for all
selects, and does so on each invocation from the client (client in this
case is a servlet in tomcat). I've been using jOOQ-to-db for a while
but the se
On Fri, Feb 11, 2022 at 05:05:20PM -0500, Tom Lane wrote:
> Bryn Llewellyn writes:
> > I confess that I'm surprised by the choice of the default behavior. It
> > seems to be at odds with the principle of least privilege that insists that
> > you actively opt in to any relevant privilege.
>
> I
On 15/02/22 8:39 am, Alan Hodgson wrote:
pg_dump -> restore will break your streaming replication. You'll need
to set it up again.
That's what I thought might be the case.
If the PG version isn't changing and you're still on the same version
of Linux, rsync would be easier.
I did an ELeva
On Tue, 2022-02-15 at 08:29 +1300, Glen Eustace wrote:
> I need to move my master postgresql deployment to a new server.
>
> I am comfortable with stopping all connections then doing a
> pg_dumpall >
> psql to move the databases, they are not huge so this completes in
> an
> acceptable time and
I need to move my master postgresql deployment to a new server.
I am comfortable with stopping all connections then doing a pg_dumpall >
psql to move the databases, they are not huge so this completes in an
acceptable time and I am not expecting any data loss but I am unsure of
what impact thi
Hi,
I am an ASP.NET Core developer and I have been using Devart dotConnect for
PostgreSQL for some time. I know about npgsql data provider but I have
never used it. Lately, I have been considering using npgsql instead of
the Devart product. Is this a good choice? Is this a good decision?
I have g
On Mon, Feb 14, 2022 at 9:36 AM Grzegorz Zając
wrote:
> Good morning,
>
> I have a problem writing in pgAdmin. when I want to write or correct
> something in code I have already written, new characters I put in erase
> what I wrote next. I do not know if you understand what I mean, but I will
> p
> ddevie...@gmail.com wrote:
>
>> b...@yugabyte.com wrote:
>>
>> …Then I did this:
>>
>> with c as (
>> select
>>proname::text as name,
>>pronamespace::regnamespace::text as schema,
>>aclexplode(proacl)as "aclexplode(proacl)"
>> from pg_catalog.
Hi!
It worked.
Thank you very much.
Andrus.
13.02.2022 16:46 Torsten Förtsch kirjutas:
WITH x AS (
SELECT *
, sum(hours) OVER w AS s
FROM hours
WINDOW w AS (PARTITION BY person ORDER BY job_id)
)
SELECT *
, greatest(least(s, 120) - coalesce(lag(s, 1) OVER w, 0), 0) AS
Good morning,I have a problem writing in pgAdmin. when I want to write or correct something in code I have already written, new characters I put in erase what I wrote next. I do not know if you understand what I mean, but I will post a file in which I will try to explain it with the help of photos.
Hi!
Thank you. In this result, regular and overtime columns contain running
totals.
How to fix this so that those columns contain just hours for each job?
sum on regular column should not be greater than 120 per person.
sum of regular and overtime columns must be same as sum of hours column
As a workaround I have removed REPLICA IDENTITY from table
closed_sessions, do cluster upgrade, and then add REPLICA IDENTITY
back.
Sergey Belyashov
чт, 10 февр. 2022 г. в 15:09, Sergey Belyashov :
>
> Hi,
> I have tried to upgrade my cluster from version 13 to 14 using the
> command: "pg_upgrade
>
>
> There is a method of simulating SQL Server by using PostgreSQL. The
> extension is called "Babelfish": https://babelfishpg.org/
>
> However, this extension requires modifications to the PostgreSQL source
> and building the PostgreSQL with Babelfish modifications from source. While
> it is a r
Just an update.
If the target is upgraded to pg13 then the initial copy goes fine.
Unfortunately it is a 40T db in a sunset, so we'd rather won't upgrade.
On Fri, Feb 11, 2022 at 4:50 PM Radoslav Nedyalkov
wrote:
> Hello All,
> It is a bit specific logical replication setup where we try to repli
Hi,
On Mon, Feb 14, 2022 at 11:16:24AM +0100, Marian Pompura wrote:
>
> Let's say:
>
> postgresql.conf configuration file on primary server:
>
> shared_preload_libraries = 'pg_stat_statements,'
>
> *Contrib package is installed on primary and replication server too.
>
> postgresql.conf co
Dear all,
I have question regarding how extensions acting during switchover process.
We running PostgreSQL 11 HA primary/replication server that is configured
with repmgr.
Everything works fine but I am interested in matter of how extensions works
during failover/switchover.process.
Let's say:
On Sat, Feb 12, 2022 at 8:43 PM Bryn Llewellyn wrote:
> I.e. three facts per row: grantee, privilege, and grantee. Then I did this:
> with c as (
> select
> proname::text as name,
> pronamespace::regnamespace::text as schema,
> aclexplode(proacl)a
25 matches
Mail list logo
