Thread: how to read bytea field

folks


 help me ,i cant read bytea type field's.
 how to convert bytea to text or varchar ?
 when using bytea types?
 any clue be appreciated
 best regards
 mdc







__________________________________________________
Preguntá. Respondé. Descubrí.
Todo lo que querías saber, y lo que ni imaginabas,
está en Yahoo! Respuestas (Beta).
¡Probalo ya!
http://www.yahoo.com.ar/respuestas

This might help you:

select encode(col1,'escape') from tblBytea;

where col1 is of type bytea...

-------------
Shoaib Mir
EnterpriseDB (www.enterprisedb.com)

Shoaib ,folks


 Yes i know, but if your define bytea field and store
 bytea in this field , decode don't work, because
 decode function has text parameter not bytea ,so
 how do that to read bytea field to text again?

 what function convert  bytea to text?
 best regards
  mdc


--- Shoaib Mir <shoaibmir@gmail.com> escribió:

> This might help you:
>
> select encode(col1,'escape') from tblBytea;
>
> where col1 is of type bytea...
>
> -------------
> Shoaib Mir
> EnterpriseDB (www.enterprisedb.com)
>
> On 1/24/07, marcelo Cortez
> <jmdc_marcelo@yahoo.com.ar> wrote:
> >
> > folks
> >
> >
> > help me ,i cant read bytea type field's.
> > how to convert bytea to text or varchar ?
> > when using bytea types?
> > any clue be appreciated
> > best regards
> > mdc
> >
> >
> >
> >
> >
> >
> >
> > __________________________________________________
> > Preguntá. Respondé. Descubrí.
> > Todo lo que querías saber, y lo que ni imaginabas,
> > está en Yahoo! Respuestas (Beta).
> > ¡Probalo ya!
> > http://www.yahoo.com.ar/respuestas
> >
> >
> > ---------------------------(end of
> broadcast)---------------------------
> > TIP 3: Have you checked our extensive FAQ?
> >
> >                http://www.postgresql.org/docs/faq
> >
>







__________________________________________________
Preguntá. Respondé. Descubrí.
Todo lo que querías saber, y lo que ni imaginabas,
está en Yahoo! Respuestas (Beta).
¡Probalo ya!
http://www.yahoo.com.ar/respuestas

Hi,

marcelo Cortez wrote:
>  Yes i know, but if your define bytea field and store
>  bytea in this field , decode don't work,

Sure it does:

test=# select encode(E'\\000\\001', 'escape')::text;
    encode
----------
  \000\x01
(1 row)


If you inspect the function, you'll find that encode can *only* handle
bytea, not text (as the first parameter):

test=# \df encode;
                       List of functions
    Schema   |  Name  | Result data type | Argument data types
------------+--------+------------------+---------------------
  pg_catalog | encode | text             | bytea, text
(1 row)


And trying to feed it text gives:

test=# select encode('some text'::text, 'escape')::text;
ERROR:  function encode(text, "unknown") does not exist


Are you sure you tested with a real bytea field?

Regards

Markus

hi Markus ,folks




> Are you sure you tested with a real bytea field?

 Yeah , i  store bytea using encode function , how you
say .
 The field of my table is bytea type , and store real
bytea data in this field.
 My problem is , i can't convert this field to text
anymore, not function receiving bytea and return text
exist's.
I think my mistake was use bytea field.
I thinking in turn this field to text and use
decode/encode for storage binary data.
please correct me if i'm wrong.
 best regards
 mdc





>
> Regards
>
> Markus
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 5: don't forget to increase your free space map
> settings
>







__________________________________________________
Preguntá. Respondé. Descubrí.
Todo lo que querías saber, y lo que ni imaginabas,
está en Yahoo! Respuestas (Beta).
¡Probalo ya!
http://www.yahoo.com.ar/respuestas

marcelo Cortez wrote:
>> Are you sure you tested with a real bytea field?
>
>  Yeah , i  store bytea using encode function , how you
> say .

No, you store using the decode function.

=> \df decode
                       List of functions
    Schema   |  Name  | Result data type | Argument data types
------------+--------+------------------+---------------------
  pg_catalog | decode | bytea            | text, text


>  The field of my table is bytea type , and store real
> bytea data in this field.
>  My problem is , i can't convert this field to text
> anymore, not function receiving bytea and return text
> exist's.

That's what your encode function should do.

=> \df encode
                       List of functions
    Schema   |  Name  | Result data type | Argument data types
------------+--------+------------------+---------------------
  pg_catalog | encode | text             | bytea, text
(1 row)

What does "\df encode" show for you?
--
   Richard Huxton
   Archonet Ltd

Decode works as expected for me....

Try the following:

select decode((encode(E'\\000\\001', 'escape')::text), 'escape');

-----------
Shoaib Mir
EnterpriseDB ( www.enterprisedb.com)

Hi,

marcelo Cortez wrote:
>> Are you sure you tested with a real bytea field?
>
>  Yeah , i  store bytea using encode function , how you
> say .

I never said 'use encode function to store bytea'. I tried to explain
that encode returns TEXT.

>  The field of my table is bytea type , and store real
> bytea data in this field.

That's contradictory to the above. Please show me exactly what you do,
please (the INSERT as well as the SELECT you want to use).

> I think my mistake was use bytea field.
> I thinking in turn this field to text and use
> decode/encode for storage binary data.

That sounds like wasting diskspace and I/O bandwith.

Regards

Markus

folks


 my table

CREATE TABLE pblfield
(
  id_ integer NOT NULL,
  value_field bytea,
  name character varying(128),
  osset integer,
  length integer,
  version_ integer,
  inst_class_ character varying(128),
  CONSTRAINT pblfield_pkey PRIMARY KEY (id_)
)

 insert into pblfield( id_ , value_field ) values( 1 ,
encode(E'\\000\\001', 'escape') ) ;
 insert into pblfield( id_ , value_field ) values( 2 ,
encode(E'\\000\\002', 'escape') ) ;
 etc...

 now, i want to recover value_field in text form
some thing like..


  select id_ , decode(value_field) from pblfield ;

  WRONG WRONG ... decode has text parameter ...!!!!!

  Ok ,next try .

  select id_ , decode(value_field ::text ) from
pblfield.

  WRONG WRONG ... bytea not cast to string  ...!!!!!

  so, how do that ???


  I think solution is:

CREATE TABLE pblfield
(
  id_ integer NOT NULL,
  value_field text ,   /*!!!!  here text field */
  name character varying(128),
  osset integer,
  length integer,
  version_ integer,
  inst_class_ character varying(128),
  CONSTRAINT pblfield_pkey PRIMARY KEY (id_)
)

 insert into pblfield( id_ , value_field ) values( 1 ,
encode('\\000\\001', 'escape')::text  ) ;
 insert into pblfield( id_ , value_field ) values( 2 ,
encode(E'\\000\\002', 'escape') ::text  ) ;

  select id_ , value_field from  pblfield ;

  works  and
  select id_ , decode(value_field ,'escaped' )  from
pblfield ;

  works too!!!

  folks thanks for your time and responses.
  best regards

 Last cuestion , when bytea field ( type)   is usable?

 for storage to external files???










__________________________________________________
Preguntá. Respondé. Descubrí.
Todo lo que querías saber, y lo que ni imaginabas,
está en Yahoo! Respuestas (Beta).
¡Probalo ya!
http://www.yahoo.com.ar/respuestas

marcelo Cortez wrote:
> folks
>
>
>  my table
>
> CREATE TABLE pblfield
> (
>   id_ integer NOT NULL,
>   value_field bytea,
>   name character varying(128),
>   osset integer,
>   length integer,
>   version_ integer,
>   inst_class_ character varying(128),
>   CONSTRAINT pblfield_pkey PRIMARY KEY (id_)
> )
>
>  insert into pblfield( id_ , value_field ) values( 1 ,
> encode(E'\\000\\001', 'escape') ) ;

NO! Go back and read what everyone is saying about the direction
decode/encode work in.

You're using them the wrong way around.

--
   Richard Huxton
   Archonet Ltd

ok my mistake ,

 insert into pblfield( id_ , value_field )
   values(1 ,encode(E'\\000\\001', 'escape') ::bytea
) ;

 best regards
 mdc








__________________________________________________
Preguntá. Respondé. Descubrí.
Todo lo que querías saber, y lo que ni imaginabas,
está en Yahoo! Respuestas (Beta).
¡Probalo ya!
http://www.yahoo.com.ar/respuestas

marcelo Cortez wrote:
> ok my mistake ,
>
>  insert into pblfield( id_ , value_field )
>    values(1 ,encode(E'\\000\\001', 'escape') ::bytea
> ) ;

No. Use "decode" to convert text to bytea, and "encode" to convert bytea
to text.

=> CREATE TABLE tb (b bytea);
CREATE TABLE
richardh=> INSERT INTO tb VALUES ( decode(E'\\000\\001\\002','escape') );
INSERT 0 1
richardh=> SELECT encode(b,'escape') FROM tb;
     encode
--------------
  \000\x01\x02
(1 row)

richardh=> SELECT encode(b,'hex') FROM tb;
  encode
--------
  000102
(1 row)

--
   Richard Huxton
   Archonet Ltd

Richard


 Hoooo i'm understood now!,
 sorry for the noise.
 thanks very, very much
 best regards
 mdc


--- Richard Huxton <dev@archonet.com> escribió:

> marcelo Cortez wrote:
> > ok my mistake ,
> >
> >  insert into pblfield( id_ , value_field )
> >    values(1 ,encode(E'\\000\\001', 'escape')
> ::bytea
> > ) ;
>
> No. Use "decode" to convert text to bytea, and
> "encode" to convert bytea
> to text.
>
> => CREATE TABLE tb (b bytea);
> CREATE TABLE
> richardh=> INSERT INTO tb VALUES (
> decode(E'\\000\\001\\002','escape') );
> INSERT 0 1
> richardh=> SELECT encode(b,'escape') FROM tb;
>      encode
> --------------
>   \000\x01\x02
> (1 row)
>
> richardh=> SELECT encode(b,'hex') FROM tb;
>   encode
> --------
>   000102
> (1 row)
>
> --
>    Richard Huxton
>    Archonet Ltd
>







__________________________________________________
Preguntá. Respondé. Descubrí.
Todo lo que querías saber, y lo que ni imaginabas,
está en Yahoo! Respuestas (Beta).
¡Probalo ya!
http://www.yahoo.com.ar/respuestas

marcelo Cortez wrote:
> Richard
>
>
>  Hoooo i'm understood now!,
>  sorry for the noise.

The thing to remember is it's encode/decode from the point of view of
the bytea type. So hex/escape are encodings of the "real" value.

--
   Richard Huxton
   Archonet Ltd