MIME-Version: 1.0
From: Sam Stearns <sam.stearns@dat.com>
Date: Wed, 16 Oct 2024 14:33:37 -0700
Message-ID: 
 <CAN6TVj=gp68UdbiWT1n5csgC=KsZojuM6vnt3ZgmKNkmkVeCxQ@mail.gmail.com>
Subject: Postgres View DDL
To: pgsql-sql <pgsql-sql@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="00000000000046041006249ed345"
Archived-At: 
 <https://www.postgresql.org/message-id/CAN6TVj%3Dgp68UdbiWT1n5csgC%3DKsZojuM6vnt3ZgmKNkmkVeCxQ%40mail.gmail.com>
Precedence: bulk

--00000000000046041006249ed345
Content-Type: text/plain; charset="UTF-8"

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#FUNCTIONS-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]
<https://www.dat.com/?utm_medium=email&utm_source=DAT_email_signature_link>

--00000000000046041006249ed345
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">Howdy,<div><br></div><div>I have an Oracle view that&#39;s=
 been converted for Postgres.=C2=A0 This block of code in the Oracle view D=
DL:</div><div><br></div><div>TO_NUMBER (NVL (REGEXP_REPLACE (broker_mc, =
9;[^0-9]+&#39;, &#39;&#39;), 0)),<br>TO_NUMBER (NVL (REGEXP_REPLACE (carrie=
r_mc, &#39;[^0-9]+&#39;, &#39;&#39;), 0)),<br>TO_NUMBER (NVL (REGEXP_REPLAC=
E (freight_forwarder_mc, &#39;[^0-9]+&#39;, &#39;&#39;), 0)),</div><div><br=
></div><div>has been converted for Postgres as:</div><div><br></div><div>(c=
oalesce(REGEXP_REPLACE(broker_mc, &#39;[^0-9]+&#39;, &#39;&#39;, &#39;g&#39=
;), 0))::numeric ,<br>(coalesce(REGEXP_REPLACE(carrier_mc, &#39;[^0-9]+&#39=
;, &#39;&#39;, &#39;g&#39;), 0))::numeric ,<br>(coalesce(REGEXP_REPLACE(fre=
ight_forwarder_mc, &#39;[^0-9]+&#39;, &#39;&#39;, &#39;g&#39;), 0))::numeri=
c ,</div><div><br></div><div>which is throwing the following error:</div><d=
iv><br></div><div>ERROR: =C2=A0COALESCE types text and integer cannot be ma=
tched<br>LINE 43: ...ce(REGEXP_REPLACE(broker_mc, &#39;[^0-9]+&#39;, &#39;&=
#39;, &#39;g&#39;), 0))::numer...<br>=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^<br><div>I have been looking through:<br></div><di=
v><br></div><div><a href=3D"https://www.postgresql.org/docs/current/functio=
ns-conditional.html#FUNCTIONS-COALESCE-NVL-IFNULL">https://www.postgresql.o=
rg/docs/current/functions-conditional.html#FUNCTIONS-COALESCE-NVL-IFNULL</a=
><br></div><div><a href=3D"https://www.postgresql.org/docs/current/typeconv=
-union-case.html">https://www.postgresql.org/docs/current/typeconv-union-ca=
se.html</a><br></div><div><br></div><div>but I&#39;m not seeing a way to re=
solve it.=C2=A0 Would anyone be able to advise how to correct this for Post=
gres, please?</div><div><br></div><div>Thanks,</div><div><br></div><div>Sam=
</div><div><br></div><span class=3D"gmail_signature_prefix">-- </span><br><=
div dir=3D"ltr" class=3D"gmail_signature" data-smartmail=3D"gmail_signature=
"><div dir=3D"ltr"><p style=3D"color:rgb(0,0,0);font-family:Gotham,&quot;He=
lvetica Neue&quot;,Helvetica,Arial,sans-serif;font-size:0.8rem;line-height:=
1.2rem"><strong style=3D"font-size:0.9rem;color:rgb(51,51,51)">Samuel Stear=
ns</strong><br><span style=3D"color:rgb(102,102,102)">Lead Database Adminis=
trator<br><strong>c:</strong>=C2=A0971 762 6879=C2=A0<span style=3D"color:r=
gb(147,149,152)">|</span>=C2=A0<strong>o:</strong>=C2=A0503 672 5115=C2=A0<=
span style=3D"color:rgb(147,149,152)">|</span>=C2=A0DAT.com</span></p><div =
style=3D"color:rgb(0,0,0);font-family:&quot;Times New Roman&quot;;font-size=
:medium"><a href=3D"https://www.dat.com/?utm_medium=3Demail&amp;utm_source=
=3DDAT_email_signature_link" target=3D"_blank"><img src=3D"https://www.dat.=
com/wp-content/uploads/media/images/global/emails/signature2/dat_126.png" a=
lt=3D"DAT" width=3D"126" height=3D"54"></a></div></div></div></div></div>

--00000000000046041006249ed345--