CODEWITHSUNDEEP
postgresqlcreate-table
theDrifter
theDrifter

Reputation: 1696

Create empty table in postgresql

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?

Upvotes: 8

Views: 22794

Answers (4)

Federico Razzoli
Federico Razzoli

Reputation: 297

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.

Upvotes: 6

user330315
user330315

Reputation:

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

Upvotes: 8

joop
joop

Reputation: 4503

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)

Upvotes: 10

André Kleinschmidt
André Kleinschmidt

Reputation: 111

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)

Upvotes: 1

Related Questions