PostgreSQL insert query

Question

I try to insert a single line to log table, but it throws an error message . ><

The log table structure is like this:

no         integer  NOT NULL nextval('log_no_seq'::regclass)    
ip         character varying(50)    
country    character varying(10)    
region     character varying(10)    
city       character varying(50)    
postalCode character varying(10)    
taken      numeric  
date       date

and my query:

INSERT INTO log (ip,country,region,city,postalCode,taken,date) VALUES 
("24.24.24.24","US","NY","Binghamton","11111",1,"2011-11-09")

=> ERROR: column "postalcode" of relation "log" does not exist

second try query : (without postalcode)

INSERT INTO log (ip,country,region,city,taken,date) VALUES 
("24.24.24.24","US","NY","11111",1,"2011-11-09")

=> ERROR: column "24.24.24.24" does not exist

I don't know what I did wrong...

And PostgreSQL does not have datetime type? (2011-11-09 11:00:10)

PostgreSQL has as datetime type, it calls it timestamp. Also, the product is PostgreSQL, not Postgre. If you were going to shorten it, it'd be Postgres (that's actually the name it had before gaining SQL support). — derobert
– derobert, Commented Nov 9, 2011 at 19:12
Double quotes are for quote identifiers (such as table and column names), single quotes are for string literals. — mu is too short
– mu is too short, Commented Nov 9, 2011 at 19:15
What happens is that people learn on MySQL, where timestamp was originally defined as auto-updating with the latest timestamp if it was the first timestamp column. After realizing they'd used a SQL reserved keyword and type for something other than what it was designated to do, MySQL, stuck with keeping backward compatibility, needed a timestamp type field that didn't autoupdate, so they invented datetime as a type. The SQL spec defines the Timestamp and timestamp with timezone types. Datetime is MySQL's creation. Wish they'd have at least reversed the meanings way back when... — Scott Marlowe
– Scott Marlowe, Commented Nov 10, 2011 at 3:15

C. Ramseyer · Accepted Answer · 2011-11-09 19:07:00Z

11

Try single quotes (e.g. '2011-11-09')

answered Nov 9, 2011 at 19:07

C. Ramseyer

2,3922 gold badges18 silver badges23 bronze badges

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

2 Comments

wildplasser Over a year ago

Also: don't use MixedCase. Someday it will bite back at you. Also: date is a reserved word (typename) don't use it, even if it appears to work.

C. Ramseyer Over a year ago

Yep that's sound advice. The postalCode error is probably already mixed-case-ass-biting.

Erwin Brandstetter · Accepted Answer · 2011-11-09 19:37:07Z

5

PostgreSQL has a "datetime" type: timestamp. Read the manual here.

The double-qutes "" are used for identifiers if you want them as is. It's best you never have to use them as @wildplasser advised.

String literals are enclosed in single quotes ''.

Start by reading the chapter Lexical Structure. It is very informative. :)

edited Nov 9, 2011 at 19:37

answered Nov 9, 2011 at 19:31

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

Comments

Jan Marek · Accepted Answer · 2011-11-10 07:35:58Z

2

Try it rewrite in this way:

INSERT INTO log (ip,country,region,city,"postalCode",taken,date) VALUES ('24.24.24.24','US','NY','Binghamton','11111',1,'2011-11-09');

When you are using mixed case in the name of column, or reserved words (such as "column", "row" etc.), you have to use double quotes, instead of values, where you have to use a single ones, as you can see in the example.

answered Nov 10, 2011 at 7:35

Jan Marek

11.3k3 gold badges24 silver badges19 bronze badges

Collectives™ on Stack Overflow

PostgreSQL insert query

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related