Re: MERGE and parsing with prepared statements

2022-09-09 Thread Alvaro Herrera
On 2022-Aug-12, Simon Riggs wrote: > Sorry, but I disagree with this chunk in the latest commit, > specifically, changing the MATCHED from after to before the NOT > MATCHED clause. > > The whole point of the second example was to demonstrate that the > order of the MATCHED/NOT MATCHED clauses mad

Re: MERGE and parsing with prepared statements

2022-08-19 Thread Justin Pryzby
On Fri, Aug 12, 2022 at 01:53:25PM +0200, Alvaro Herrera wrote: > On 2022-Aug-12, Simon Riggs wrote: > > > Sorry, but I disagree with this chunk in the latest commit, > > specifically, changing the MATCHED from after to before the NOT > > MATCHED clause. 3d895bc84 also moved a semicolon into the

Re: MERGE and parsing with prepared statements

2022-08-12 Thread Alvaro Herrera
On 2022-Aug-12, Simon Riggs wrote: > Sorry, but I disagree with this chunk in the latest commit, > specifically, changing the MATCHED from after to before the NOT > MATCHED clause. > > The whole point of the second example was to demonstrate that the > order of the MATCHED/NOT MATCHED clauses mad

Re: MERGE and parsing with prepared statements

2022-08-12 Thread Simon Riggs
On Fri, 12 Aug 2022 at 12:20, Alvaro Herrera wrote: > On 2022-Jul-18, Justin Pryzby wrote: > > > On Fri, Jul 15, 2022 at 03:43:41PM -0500, Justin Pryzby wrote: > > > Should that sentence be removed from MERGE ? > > > > Also, I think these examples should be more similar. > > Agreed, done. Sorry,

Re: MERGE and parsing with prepared statements

2022-08-12 Thread Alvaro Herrera
On 2022-Jul-15, Justin Pryzby wrote: > Should that sentence be removed from MERGE ? Removed On 2022-Jul-18, Justin Pryzby wrote: > On Fri, Jul 15, 2022 at 03:43:41PM -0500, Justin Pryzby wrote: > > Should that sentence be removed from MERGE ? > > Also, I think these examples should be more sim

Re: MERGE and parsing with prepared statements

2022-07-18 Thread Justin Pryzby
On Fri, Jul 15, 2022 at 03:43:41PM -0500, Justin Pryzby wrote: > Should that sentence be removed from MERGE ? Also, I think these examples should be more similar. doc/src/sgml/ref/merge.sgml > > MERGE INTO CustomerAccount CA > USING RecentTransactions T > ON T.CustomerId = CA.CustomerId > WHEN

Re: MERGE and parsing with prepared statements

2022-07-15 Thread Justin Pryzby
On Fri, Jul 15, 2022 at 12:59:34PM -0700, David G. Johnston wrote: > On Fri, Jul 15, 2022 at 12:40 PM Justin Pryzby wrote: > > > > | If the expression for any column is not of the correct data type, automatic > type conversion will be attempted. > > That appears to be copied from the INSERT page

Re: MERGE and parsing with prepared statements

2022-07-15 Thread David G. Johnston
On Fri, Jul 15, 2022 at 12:40 PM Justin Pryzby wrote: > > That appears to be copied from the INSERT page. > What does that mean, if not that data types will be resolved as needed ? > Yep, and the system needs to resolve the type at a point where there is no contextual information and so it choos

Re: MERGE and parsing with prepared statements

2022-07-15 Thread David G. Johnston
On Fri, Jul 15, 2022 at 12:40 PM Justin Pryzby wrote: > On Fri, Jul 15, 2022 at 12:17:51PM -0700, David G. Johnston wrote: > > On Fri, Jul 15, 2022 at 11:40 AM Alvaro Herrera > wrote: > > > On 2022-Jul-15, Justin Pryzby wrote: > > > > > > > It seems a bit odd that it's impossible to use merge wi

Re: MERGE and parsing with prepared statements

2022-07-15 Thread Justin Pryzby
On Fri, Jul 15, 2022 at 12:17:51PM -0700, David G. Johnston wrote: > On Fri, Jul 15, 2022 at 11:40 AM Alvaro Herrera > wrote: > > On 2022-Jul-15, Justin Pryzby wrote: > > > > > It seems a bit odd that it's impossible to use merge with prepared > > > statements > > > without specifically casting

Re: MERGE and parsing with prepared statements

2022-07-15 Thread David G. Johnston
On Fri, Jul 15, 2022 at 11:40 AM Alvaro Herrera wrote: > On 2022-Jul-15, Justin Pryzby wrote: > > > It seems a bit odd that it's impossible to use merge with prepared > statements > > without specifically casting the source types (which I did now to > continue my > > experiment). > > I have no co

Re: MERGE and parsing with prepared statements

2022-07-15 Thread Tom Lane
Alvaro Herrera writes: > On 2022-Jul-15, Justin Pryzby wrote: >> I see now that the same thing can happen with "ON CONFLICT" if used with a >> subselect. >> >> PREPARE p AS INSERT INTO t SELECT a FROM (SELECT $1 AS a)a >> ON CONFLICT (i) DO UPDATE SET i=excluded.i; >> ERROR: column "i" is of typ

Re: MERGE and parsing with prepared statements

2022-07-15 Thread Alvaro Herrera
On 2022-Jul-15, Justin Pryzby wrote: > On Fri, Jul 15, 2022 at 11:25:35AM +0200, Matthias van de Meent wrote: > Thanks for looking into it. Definitely! Thanks, Matthias. > I see now that the same thing can happen with "ON CONFLICT" if used with a > subselect. > > PREPARE p AS INSERT INTO t SE

Re: MERGE and parsing with prepared statements

2022-07-15 Thread Justin Pryzby
On Fri, Jul 15, 2022 at 11:25:35AM +0200, Matthias van de Meent wrote: > On Thu, 14 Jul 2022, 18:26 Justin Pryzby, wrote: > > > > Why is $1 construed to be of type text ? > > The select statement that generates the row type of T `(select $1 CID, > $2 TxV) AS T` does not put type bounds on the inp

Re: MERGE and parsing with prepared statements

2022-07-15 Thread Matthias van de Meent
On Thu, 14 Jul 2022, 18:26 Justin Pryzby, wrote: > > We've used INSERT ON CONFLICT for a few years (with partitions as the target). > That's also combined with prepared statements, for bulk loading. > > I was looking to see if we should use MERGE (probably not, but looking > anyway). > And came a