Skip to content

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

Closed

Description

@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);

Activity

  1. alamb commented on Mar 13, 2023

    @alamb
    ContributorAuthor

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

  2. Weijun-H commented on Mar 13, 2023

    @Weijun-H
    Member

    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?

  3. comphead commented on Mar 14, 2023

    @comphead
    Contributor

    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

  4. metesynnada commented on Mar 14, 2023

    @metesynnada
    Contributor

    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.

  5. alamb commented on Mar 15, 2023

    @alamb
    ContributorAuthor

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

    Thank you @comphead -- that would be great!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    bugSomething isn't workinggood first issueGood for newcomers

    Type

    No type

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions