Starnge things with big datas...

["F. BROUARD / SQLpro" <sqlpro@xxxxxxxxxxxxxxxx>]

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


--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general