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!
Single row INSERTs So, if your app and database are in different regions and latency is 5ms for example, then you can expect to see around 100 INSERTs (1000 milliseconds /(5ms+5ms)) per second. In the same region on AWS with lets say 1ms latency, this number can go up to ~500 INSERTs per second.
The INSERT INTO SELECT statement copies data from one table and inserts it into another table. The INSERT INTO SELECT statement requires that the data types in source and target tables match. Note: The existing records in the target table are unaffected.
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.
INSERT INTO "imageTagBusinessMainCategory"
("businessMainCategory")
VALUES
('auto dealer')
EDIT: Added double-quotes around the column name
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')
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With