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 Mon, Feb 27, 2023 at 6:22 PM Thorsten Glaser wrote:
>
> Not about the DISTINCTs. I haven’t used JOIN much (usually WHERE
> as inner join) nor on tables this massive, and this is my second
> foray into aggregate functions only.
>
>
Fair.
Consider this then as a jumping point to a more precise
Thorsten Glaser writes:
> 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.
Sure, I was just trying to explain the rule.
>> For the specific example you give, it's true that any
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 2/28/23 01:17, Erik Wienhold wrote:
>> On 27/02/2023 13:13 CET Laurenz Albe wrote:
>>
>> I'd be curious to know how the customer managed to do that.
>> Perhaps there is a loophole in PostgreSQL that needs to be fixed.
> Probably via some data access layer and not directly via Postgres. It's ea
On 2/27/23 22:13, Laurenz Albe wrote:
On Mon, 2023-02-27 at 06:28 +, Jan Bilek wrote:
Our customer was able to sneak in an Unicode data into a column of a JSON Type
and now that record fails on select.
Would you be able to suggest any way out of this? E.g. finding infringing row,
updating
On Mon, Feb 27, 2023 at 5:22 PM David G. Johnston <
david.g.johns...@gmail.com> wrote:
>
> Lastly, if you do need to care about normalizing the output of JSON you
> should consider writing a function that takes arbitrary json input and
> reformats it, rather than trying to build up json from scrat
On Mon, Feb 27, 2023 at 4:11 PM mirabilos wrote:
>
> jsonb_build_object('opening_times',
> jsonb_agg(DISTINCT jsonb_build_object(
> 'weekday', cot.weekday,
> 'from_hour', cot.from_hour,
> 'to_hour', cot.to_hour)
> ORDER BY cot.weekday, cot.from_hour, cot.to_hour)
>
mirabilos writes:
> This works well. However, what I seem to be not allowed to do is
> (without the extra COALESCE, to simplify):
> ...
> This is because, when I use DISTINCT (but only then‽), the ORDER BY
> arguments must be… arguments to the function, or something.
Well, yeah. Simplify it to
Hi,
I’ve got a… rather large query (see below), in which I join a complex
data structure (whose exact contents do not, at this point, matter)
together to get some auxiliary data to expose as JSON field.
In this query I can use, for example…
jsonb_build_object('user_permissions',
jsonb_agg(DI
On Mon, Feb 27, 2023 at 2:40 PM Adrian Klaver
wrote:
> On 2/27/23 11:34 AM, Ron wrote:
> > Is there any direct way in Postgresql to get rid of the frankly
> > anti-useful junk at the end of each line (which also infects
> > pg_stat_activity.query), or must I resort to sed post-processing?
> >
> >
On Mon, Feb 27, 2023 at 1:17 PM nikhil raj wrote:
>
> The same query is executed outside the function its working properly
> means just the qurey from drop temp table to insert but when i keep the
> query inside a function its through me the error an after temp ra how
> creation only i am using
Hi Tom,
The same query is executed outside the function its working properly
means just the qurey from drop temp table to insert but when i keep the
query inside a function its through me the error an after temp ra how
creation only i am using the select statment still its not exectuing the
creat
nikhil raj writes:
> This is the Function I have created successfully but while executing it
> throughs an error temp table doesn't exist.
You won't be able to do it like that in a SQL-language function, because
the whole function body is parsed and parse-analyzed in one go. So the
later query r
On 2/27/23 11:52 AM, nikhil raj wrote:
Reply to list also
Ccing list.
HI Adrian,
Yes, I have tried it through the same error.
Orion_db=> select api."post_publish_Roster"()
Orion_db -> ;
ERROR: relation "roster_table" does not exist
LINE 94: ... interval '1 day')::date as d_date F
On 2/27/23 11:10 AM, nikhil raj wrote:
HI Team,
This is the Function I have created successfully but while executing it
throughs an error temp table doesn't exist.
But the same when I execute it not inside the function from **drop temp
table to end insert select ** it work fine
Please can
On 2/27/23 11:34 AM, Ron wrote:
Is there any direct way in Postgresql to get rid of the frankly
anti-useful junk at the end of each line (which also infects
pg_stat_activity.query), or must I resort to sed post-processing?
Or
\ef foo
test=# select pg_get_functiondef(oid)
test-# from pg_p
On 2/27/23 11:34 AM, Ron wrote:
Is there any direct way in Postgresql to get rid of the frankly
anti-useful junk at the end of each line (which also infects
pg_stat_activity.query), or must I resort to sed post-processing?
\pset format unaligned
test=# select pg_get_functiondef(oid)
test-#
Is there any direct way in Postgresql to get rid of the frankly anti-useful
junk at the end of each line (which also infects pg_stat_activity.query), or
must I resort to sed post-processing?
test=# select pg_get_functiondef(oid)
test-# from pg_proc
test-# where proname = 'foo';
HI Team,
This is the Function I have created successfully but while executing it
throughs an error temp table doesn't exist.
But the same when I execute it not inside the function from **drop temp
table to end insert select ** it work fine
Please can any one help me why in the function i am not
1) i downloaded both versions using apt-get install postgres
2) i will check the tablespace and log files tomorrow, i don't have access
to the workstation right now.
po 27. 2. 2023 o 18:44 Adrian Klaver napísal(a):
> On 2/27/23 09:10, Adrian Klaver wrote:
> > On 2/27/23 09:05, Dávid Suchan wrote
I used "pg_upgradecluster 9.6 main", all commands are in my first message:
I freshly installed pg 14 -> ran pg_dropcluster 14 main --stop -> and then
upgraded using pg_upgradecluster 9.6 main.
po 27. 2. 2023 o 18:40 Laurenz Albe napísal(a):
> On Mon, 2023-02-27 at 18:18 +0100, Dávid Suchan wrote
On 2/27/23 09:10, Adrian Klaver wrote:
On 2/27/23 09:05, Dávid Suchan wrote:
Please use Reply All
Ccing list
My bad,
\l+ lists databases and their respective sizes- I used that and also
pg_size_pretty(), the result size was the same - before it was 20gb
for the biggest db, after it was 700mb
On Mon, 2023-02-27 at 18:18 +0100, Dávid Suchan wrote:
> I did not use the -k --link argument while upgrading as that I presume does
> not copy the data
It would be great if you shared the exact command line you used.
The man page of "pg_upgradecluster" says:
-m, --method=dump|upgrade|link|cl
I did not use the -k --link argument while upgrading as that I presume does
not copy the data
Dňa po 27. 2. 2023, 18:10 Adrian Klaver
napísal(a):
> On 2/27/23 09:05, Dávid Suchan wrote:
>
> Please use Reply All
> Ccing list
>
> > My bad,
> > \l+ lists databases and their respective sizes- I used
On 2/27/23 09:05, Dávid Suchan wrote:
Please use Reply All
Ccing list
My bad,
\l+ lists databases and their respective sizes- I used that and also
pg_size_pretty(), the result size was the same - before it was 20gb for
the biggest db, after it was 700mb.
I counted rows before the upgrade in o
On 2/27/23 08:49, Dávid Suchan wrote:
Reply to list
Ccing list for real this time.
1) I used \l+ in psql and then counted rows- millions were missing
\l lists databases.
Are you saying there are millions of database?
Otherwise what rows where you counting?
3) nothing at all, everything wa
On 2/27/23 08:48, Tom Lane wrote:
Adrian Klaver writes:
On 2/27/23 08:36, Tom Lane wrote:
If it was based on something like "du", perhaps the measurement
was fooled by the fact that most of the data files will be hard-linked
between the old and new clusters.
Does that happen without the --l
Adrian Klaver writes:
> On 2/27/23 08:36, Tom Lane wrote:
>> If it was based on something like "du", perhaps the measurement
>> was fooled by the fact that most of the data files will be hard-linked
>> between the old and new clusters.
> Does that happen without the --link option?
No, but the OP
On 2/27/23 08:36, Tom Lane wrote:
Adrian Klaver writes:
On 2/27/23 07:44, Dávid Suchan wrote:
After a successful prompt finished, I checked the database and the size
went from originally 20gb (in 9.6) to 700~ mb (in 14) while the disk
space available shrank by about 2gb meaning that there is s
Adrian Klaver writes:
> On 2/27/23 07:44, Dávid Suchan wrote:
>> After a successful prompt finished, I checked the database and the size
>> went from originally 20gb (in 9.6) to 700~ mb (in 14) while the disk
>> space available shrank by about 2gb meaning that there is still the 20gb
>> of data
On 2/27/23 07:44, Dávid Suchan wrote:
Hello, I tried upgrading pg db from version 9.6 to 14 by using
pg_upgradecluster command. I freshly installed pg 14 -> ran
pg_dropcluster 14 main --stop -> and then upgraded using
pg_upgradecluster 9.6 main.
After a successful prompt finished, I checked the
Erik Wienhold writes:
>> On 27/02/2023 13:13 CET Laurenz Albe wrote:
>> I'd be curious to know how the customer managed to do that.
>> Perhaps there is a loophole in PostgreSQL that needs to be fixed.
> Another reason to prefer jsonb over json to reject such inputs right away.
> The documentatio
Hello, I tried upgrading pg db from version 9.6 to 14 by using
pg_upgradecluster command. I freshly installed pg 14 -> ran pg_dropcluster
14 main --stop -> and then upgraded using pg_upgradecluster 9.6 main.
After a successful prompt finished, I checked the database and the size
went from originall
> On 27/02/2023 13:13 CET Laurenz Albe wrote:
>
> I'd be curious to know how the customer managed to do that.
> Perhaps there is a loophole in PostgreSQL that needs to be fixed.
Probably via some data access layer and not directly via Postgres. It's easy
to reproduce with psycopg:
impor
On 2/27/23 05:53, celati Laurent wrote:
Good morning,
I am new to Postgresql. I have 50 tables into a "ign" schema (schema other
than public).
I would like for each of these 50 tables:
- Add a prefix to the name of the table: "IGN_bdTopo_"
- Add a suffix to the table name: "_V1"
- create a ne
On Mon, 2023-02-27 at 03:52 +, Neethu P wrote:
> Is it possible to access the pg_ddl_command using C function? In the shared
> links,
> I couldn't find one. Can you please share an example for the same?
No. You could hire a professional.
Yours,
Laurenz Albe
On Mon, 2023-02-27 at 06:28 +, Jan Bilek wrote:
> Our customer was able to sneak in an Unicode data into a column of a JSON
> Type and now that record fails on select.
> Would you be able to suggest any way out of this? E.g. finding infringing
> row, updating its data ... ?
I'd be curious to
Good morning,
I am new to Postgresql. I have 50 tables into a "ign" schema (schema other
than public).
I would like for each of these 50 tables:
- Add a prefix to the name of the table: "IGN_bdTopo_"
- Add a suffix to the table name: "_V1"
- create a new "date" column of date type. And populate th
Laurenz Albe, you are right, thank you; actually, it depends (of course) on the
time zone:
# With GMT (no Daylight Saving Time):
SHOW timezone ;
TimeZone
--
GMT
SELECT timestamp with time zone '2022-03-29 12:00:00' - timestamp with time
zone '2022-03-26 12:00:00' ;
?column?
--
Does PostgreSQL take into account daylight saving time in its calendar?
For the last summer hour of the spring (Daylight Saving Time), on Sunday March
27, 2022:
SELECT to_timestamp('20220329 00:00:00','mmdd hh24:mi:ss') -
to_timestamp('20220320 00:00:00','mmdd hh24:mi:ss') intervalle ;
On Mon, 2023-02-27 at 07:26 +, PALAYRET Jacques wrote:
> # An interval in " years months ... seconds " given in seconds by
> EXTRACT(EPOCH ...) transtyped into INTERVAL :
> SELECT (EXTRACT(EPOCH FROM ('3 years 2 months 1 day 10 hours 11 minutes 12
> seconds'::interval) ) || ' seconds')::inter
42 matches
Mail list logo
