Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1plCpz-0001Be-5v for pgsql-performance@arkaria.postgresql.org; Sat, 08 Apr 2023 17:58:55 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1plCpx-0008UD-Ml for pgsql-performance@arkaria.postgresql.org; Sat, 08 Apr 2023 17:58:53 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1plCpx-0008U4-Bc for pgsql-performance@lists.postgresql.org; Sat, 08 Apr 2023 17:58:53 +0000 Received: from mout.kundenserver.de ([212.227.126.133]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1plCpu-001g35-2d for pgsql-performance@lists.postgresql.org; Sat, 08 Apr 2023 17:58:52 +0000 Received: from msim1 ([78.197.128.121]) by mrelayeu.kundenserver.de (mreue012 [212.227.15.168]) with ESMTPSA (Nemesis) id 1M3mHT-1plTgS364B-000vJe; Sat, 08 Apr 2023 19:58:47 +0200 From: To: "'ahi'" , "'Tom Lane'" Cc: References: <2307636.1680792630@sss.pgh.pa.us> In-Reply-To: Subject: RE: Query unable to utilize index without typecast to fixed length character Date: Sat, 8 Apr 2023 19:58:46 +0200 Message-ID: <006701d96a43$c48edb70$4dac9250$@msym.fr> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0068_01D96A54.881847B0" X-Mailer: Microsoft Outlook 16.0 Thread-Index: AQKl54ZohdxHNj2KRykkqx/yhFbC5gHR5upLAtfUnp+tY2iaEA== Content-Language: fr X-Provags-ID: V03:K1:k7zrzyoJvAVfvAkSZukQyYl//SJ8iAtbSd1Il3PUestY+1er6FU rG0V/8qvou5LsftIW0Fr6kuxFROhODlUwSgWHkDjnsXZobr2rQS89K3dmYqBvjkfr2KJjEZ LMt13Y5m1dd8XmPP7sq7Zv8emN0VwY5ESJGo7nEI/41cbMoZ1xiAll+xdcuc4OdLr45h/T1 UuhkXr1hwIfGA0ATEoZsw== X-Spam-Flag: NO UI-OutboundReport: notjunk:1;M01:P0:OaQY1ZfJqvk=;ER7ws0il4e4em86BQK1Rl2N2/JT k4Axtwb0tHNxIGvLmrlF1WqDWhHRZu5BDvNPdd29cQQJ9KgcsXldVd8iBpFeHAvWKkW/ltZjq lsaOHALplpUqKnk/ho6Ky6DluSbic6wS5Qe5onP+vuqfZALZyF4BjzaIS5Gpmkl8psD/121Tj hvtTYn12cu7kbV8yyo2Fs8HiVDdSc02sasjVt+py+cfzp/SLNcIcdlpFZei+ASj399+O9f9T6 4ifJpvABsMFtQ/xhBlDKKXqptuNP1EM86I65sq6T2NqfLQmzkuqCHGKanOjtj+Ol4M+Ik4IuA rTJZhkQPl/z7v4fH6zKyaOSg6wrQPQsv8PvjookoxaMb5zvk+zPaYow2K0z5Y7bA5V/+WNsJK 7uU0ACINYW4G+jlMStSjHOc1MYFv/G1leM+oBSWMun3E6C3wyHsC4bnwTvCiX13zfyZNSJBcK 3qPuUF+SsFyuW6dlhpVNAN/YQBz9yM6nxpxznIqNVZPO4LfrwZZQA2smkEuGngbiHmyx53LjW nH5bD/4y06Fde51JTQ6eTLwJ7DBr1yz4WIW3RvmkZzcefEI6BQTG7hFG86kdvUYf15FhQBo3C mi83E2kB9ZmlVeLGTMabIBQVTeDdcS64GYcUQEgxBaYuojF5I9hXIVUbfcK/lLtHSINrvzMzA 5S6Kk8deTaFYWBJflOL3xYiIQW1b0KGmjfkM8S9sYQ== List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multipart message in MIME format. ------=_NextPart_000_0068_01D96A54.881847B0 Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: quoted-printable Hi, =20 Your error is the use of quotes around the constant numeric value! You should not use it because that means then that it is a character = constant causing an implicit conversion.=20 We must consider any implicit conversion in our queries as a potential = problem and we must absolutely avoid using implicit conversions=E2=80=A6 =20 Best regards =20 Michel SALAIS Consultant Oracle, PostgreSQL De : ahi =20 Envoy=C3=A9 : vendredi 7 avril 2023 09:09 =C3=80 : Tom Lane Cc : pgsql-performance@lists.postgresql.org Objet : Re: Query unable to utilize index without typecast to fixed = length character =20 You are right we should move from character(N) to text, however the = explicit typecast is also required for the numeric column not just the = character one =20 On Thu, Apr 6, 2023 at 4:50=E2=80=AFPM Tom Lane > wrote: ahi > writes: > CREATE TABLE public.marketplace_sale ( > log_index integer NOT NULL, > created_at timestamp with time zone DEFAULT now() NOT NULL, > updated_at timestamp with time zone DEFAULT now() NOT NULL, > block_timestamp timestamp with time zone NOT NULL, > block bigint NOT NULL, > contract_address character(42) NOT NULL, > buyer_address character(42) NOT NULL, > seller_address character(42) NOT NULL, > transaction_hash character(66) NOT NULL, > quantity numeric NOT NULL, > token_id numeric NOT NULL, ... Type character(N) is a hangover from the days of punched cards. Don't use it. It has weird semantics concerning trailing spaces, which are almost never the behavior you actually want, and cause interoperability issues with type text. (Text is Postgres' native string type, meaning that unlabeled string constants will tend to get resolved to that.) regards, tom lane ------=_NextPart_000_0068_01D96A54.881847B0 Content-Type: text/html; charset="utf-8" Content-Transfer-Encoding: quoted-printable

Hi,

 

Your error is the use of quotes = around the constant numeric value!

You should not use it because that = means then that it is a character constant causing an implicit = conversion.

We must consider any = implicit conversion in our queries as a potential problem and we must = absolutely avoid using implicit = conversions=E2=80=A6

 

Best = regards

 

Michel = SALAIS

Consultant Oracle, = PostgreSQL

De : ahi = <ahm3d.hisham@gmail.com>
Envoy=C3=A9 : vendredi 7 = avril 2023 09:09
=C3=80 : Tom Lane = <tgl@sss.pgh.pa.us>
Cc : = pgsql-performance@lists.postgresql.org
Objet : Re: Query = unable to utilize index without typecast to fixed length = character

 

You are = right we should move from character(N) to text, however the explicit = typecast is also required for the numeric column not just the character = one

 

On = Thu, Apr 6, 2023 at 4:50=E2=80=AFPM Tom Lane <tgl@sss.pgh.pa.us> = wrote:

ahi <ahm3d.hisham@gmail.com> writes:
> CREATE = TABLE public.marketplace_sale (
>     log_index = integer NOT NULL,
>     created_at timestamp with = time zone DEFAULT now() NOT NULL,
>     updated_at = timestamp with time zone DEFAULT now() NOT NULL,
>    =  block_timestamp timestamp with time zone NOT NULL,
>  =    block bigint NOT NULL,
>    =  contract_address character(42) NOT NULL,
>    =  buyer_address character(42) NOT NULL,
>    =  seller_address character(42) NOT NULL,
>    =  transaction_hash character(66) NOT NULL,
>    =  quantity numeric NOT NULL,
>     token_id = numeric NOT NULL,
      ...

Type character(N) = is a hangover from the days of punched cards.
Don't use it.  It = has weird semantics concerning trailing spaces,
which are almost = never the behavior you actually want, and cause
interoperability = issues with type text.  (Text is Postgres' native
string type, = meaning that unlabeled string constants will tend to
get resolved to = that.)

                =         regards, tom = lane

------=_NextPart_000_0068_01D96A54.881847B0--