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 1t1C8G-00F7hj-GW for pgsql-sql@arkaria.postgresql.org; Wed, 16 Oct 2024 22:04:41 +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 1t1C8E-00BGMu-PE for pgsql-sql@arkaria.postgresql.org; Wed, 16 Oct 2024 22:04:39 +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 1t1C8D-00BGM5-Ao for pgsql-sql@lists.postgresql.org; Wed, 16 Oct 2024 22:04:38 +0000 Received: from mail-pj1-x1036.google.com ([2607:f8b0:4864:20::1036]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1t1C89-001QbX-Hj for pgsql-sql@lists.postgresql.org; Wed, 16 Oct 2024 22:04:36 +0000 Received: by mail-pj1-x1036.google.com with SMTP id 98e67ed59e1d1-2e31977c653so41234a91.0 for ; Wed, 16 Oct 2024 15:04:32 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=unochapeco.edu.br; s=google; t=1729116270; x=1729721070; darn=lists.postgresql.org; h=references:to:cc:in-reply-to:date:subject:mime-version:message-id :from:from:to:cc:subject:date:message-id:reply-to; bh=OMQf4TE/IpnOvjYTDgvU1cmTQs64PBxW7lkU8n2XMY4=; b=RxSykvKcs8rh8+gOmW5R2xpZvP5FIRbJ8QTq104lOU/HCVejZ4x8BRVck/f5TKxwz9 XAAkMvtDWZTa+TFh8jQpSrCKFR1mtXujpxqpUFfj4J11qu371v7bRAieArVXIVLuvGz6 OPbusArbA5xQ1N2GJt/RmPPR1H26ElIYoqe6l/yXPvGdFcG5f6HlR0mQRLy7gNii4Rkm tNWkc3WsPkXQJU3H2iOYnp8eHIxDwiKEfIPF55op1ujpa/UpEq3BgntYCj+NSMEOLXdB 1c0AMsekhq8NgMx3sXrwTDuMZjt/b9COuTmAJeTqAIyQ6BAnyFwQ603sEyeOFF9UYoen +0nQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1729116270; x=1729721070; h=references:to:cc:in-reply-to:date:subject:mime-version:message-id :from:x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=OMQf4TE/IpnOvjYTDgvU1cmTQs64PBxW7lkU8n2XMY4=; b=ZfIW/hJUVKhuziMvymbXpEXhjRXUEA5hL6sk8DubUQz4zwvykYCT4OM05OalMvMO/g jqHLT7Rx3Wgn3Au1cC8po/geE2FrYy9fph9omXc0cu0McQUAbxJdZtgVdG9MBEvU9Spa fo2+dCE/ou6HAzA0mXS2TEG2Jdb2m770Q4ypsAbhjg2CAERV8ie3K9jvpXN54ylp6n1Z KSQw9aPr6N0YqcqwP5HwJiCR7FBNYSgMDCqptMUO18438Rf+6hJbvF6W7BpD7o14nXVI 8lYSnzvPUZ3Jdkq24Sl9qIBddxQeHsY5U3rckhPOBmlh15lbf+Gve7Bdu8iW77ZTmIfo vfVw== X-Gm-Message-State: AOJu0YzbA3r3Tr/JiYx2YnuQS36tC2ni5EsIQpTU+gbKVY9GSEL+VMCX ZETlzdWJwMUZUdqNwd7/gnqvGVZcfc/uGh6RO60Ykg7H/kAJvBi5ERQv0tAuFxDvwB9CestNpPW C X-Google-Smtp-Source: AGHT+IEN2Ll66g1fdz8E6QKLhZH7tbw72c0gENdr7szrsfbTjMyJgRPTWqWGLQ8cfkPJVKByqYTGLw== X-Received: by 2002:a17:902:e74b:b0:20c:bb35:dae5 with SMTP id d9443c01a7336-20d2fe21712mr24762635ad.11.1729116270239; Wed, 16 Oct 2024 15:04:30 -0700 (PDT) Received: from smtpclient.apple ([2804:108c:c89b:2900:6516:8889:9134:75d]) by smtp.gmail.com with ESMTPSA id 98e67ed59e1d1-2e3e090859fsm323001a91.55.2024.10.16.15.04.28 (version=TLS1_2 cipher=ECDHE-ECDSA-AES128-GCM-SHA256 bits=128/128); Wed, 16 Oct 2024 15:04:29 -0700 (PDT) From: William Alves Da Silva Message-Id: <769A131E-D425-4BEA-B48E-E9E17F53DAD2@unochapeco.edu.br> Content-Type: multipart/alternative; boundary="Apple-Mail=_29F3B3CF-7524-46B8-A32B-A21CC111734A" Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3774.300.61.1.2\)) Subject: Re: Postgres View DDL Date: Wed, 16 Oct 2024 19:04:17 -0300 In-Reply-To: Cc: pgsql-sql To: Sam Stearns References: X-Mailer: Apple Mail (2.3774.300.61.1.2) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --Apple-Mail=_29F3B3CF-7524-46B8-A32B-A21CC111734A Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 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 instead of 0; Regards, William Alves. > On 16 Oct 2024, at 18:42, Sam Stearns wrote: >=20 > Tried changing to this: >=20 > (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)), >=20 > but that throws this error: >=20 > 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. >=20 > Sam >=20 > On Wed, Oct 16, 2024 at 2:33=E2=80=AFPM Sam Stearns = > wrote: >> Howdy, >>=20 >> I have an Oracle view that's been converted for Postgres. This block = of code in the Oracle view DDL: >>=20 >> 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)), >>=20 >> has been converted for Postgres as: >>=20 >> (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 , >>=20 >> which is throwing the following error: >>=20 >> 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: >>=20 >> = https://www.postgresql.org/docs/current/functions-conditional.html#FUNCTIO= NS-COALESCE-NVL-IFNULL >> https://www.postgresql.org/docs/current/typeconv-union-case.html >>=20 >> but I'm not seeing a way to resolve it. Would anyone be able to = advise how to correct this for Postgres, please? >>=20 >> Thanks, >>=20 >> Sam >>=20 >> -- >> Samuel Stearns >> Lead Database Administrator >> c: 971 762 6879 | o: 503 672 5115 | DAT.com >>=20 >> = >=20 > -- > Samuel Stearns > Lead Database Administrator > c: 971 762 6879 | o: 503 672 5115 | DAT.com >=20 > = --Apple-Mail=_29F3B3CF-7524-46B8-A32B-A21CC111734A Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8 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 instead of = 0;

Regards,
William = Alves.

On 16 Oct 2024, = at 18:42, Sam Stearns <sam.stearns@dat.com> 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 <sam.stearns@dat.com> = 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:


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: 50= 3 672 5115 | DAT.com

3D"DAT"


--

Samuel = Stearns
Lead Database = Administrator
c: 971 762 6879 | o: 50= 3 672 5115 | DAT.com

3D"DAT"

= --Apple-Mail=_29F3B3CF-7524-46B8-A32B-A21CC111734A--