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?

Scuttle

I am curious about what problem a zero-column table solves. Care to elaborate? – Chelsiechelsy

I am doing a pet project and working on tests. I could create empty table in postgres for test purpose, but the same automated test failed for sqlite. So I wanted to be sure if I am not doing anything wrong. Since empty tables are not supported by sql standards I don't need to care about it. – Scuttle

@Chelsiechelsy one might want to create empty tables and then go on adding columns. – Inaugural

Answer 1

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

Mathia

Interestingly, it's possible in PostgreSQL, though – Raney

@LukasEder That... doesn't seem very useful beyond entirely academic purposes. PostgreSQL doesn't even require the FROM clause, so it doesn't need a DUAL table like Oracle does. – Crossbill

@BaconBits: You can implement some cool predicate logic in joins using these tables. No one said this was't academic :) – Raney

Zero-column tables are also possible in Access as in CREATE TABLE NoColumns;. Interesting that SQLite3 doesn't support this. – Chenault

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;

Cattery

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()

Constitute

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

Culmination

The question is: "How to create a table with no columns in SQLite?" Your code creates a table with 1 column with a name that is an empty string: dbfiddle.uk/nrvQii19 – Salient

I don't know if I can call empty string a column. Is it possible to have a column without type in SQLite? – Culmination

Yes it is possible: dbfiddle.uk/S_g9MFJM – Salient

Can I see the explanation about the column without type in SQLite doc? – Culmination

Here: sqlite.org/datatype3.html#affinity_name_examples, search for "no datatype specified". – Salient