Create table with Boolean column type from a VARCHAR column source table

arilwan 0
Viewed 29

So, I have the following table:

CREATE TABLE response (response_id INT, utime INT, 
                     question_id INT, response VARCHAR(500))

INSERT INTO response (response_id,  utime, question_id, response)
VALUES (5,1459542506,11,1),(5,1459542506,12,0),(5,1459542506,13,0),
  (5,1459542506,14,0),(5,1459542506,15,0),(7,1458210291,11,0),(7,1458210291,12,1),
  (7,1458210291,13,1),(7,1458210291,14,0),(7,1458216077,15,1),(10,1458212391,11,1),
  (10,1458212391,12,0),(10,1458212391,13,1),(10,1458212391,14,0),(10,1458212391,15,0)

Which stores survey response of people's activity in a designated hour. Each question_id represent 1 question from five possibilities. The response to a user indicates his choices from all the possibilities. Where s/he did not answer is assumed to be false.

question_id  |   activity
 ------------+----------   
11           -   sitting
12           -   walking
13           -   standing
14           -   running
15           -   jogging

These responses are stored in column response, for value of each question. So example the response from response_id=5 is sitting, and no other activity, response_id=7 is walking, standing, jogging.

Unfortunately, in the source table, response is defined type VARCHAR not BOOLEAN.

I want to create a new table that describes the activity for each response where such an activity is reported. Here's how I go:

CREATE TABLE target_table AS
  SELECT response_id, to_timestamp(utime), response = 'true' activity
  FROM response; 
=> SELECT 15

-- but then
SELECT * FROM target_table WHERE activity IS TRUE
=> SELECT 0

Equally, activity column values all reported as f

response_id  activity_day       activity
   5        2016-04-01 21:28:26+01  f
   5        2016-04-01 21:28:26+01  f
   5        2016-04-01 21:28:26+01  f

Required output

I would like to have target_table in the for:

response_id  | activity_day        | activity 
-------------+---------------------+---------
    5         2016-04-01 21:28:26    sitting
    7         2016-03-17 10:24:51    walking
    7         2016-03-17 10:24:51    standing
    7         2016-03-17 10:24:51    jogging
   10         2016-03-17 10:59:51    sitting
   10         2016-03-17 10:59:51    standing 
....

NOTE: I provide this dbfiddle

sql postgresql
1 Answers

Create the target_table like this:

CREATE TABLE target_table AS
  SELECT response_id, to_timestamp(utime) AS activity_day, response = '1' activity
  FROM response

After this selecting the TRUE answers:

SELECT * FROM target_table WHERE activity

see: DBFIDDLE

This new table (target_table) will have been created with the following columns:

select 
     column_name,
     data_type
  from information_schema.columns 
  where table_name='target_table';

output:

column_name data_type
response_id integer
activity_day timestamp with time zone
activity boolean
Luuk0
Related