Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1gKWDX-0002Kg-HK for pgsql-admin@arkaria.postgresql.org; Wed, 07 Nov 2018 22:22:31 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1gKWDV-0002DK-Pk for pgsql-admin@arkaria.postgresql.org; Wed, 07 Nov 2018 22:22:29 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1gKWDV-0002BI-Ci for pgsql-admin@lists.postgresql.org; Wed, 07 Nov 2018 22:22:29 +0000 Received: from mail-oi1-x235.google.com ([2607:f8b0:4864:20::235]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1gKWDO-0004i1-Ni for pgsql-admin@lists.postgresql.org; Wed, 07 Nov 2018 22:22:28 +0000 Received: by mail-oi1-x235.google.com with SMTP id x63-v6so11540350oix.9 for ; Wed, 07 Nov 2018 14:22:22 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=subject:to:references:from:message-id:date:user-agent:mime-version :in-reply-to:content-language; bh=DRJpCJgY4klGQCnr/+MzGOt4CcHBYI3QrXoTa5MZNn4=; b=tJhR6HQzKDau+E+cCkyPKc8zl5slt+veFCEzBLwSstmCBp18bs3ZfOV/vtQVsdzLnw MfXpYDjrwKsGfwCTlffBPU9uuGdxZpthWlRiBnvK8fK5md2xIkKXt3vzBeI3SQdIhPPG LDcm1InRTmZgZW4MWEmlcgwhQF8IDTS4ImsfmW1b9gKUB0JTmOMcf+Q6W9Mmuk3mYzbR 3Fj8KRt5Zu90voookGzaD89G8x98VA4mq4VU7eaVvtlE3k3n0QHf8cETF46RcTudzFoi eBG9qFxY7wPGp1mwTHSsbq7J5+OYrDB6TWUVbKbIoQFdtLHmpr4H7UQVa44y92pTQ+Ut KHnA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:subject:to:references:from:message-id:date :user-agent:mime-version:in-reply-to:content-language; bh=DRJpCJgY4klGQCnr/+MzGOt4CcHBYI3QrXoTa5MZNn4=; b=BLtCYzc87vRCwFGt4fI+nN8DBz5oXD8imYbt6s6IEBs8JlFHTPBXji560Swee39oKf dl10/LLaKtbyyiQZzJGHRTm0j8TJyXMQ3hgfZOOtC8vFfU3iEfSC9ce4XVWOkq6Jf8kO hMpG66lqChtyiWtjVK4B2UFEx/dKCne5TxqWbI1uKorzxpR33Yh1FjfVkZwTwSd+GiiB WYFfUR7utMpOeLTChKprY9MeK3HPumbzelbb3REM7lSS9obg3qIuBdyAhF6VRhlhc0xX d7gnOjnRq+LvSrOpcwjbtljtcl+7YntU3GlImILE1NF2tLv73FupPQALaNICE5CXhkv5 tI/g== X-Gm-Message-State: AGRZ1gKsvrNnx7ERMJz/+LanAg0lOh0HUPX7COnsHW0hOEq2ZN9nuHcD ZKLOM/aJMjJb8nF74kFP++70VrRj X-Google-Smtp-Source: AJdET5cHKdoZI6umXPPJR9r0TjTQ1HxKZoOK+AR4O3keogM/tX5yPp090f+PXBGqtqjQsRMShGPdAw== X-Received: by 2002:aca:d8d5:: with SMTP id p204-v6mr1297155oig.154.1541629341381; Wed, 07 Nov 2018 14:22:21 -0800 (PST) Received: from [192.168.1.10] (ip70-171-116-89.no.no.cox.net. [70.171.116.89]) by smtp.googlemail.com with ESMTPSA id q32sm1083921otd.15.2018.11.07.14.22.20 for (version=TLS1_2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Wed, 07 Nov 2018 14:22:20 -0800 (PST) Subject: Re: query can't merge into table of the other schema To: pgsql-admin@lists.postgresql.org References: <1425722754.312953.1541603425493.ref@mail.yahoo.com> <1425722754.312953.1541603425493@mail.yahoo.com> From: Ron Message-ID: Date: Wed, 7 Nov 2018 16:22:19 -0600 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:52.0) Gecko/20100101 Thunderbird/52.9.1 MIME-Version: 1.0 In-Reply-To: <1425722754.312953.1541603425493@mail.yahoo.com> Content-Type: multipart/alternative; boundary="------------8CCCCC195DC282C00C8D41BB" Content-Language: en-US List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multi-part message in MIME format. --------------8CCCCC195DC282C00C8D41BB Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit On 11/07/2018 09:10 AM, Pepe TD Vo wrote: [snip] > > *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? MERGE INTO cidrdba.sc_date_in prod USING (         SELECT Receipt_Number,date_in,                mig_filename,mig_insert_dt,                mig_modified_dt         FROM cidr_staging.STG_Date_In         ORDER by mig_seq       ) stg ON ( prod.receipt_number = stg.receipt_number ) WHEN MATCHED THEN UPDATE SET --   prod.Receipt_Number     = stg.Receipt_Number,    prod.Date_In              = stg.Date_In,    prod.mig_filename         = stg.mig_filename,    --prod.mig_insert_dt      = stg.mig_insert_dt,    --prod.mig_modified_dt    = stg.mig_modified_dt    prod.mig_modified_dt      = sysdate WHEN NOT MATCHED THEN INSERT         (                 prod.Receipt_Number,                 prod.Date_In,                 prod.mig_filename,                 prod.mig_insert_dt,                 prod.mig_modified_dt         ) VALUES (                 stg.Receipt_Number,                 stg.Date_In,                 stg.mig_filename,                 sysdate,                 --stg.mig_insert_dt,                 null                 --stg.mig_modified_dt         ) ; -- Angular momentum makes the world go 'round. --------------8CCCCC195DC282C00C8D41BB Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit On 11/07/2018 09:10 AM, Pepe TD Vo wrote:
[snip]

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?

MERGE INTO cidrdba.sc_date_in prod
USING (
        SELECT Receipt_Number,date_in,
               mig_filename,mig_insert_dt,
               mig_modified_dt
        FROM cidr_staging.STG_Date_In
        ORDER by mig_seq
      ) stg
ON ( prod.receipt_number = stg.receipt_number )
WHEN MATCHED THEN UPDATE SET
--   prod.Receipt_Number     = stg.Receipt_Number,
   prod.Date_In              = stg.Date_In,
   prod.mig_filename         = stg.mig_filename,
   --prod.mig_insert_dt      = stg.mig_insert_dt,
   --prod.mig_modified_dt    = stg.mig_modified_dt
   prod.mig_modified_dt      = sysdate
WHEN NOT MATCHED THEN INSERT
        (
                prod.Receipt_Number,
                prod.Date_In,
                prod.mig_filename,
                prod.mig_insert_dt,
                prod.mig_modified_dt
        ) VALUES (
                stg.Receipt_Number,
                stg.Date_In,
                stg.mig_filename,
                sysdate,
                --stg.mig_insert_dt,
                null
                --stg.mig_modified_dt
        )
;


--
Angular momentum makes the world go 'round.
--------------8CCCCC195DC282C00C8D41BB--