CTAS ("Create Table As Select ..."). Question is about columns NULL-inheritance

[Pavel Zotov]

Consider script:

create domain dm_nn as bigint not null;
recreate table tbase(
   id_nn_primary_key int generated always as identity primary key
  ,a_nn_explicit int not null
  ,b_nn_implicit dm_nn
  ,c_nn_ident_always int generated always as identity
  ,d_nn_ident_by_def int generated by default as identity
);
recreate table ctas_permanent as (select * from tbase);
recreate global temporary table ctas_gtt_sessn as (select * from tbase) on commit preserve rows;
recreate global temporary table ctas_gtt_trans as (select * from tbase) on commit delete rows;
commit;

Its output on 6.0.0.2050 is:

show table tbase;
Table: PUBLIC.TBASE
ID_NN_PRIMARY_KEY               INTEGER Not Null Identity (always)
A_NN_EXPLICIT                   INTEGER Not Null
B_NN_IMPLICIT                   (PUBLIC.DM_NN) BIGINT Not Null
C_NN_IDENT_ALWAYS               INTEGER Not Null Identity (always)
D_NN_IDENT_BY_DEF               INTEGER Not Null Identity (by default)
CONSTRAINT INTEG_1:
  Primary key (ID_NN_PRIMARY_KEY)

show table ctas_permanent;
Table: PUBLIC.CTAS_PERMANENT
ID_NN_PRIMARY_KEY               INTEGER Nullable
A_NN_EXPLICIT                   INTEGER Nullable
B_NN_IMPLICIT                   BIGINT Nullable
C_NN_IDENT_ALWAYS               INTEGER Nullable
D_NN_IDENT_BY_DEF               INTEGER Nullable

show table ctas_gtt_sessn;
Table: PUBLIC.CTAS_GTT_SESSN
ID_NN_PRIMARY_KEY               INTEGER Nullable
A_NN_EXPLICIT                   INTEGER Nullable
B_NN_IMPLICIT                   BIGINT Nullable
C_NN_IDENT_ALWAYS               INTEGER Nullable
D_NN_IDENT_BY_DEF               INTEGER Nullable

show table ctas_gtt_trans;
Table: PUBLIC.CTAS_GTT_TRANS
ID_NN_PRIMARY_KEY               INTEGER Nullable
A_NN_EXPLICIT                   INTEGER Nullable
B_NN_IMPLICIT                   BIGINT Nullable
C_NN_IDENT_ALWAYS               INTEGER Nullable
D_NN_IDENT_BY_DEF               INTEGER Nullable 

My question is about nullability inheritance: shouldn't  all columns in CTAS-tables (from this script) have NOT null attribute ?

[Adriano dos Santos Fernandes]

On 7/2/26 13:58, Pavel Zotov wrote:
>
> My question is about nullability inheritance: shouldn't  all columns in
> CTAS-tables (from this script) have NOT null attribute ?
>

Fixed. It will also copy exact source columns types when possible.


Adriano