pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedCasting bytea to varchar
4+ messages / 3 participants
[nested] [flat]
* Casting bytea to varchar
@ 2013-01-15 05:11 Rajagopalan, Jayashree <Jayashree.Rajagopalan@emc.com>
0 siblings, 1 reply; 4+ messages in thread
From: Rajagopalan, Jayashree @ 2013-01-15 05:11 UTC (permalink / raw)
To: pgsql-admin
Hi:
I'm relatively new to postgres:
I've to alter a column which is of datatype bytea to varchar(255).
I used this:
ALTER TABLE tablename ALTER COLUMN columname TYPE varchar(255);
But I'm really not sure, if the value is casted properly.
Is there a way to explicity to cast the value, while altering a columns.
Example sql are welcome.
Thanks
Jayashree
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Casting bytea to varchar
@ 2013-01-15 08:42 Albe Laurenz <laurenz.albe@wien.gv.at>
parent: Rajagopalan, Jayashree <Jayashree.Rajagopalan@emc.com>
0 siblings, 1 reply; 4+ messages in thread
From: Albe Laurenz @ 2013-01-15 08:42 UTC (permalink / raw)
To: Rajagopalan, Jayashree *EXTERN* <Jayashree.Rajagopalan@emc.com>; pgsql-admin
Jayashree Rajagopalan wrote:
> I've to alter a column which is of datatype bytea to varchar(255).
>
> I used this:
> ALTER TABLE tablename ALTER COLUMN columname TYPE varchar(255);
>
> But I'm really not sure, if the value is casted properly.
> Is there a way to explicity to cast the value, while altering a columns.
>
> Example sql are welcome.
What you did is maybe not what you expect.
See the following example (9.2, encoding UTF8):
CREATE TABLE test (id integer primary key, val bytea);
INSERT INTO test VALUES (1, 'Schön'::bytea);
ALTER TABLE test ALTER COLUMN val TYPE varchar(255);
INSERT INTO test VALUES (2, 'Schön');
SELECT * FROM test;
id | val
----+----------------
1 | \x536368c3b66e
2 | Schön
(2 rows)
So the bytea column will be replaced with the string
representation of the bytea.
You cannot specify a conversion function while altering
a column's type, you'd have to use a new column like this:
ALTER TABLE test ADD COLUMN val2 varchar(255);
UPDATE test SET val2 = convert_from(val, 'UTF8'); -- because my encoding is UTF8
ALTER TABLE test DROP COLUMN val;
ALTER TABLE test RENAME COLUMN val2 TO val;
Yours,
Laurenz Albe
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Casting bytea to varchar
@ 2013-01-15 15:40 Tom Lane <tgl@sss.pgh.pa.us>
parent: Albe Laurenz <laurenz.albe@wien.gv.at>
0 siblings, 1 reply; 4+ messages in thread
From: Tom Lane @ 2013-01-15 15:40 UTC (permalink / raw)
To: Albe Laurenz <laurenz.albe@wien.gv.at>; +Cc: Rajagopalan, Jayashree *EXTERN* <Jayashree.Rajagopalan@emc.com>; pgsql-admin
Albe Laurenz <laurenz.albe@wien.gv.at> writes:
> You cannot specify a conversion function while altering
> a column's type, you'd have to use a new column like this:
Sure you can; that's the whole point of the USING option.
It'd look something like
ALTER TABLE test ALTER COLUMN val TYPE varchar(255) USING convert(val);
with whatever is appropriate in place of convert().
But the real question of course is what is "appropriate" for the OP's
situation --- he didn't specify exactly what he's got in his bytea
column or how that should be converted to varchar.
regards, tom lane
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Casting bytea to varchar
@ 2013-01-15 15:56 Albe Laurenz <laurenz.albe@wien.gv.at>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 4+ messages in thread
From: Albe Laurenz @ 2013-01-15 15:56 UTC (permalink / raw)
To: Tom Lane *EXTERN* <tgl@sss.pgh.pa.us>; +Cc: Rajagopalan, Jayashree *EXTERN* <Jayashree.Rajagopalan@emc.com>; pgsql-admin
Tom Lane wrote:
>> You cannot specify a conversion function while altering
>> a column's type, you'd have to use a new column like this:
>
> Sure you can; that's the whole point of the USING option.
> It'd look something like
>
> ALTER TABLE test ALTER COLUMN val TYPE varchar(255) USING convert(val);
I didn't know that such an option exists.
Thanks for the correction!
Yours,
Laurenz Albe
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2013-01-15 15:56 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2013-01-15 05:11 Casting bytea to varchar Rajagopalan, Jayashree <Jayashree.Rajagopalan@emc.com>
2013-01-15 08:42 ` Albe Laurenz <laurenz.albe@wien.gv.at>
2013-01-15 15:40 ` Tom Lane <tgl@sss.pgh.pa.us>
2013-01-15 15:56 ` Albe Laurenz <laurenz.albe@wien.gv.at>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox