Starnge things with big datas... - Mailing list pgsql-general

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 *************************