"Create table as select" does not save non null

Question

I am trying to use the "Create Table How to Select" function from Oracle for a quick update. The problem that I see is that the Zero field is not saved.

I defined the following table:

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

When I do the initial CTAS, NOT NULL on the account is saved:

  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 replacement in the account name, NOT NULL is not saved.

  create table ctamytable as select replace(accountname, 'foo', 'foo2') accountname, username from mytable; describe ctamytable; Name Null Type ----------- ---- ------------- ACCOUNTNAME VARCHAR2(160) USERNAME VARCHAR2(40) 

Note that the accountname field is no longer null, and the varchar2 field is between 40 and 160 characters. Has anyone seen this before?

Answer 1

This is because you no longer select ACCOUNTNAME , which has a column definition and metadata. Rather, you select STRING, the result of a replace function that does not have metadata. This is a completely different data type.

A (potentially) best way that can work is to create a table using a query with source columns, but with a WHERE that guarantees 0 rows.

Then you can insert into the table usually using the actual SELECT .

Having received a query of 0 rows, you still get the metadata of the column, so the table should be created, but no rows will be inserted. Make sure that the WHERE is executed quickly, for example WHERE primary_key = -999999 , some kind of number that, as you know, will never exist.

Answer 2

Another option here is to define columns when calling CREATE TABLE AS SELECT. You can specify column names and include restrictions, excluding data types.

An example is shown below:

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

Remember that although this syntax is valid, you cannot include a data type. In addition, explicitly declaring all columns is somewhat striking for the purpose of using CREATE TABLE AS SELECT.