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 1t1D7N-00FCcD-Ak for pgsql-sql@arkaria.postgresql.org; Wed, 16 Oct 2024 23:07:49 +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 1t1D7L-00BzVo-47 for pgsql-sql@arkaria.postgresql.org; Wed, 16 Oct 2024 23:07:47 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1t1D7K-00BzVW-JF for pgsql-sql@lists.postgresql.org; Wed, 16 Oct 2024 23:07:47 +0000 Received: from mx0b-0039f802.pphosted.com ([205.220.176.45]) by makus.postgresql.org with esmtps (TLS1.2) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1t1D7G-001GBe-TD for pgsql-sql@lists.postgresql.org; Wed, 16 Oct 2024 23:07: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 49GGtGms002734 for ; Wed, 16 Oct 2024 16:07:41 -0700 Received: from mail-yw1-f198.google.com (mail-yw1-f198.google.com [209.85.128.198]) by mx0b-0039f802.pphosted.com (PPS) with ESMTPS id 42a3bnh7nu-1 (version=TLSv1.2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128 verify=NOT) for ; Wed, 16 Oct 2024 16:07:41 -0700 (PDT) Received: by mail-yw1-f198.google.com with SMTP id 00721157ae682-6e31d9c8efcso9853527b3.0 for ; Wed, 16 Oct 2024 16:07:41 -0700 (PDT) X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1729120061; x=1729724861; h=cc: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=aKddCm3+36cpnt4J/Wb9ym1Cp9blM14t7tICfhOYdNU=; b=GI350PXzBHiOuayi3F0l++o0qaICH76ZaS17or7+yPqNeAetYI+QSp63oTTMbxTs5N 1HKbkSph8HiVn/bAFggYnI3R3a9QccaoVcDM5QBuLjJ2K5C/ws6wzGacBT+YuyYEGn0o 9MUgOYsKpa0F32u5roR6xbBaSmBPqtC4c0XjEU0zFu3bAfvQxVcOod+11FwhjY4nPhKJ htFpZnDQ89iChgXuzN8NR8/hkVfyy7sKcdlQMNcX/q49KTkD13FV9mhcWN5LPAahVG13 Yfhb/eKX/W3WqFjY0yev66LJ2skxN8kdnE0veIzh4D3Qf+Ewvpvrv8iYpDz30GENbXNZ r9Xg== X-Gm-Message-State: AOJu0YzQWXdcjMfIH7jGz4kF3STI09QPP9iuc5XvD+R3CbZKYzT5Oc2q GBhVrxYIUgFttLzAj6uTE3/L0Wb1ZkYEBCp1iXq+kC23g4ioZ8w5J+AqdA4+XgBSicMs5ObPwFK FollFYbFT5C/qMrIPVUQFM1cCTJJQk8MeOIAOiM1NhujuVIRtG/Y2lA9l4YPB56HhE659h9m3UQ VcE7dq0bJkzVQE7Y4unuCfnaiI2BfUMrRyvRFoblArk7ksgck418jvz+rxsfIoxWRU X-Received: by 2002:a05:690c:f11:b0:6e3:8562:fd4 with SMTP id 00721157ae682-6e3856214f9mr132757647b3.8.1729120060947; Wed, 16 Oct 2024 16:07:40 -0700 (PDT) X-Google-Smtp-Source: AGHT+IEVrqDOmtW65XqGpHwDFKkgMtlTEN4FHo84Lp7BalL+9Lpx8fsd/p8l6BX8+3BkkDhY/KYRYeJRQ6GTH/bORes= X-Received: by 2002:a05:690c:f11:b0:6e3:8562:fd4 with SMTP id 00721157ae682-6e3856214f9mr132757397b3.8.1729120060468; Wed, 16 Oct 2024 16:07:40 -0700 (PDT) MIME-Version: 1.0 References: <769A131E-D425-4BEA-B48E-E9E17F53DAD2@unochapeco.edu.br> In-Reply-To: <769A131E-D425-4BEA-B48E-E9E17F53DAD2@unochapeco.edu.br> From: Sam Stearns Date: Wed, 16 Oct 2024 16:07:29 -0700 Message-ID: Subject: Re: Postgres View DDL To: William Alves Da Silva Cc: pgsql-sql Content-Type: multipart/alternative; boundary="000000000000fa90720624a022e1" 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 --000000000000fa90720624a022e1 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Just adopting William's advice and changing to this: (coalesce(REGEXP_REPLACE(broker_mc::text, '[^0-9]+', '', 'g')::numeric, 0))= , (coalesce(REGEXP_REPLACE(carrier_mc::text, '[^0-9]+', '', 'g')::numeric, 0)), (coalesce(REGEXP_REPLACE(freight_forwarder_mc::text, '[^0-9]+', '', 'g')::numeric, 0)), has resolved the problem. Thank you, William and David! Learning a lot here. Appreciate all the help. Sam On Wed, Oct 16, 2024 at 3:04=E2=80=AFPM William Alves Da Silva < william_silva@unochapeco.edu.br> wrote: > Hello Sam. > > I think you want this: > > SELECT COALESCE(regexp_replace('abc12345'::TEXT, '[^0-9]+', '', > 'g')::NUMERIC, 0); > > The coalesce need the same type from origin field, or you cast the result > from regex to interger/numeric etc, or you use cast =E2=80=980=E2=80=99 i= nstead of 0; > > Regards, > William Alves. > > On 16 Oct 2024, at 18:42, Sam Stearns wrote: > > 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 = wrote: > >> 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#FUNCT= IONS-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] >> >> > > > -- > > *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] --000000000000fa90720624a022e1 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Just adopting William's advice and changing to this:
(coalesce(REGEXP_REPLACE(broker_mc::text, '[^0-9]+= 9;, '', 'g')::numeric, 0)),
(coalesce(REGEXP_REPLACE(car= rier_mc::text, '[^0-9]+', '', 'g')::numeric, 0)),(coalesce(REGEXP_REPLACE(freight_forwarder_mc::text, '[^0-9]+', &= #39;', 'g')::numeric, 0)),

has res= olved the problem.=C2=A0 Thank you, William and David!=C2=A0 Learning a lot= here.=C2=A0 Appreciate all the help.

Sam


On Wed, Oct 16, 2024 at 3:04=E2=80=AFPM William Alves Da Silva = <william_silva@unocha= peco.edu.br> wrote:
Hello Sam.

I think you want this:
<= div>

SELECT = COALESCE(regexp_replace('abc12345'::TEXT,=C2=A0 '[^0-9]+', = '', 'g')::NUMERIC, 0);


The coa= lesce need the same type from origin field, or you cast the result from reg= ex to interger/numeric etc, or you use cast =E2=80=980=E2=80=99 instead of = 0;

Regards,
William Alves.

On 16 Oct 2024, at 18:42, Sam Stearns = <sam.stearns@da= t.com> wrote:

Tried changing to this:=

=C2=A0 =C2=A0 (coalesce(REGEXP_REPLACE(broker_mc::numer= ic, '[^0-9]+', '', 'g'), 0)),
=C2=A0 =C2=A0 (coa= lesce(REGEXP_REPLACE(carrier_mc::numeric, '[^0-9]+', '', &#= 39;g'), 0)),
=C2=A0 =C2=A0 (coalesce(REGEXP_REPLACE(freight_forwarde= r_mc::numeric, '[^0-9]+', '', 'g'), 0)),
<= div>
but that throws this error:

ERR= OR: =C2=A0function regexp_replace(numeric, unknown, unknown, unknown) does = not exist
LINE 46: =C2=A0 =C2=A0 (coalesce(REGEXP_REPLACE(broker_mc::num= eric, '[^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 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 <sam.stearns@dat.com> wrote:
Howdy,

I have an Oracle view that's b= een converted for Postgres.=C2=A0 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)),

<= /div>
has been converted for Postgres as:

(coa= lesce(REGEXP_REPLACE(broker_mc, '[^0-9]+', '', 'g')= , 0))::numeric ,
(coalesce(REGEXP_REPLACE(carrier_mc, '[^0-9]+',= '', 'g'), 0))::numeric ,
(coalesce(REGEXP_REPLACE(freig= ht_forwarder_mc, '[^0-9]+', '', 'g'), 0))::numeric = ,

which is throwing the following error:

ERROR: =C2=A0COALESCE types text and integer cannot be matc= hed
LINE 43: ...ce(REGEXP_REPLACE(broker_mc, '[^0-9]+', '= 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 through:


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
<= span style=3D"color:rgb(102,102,102)">Lead Database Administrator
c:=C2=A0971 762 6879=C2=A0|=C2=A0o:=C2=A0503 672 5115=C2=A0|=C2=A0DAT.com

3D"DAT"


--

Samuel Stearns
Lead Database Administrator
c:<= /strong>=C2=A0971 762 6879=C2=A0|=C2=A0o:=C2=A0503 672 5115=C2=A0|=C2=A0DAT.com




--

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

--000000000000fa90720624a022e1--