Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1t1Bn6-00F6O7-9Z for pgsql-sql@arkaria.postgresql.org; Wed, 16 Oct 2024 21:42:48 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1t1Bn4-00Aq98-KG for pgsql-sql@arkaria.postgresql.org; Wed, 16 Oct 2024 21:42:46 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1t1Bn4-00Aq8Z-Aa for pgsql-sql@lists.postgresql.org; Wed, 16 Oct 2024 21:42:46 +0000 Received: from mx0b-0039f802.pphosted.com ([205.220.176.45]) by magus.postgresql.org with esmtps (TLS1.2) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1t1Bn1-001QKL-7G for pgsql-sql@lists.postgresql.org; Wed, 16 Oct 2024 21:42:45 +0000 Received: from pps.filterd (m0209982.ppops.net [127.0.0.1]) by mx0b-0039f802.pphosted.com (8.18.1.2/8.18.1.2) with ESMTP id 49GGpxlu002729 for ; Wed, 16 Oct 2024 14:42:40 -0700 Received: from mail-yw1-f200.google.com (mail-yw1-f200.google.com [209.85.128.200]) by mx0b-0039f802.pphosted.com (PPS) with ESMTPS id 42a3bnh4ft-1 (version=TLSv1.2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128 verify=NOT) for ; Wed, 16 Oct 2024 14:42:40 -0700 (PDT) Received: by mail-yw1-f200.google.com with SMTP id 00721157ae682-6e390b164c7so6790887b3.1 for ; Wed, 16 Oct 2024 14:42:40 -0700 (PDT) X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1729114960; x=1729719760; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=MNEzbTdWjdi30KCppKwBWPCaYWVBZ5PEYRN98zMqgHs=; b=V4pl7siivh2H0POfDTRsAmWSRhllcGb0dGN4OKiLgHKepsaD3i1yVjXK4labg1GC8S vyT1PA1EJgqcnlQZGDLkGk7OATk9YuNkZW4Wje99BwT4t29GXFHA4qOY6MwxWzd7IW4F 3GtqGWLcBfC5oIt5k3nM7HgIYs6phAbxBqHnfq0vbIlg2ubOPUd2yHa+Kgl9//DuUwEV nncEcYSXEkTH3r0xNVj0+7SDkZhD6ytksXFzLdNzlkfh1vNeLp81iwzDAhfZRQfmNVgL ozVSk2H4KMv/fVimRpfS48BVNo51eJ6CwkSC0IrlAyuDOaR342E+UyLQwmNYu33xXAx4 sWEQ== X-Gm-Message-State: AOJu0Yw1R0kxiwBG+lyZQL8Bt95MSX8t3KfaYTI5/dAL9uLiEXRHegj3 XFsq2sOKNm/uuEexr/08t4hPVJv35q6IVhWlkrrr7O5sWQnMcxOuePYoOBM1BFVEBbEc61h5m92 g+CwAcaV3/qe02xaKmBR5hAzawCsN0v50+6I7ixV+GOxfkuhqItlaGKhrrlLq0Wor/n10NI/QiT U6QM8XGdoNAvE/whSWLGwnmVkR92QhFDhkPRXHZLR0b6TPUZKt+87rVvdwbFML0RpobJXNirmgT 10= X-Received: by 2002:a05:690c:3189:b0:6e3:2e4b:ad9a with SMTP id 00721157ae682-6e3479b971emr138145787b3.12.1729114959785; Wed, 16 Oct 2024 14:42:39 -0700 (PDT) X-Google-Smtp-Source: AGHT+IG82uUsIw5TliZgaXJskQMCL6+RY8/a0FC02iCHNSnYHYX+jo/Lt3mWIwC5+u4E1gVocmYXphFcLyMFAq/Uhtg= X-Received: by 2002:a05:690c:3189:b0:6e3:2e4b:ad9a with SMTP id 00721157ae682-6e3479b971emr138145577b3.12.1729114959321; Wed, 16 Oct 2024 14:42:39 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Sam Stearns Date: Wed, 16 Oct 2024 14:42:28 -0700 Message-ID: Subject: Re: Postgres View DDL To: pgsql-sql Content-Type: multipart/alternative; boundary="000000000000ed387206249ef212" X-Proofpoint-Virus-Version: vendor=baseguard engine=ICAP:2.0.293,Aquarius:18.0.1051,Hydra:6.0.680,FMLib:17.12.62.30 definitions=2024-10-16_17,2024-10-16_01,2024-09-30_01 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000ed387206249ef212 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Tried changing to this: (coalesce(REGEXP_REPLACE(broker_mc::numeric, '[^0-9]+', '', 'g'), 0)), (coalesce(REGEXP_REPLACE(carrier_mc::numeric, '[^0-9]+', '', 'g'), 0)), (coalesce(REGEXP_REPLACE(freight_forwarder_mc::numeric, '[^0-9]+', '', 'g'), 0)), but that throws this error: ERROR: function regexp_replace(numeric, unknown, unknown, unknown) does not exist LINE 46: (coalesce(REGEXP_REPLACE(broker_mc::numeric, '[^0-9]+', ... ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. Sam On Wed, Oct 16, 2024 at 2:33=E2=80=AFPM Sam Stearns w= rote: > Howdy, > > I have an Oracle view that's been converted for Postgres. This block of > code in the Oracle view DDL: > > TO_NUMBER (NVL (REGEXP_REPLACE (broker_mc, '[^0-9]+', ''), 0)), > TO_NUMBER (NVL (REGEXP_REPLACE (carrier_mc, '[^0-9]+', ''), 0)), > TO_NUMBER (NVL (REGEXP_REPLACE (freight_forwarder_mc, '[^0-9]+', ''), 0))= , > > has been converted for Postgres as: > > (coalesce(REGEXP_REPLACE(broker_mc, '[^0-9]+', '', 'g'), 0))::numeric , > (coalesce(REGEXP_REPLACE(carrier_mc, '[^0-9]+', '', 'g'), 0))::numeric , > (coalesce(REGEXP_REPLACE(freight_forwarder_mc, '[^0-9]+', '', 'g'), > 0))::numeric , > > which is throwing the following error: > > ERROR: COALESCE types text and integer cannot be matched > LINE 43: ...ce(REGEXP_REPLACE(broker_mc, '[^0-9]+', '', 'g'), 0))::numer.= .. > > ^ > I have been looking through: > > > https://www.postgresql.org/docs/current/functions-conditional.html#FUNCTI= ONS-COALESCE-NVL-IFNULL > https://www.postgresql.org/docs/current/typeconv-union-case.html > > but I'm not seeing a way to resolve it. Would anyone be able to advise > how to correct this for Postgres, please? > > Thanks, > > Sam > > -- > > *Samuel Stearns* > Lead Database Administrator > *c:* 971 762 6879 | *o:* 503 672 5115 | DAT.com > [image: DAT] > > --=20 *Samuel Stearns* Lead Database Administrator *c:* 971 762 6879 | *o:* 503 672 5115 | DAT.com [image: DAT] --000000000000ed387206249ef212 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Tried changing to this:

=C2=A0 =C2=A0 (= coalesce(REGEXP_REPLACE(broker_mc::numeric, '[^0-9]+', '', = 'g'), 0)),
=C2=A0 =C2=A0 (coalesce(REGEXP_REPLACE(carrier_mc::nu= meric, '[^0-9]+', '', 'g'), 0)),
=C2=A0 =C2=A0 (= coalesce(REGEXP_REPLACE(freight_forwarder_mc::numeric, '[^0-9]+', &= #39;', 'g'), 0)),

but that throws = this error:

ERROR: =C2=A0function regexp_replace(n= umeric, unknown, unknown, unknown) does not exist
LINE 46: =C2=A0 =C2=A0= (coalesce(REGEXP_REPLACE(broker_mc::numeric, '[^0-9]+', ...
=C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 ^
HINT: =C2=A0No functi= on matches the given name and argument types. You might need to add explici= t type casts.

Sam

On Wed, Oct 16, 2024 at= 2:33=E2=80=AFPM Sam Stearns <sam= .stearns@dat.com> wrote:
Howdy,

I have an Oracle= view that's been converted for Postgres.=C2=A0 This block of code in t= he Oracle view DDL:

TO_NUMBER (NVL (REGEXP_REPLACE= (broker_mc, '[^0-9]+', ''), 0)),
TO_NUMBER (NVL (REGEXP= _REPLACE (carrier_mc, '[^0-9]+', ''), 0)),
TO_NUMBER (NV= L (REGEXP_REPLACE (freight_forwarder_mc, '[^0-9]+', ''), 0)= ),

has been converted for Postgres as:
<= br>
(coalesce(REGEXP_REPLACE(broker_mc, '[^0-9]+', '&= #39;, 'g'), 0))::numeric ,
(coalesce(REGEXP_REPLACE(carrier_mc, = '[^0-9]+', '', 'g'), 0))::numeric ,
(coalesce(RE= GEXP_REPLACE(freight_forwarder_mc, '[^0-9]+', '', 'g= 9;), 0))::numeric ,

which is throwing the followin= g error:

ERROR: =C2=A0COALESCE types text and inte= ger cannot be matched
LINE 43: ...ce(REGEXP_REPLACE(broker_mc, '[^0-= 9]+', '', 'g'), 0))::numer...
=C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0^
I have been looking thro= ugh:

=

but I'm not seeing a way to resolve it.=C2=A0 Would= anyone be able to advise how to correct this for Postgres, please?

Thanks,

Sam

= --

Samuel Stearns
Le= ad Database Administrator
c:=C2=A0971 762 6879=C2=A0|=C2=A0o:=C2=A0= 503 672 5115=C2=A0|=C2=A0DAT.= com

3D"DAT"
=


--

Samuel Stearns
Lead Database Administrat= or
c:=C2=A0971 762 6879=C2=A0|=C2=A0o:=C2=A0503 672 5115=C2=A0|=C2=A0DAT.com

3D=
--000000000000ed387206249ef212--