Postgres Pro
Downloads
Home   >   mailing lists

Thread: view columm size.....

view columm size.....

From
Arnaud FLORENT
Date:
hi,

i 've create a view
one of the column is the result of col1 || col2.....
col1 is 8 char long
col 2 is 4 char long

but my column view is 254 char long........

so i can't UPDATE a 12 char length col using this view a table because
"Length is not equal to length of the target column"

what should i do to force the length of the view column to 12 char?

thanks

--
______________________________
 Arnaud FLORENT
 IRIS Technologies

 phone: (33) 03 20 65 85 80
 fax: (33) 03 20 65 85 81
 GSM: (33) 06 15 14 32 90

 mailto:aflorent@iris-tech.fr
______________________________


FROM <aflorent@iris-tech.fr>
RCPT <pgsql-general@hub.org>
BODY
Received: from siris.iris-tech.fr(192.168.0.100) by scomm.iris-tech.fr via smap (V2.1)
    id xma011429; Thu, 10 Feb 00 10:34:28 +0100
Received: from iris-tech.fr (aflorent@afl.iris-tech.fr [192.168.0.5])
          by siris.iris-tech.fr (8.9.3/jtpda-5.3) with ESMTP id KAA05811
          for <pgsql-general@hub.org>; Thu, 10 Feb 2000 10:31:17 +0100
Message-ID: <38A28565.B72BF288@iris-tech.fr>
Date: Thu, 10 Feb 2000 10:31:18 +0100
From: Arnaud FLORENT <aflorent@iris-tech.fr>
X-Mailer: Mozilla 4.7 [fr] (Win95; I)
X-Accept-Language: fr,en
MIME-Version: 1.0
To: PostgreSQL general ML <pgsql-general@hub.org>
Subject: view columm size.....

Re: [GENERAL] view columm size.....

From
"Ross J. Reedstrom"
Date:
On Thu, Feb 10, 2000 at 10:31:18AM +0100, Arnaud FLORENT wrote:
> hi,
>
> i 've create a view
> one of the column is the result of col1 || col2.....
> col1 is 8 char long
> col 2 is 4 char long
>
> but my column view is 254 char long........
>
> so i can't UPDATE a 12 char length col using this view a table because
> "Length is not equal to length of the target column"
>
> what should i do to force the length of the view column to 12 char?
>

Hmm, try something like:

substr("ColView",1,12)

That should give you the first 12 characters, i.e. all of them. See if
the UPDATE likes it.

Ross
--
Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu>
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St.,  Houston, TX 77005

Re: [GENERAL] view columm size.....

From
Arnaud FLORENT
Date:
"Ross J. Reedstrom" a écrit :

> On Thu, Feb 10, 2000 at 10:31:18AM +0100, Arnaud FLORENT wrote:
> > hi,
> >
> > i 've create a view
> > one of the column is the result of col1 || col2.....
> > col1 is 8 char long
> > col 2 is 4 char long
> >
> > but my column view is 254 char long........
> >
> > so i can't UPDATE a 12 char length col using this view a table because
> > "Length is not equal to length of the target column"
> >
> > what should i do to force the length of the view column to 12 char?
> >
>
> Hmm, try something like:
>
> substr("ColView",1,12)
>
> That should give you the first 12 characters, i.e. all of them. See if
> the UPDATE likes it.

this works but i would like to understant this behaviour......

i've got the same problem with a select into
MAX( <char(12) column>) becomes a char (8190)!!!!!!!!!!!!

this column should be used as a joined key, and the join gave no results....

--
______________________________
 Arnaud FLORENT
 IRIS Technologies

 phone: (33) 03 20 65 85 80
 fax: (33) 03 20 65 85 81
 GSM: (33) 06 15 14 32 90

 mailto:aflorent@iris-tech.fr
______________________________

Re: [GENERAL] view columm size.....

From
"Ross J. Reedstrom"
Date:
On Thu, Feb 10, 2000 at 05:17:18PM +0100, Arnaud FLORENT wrote:
> "Ross J. Reedstrom" a écrit :
>
> > On Thu, Feb 10, 2000 at 10:31:18AM +0100, Arnaud FLORENT wrote:
> > > hi,
> > >
> > Hmm, try something like:
> >
> > substr("ColView",1,12)
> >
> > That should give you the first 12 characters, i.e. all of them. See if
> > the UPDATE likes it.
>
> this works but i would like to understant this behaviour......
>
> i've got the same problem with a select into
> MAX( <char(12) column>) becomes a char (8190)!!!!!!!!!!!!
>

This depends on the field definitions you're using. The SQL standard
says that CHAR(X) values will be padded with whitespace to the full
column width. Sometimes, when moving data around, one needs to be
careful where it's going to be expanded, and were not. Usually, I don't
need the whitespace at all, so I can fix things with btrim() (removes
whitespace from both ends)

Ross
--
Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu>
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St.,  Houston, TX 77005