Create empty table in postgresql

Question

i want to create a simple table inside a database in postgresql. From the Documentation i have CREATE TABLE will create a new, initially empty table in the current database. The table will be owned by the user issuing the command. With this command

CREATE TABLE *table_name*;

I thought i get a new empty table.But psql throws ERROR: syntax error at or near ";". When i user an empty argument list like:

CREATE TABLE *table_name*();

psql tells me that the table was created through

postgres=# create table *table_name*();
CREATE TABLE

But \l shows is not showing the newly created table. And its also not possible to login with psql -d table_name -U user_name. Can anyone help?

Do you really need a table with no columns? One usually calls an empty table the one with no records. The one without columns is kind of odd. — mlt
– mlt, Commented Jul 9, 2015 at 15:34
You got to add columns. Or since you use rails, do it with migrations. You'd want to use raw SQL if you had an existing DB you wanted to integrated with. — mlt
– mlt, Commented Jul 9, 2015 at 15:39
rails can create DB for you as well via rake db:create or something. — mlt
– mlt, Commented Jul 9, 2015 at 18:35

joop · Accepted Answer · 2015-07-09 16:39:11Z

10

You can have a table with no columns, and even with some rows in it:

CREATE TABLE nocolumn (dummy INTEGER NOT NULL PRIMARY KEY)
    ;

INSERT INTO nocolumn(dummy) VALUES (1);

ALTER TABLE nocolumn
        DROP COLUMN dummy;

\d nocolumn

SELECT COUNT(*) FROM nocolumn;

Output:

CREATE TABLE
INSERT 0 1
ALTER TABLE
   Table "tmp.nocolumn"
 Column | Type | Modifiers 
--------+------+-----------

 count 
-------
     1
(1 row)

answered Jul 9, 2015 at 16:39

joop

4,5431 gold badge18 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

theberzi Over a year ago

Overly complicated approach. It's enough to just CREATE TABLE mytable ();.

user330315 · Accepted Answer · 2015-07-13 17:50:46Z

8

You seem to be confusing the terms database and table

But \l is not showing the newly created table.

Of course \l will not show you that table, because \l will list databases not relations. To see all tables you need to use \d or \dt.

And its also not possible to login with psql -d table_name -U user_name

Of course this is not possible, because the -d parameter is used to specify a database, not a table

answered Jul 13, 2015 at 17:50

user330315

Comments

Federico Razzoli · Accepted Answer · 2021-10-20 23:48:35Z

I'm not sure why other answers suggest to create a table with a column and then ignore that column. It is certainly possible, but it seems different from what you tried to do.

It seems you have to use parenthesis:

postgres=# CREATE TABLE t ();
CREATE TABLE

To insert a row:

postgres=# INSERT INTO t DEFAULT VALUES;
INSERT 0 1

To count the rows you inserted:

postgres=# SELECT FROM t;
--
(2 rows)

You can't delete a single rows, because all rows are equal. But to completely empty the table, you can use DELETE without WHERE, or TRUNCATE TABLE.

You can find more info here: PostgreSQL: Tables without columns.

That said, I have to say that I understand "empty table" as "table without rows", not necessarily without columns.

André Kleinschmidt · Accepted Answer · 2015-07-09 16:29:03Z

1

An hour ago i suggested to add at least one column like this:

create table tab1 (columnname varchar(42) not null)

But this seems to be not necessary as a commentator just told. (I consider to keep the wrong answer here instead of deleting it, to prevent that others suggest the same)

edited Jul 9, 2015 at 16:29

answered Jul 9, 2015 at 15:37

André Kleinschmidt

1115 bronze badges

7 Comments

davethecoder Over a year ago

This is not really an answer, unless you actually add in a code block that shows you how to add columns, at best this is just a sarcastic dig at the op, and you can do this in the comments, without putting it out as an answer

André Kleinschmidt Over a year ago

I just tested to create a columless table with a database client for mixed server types (HeidiSQL) it always prevents generating tables without columns. Anyway I come to no idea of a usefull use of tables containing no colums that enables them to ever carry data in it. But thanks for the downvote.. 2 more an I can earn the peer pressure badge -.-

André Kleinschmidt Over a year ago

I've added a code block adding a column... I was not aware of the rule that answers on Stackoverflow.SE must contain codeblocks always. Sorry.

davethecoder Over a year ago

There is a rule that an answer, is an answer. If you ask a question, Do people just answer with " You are doing it wrong " answers?

user330315 Over a year ago

You can actually create a table without columns in Postgres. create table empty(); works just fine.

|

Collectives™ on Stack Overflow

Create empty table in postgresql

4 Answers 4

1 Comment

Comments

Comments

7 Comments

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

7 Comments

Related