PostgreSQL sql command error column 'does not exist'

Question

dxdb=> \d dxtest_loadprofiletosale
                            Table "public.dxtest_loadprofiletosale"
   Column    |   Type   |                               Modifiers                               
-------------+----------+-----------------------------------------------------------------------
 id          | integer  | not null default nextval('dxtest_loadprofiletosale_id_seq'::regclass)
 TransDate   | date     | 
 IssueDate   | date     | 
 CustomerNum | smallint | not null
Indexes:
    "dxtest_loadprofiletosale_pkey" PRIMARY KEY, btree (id)

dxdb=> INSERT INTO dxtest_loadprofiletosale(id, TransDate, IssueDate, CustomerNum) VALUES(1, '2015-03-04','2015-01-01',01);
ERROR:  column "transdate" of relation "dxtest_loadprofiletosale" does not exist
LINE 1: INSERT INTO dxtest_loadprofiletosale(id, TransDate, IssueDat...

excuse me,I already has the column "transdate", why it said does not exist?

user330315 · Accepted Answer · 2015-03-04 07:56:41Z

8

Your column is called "TransDate" not transdate. You created your table using double quotes for the column names, which makes them case sensitive and you must use double quotes all the time:

INSERT INTO dxtest_loadprofiletosale
  (id, "TransDate", "IssueDate", "CustomerNum") 
VALUES
  (1, '2015-03-04','2015-01-01',01);

More details about SQL identifiers are in the manual:
http://www.postgresql.org/docs/current/static/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS

In general it is better to never use double quotes - it will give you a lot less trouble in the long run.

answered Mar 4, 2015 at 7:56

user330315

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

1 Comment

Joe Lin Over a year ago

thank you very much,then I try :INSERT INTO dxtest_loadprofiletosale(id, "TransDate", "IssueDate", "CustomerNum") VALUES(1, '2015-03-04','2015-01-01',01); it works!

Collectives™ on Stack Overflow

PostgreSQL sql command error column 'does not exist'

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related