CTAS. Questions about character sets for textual columns

[Pavel Zotov]

Let's consider script which sets connection charset = utf8 and creates:

1) DB with default charset = 1252

2) a table with several textual columns of different character sets (utf8;  cp1254; cp1252).

Here it is:

=======================

set bail on;
set list on;
shell del r:\temp\tmp4test.fdb 2>nul;
set names utf8;
create database 'localhost:r:\temp\tmp4test.fdb' user 'sysdba' password 'masterkey' default character set win1252;

create table test (
    id int primary key
   ,f_utf8 varchar(30) character set utf8
   ,f_1254 varchar(30) character set win1254
   ,f_1252 varchar(30) -- DB-default: 1252
);

insert into test (
    id
   ,f_utf8
   ,f_1254
   ,f_1252
) values(
    1
   ,'Թող երաժշտությունը հնչի' -- armenian
   ,'Bırak müzik çalsın'      -- turkish
   ,'Låt musiken spela'       -- swedish
);
commit;

create table ctas_test as (
    select
        t.*
       ,q'#Άσε τη μουσική να παίζει#' as let_the_music_play
    from test t
) with data;
commit;

set echo on;

show table ctas_test;

select * from ctas_test;

=======================

Output of this script will be:

show table ctas_test;
Table: PUBLIC.CTAS_TEST
ID                              INTEGER Not Null
F_UTF8                          VARCHAR(30) CHARACTER SET SYSTEM.UTF8 Nullable
F_1254                          VARCHAR(30) CHARACTER SET SYSTEM.WIN1254 Nullable
F_1252                          VARCHAR(30) CHARACTER SET SYSTEM.WIN1252 Nullable
LET_THE_MUSIC_PLAY              CHAR(24) CHARACTER SET SYSTEM.UTF8 Not Null 

...

If we then extract metadata from this DB then DDL for table CTAS_TEST will be shown as:

CREATE TABLE PUBLIC.CTAS_TEST (ID INTEGER NOT NULL,
        F_UTF8 VARCHAR(30) CHARACTER SET SYSTEM.UTF8,
        F_1254 VARCHAR(30) CHARACTER SET SYSTEM.WIN1254,
        F_1252 VARCHAR(30),
        LET_THE_MUSIC_PLAY CHAR(24) NOT NULL);

Q-1.

IIUC, column with charset that equals to DB default charset may be shown without explicit specification of this character set - like it is for F_1252. Is it expected ?

Q-2.

Why the column 'LET_THE_MUSIC_PLAY' is created with NOT-null flag ?

Q-3.

Why  the   column 'LET_THE_MUSIC_PLAY' has no `charset` attribute in extracted metadata ? (i supposed that it will be same as connection charset = utf8)

[Dimitry Sibiryakov]

Pavel Zotov wrote 07.07.2026 19:57:
> IIUC, column with charset that equals to DB default charset may be shown without

> explicit specification of this character set - like it is for *F_1252*. Is it
> expected ?

Yes. It can make global charset migration easier: just extract and replace
charset in one place instead of every field.

> Why the column 'LET_THE_MUSIC_PLAY' has no `charset` attribute in extracted metadata ? (i supposed that it will be same as connection charset = utf8)

It looks like a bug.

--
WBR, SD.

[Mark Rotteveel]

On 07-07-2026 19:57, Pavel Zotov wrote:
> Q-2.
> Why the column 'LET_THE_MUSIC_PLAY' is created with NOT-null flag ?

The value you're basing it on is a literal string, which is - AFAIK - by
definition NOT NULL. Given the column definitions are derived from the
select, that means the column is defined as NOT NULL as well.

Mark
--
Mark Rotteveel

[Adriano dos Santos Fernandes]

On 7/7/26 14:57, Pavel Zotov wrote:
> Q-3.
> Why  the   column 'LET_THE_MUSIC_PLAY' has no `charset` attribute in
> extracted metadata ? (i supposed that it will be same as connection
> charset = utf8)

Fixed.


Adriano