Thread: Converting yes or no to one letter strings.
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
On Tue, 4 Jun 2019, Lou wrote: > 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, I'm far from an expert so take my comments with a bag of salt. First, 'false' is displayed in a column as 'f' when you look at a table with psql. 'true' is displayed as 't'. Second, if you need to display to the user something other than 't' and 'f' that should be done with your UI, not by changing postgres. Regards, Rich
On 4 June 2019 23:30:33 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
select case when (boolean variable) then (whatever) else (whatever else) end;
On Tue, Jun 4, 2019 at 3:30 PM Lou <lou@dayspringpublisher.com> wrote:
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'
Something like the following should work:
ALTER TABLE ... ALTER COLUMN ... TYPE text USING (CASE WHEN ... THEN 's' ELSE 'f' END);
Though at this point you are probably better off creating a category table with a primary key and converting this column to be a foreign key.
David J.
On Tue, Jun 4, 2019 at 4:01 PM Rich Shepard <rshepard@appl-ecosys.com> wrote:
On Tue, 4 Jun 2019, Lou wrote:
> 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.
Second, if you need to display to the user something other than 't' and 'f'
that should be done with your UI, not by changing postgres.
Actually, given that "f" is becoming multiple different values it seems like the OP is improving upon their data model. That should very much be done at the table level at not relegated to views, let alone a UI layer.
It is not unusual to want to convert something that was a simple boolean to an enumeration of values. Though it is probably better to add a new column to reflect the new model element and effectively deprecate the old column - eventually removing it from use when it is no longer needed. Altering the column type, while possible, is not usually desirable. In particular it makes rolling back (or supporting older) client software more difficult because the old version will not recognize the new field and the old field will be gone.
David J.
On Tue, 4 Jun 2019, David G. Johnston wrote: > Actually, given that "f" is becoming multiple different values it seems > like the OP is improving upon their data model. That should very much be > done at the table level at not relegated to views, let alone a UI layer. > > It is not unusual to want to convert something that was a simple boolean > to an enumeration of values. Though it is probably better to add a new > column to reflect the new model element and effectively deprecate the old > column - eventually removing it from use when it is no longer needed. > Altering the column type, while possible, is not usually desirable. In > particular it makes rolling back (or supporting older) client software > more difficult because the old version will not recognize the new field > and the old field will be gone. David, Thanks for the insights. Rich
On 6/4/19 3:29 PM, Lou 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. Why not an enum field?: https://www.postgresql.org/docs/11/datatype-enum.html > > Lou > -- Adrian Klaver adrian.klaver@aklaver.com
On 6/4/19 7:19 PM, Adrian Klaver wrote: > On 6/4/19 3:29 PM, Lou 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. > > Why not an enum field?: > > https://www.postgresql.org/docs/11/datatype-enum.html I thought enum was Considered Harmful because of the difficulty in altering the enums? -- Angular momentum makes the world go 'round.
On 6/4/19 7:27 PM, Ron wrote: > > > On 6/4/19 7:19 PM, Adrian Klaver wrote: >> On 6/4/19 3:29 PM, Lou 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. >> >> Why not an enum field?: >> >> https://www.postgresql.org/docs/11/datatype-enum.html > > I thought enum was Considered Harmful because of the difficulty in > altering the enums? > The difficulty is removing a value: https://www.postgresql.org/docs/11/sql-altertype.html Otherwise I see equal difficulty in pretending that s, f, p and e are a boolean choice. -- Adrian Klaver adrian.klaver@aklaver.com
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 TABLEorigin=# alter table foo alter column tfvalue set data type character;
ALTER TABLE
Perfectly fine if the table has no values in that column.
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)
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?"
question, "How would the Lone Ranger handle this?"