cannot get simple PostgreSQL insert to work

Question

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!

@muratgu that gives the error: `ERROR: ERROR: syntax error at or near "'imageTagBusinessMainCategory'" Position: 13` — 1252748, Sep 14 '12 at 16:31
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, Sep 14 '12 at 16:39

Matt · Accepted Answer · 2012-09-14T16:39:13.680

227

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.

edited Sep 14 '12 at 16:39

answered Sep 14 '12 at 16:32

Matt

4,515
5
22
29

1

that gives me this error `ERROR: ERROR: column "businessmaincategory" of relation "imageTagBusinessMainCategory" does not exist` – 1252748 Sep 14 '12 at 16:34
2

You probably created the column in mixed case, just like the table name. You must also quote the column name if this is the case. Stop using mixed case in naming, and you will save everyone a lot of pain. – Matt Sep 14 '12 at 16:38
@Matt I'm facing the same problem and trying to use pg with C++ is a pain when I have to type those double-quotes. Isn't there a workaround like some setting that would eliminate the need for these quotes? – itsols Jul 19 '13 at 16:10
@itsols Fist bullet of the "Also" would eliminate the need for the quotes; define your identities only using [a-z0-9] and don't specify them as having any particular case (define without quoting). Then you can refer to them however you want without quotes forever. If you or someone else decided that it was really important to have table names with spaces or in camel case, have fun quoting everywhere! – Matt Jul 19 '13 at 17:47
Point well taken Matt... I guess I've just got used to CamelCase in a way - it's been a long time (over 20 years)... Actually the trouble is not in double-quoting (yes, it would be easier without it), but the real pain is having to escape them in strings. I didn't see this coming until I put C++ and pg together. Thanks anyway for your inputs! – itsols Jul 19 '13 at 18:03

score 12 · Answer 2 · edited Sep 14 '12 at 17:14

12

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

EDIT: Added double-quotes around the column name

edited Sep 14 '12 at 17:14

David Faber

12,277
2
29
40

answered Sep 14 '12 at 16:33

Randy

16,480
1
37
55

1

Hi, thanks. that gives this error: `ERROR: ERROR: column "businessmaincategory" of relation "imageTagBusinessMainCategory" does not exist` – 1252748 Sep 14 '12 at 16:36
Sounds like you need to put the column name in double-quotes too. One reason why mixed-case table names in PostgreSQL are a bad idea. – David Faber Sep 14 '12 at 16:38
@DavidFaber yeah, you're right. makes it a bit difficult to read though.. can i use dashes? ^^ – 1252748 Sep 14 '12 at 16:42
1

@DavidFaber: personally I think identifiers that need quoting (e.g. mixed case) are a bad idea in *any* DBMS – Sep 14 '12 at 16:44
1

Keep in mind that everywhere EXCEPT when creating the identifier, you can refer to it in mixed case and without quotes. So the table named `thishasareallylongname` could be accessed using `select * from thisHasAReallyLongName`. – Matt Sep 14 '12 at 16:46
@a_horse_with_no_name, I agree with you. I don't even like mixed-case identifiers in SQL Server, which supports mixed-case table- and column-names. – David Faber Sep 14 '12 at 16:54
@thomas, I would use underscores ('_') over dashes any day of the week for DB identifiers. – David Faber Sep 14 '12 at 16:55
@thomas thanks, David. I decided to go with that as well. Cheers! – 1252748 Sep 14 '12 at 17:49
The whole idea of the hungarian notation or camel case was to increase readability of code (at least for me)... So using entirely lowercase field names is not a good option for me. I wish there's a good (I mean easy) way to get about this. – itsols Jul 19 '13 at 16:16

score 5 · Answer 3 · answered Apr 21 '15 at 06:48

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')

score 0 · Answer 4 · answered Jun 07 '21 at 14:57

0

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

answered Jun 07 '21 at 14:57

Cristea

913
10
15

cannot get simple PostgreSQL insert to work

4 Answers4

Linked

Related