how to read bytea field

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