CODEWITHSUNDEEP
sqlstringpostgresqlsql-updatesql-insert
nad
nad

Reputation: 2850

PostgreSQL ERROR column does not exist refers to a column value

I have a table projects in postgreSQL that looks like below

id.    project_name.   col3.  col4
1111.   test.          ...    ...

I want to insert a new row for a new id only if the id does not already exist. So I wrote below query

INSERT INTO projects(id, project_name)
VALUES("1234", "Test_Project")
ON CONFLICT (id)
DO NOTHING

But it throws me an error

Query 1 ERROR: ERROR:  column "1234" does not exist
LINE 2: VALUES("1234", "Test_Project_...

Please suggest.

** EDIT**

The id column in the table is a uuid and not unique. There can be multiple rows with the same id. Based on GMBs suggestion, I tried below

INSERT INTO projects(id, project_name)
    VALUES('1234', 'Test_Project')
    ON CONFLICT (create unique index on projects(id))
    DO NOTHING

I get below error with it

Query 1 ERROR: ERROR:  syntax error at or near "create"
LINE 3: ON CONFLICT (create unique index on projects(id...

I am new to this so I am sure missing something obvious.

Upvotes: 2

Views: 4241

Answers (1)

GMB
GMB

Reputation: 222462

Use single quotes for literal strings. Double quotes stand for identifiers (such as column names or table names) - hence the error that you are getting:

INSERT INTO projects(id, project_name)
VALUES('1234', 'Test_Project')
ON CONFLICT (id)
DO NOTHING

That said, I would suspect that id is of integer datatype. If so, don't quote it at all:

INSERT INTO projects(id, project_name)
VALUES(1234, 'Test_Project')
ON CONFLICT (id)
DO NOTHING

Upvotes: 8

Related Questions