Re: query can't merge into table of the other schema

[Guillaume Lelarge <guillaume@xxxxxxxxxxxx>]

Hi,

Le mer. 7 nov. 2018 à 23:52, Pepe TD Vo <pepevo@xxxxxxxxx> a écrit :

thank you for replying, if there's no Merge stating in Postgres, what statement in Postgres should I use?  Update?

INSERT ... ON CONFLICT ... (see https://www.postgresql.org/docs/11/sql-insert.html for details)

I found merge examples on https://wiki.postgresql.org/wiki/MergeTestExamples and the syntax is about the same as Oracle but a function is not working. 

Yeah, but at the top of this page, there is this text: "This was never integrated into PostgreSQL, and requires significant work to be production quality".

If I used update syntax it would long implement for this query.  I'm not a developer and I'm new to Postgres.  Is there a good document you suggest me to use?

The manual would be a good place to start: https://www.postgresql.org/docs/

thank you again.

 

Bach-Nga

No one in this world is pure and perfect.  If you avoid people for their mistakes you will be alone. So judge less, love and forgive more.

To call him a dog hardly seems to do him justice though in as much as he had four legs, a tail, and barked, I admit he was, to all outward appearances. But to those who knew him well, he was a perfect gentleman (Hermione Gingold)

**Live simply **Love generously **Care deeply **Speak kindly.

*** Genuinely rich *** Faithful talent *** Sharing success

On Wednesday, November 7, 2018 5:36 PM, Tom Lane <tgl@xxxxxxxxxxxxx> wrote:

Ron <ronljohnsonjr@xxxxxxxxx> writes:

> On 11/07/2018 09:10 AM, Pepe TD Vo wrote:
>> *ERROR: "cidrdba.sc_date_in" is not a known variable*

> That doesn't look like a "can't merge table from another schema" error.

>> *LINE 13:    MERGE INTO cidrdba.sc_date_in prod*
>> *               ^*
>> *SQL state: 42601*
>> *Character: 352*

> What happens when you run the statement through psql?

It will fail, of course, since there's no MERGE statement in Postgres.

I think the reason for the weird error is that the plpgsql scanner is
seeing the "INTO cidrdba.sc_date_in" part and trying to process that
as an "INTO plpgsql-variable" clause, before it's fed the rest of the
statement to the core parser, which is what would notice that MERGE
isn't a known command.  Maybe we could improve matters by reordering
that processing, but it might be a lot of work for a small benefit.

            regards, tom lane

--

Guillaume.