-1

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?

I'm using PostgreSQL version 9.1.4 on Windows Server Standard SP2

user1821563
  • 9
  • -1 Cross posted to PostgreSQL mailing list here http://archives.postgresql.org/message-id/E1TYJq5-00038e-4s@wrigleys.postgresql.org , didn't show the actual INSERT statement or error message. – Craig Ringer Nov 14 '12 at 00:58

2 Answers2

1

This was answered by Hubert ("Depesz") on the PostgreSQL mailing list, where the question was cross-posted. For the reference of anyone reading it here, here is the answer. Since it hasn't been added to the archives yet that link will 404 for a little while, so I'll reproduce Herbert's answer below:

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

In other words: If you double-quote names in table definitions, double-quote them wherever you refer to them too.

This is a FAQ; see:

and more.

Community
  • 1
  • 1
Craig Ringer
  • 307,061
  • 76
  • 688
  • 778
0

Just get rid of the quotes as they are not needed.

CREATE TABLE mod_1237 (Collecteddepth float8 NOT NULL, Collectedtime float8 NOT NULL, CollectedDate Varchar(45) NOT NULL, Collectedpass float8 NOT NULL, Collectedmodtime float8 NOT NULL) WITH (OIDS = FALSE);
A. Gilfrin
  • 302
  • 1
  • 8
  • The quotes are necessary if some other software is going to be using `"Collecteddepth"` as a column name. – mu is too short Nov 13 '12 at 18:08
  • I can't take out the quotes because the letter case is required. The example above was just an example, but it was giving me the same problem as the table that I was actually trying to create. Any other ideas? – user1821563 Nov 13 '12 at 19:50