The following bug has been logged on the website:
Bug reference: 14920
Logged by: Jorge Solorzano
Email address: jorsol@gmail.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 ');
