BUG #14920: TEXT binding not works correctly with BPCHAR

From: jorsol(at)gmail(dot)com
To: pgsql-bugs(at)postgresql(dot)org
Cc: jorsol(at)gmail(dot)com
Subject: BUG #14920: TEXT binding not works correctly with BPCHAR
Date: 2017-11-22 00:25:43
Message-ID: 20171122002543.1468.39211@wrigleys.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 14920
Logged by: Jorge Solorzano
Email address: jorsol(at)gmail(dot)com
PostgreSQL version: 10.1
Operating system: Ubuntu 16.04
Description:

TEXT type is the preferred data type for the category String, but I'm having
a hard time using it with bpchar:

The reproducible use case is here:
1. Create a table with a bpchar(3).
2. Insert a tuple with trailing space.
3. If I select the values using varchar, it works fine with and without
trailing space.
4. If I select the values using text, it only works without trailing
space.

This behavior is odd and is not documented (at least I not found it). This
can be a limiting factor to use the text data type for strings instead of
varchar.

------------------------------------------

DROP TABLE IF EXISTS texttable;
CREATE TABLE texttable (
ch bpchar(3), te text, vc varchar(3)
);

PREPARE fooplaninsert (text, text, text) AS
INSERT INTO texttable (ch, te, vc) VALUES ($1, $2, $3);
EXECUTE fooplaninsert('c ', 'c ', 'c ');

-- USING VARCHAR
PREPARE fooplanselectvc (varchar, varchar, varchar) AS
SELECT ch, te, vc FROM texttable WHERE ch=$1 AND te=$2 AND vc=$3;
-- THIS WORKS WITH AND WITHOUT TRAILING SPACE
EXECUTE fooplanselectvc('c ', 'c ', 'c ');
EXECUTE fooplanselectvc('c', 'c ', 'c ');

-- USING TEXT
PREPARE fooplanselecttx (text, text, text) AS
SELECT ch, te, vc FROM texttable WHERE ch=$1 AND te=$2 AND vc=$3;
-- THIS NOT WORKS WITH TRAILING SPACE
EXECUTE fooplanselecttx('c ', 'c ', 'c ');
-- THIS WORKS WITHOUT TRAILING SPACE
EXECUTE fooplanselecttx('c', 'c ', 'c ');

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Tom Lane 2017-11-22 00:49:11 Re: BUG #14920: TEXT binding not works correctly with BPCHAR
Previous Message Peter Geoghegan 2017-11-21 19:00:02 Re: BUG #14917: process hang on create index