BUG #7657: Create Table doesn't create columns

On Tue, Nov 13, 2012 at 04:59:53PM +0000, Matt@rigminder.com wrote:

The following bug has been logged on the website:

Bug reference: 7657
Logged by: Matt
Email address: Matt@rigminder.com
PostgreSQL version: 9.1.4
Operating system: Windows Server Standard SP2
Description:

When I run the following statement, the table is created, but there are no
columns:

CREATE TABLE "mod_1237" ("Collecteddepth" float8 NOT NULL, "Collectedtime"
float8 NOT NULL, "CollectedData" Varchar(45) NOT NULL, "Collectedpass"
float8 NOT NULL, "Collectedmodtime" float8 NOT NULL) WITH (OIDS = FALSE);

I know I'm not creating a primary key, but that shouldn't prevent the
columns from being generated. When I run this code it also doesn't generate
any errors so everything looks fine until I try to write to the table. Any
ideas as to why this wouldn't work or how to make a table with the given
columns?

most likely you did insert like:
insert into mod_1237 (Collecteddepth) values (...)
i.e. you didn't quote the column names. Hence the problem.

In psql, you can do:
\d mod_1237

and you will see the columns are there.

Best regards,

depesz

--
The best thing about modern society is how easy it is to avoid contact with it.
http://depesz.com/

Depesz -

I'm fairly sure it's not a problem with something I'm doing wrong because I've used the same code before. It has to be something wrong on the DB side. But just to entertain you I did as you requested:

RigMinder_NewDBTest02=# \d test
Table "public.test"
Column | Type | Modifiers
--------+------+-----------

RigMinder_NewDBTest02=#

Before I ran this I created a table using the following:

create table "test" ("column1" text, "column2" float);

It ran without any errors. Yesterday after I sent out the help request I uninstalled postgresql and installed version 9.0.10 and I'm having the same problem. It does look like it only occurs when my logging package is running. Ie if I shut it down, I can create tables in pgAdmin, but as soon as my program is running, I get the errors described above. Any other ideas on how to fix this?

Thanks for all the help!
Matt

-----Original Message-----
From: depesz@depesz.com [mailto:depesz@depesz.com]
Sent: Tuesday, November 13, 2012 5:20 PM
To: Matt@rigminder.com
Cc: pgsql-bugs@postgresql.org
Subject: Re: [BUGS] BUG #7657: Create Table doesn't create columns

On Tue, Nov 13, 2012 at 04:59:53PM +0000, Matt@rigminder.com wrote:

The following bug has been logged on the website:

Bug reference: 7657
Logged by: Matt
Email address: Matt@rigminder.com
PostgreSQL version: 9.1.4
Operating system: Windows Server Standard SP2
Description:

When I run the following statement, the table is created, but there
are no
columns:

CREATE TABLE "mod_1237" ("Collecteddepth" float8 NOT NULL, "Collectedtime"
float8 NOT NULL, "CollectedData" Varchar(45) NOT NULL, "Collectedpass"
float8 NOT NULL, "Collectedmodtime" float8 NOT NULL) WITH (OIDS =
FALSE);

I know I'm not creating a primary key, but that shouldn't prevent the
columns from being generated. When I run this code it also doesn't
generate any errors so everything looks fine until I try to write to
the table. Any ideas as to why this wouldn't work or how to make a
table with the given columns?

most likely you did insert like:
insert into mod_1237 (Collecteddepth) values (...) i.e. you didn't quote the column names. Hence the problem.

In psql, you can do:
\d mod_1237

and you will see the columns are there.

Best regards,

depesz

--
The best thing about modern society is how easy it is to avoid contact with it.
http://depesz.com/

On Wed, Nov 14, 2012 at 10:00:08AM -0600, Matthew Kuss wrote:

Depesz -

I'm fairly sure it's not a problem with something I'm doing wrong
because I've used the same code before. It has to be something wrong
on the DB side. But just to entertain you I did as you requested:

RigMinder_NewDBTest02=# \d test
Table "public.test"
Column | Type | Modifiers
--------+------+-----------

RigMinder_NewDBTest02=#

Before I ran this I created a table using the following:

create table "test" ("column1" text, "column2" float);

It ran without any errors. Yesterday after I sent out the help request
I uninstalled postgresql and installed version 9.0.10 and I'm having
the same problem. It does look like it only occurs when my logging
package is running. Ie if I shut it down, I can create tables in
pgAdmin, but as soon as my program is running, I get the errors
described above. Any other ideas on how to fix this?

What "logging package"?

From what you describe, it looks that it interferes, which would clearly

suggest that it's the guilty part.

I did create the table you said, and it works without any problems:

(depesz@[local]:5920) 17:05:00 [depesz]
$ create table "test" ("column1" text, "column2" float);
CREATE TABLE
(depesz@[local]:5920) 17:05:05 [depesz]
$ \d test
Table "public.test"
Column | Type | Modifiers
---------+------------------+-----------
column1 | text |
column2 | double precision |

I know that this is not helpful, but as you said - when you remove your
program from the situation, everything works ok. check then what the
program does.

Best regards,

depesz

--
The best thing about modern society is how easy it is to avoid contact with it.
http://depesz.com/

"Matthew Kuss" <matt@rigminder.com> writes:

Depesz -
I'm fairly sure it's not a problem with something I'm doing wrong because I've used the same code before. It has to be something wrong on the DB side. But just to entertain you I did as you requested:

RigMinder_NewDBTest02=# \d test
Table "public.test"
Column | Type | Modifiers
--------+------+-----------

RigMinder_NewDBTest02=#

Before I ran this I created a table using the following:

create table "test" ("column1" text, "column2" float);

Hm ... maybe that's creating the table somewhere other than schema
public? What have you got search_path set to? Try
\dt *.test
to see if there's more than one table named "test".

regards, tom lane

Thanks everyone for the help. I have it working now. Unfortunately I'm not
sure why it wasn't working before or what was causing the problem. I spent
all day taking piece after piece out of my "logging package" (Depesz - this
is a program that I wrote and modified over the past few years) until I
found the part that was causing the error. I was still unable to figure out
why this particular part wasn't working so I just replaced it with some
other code I had for a different project that was based off of this problem
code. Long story short, it's working now, but I'm not sure why it's working
this way but not the other way. Hopefully sometime soon I'll have time to go
back and figure out what happened. If I do, I'll let ya'll know!

Thanks again for the help!
Matt

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Wednesday, November 14, 2012 2:03 PM
To: Matthew Kuss
Cc: depesz@depesz.com; pgsql-bugs@postgresql.org
Subject: Re: [BUGS] BUG #7657: Create Table doesn't create columns

"Matthew Kuss" <matt@rigminder.com> writes:

Depesz -
I'm fairly sure it's not a problem with something I'm doing wrong because

I've used the same code before. It has to be something wrong on the DB side.
But just to entertain you I did as you requested:

RigMinder_NewDBTest02=# \d test
Table "public.test"
Column | Type | Modifiers
--------+------+-----------

RigMinder_NewDBTest02=#

Before I ran this I created a table using the following:

create table "test" ("column1" text, "column2" float);

Hm ... maybe that's creating the table somewhere other than schema public?
What have you got search_path set to? Try
\dt *.test
to see if there's more than one table named "test".

regards, tom lane