Re: Order by in a sub query when aggregating the main query

Understood, thanks for the explanation. I'll work on updating the queries used by sqlalchemy to do array_agg(x order by x) instead of the order by in the subquery. > I think that right now that'd > incur additional sorting overhead, which is annoying. But work is > ongoing to recognize when the i

Re: Order by in a sub query when aggregating the main query

Federico writes: > A basic example of the type of query in question is the following (see > below for the actual query): > select w, array_agg(x) > from ( > select v, v / 10 as w > from pg_catalog.generate_series(25, 0, -1) as t(v) > order by v > ) as t(x) > group by w > This

Order by in a sub query when aggregating the main query

Hi all, I have a question related to the order by clause used in a subquery of a main query that uses one or more aggregation functions with a group by. A basic example of the type of query in question is the following (see below for the actual query): select w, array_agg(x) from ( select

Re: problem with on conflict / do update using psql 14.4

On 9/24/22 09:04, Barry Kimelman wrote: On Sat, Sep 24, 2022 at 10:56 AM Christophe Pettus > wrote: > On Sep 24, 2022, at 08:49, Barry Kimelman mailto:blkimel...@gmail.com>> wrote: > I thought the whole point of ON CONFLICT DO UPDATE was so that you co

Re: problem with on conflict / do update using psql 14.4

On Sat, Sep 24, 2022 at 8:49 AM Barry Kimelman wrote: > > I thought the whole point of ON CONFLICT DO UPDATE was so that you could > modify the data so that it would be inserted > Nope, the words "DO UPDATE" mean "DO an UPDATE command instead of failing for the CONFLICTing INSERT command". As m

Re: problem with on conflict / do update using psql 14.4

> On Sep 24, 2022, at 09:04, Barry Kimelman wrote: > Unfortunately, that is exactly what I need to do, I need to modify the data > on the 2nd request so that it also gets inserted. The best approach is to optimistically insert the row, catch the error if there's a conflict, and modify the da

Re: problem with on conflict / do update using psql 14.4

On Sat, Sep 24, 2022 at 10:56 AM Christophe Pettus wrote: > > > > On Sep 24, 2022, at 08:49, Barry Kimelman wrote: > > I thought the whole point of ON CONFLICT DO UPDATE was so that you could > modify the data so that it would be inserted > > ON CONFLICT DO UPDATE allows you to modify the existi

Re: problem with on conflict / do update using psql 14.4

> On Sep 24, 2022, at 08:49, Barry Kimelman wrote: > I thought the whole point of ON CONFLICT DO UPDATE was so that you could > modify the data so that it would be inserted ON CONFLICT DO UPDATE allows you to modify the existing row that conflicted with the row being inserted, but it won't a

Re: problem with on conflict / do update using psql 14.4

On Sat, Sep 24, 2022 at 10:44 AM Christophe Pettus wrote: > > > > On Sep 24, 2022, at 08:29, Barry Kimelman wrote: > > Thanks for the response. When I ran the INSERT with your suggested > change I got an error message telling me > > "column reference 'company_name' is ambiguous" > > As previousl

Re: problem with on conflict / do update using psql 14.4

> On Sep 24, 2022, at 08:29, Barry Kimelman wrote: > Thanks for the response. When I ran the INSERT with your suggested change I > got an error message telling me > "column reference 'company_name' is ambiguous" As previously noted, you'll need to do both: add "excluded." to qualify the colu

Re: problem with on conflict / do update using psql 14.4

On Sat, Sep 24, 2022 at 9:47 AM Christophe Pettus wrote: > > > > On Sep 24, 2022, at 07:29, Barry Kimelman wrote: > > > > CREATE UNIQUE INDEX my_companies_company_name_unique ON > my_companies(company_name) WHERE delete_timestamp IS NULL; > > The issue here is that the unique index is partial (i

Re: problem with on conflict / do update using psql 14.4

> On Sep 24, 2022, at 07:29, Barry Kimelman wrote: > > CREATE UNIQUE INDEX my_companies_company_name_unique ON > my_companies(company_name) WHERE delete_timestamp IS NULL; The issue here is that the unique index is partial (it has a WHERE clause). In order to use that as an arbiter, you ne

Re: problem with on conflict / do update using psql 14.4

On Fri, Sep 23, 2022 at 4:42 PM Steve Baldwin wrote: > You need to prefix the rhs column(s) with 'excluded.'. For example: > > on conflict (company_name) do update set company_name = > concat(excluded.company_name,'++',excluded.string_company_id); > > On Sat, Sep 24, 2022 at 7:28 AM Barry Kimelma