Starnge things with big datas...

Hi there,

"PostgreSQL 9.1.2, compiled by Visual C++ build 1500, 64-bit"
catalog version : PG_9.1_201105231

--> the table :

CREATE TABLE T_TEST_TAILLE
(TTT_ID SERIAL NOT NULL PRIMARY KEY,
TTT_DATAFIX CHAR(16),
TTT_DATAVAR VARCHAR(16),
TTT_DATAVARBIG VARCHAR(6000));

--> the inserts :

INSERT INTO T_TEST_TAILLE (TTT_DATAFIX, TTT_DATAVAR, TTT_DATAVARBIG)
SELECT NULL, NULL, NULL
UNION ALL SELECT '', '', ''
UNION ALL SELECT '12345678', '12345678', REPEAT('*', 3000)
UNION ALL SELECT '1234567890123456', '1234567890123456',
REPEAT('*', 6000)

--> the query

SELECT *, pg_column_size(TTT_DATAFIX),
char_length(TTT_DATAFIX),
octet_length(TTT_DATAFIX),
pg_column_size(TTT_DATAVAR),
char_length(TTT_DATAVAR),
octet_length(TTT_DATAFIX),
pg_column_size(TTT_DATAVARBIG),
char_length(TTT_DATAVARBIG),
octet_length(TTT_DATAFIX)
FROM T_TEST_TAILLE;

!!! PROBLEM !!!

--> at this time the 4th row show nothing in ttt_datavarbig column...
it is supposed to retrive the long '********************* ... '
6000 characters !

What happened ?

--> now, with this insert :

WITH RECURSIVE
S AS (SELECT generate_series AS I FROM generate_series(1, 6000)),
T AS (SELECT I, chr(20 + CAST(ceiling(random()*236) AS SMALLINT)) AS C
FROM S),
R AS (SELECT CAST(C AS VARCHAR(6000)) AS CC, C, I
FROM T
WHERE I = 1
UNION ALL
SELECT CAST(CC || T.C AS VARCHAR(6000)),T.C, T.I
FROM T
INNER JOIN R
ON T.I = R.I + 1)
INSERT INTO T_TEST_TAILLE (TTT_DATAVARBIG)
SELECT CC
FROM R
WHERE I = 6000;

One row appear with somtehing like :

"YÓ³Ó¡Àÿ-ÅĀöÕñù:’럙͇§-ƒÁbܺúJÆ+÷+~ð ...

6000 characters... ok !

thanks

--
Frédéric BROUARD - expert SGBDR et SQL - MVP SQL Server - 06 11 86 40 66
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Enseignant Arts & Métiers PACA, ISEN Toulon et CESI/EXIA Aix en Provence
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
*********************** http://www.sqlspot.com *************************