On Tue, 18 Apr 2023 at 11:53, John Howroyd wrote:
>
> Sorry, I may have jumped to a conclusion that's not quite correct.
Not at all, thanks for moving this along
> On Mon, 17 Apr 2023 at 23:58, Federico wrote:
>>
>> On Tue, 18 Apr 2023 at 00:21, John Howroyd wrote:
>> > ...
>> >
>> >
Sorry, I may have jumped to a conclusion that's not quite correct.
On Mon, 17 Apr 2023 at 23:58, Federico wrote:
> On Tue, 18 Apr 2023 at 00:21, John Howroyd
> wrote:
> > ...
> >
> > Personally, I didn't see Frederico's comment as anything to do with
> order; just how one could output
On Tue, 18 Apr 2023 at 00:21, John Howroyd wrote:
>
> May I clarify the ideas being discussed so far, perhaps with a view to make a
> relevant proposal. My apologies if I get anything wrong or go too far.
>
> As I understand it the proposal is to supplement the syntax to something like:
>
>
PS: Sorry, I haven't yet thought how this might work with UPDATE or MERGE,
but if I am on the right track with INSERT I'll give this some thought.
On Mon, 17 Apr 2023 at 18:48, John Howroyd wrote:
> May I clarify the ideas being discussed so far, perhaps with a view to
> make a relevant
May I clarify the ideas being discussed so far, perhaps with a view to make
a relevant proposal. My apologies if I get anything wrong or go too far.
As I understand it the proposal is to supplement the syntax to something
like:
INSERT INTO table (a, b, c)
VALUES ((1,2,3), (4,5,6), ...)
WITH
On Fri, Apr 14, 2023, at 11:17 PM, Tom Lane wrote:
> Federico writes:
>> Would something like what was proposed by Mike Bayer be considered?
>
>>> A new token called "tuple_order" or something
>>>
>>> INSERT INTO table (a, b, c) VALUES ((1, 2, 3), (4, 5, 6), ...) RETURNING
>>> table.id,
On Sat, 15 Apr 2023 at 15:40, Tom Lane wrote:
>
> Federico writes:
> > I think the tuple order would not be connected to the values, but be
> > determined by the input order of the rows in the insert. So when using
> > INSERT ... SELECT the tuple_order value would be determined by the
> > output
Federico writes:
> I think the tuple order would not be connected to the values, but be
> determined by the input order of the rows in the insert. So when using
> INSERT ... SELECT the tuple_order value would be determined by the
> output of the select, using the same logic as ROW_NUMBER.
We
On Sat, 15 Apr 2023 at 05:17, Tom Lane wrote:
>
> Federico writes:
> > Would something like what was proposed by Mike Bayer be considered?
>
> >> A new token called "tuple_order" or something
> >>
> >> INSERT INTO table (a, b, c) VALUES ((1, 2, 3), (4, 5, 6), ...) RETURNING
> >> table.id,
On Fri, Apr 14, 2023 at 8:17 PM Tom Lane wrote:
> Federico writes:
> > Would something like what was proposed by Mike Bayer be considered?
>
> >> A new token called "tuple_order" or something
> >>
> >> INSERT INTO table (a, b, c) VALUES ((1, 2, 3), (4, 5, 6), ...)
> RETURNING table.id,
Federico writes:
> Would something like what was proposed by Mike Bayer be considered?
>> A new token called "tuple_order" or something
>>
>> INSERT INTO table (a, b, c) VALUES ((1, 2, 3), (4, 5, 6), ...) RETURNING
>> table.id, inserted.tuple_order
>>
>> tuple_order would be incrementing
On 4/14/23 15:44, Karsten Hilbert wrote:
Am Fri, Apr 14, 2023 at 10:44:19PM +0100 schrieb John Howroyd:
The problem is that SQLAlchemy is an ORM [...]
...
[...] as the majority of the python world will use this ORM for
their database needs.
I wouldn't be so sure on this count ...
+1
Am Fri, Apr 14, 2023 at 10:44:19PM +0100 schrieb John Howroyd:
> The problem is that SQLAlchemy is an ORM [...]
...
> [...] as the majority of the python world will use this ORM for
> their database needs.
I wouldn't be so sure on this count ...
Karsten
--
GPG 40BE 5B0E C98E 1713 AFA6 5BC0
On Fri, Apr 14, 2023 at 12:47 PM Federico wrote:
>
> Would something like what was proposed by Mike Bayer be considered?
>
> > A new token called "tuple_order" or something
> >
> > INSERT INTO table (a, b, c) VALUES ((1, 2, 3), (4, 5, 6), ...)
> RETURNING table.id, inserted.tuple_order
> >
> >
On Fri, Apr 14, 2023 at 2:44 PM John Howroyd
wrote:
> A patch for what? All my testing gives me the same output order as the
> declaration order. Does anyone have an example where this is not the case?
>
> The problem is that SQLAlchemy is an ORM and they need to be sure to match
> records
A patch for what? All my testing gives me the same output order as the
declaration order. Does anyone have an example where this is not the case?
The problem is that SQLAlchemy is an ORM and they need to be sure to match
records from the insert to the relevant code side objects. This needs to
On Fri, 14 Apr 2023 at 21:37, David G. Johnston
wrote:
>
> On Fri, Apr 14, 2023 at 11:42 AM John Howroyd
> wrote:
>>
>> @PostgreSQL: Might I ask if this is still being actively considered or
>> should we repost to another mailing list (perhaps pgsql-hackers or any other
>> you might
On Fri, Apr 14, 2023 at 11:42 AM John Howroyd
wrote:
> @PostgreSQL: Might I ask if this is still being actively considered or
> should we repost to another mailing list (perhaps pgsql-hackers or any
> other you might suggest)?
>
This is the right place for such a discussion. Unless you think
@PostgreSQL: Might I ask if this is still being actively considered or
should we repost to another mailing list (perhaps pgsql-hackers or any
other you might suggest)?
On 2023-04-11 15:45:59 -0600, Rob Sargent wrote:
> > > Can your client retain a hashmap of md5,data pairings, allowing the
> > > lookup on the way back using the returned data and supplied id?
> > >
> > When using unique columns or similar, that's something that is done,
> > but if there are no
On Thu, Apr 13, 2023, at 8:26 AM, John Howroyd wrote:
>
> However, the marshalling is presumably already happening (except any tie to
> ordering to the original declared tuples); otherwise I really don't
> understand how the with syntax (I think first suggested by Adrian Klaver)
> would
A few more thoughts.
My " simply marshal the RETURNING results through and in memory/temporary
table maintaining the order from the SELECT and return these to the client
upon commit" isn't quite right with the "upon commit": that (the commit)
may happen much later with several statements wrapped
here's what would be cool, a new token called "tuple_order" or something
INSERT INTO table (a, b, c) VALUES ((1, 2, 3), (4, 5, 6), ...) RETURNING
table.id, inserted.tuple_order
tuple_order would be incrementing values 1, 2, 3, 4, 5, ... which correlate the
each row delivered by RETURNING to
To clarify, the work around (for SQLAlchemy) does not require the RETURNING
values to be ordered; they will be reordered code side. Longer term
matching the order up would be an added bonus!
On Thu, 13 Apr 2023 at 04:02, John Howroyd wrote:
> The ideal solution would be that the RETURNING
The ideal solution would be that the RETURNING values are in the same order
as the declared rows in the table literal "FROM (VALUES (10, 11, 1), (12,
13, 2)) AS sel(p1, p2, num)". But before any of you jump down my throat
about about sets and a bar to parallel inserts, I think it's a small matter
We do something different for uuids. These are usually created client side
anyway or are very easy to create client side if not and then we also have a
separate sentinel column option.
Overall the whole thing is an optimization that can be turned off for uncommon
cases. We just can't go
On 4/12/23 17:58, Thorsten Glaser wrote:
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?
This for an ORM that will be using a sequence.
bye,
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 Wed, Apr 12, 2023, at 6:24 PM, Thorsten Glaser wrote:
> 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
This would be a nice solution… but the PK could be always generated,
or not even sequential (UUIDs for example). If you’re developing
schema-first the application would not even (need to) know about how
the IDs are generated as it’s the DB that generates them.
AIUI the OP’s an SQLAlchemy
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
On Wed, Apr 12, 2023 at 5:49 PM Adrian Klaver
wrote:
> On 4/12/23 2:35 PM, Kirk Wolak wrote:
> > On Tue, Apr 11, 2023 at 4:38 PM Federico
> >
> > A couple of comments. For the more generic, I prefer RETURNING *
> > you get back all the columns for matching. To me, this solves the
> > problem
On Wed, 12 Apr 2023 at 23:49, Adrian Klaver wrote:
>
> On 4/12/23 2:35 PM, Kirk Wolak wrote:
> > On Tue, Apr 11, 2023 at 4:38 PM Federico
> >
> > A couple of comments. For the more generic, I prefer RETURNING *
> > you get back all the columns for matching. To me, this solves the
> > problem
On 4/12/23 2:35 PM, Kirk Wolak wrote:
On Tue, Apr 11, 2023 at 4:38 PM Federico
A couple of comments. For the more generic, I prefer RETURNING *
you get back all the columns for matching. To me, this solves the
problem in a very generic way.
From what I gather from the conversation
On Tue, Apr 11, 2023 at 4:38 PM Federico wrote:
> Thanks for the ansers
>
> > 2) What would you order by, id or data or both?
>
> by values order, (that incidentally seems to be what PG does)
>
> > with i as (INSERT INTO t(data) VALUES ('a', 1), ('b', 2), ('c', 3)
> > returning id)
> > select
Hi All,
The fundamental question here is:
What does "insert into object(col1, ... , coln) select sel1, ... , seln
from ... order by ord1, ... ordm returning val1, ..., valk" mean?
In particular, what does the "order by" do in "insert ... select ... order
by"? Will this be honoured with parallel
On Wed, 12 Apr 2023 at 11:46, Thorsten Glaser wrote:
>
> 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.)
That's kind of the point for this question, to see if that's correct or not.
I'm not getting every reply in the list but I want to point this out from the
archive version of this thread:
> > 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,
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
On Tue, 11 Apr 2023 at 23:46, Rob Sargent wrote:
>
>
> >> Can your client retain a hashmap of md5,data pairings, allowing the
> >> lookup on the way back using the returned data and supplied id?
> >>
> > When using unique columns or similar, that's something that is done,
> > but if there are no
On Tue, 11 Apr 2023 at 23:44, Thorsten Glaser wrote:
>
> 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
Can your client retain a hashmap of md5,data pairings, allowing the
lookup on the way back using the returned data and supplied id?
When using unique columns or similar, that's something that is done,
but if there are no unique columns in the value no match can be done
reliably with the
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
On Tue, 11 Apr 2023 at 23:31, Rob Sargent wrote:
>
> On 4/11/23 14:37, Federico wrote:
> >
> > The problem here is not having the auto increment id in a particular
> > order, is that there
> > is apparently no correlation with the position of an element in the
> > values clause with the
> > id
On Tue, 11 Apr 2023 at 23:22, Thorsten Glaser wrote:
>
> 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
On 4/11/23 14:37, Federico wrote:
The problem here is not having the auto increment id in a particular
order, is that there
is apparently no correlation with the position of an element in the
values clause with the
id generated. That's the reason for using the sentinel column in the
general
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
On Tue, 11 Apr 2023 at 22:59, Thorsten Glaser wrote:
>
> 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
On Tue, Apr 11, 2023, at 4:22 PM, Tom Lane wrote:
> Federico writes:
> > Searching the archive seems that a using the INSERT SELECT ORDER BY
> > form should be a better solution,
> > so the above insert should be rewritten as
>
> > INSERT INTO t(data)
> > SELECT data FROM (VALUES ('a',
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
Thanks for the ansers
> 2) What would you order by, id or data or both?
by values order, (that incidentally seems to be what PG does)
> with i as (INSERT INTO t(data) VALUES ('a', 1), ('b', 2), ('c', 3)
> returning id)
> select i.id from i order by id;
The problem here is not having the auto
Federico writes:
> Searching the archive seems that a using the INSERT SELECT ORDER BY
> form should be a better solution,
> so the above insert should be rewritten as
> INSERT INTO t(data)
> SELECT data FROM (VALUES ('a', 1), ('b', 2), ('c', 3)) as vv(data,
> num) ORDER BY num
>
On 4/11/23 12:47, Federico wrote:
Hello list,
https://www.sqlite.org/lang_returning.html#limitations_and_caveats
Searching the archive seems that a using the INSERT SELECT ORDER BY
form should be a better solution,
so the above insert should be rewritten as
INSERT INTO t(data)
On 4/11/23 12:47, Federico wrote:
Hello list,
I have a few clarification questions regarding using insert with returning.
The use case is SQLAlchemy development, where the orm wants to insert
a list of rows,
get back the generated ids, defaults, etc, and match the returned values with
the
Hello list,
I have a few clarification questions regarding using insert with returning.
The use case is SQLAlchemy development, where the orm wants to insert
a list of rows,
get back the generated ids, defaults, etc, and match the returned values with
the original list of orm objects.
The
55 matches
Mail list logo
