From: msalais@msym.fr
To: 'ahi' <ahm3d.hisham@gmail.com>
To: 'Tom Lane' <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@lists.postgresql.org
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> (raw)
In-Reply-To: <CADaW2UO8J5TZUfCk5YWjMOabXTu3QO=hbFA4YC9Zc60wDKT8Yg@mail.gmail.com>
References: <CADaW2UPo9tBmNVCiabhYaMG9qQ7Xk6-z4mwso44OqPntoYSsRQ@mail.gmail.com>
<2307636.1680792630@sss.pgh.pa.us>
<CADaW2UO8J5TZUfCk5YWjMOabXTu3QO=hbFA4YC9Zc60wDKT8Yg@mail.gmail.com>
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…
Best regards
Michel SALAIS
Consultant Oracle, PostgreSQL
De : ahi <ahm3d.hisham@gmail.com>
Envoyé : vendredi 7 avril 2023 09:09
À : 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 PM Tom Lane <tgl@sss.pgh.pa.us <mailto:tgl@sss.pgh.pa.us> > wrote:
ahi <ahm3d.hisham@gmail.com <mailto: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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: msalais@msym.fr, ahm3d.hisham@gmail.com, tgl@sss.pgh.pa.us, pgsql-performance@lists.postgresql.org
Subject: RE: Query unable to utilize index without typecast to fixed length character
In-Reply-To: <006701d96a43$c48edb70$4dac9250$@msym.fr>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox