cannot get simple PostgreSQL insert to work

92
votes

I'm trying to do a simple insert into a postgres table, but am getting an error that the value I'm trying to insert is being interpreted as a column name

INSERT INTO "imageTagBusinessMainCategory"
(id, businessMainCategory)
VALUES
(DEFAULT, "auto dealer")

Where id is set up to be the primary key, and auto increment, and not null. Those are the boxes I ticked when I set up the table in phpPgAdmin.

I'm getting this error though:

ERROR: ERROR: column "auto dealer" does not exist
Query = INSERT
INTO "imageTagBusinessMainCategory"
(id, businessMainCategory)
VALUES
(DEFAULT,
"auto dealer")

I've put my table name in double quotes, as I've read here I should.

And used DEFAULT to auto-increment the id as I've read here I should.

Any ideas? Thanks!

4
sqlpostgresqlstring-constant
use single quotes for 'auto dealer'. – muratgu
Single quotes. Leave off the id. – Paul Tomblin
@muratgu that gives the error: ERROR: ERROR: syntax error at or near "'imageTagBusinessMainCategory'" Position: 13 – 1252748
@PaulTomblin how do you mean "leave off the id"? Thanks! – 1252748
I mean like it shows in @Randy's answer. Except it looks like you need double quotes around the column name as well. – Paul Tomblin

4 Answers

185
votes

Use 'auto dealer' instead. PostgreSQL interprets " as being quotes for identifiers, ' as being quotes for strings.

Also:

  • If this is a new project, just don't use mixed case tables; it is a source of frustration later. Instead of being able to use any case in your SQL statements, you must both quote the identifier name and get the case correct.

  • There is no need to specify id/DEFAULT, you're asking it to do what it would have done already. I haven't met a DBMS that requires you to include columnName/DEFAULT if you want it to put the default value in the column, so I don't think this extra KV pair is going to make what is happening clearer to anyone reading your code later.

9
votes
INSERT INTO "imageTagBusinessMainCategory"
("businessMainCategory")
VALUES
('auto dealer')

EDIT: Added double-quotes around the column name

4
votes

Postgres, Oracle etc expect the column name to be in quotes if they have mixed case. So either create a convention of all small or all caps for your table columns or use quotes as David Faber suggested

INSERT INTO "imageTagBusinessMainCategory"
("businessMainCategory")
VALUES
('auto dealer')
0
votes

On my case I had to change column name from camelCase 'isAdmin' to snake_case 'is_admin'.