"Create table as select" does not preserve not null

Question

I am trying to use the "Create Table As Select" feature from Oracle to do a fast update. The problem I am seeing is that the "Null" field is not being preserved.

I defined the following table:

create table mytable(
  accountname varchar2(40) not null,
  username varchar2(40)
 );

When I do a raw CTAS, the NOT NULL on account is preserved:

 create table ctamytable as select * from mytable;

 describe ctamytable;

Name        Null     Type         
----------- -------- ------------ 
ACCOUNTNAME NOT NULL VARCHAR2(40)

USERNAME             VARCHAR2(40) 

However, when I do a replace on accountname, the NOT NULL is not preserved.

 create table ctamytable as 
   select replace(accountname, 'foo', 'foo2') accountname, 
          username 
     from mytable;

 describe ctamytable;

Name        Null Type          
----------- ---- ------------- 
ACCOUNTNAME      VARCHAR2(160) 
USERNAME         VARCHAR2(40) 

Notice that the accountname field no longer has a null, and the varchar2 field went from 40 to 160 characters. Has anyone seen this before?

Answer 1

This is because you are no longer selecting ACCOUNTNAME, which has a column definition and meta-data. Rather you are selecting a STRING, the result of the replace function, which doesn't have any meta-data. This is a different data type entirely.

A (potentially) better way that might work is to create the table using a query with the original columns, but with a WHERE clause that guarantees 0 rows.

Then you can insert in to the table normally with your actual SELECT.

By having query of 0 rows, you'll still get the column meta-data, so the table should be created, but no rows will be inserted. Make sure you make your WHERE clause something fast, like WHERE primary_key = -999999, some number you know would never exist.