CREATE TABLE defaults to NON nullable (should be nullable to follow postgres)

[alamb]

CREATE TABLE statements create tables with non-nullable columns by default, which contradicts PostgreSQL 15.

CREATE TABLE table_without_values(field1 BIGINT, field2 BIGINT);

DDL query. It defaults to non-nullable columns while creating a table, which is not the case in PostgreSQL 15.

NULL
The column is allowed to contain null values. This is the default.
This clause is only provided for compatibility with non-standard SQL databases. Its use is discouraged in new applications.

PS: The code piece that results non-nullable default:
https://github.com/apache/arrow-datafusion/blob/1a22f9fd436c9892566b668e535e0d6c8cb9fbd3/datafusion/sql/src/planner.rs#L127-L144

Originally posted by @metesynnada in #5520 (comment)

Desired behavior:

Make the default columns NULLABLE

this should work (and insert a single null value into foo)

create table foo(x int);
insert into foo values (null);

[alamb]

I think this is a good first issue as the desired behavior is well understood and the code location is identified

[Weijun-H]

I tried to test CREATE TABLE table_without_values(field1 BIGINT NULL, field2 BIGINT NULL);, but found Error: NotImplemented("Only CREATE TABLE table_name AS SELECT ... statement is supported"). Is it reasonable?

[comphead]

Currently on CTAS supported

create table the_nulls as values (null::bigint, 1), (null::bigint, 1), (null::bigint, 2);

I think we also support traditional create table. Moreover we already have methods to create database.
@alamb I can create a separate ticket for traditional create table stmt

[metesynnada]

Integrating its support into the planner is straightforward. Simply changing the input of CreateMemoryTable (a logical plan) into optional, constructing a MemTable with vec![] and the correct schema is sufficient to enable this functionality.

[alamb]

@alamb I can create a separate ticket for traditional create table stmt

Thank you @comphead -- that would be great!