How to prevent Redshift from converting boolean to varchar when creating table AS query result

0

I am trying to create a new table based on a simple query that contains boolean type columns. When I run the query it gives me this error: cannot cast type character varying to boolean.

Why would I want to do this?

I'm trying to define the datatype of a column in my new table as a BOOLEAN but without having a value to put in it (yet). Basically the outcome i'm looking for is the equivalent of running the following DDL: create table test_table (test_bool boolean);

A simple example to reproduce this is:

CREATE table test_table AS (
  SELECT CAST(NULL AS BOOLEAN) AS test_bool
);

Note: I am not using a string or casting to a varchar in this code!!

Conversely, the following works as expected - ie. has the correct column types:

CREATE table test_table AS (
    SELECT
        CAST(NULL AS VARCHAR(256)) AS test_varchar,
        CAST(NULL AS TIMESTAMP) AS test_ts,
        CAST(NULL AS INT) AS test_int,
        CAST(NULL AS NUMERIC(10,2)) AS test_numeric
);

Hopefully I'm missing something fairly basic with the data implicit conversion or the conversion from a result set to a table definition. Also fwiw, this was on Redshift serverless, although I did not try on regular redshift so it could be specific to serverless, but I cannot say one way or the other.

---CLARIFICATION BY EXAMPLE---

Here's a more concrete example:

I have source data that has people with eye_color and hair_color but nothing else. I want my target table to have the following schema: person(eye_color VARCHAR(256), hair_color VARCHAR(256), is_left_handed BOOLEAN, salary DECIMAL(10,2)).

I am creating this table new each run of my pipeline, and I'm using DBT to create a table from a source table (containing the source data mentioned above). This means I don't plan on running DDL to create the table and then fill it - rather I will "CREATE TABLE AS".

Since I want the destination table (person) to have all the columns (i.e. the correct schema) I need placeholders for them in my select statement. Right now it would look something like this:

CREATE TABLE person AS (
  SELECT
    eye_color,
    hair_color,
    CAST (NULL AS BOOLEAN) AS is_left_handed,
    CAST (NULL AS DECIMAL(10,2)) AS salary
  FROM source_data
);

This command fails with the error about converting to varchar. My question is about determining when the boolean value is converted to a varchar and how to prevent it from happening. If I remove the offending column CAST (NULL AS BOOLEAN) AS is_left_handed it works as expected. and can be verified with this query:

SELECT column_name, is_nullable, data_type, character_maximum_length, numeric_precision, numeric_scale, datetime_precision
FROM information_schema.columns
WHERE table_schema = 'my_schema' AND table_name   = 'person';
phil
asked a year ago2391 views
1 Answer
0

Hi,

From the notes, I understand that you are trying to cast a boolean field to varchar and encountering below error:


ERROR: cannot cast type character varying to boolean

In order to replicate and troubleshoot the issue at my end, please find below the steps I followed :

  1. Created a test table and inserted some sample data in it.

dev=# create table booltest(id int, flag boolean); CREATE TABLE

dev=# insert into booltest values(1, true); INSERT 0 1 dev=# insert into booltest values(2, true); INSERT 0 1 dev=# insert into booltest values(3, false); INSERT 0 1 dev=# insert into booltest values(4, false); INSERT 0 1

dev=# select * from booltest; id | flag ----+------ 1 | t 2 | t 3 | f 4 | f (4 rows)

  1. Then, I tried using cast/convert function to convert boolean datatype to text datatype.

dev=# select convert(varchar, flag) from booltest; ERROR: cannot cast type boolean to character varying

dev=# select cast(flag as varchar) from booltest; ERROR: cannot cast type boolean to character varying

dev=# select flag::varchar from booltest; ERROR: cannot cast type boolean to character varying

[+] CAST and CONVERT functions - https://docs.aws.amazon.com/redshift/latest/dg/r_CAST_function.html

  1. As a workaround, I tried the below commands and it worked fine.

dev=# select id,case flag when true then 'true'::varchar else 'false'::varchar end from booltest; id | case
----+------- 1 | true 2 | true 3 | false 4 | false (4 rows)

or

dev=# select id,decode(flag,true,'true',false,'false') from booltest; id | case
----+------- 1 | true 2 | true 3 | false 4 | false (4 rows)

[+] https://docs.aws.amazon.com/redshift/latest/dg/r_DECODE_expression.html

Unfortunately, I regret to inform you that above mentioned are the possible ways to cast a boolean field to varchar.

I hope the above information helps.

Thank you and have nice day !

answered a year ago
  • Thanks for your reply. My question is not about converting the boolean to a varchar, but rather how keep it as a boolean. I will try to update my original question to more clearly state what I am trying to do.

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions