MIME-Version: 1.0
In-Reply-To: 
 <CAN6TVjkTRYMBEq97tbpLsho1dGxtbJX4T_Gvy_dsZx3U82MEmA@mail.gmail.com>
References: 
 <CAN6TVj=gp68UdbiWT1n5csgC=KsZojuM6vnt3ZgmKNkmkVeCxQ@mail.gmail.com>
 <CAN6TVjkTRYMBEq97tbpLsho1dGxtbJX4T_Gvy_dsZx3U82MEmA@mail.gmail.com>
From: "David G. Johnston" <david.g.johnston@gmail.com>
Date: Wed, 16 Oct 2024 14:47:27 -0700
Message-ID: 
 <CAKFQuwYzdVQ2pdnjD7w2HqTUR8h-Dm6L_J8YXjzYLiu9Cknt0Q@mail.gmail.com>
Subject: Re: Postgres View DDL
To: Sam Stearns <sam.stearns@dat.com>
Cc: pgsql-sql <pgsql-sql@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="0000000000001c577106249f04a2"
Archived-At: 
 <https://www.postgresql.org/message-id/CAKFQuwYzdVQ2pdnjD7w2HqTUR8h-Dm6L_J8YXjzYLiu9Cknt0Q%40mail.gmail.com>
Precedence: bulk

--0000000000001c577106249f04a2
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Wednesday, October 16, 2024, Sam Stearns <sam.stearns@dat.com> wrote:

> Tried changing to this:
>     (coalesce(REGEXP_REPLACE(broker_mc::numeric, '[^0-9]+', '', 'g'), 0))=
,
>

Both parts of the coalesce need to be integer.  Therefore:

regexp_replace(=E2=80=A6)::integer

In short, the output of the text manipulation better be something that can
be cast to integer if you want coalesce to produce an integer.

David J.

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

On Wednesday, October 16, 2024, Sam Stearns &lt;<a href=3D"mailto:sam.stear=
ns@dat.com">sam.stearns@dat.com</a>&gt; wrote:<br><blockquote class=3D"gmai=
l_quote" style=3D"margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left=
:1ex"><div dir=3D"ltr">Tried changing to this:<div>=C2=A0 =C2=A0 (coalesce(=
REGEXP_REPLACE(<wbr>broker_mc::numeric, &#39;[^0-9]+&#39;, &#39;&#39;, &#39=
;g&#39;), 0)),<br></div></div></blockquote><div><br></div><div>Both parts o=
f the coalesce need to be integer.=C2=A0 Therefore:</div><div><br></div><di=
v>regexp_replace(=E2=80=A6)::integer</div><div><br></div><div>In short, the=
 output of the text manipulation better be something that can be cast to in=
teger if you want coalesce to produce an integer.</div><div><br></div><div>=
David J.</div><div>=C2=A0</div>

--0000000000001c577106249f04a2--