How to create a table with no columns in SQLite?

Question

I want to create table with no columns in sqlite3. It is possible in postgres database, but not in a sqlite3 one. Is there any way to achieve this, or is it simply not supported (maybe not in sql standard?) I have checked sqlite3 CREATE TABLE grammar and it seems, that there must be at least one column, but maybe I have missed something?

Answer 1

Zero-column tables aren't supported in SQLite. Or in the SQL standard either.

Answer 2

I had this same question because I wanted a table with only the rowid field. While you may not be able to create a table without columns, you can make a table with only a rowid field as the primary key using the following code:

CREATE TABLE tablename (rowid INTEGER PRIMARY KEY) WITHOUT ROWID;

Answer 3

you can create table with only id column instead of creating empty table:

def create_table(DATABESE_NAME):
    conn = sqlite3.connect(DATABESE_NAME)
    c = conn.cursor()
    c.execute(''' CREATE TABLE IF NOT EXISTS rate_table(
    id INTEGER PRIMARY KEY AUTOINCREMENT) ''')
    conn.commit()
    conn.close()

Answer 4

You cannot create a table with no columns in SQLite but you can easily create a table with one column which is empty string with BLOB type as shown below. *If specifying no type, the type is BLOB type according to the doc:

CREATE TABLE person ("");

Or:

CREATE TABLE person ([]);

Or:

CREATE TABLE person (``);

And, you can add a row if you want as shown below:

INSERT INTO person ("") values ('John');

Or:

INSERT INTO person ([]) values ('John');

Or:

INSERT INTO person (``) values ('John');

Or:

INSERT INTO person values ('John');