Thread: Error creating tables.

Hello
I am new to this list, mostly because I just started using (or trying to) postgreSQL.
I tried to create a table using the example at www.freebsddiary.org/postgresql.html and also at
http://www.postgresql.org/users-lounge/docs/7.0/tutorial/query1356.htm. None works. 
Tracing down what could be wrong, I came to the conclusion that I can't create any table containing a varchar().
The statement:
create table test4 (name varchar(10)) ;
returns
ERROR:  cannot create test4

however, the statement
create table test (id serial, name varchar(10) ) ;
returns:
NOTICE:  CREATE TABLE will create implicit sequence 'test_id_seq' for SERIAL column 'test.id'
NOTICE:  CREATE TABLE/UNIQUE will create implicit index 'test_id_key' for table 'test'
ERROR:  cannot create test_id_seq

But just the statement
create table test3 (id serial) ;
succeedes.

Any ideas?

my system is a K6-2 PC running SuSE Linux 6.3, postgress was installed from the SuSE CDs, not from the sources.
My version of postgresql is 6.5.1

Other error that may be related is that sometimes instead of the error messages I gave (or instead of any return
message),psql aborts saying: 
pqReadData() -- backend closed the channel unexpectedly.
        This probably means the backend terminated abnormally
        before or while processing the request.
We have lost the connection to the backend, so further processing is impossible.  Terminating.

Any advice is welcome.
Aristide

Salut Aristide,

Le Thursday, October 12, 2000 à 7:23:18 PM, tu me disais:

AA> create table test (id serial, name varchar(10) ) ;
AA> returns:
AA> NOTICE:  CREATE TABLE will create implicit sequence 'test_id_seq' for SERIAL column 'test.id'
AA> NOTICE:  CREATE TABLE/UNIQUE will create implicit index 'test_id_key' for table 'test'
AA> ERROR:  cannot create test_id_seq

Maybe the test_id_seq sequence already exists (you created the table
already, dropped it and, as someone said today, the DROP does not
automagically get rid of the automagic sequence created when you
declare a SERIAL field).

You can try :
DROP SEQUENCE test_id_seq;

Just guessing...

--
Jean-Christophe Boggio
cat@thefreecat.org
Independant Consultant and Developer
Delphi, Linux, Oracle, Perl

Aristide Aragon <aristide@lionking.org> writes:
> create table test4 (name varchar(10)) ;
> returns
> ERROR:  cannot create test4

That looks like the physical creation of the table data file for 'test4'
is failing.  Unfortunately older releases of postgres are not very
good about reporting the kernel error code that would tell us why it
failed.  (This is fixed in current sources, and I think in 7.0.2 as well.)

I will venture that there is a file named 'test4' hanging around in your
database directory, possibly from an earlier version of the table that
was incompletely created or deleted.  (Evidently there's no pg_class
entry for test4, or you'd have gotten a different error message.  But
the physical file is there.)  Since you mention backend crashes,
an incomplete table creation seems the most likely bet for the cause.

If Postgres doesn't believe it has a table named test4 (use psql's \d
to check) then it's safe to just delete the unwanted file with rm.

> My version of postgresql is 6.5.1

I'd strongly recommend an update to 7.0.2.  We've fixed an awful lot
of bugs since 6.5.

            regards, tom lane