Re: Converting yes or no to one letter strings. - Mailing list pgsql-general

From Christopher Browne
Subject Re: Converting yes or no to one letter strings.
Date
Msg-id CAFNqd5X2Cj_i3kUK=3vuQhphKPrye8jYKbMTVx3bnsM69nR3TQ@mail.gmail.com
Whole thread Raw
In response to Converting yes or no to one letter strings.  (Lou <lou@dayspringpublisher.com>)
List pgsql-general


On Tue, 4 Jun 2019 at 18:30, Lou <lou@dayspringpublisher.com> 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?"

pgsql-general by date:

Previous
From: Dmitry O Litvintsev
Date:
Subject: Re: postgresql11-devel RPM is missing from "Direct RPM downloads"
Next
From: Perumal Raj
Date:
Subject: Flood Warning message : user=[iso-8859-1],db=[iso-8859-1],host= WARNING:pg_getnameinfo_all() failed: Temporary failure in name resolution