Re: Converting yes or no to one letter strings.

[Christopher Browne <cbbrowne@xxxxxxxxx>]

On Tue, 4 Jun 2019 at 18:30, Lou <lou@xxxxxxxxxxxxxxxxxxxxxx> wrote:

Hi everyone,

Is it possible to convert a boolean yes or no field to hold a one letter string? For example, the strings: 's' 'f' 'p' 'e'

To start off, I just need to convert true to 's'. false will have to be manually changed to 'f' or 'p' or 'e' as appropriate.

Lou

Certainly you can.

origin=# create table foo (id serial primary key, name text, tfvalue boolean);

CREATE TABLE
origin=# alter table foo alter column tfvalue set data type character;

ALTER TABLE

Perfectly fine if the table has no values in that column.

But wait, maybe not...

origin=# create table foo (id serial primary key, name text, tfvalue boolean);
CREATE TABLE
origin=# insert into foo (name, tfvalue) values ('chris', 'true');
INSERT 0 1
origin=# insert into foo (name, tfvalue) values ('dave', 'false');
INSERT 0 1
origin=# insert into foo (name) values ('brad');
INSERT 0 1
origin=# alter table foo alter column tfvalue set data type character;
ERROR:  value too long for type character(1)

Nope, you can't do that if there's data in the table. 

This seems like an altogether terrible idea, actually.  If you need a new column with a totally different interpretation, you should probably create a totally new column, that way you can do whatever you wish to the new column.

Might want to consult the trusty documentation, too.  <https://www.postgresql.org/docs/9.5/datatype-boolean.html>

--

When confronted by a difficult problem, solve it by reducing it to the
question, "How would the Lone Ranger handle this?"