dot dot - 7 months ago 17
SQL Question

insert values only if subselect is not null

I am trying to write sql that will create a new table using data from an existing table.
The new table will have, among other columns, two columns like so:

reserved boolean
reserved_for character varying(10)


In the original table, I have data that looks like this:

id | identification | department | description | lastchange | available | type
------------+----------------+------------------------------------+-------------------------------------------+---------------------+-----------+--------
9145090050 | | | Reserved for llb | 2011-05-20 11:46:21 | f |
9145090096 | | | Reserved for ppa | 2013-01-26 12:31:56 | f |
9145090046 | | | | 2011-05-06 10:34:21 | f |


If the original table has the text "Reserved for ..." then, I want the reserved field in the new table to be set to "true" and reserved_for to contain the 3 or 4 characters that follow the "Reserved for" text in the original table.

So using the above table as an example, I want my new table to look like this;

id | reserved | reserved_for | lastchange |
------------+----------+----------------+---------------------+
9145090050 | true | llb | 2011-05-20 11:46:21 |
9145090096 | true | ppa | 2013-01-26 12:31:56 |
9145090046 | false | | 2011-05-06 10:34:21 |


The query I have to extract the 4 characters after the "Reserved for " looks like this:

select
substring(description from 13 for 4)
from
definition
where
description like 'Reserved for%';


It works in that it extracts the characters I need. How do I write the conditional statement in my create table command?

Answer

I think this is just some string manipulation on the original table:

select id,
       (reserved_for like 'Reserved for%') as Reserved,
       (case when reserved_for like 'Reserved for%'
             then substring(reserved_for from 14) 
        end) as Reserved_For,
       last_change
from original;