ERROR: column of relation does not exist PostgreSQL ,Unable to run insert query

Question

Hi I am trying to insert into a table tester3 it fails when i use the below syntax

insert into tester3 (UN0, UN1) values ( 1, 'jishnu1');

but below insert

insert into tester3 values ( 1, 'jishnu1');

works fine.

mydb=# CREATE TABLE tester3
mydb-#    (
mydb(#     "UN0" integer,
mydb(#     "UN1" VARCHAR(40)
mydb(#    );
CREATE TABLE
mydb=# insert into tester3 (UN0, UN1) values ( 1, 'jishnu1');
ERROR:  column "un0" of relation "tester3" does not exist
mydb=# \d tester3
           Table "public.tester3"
 Column |         Type          | Modifiers
--------+-----------------------+-----------
 UN0    | integer               |
 UN1    | character varying(40) |

I think i am missing something very trivial, I tried several other column names some of them works fine and some are not. I am confused.

Does PostgreSQL have restriction in column names for which the first syntax of insert query works?

Edit :

Checkout Girdon Linoff's answer here , as Frank Heikens pointed out the other column names which were working without quotes were in lower case.

Lower case column is the standard within PostgreSQL and also works without quotes

You can find details about identifier names here: postgresql.org/docs/current/static/sql-syntax-lexical.html — Ihor Romanchenko
– Ihor Romanchenko, Commented Jun 26, 2015 at 14:52
Very useful for your issue. stackoverflow.com/questions/7651417/… — Nadeem Taj
– Nadeem Taj, Commented Aug 16, 2021 at 18:23

Gordon Linoff · Accepted Answer · 2020-04-27 17:32:24Z

69

If you define the columns with double quotes, then you generally need to use them when you refer to the column:

insert into tester3 ("UN0", "UN1")
     values ( 1, 'jishnu1');

I would suggest you remove the double quotes from the column names in the CREATE TABLE statement.

You don't need the double quotes if the name is all lower case.

edited Apr 27, 2020 at 17:32

answered Jun 26, 2015 at 11:39

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

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

5 Comments

sh03 Over a year ago

Weird, the description of the table does not show the double quotes. But yes, this appears to be the correct answer.

Frank Heikens Over a year ago

@JishnuPrathap: That's all lower case, that's the standard within PostgreSQL and also works without quotes.

Unglückspilz Over a year ago

@JishnuPrathap you created those columns explicitly as upper case, but then later query for them in a case-insensitive manner, and so they are not found. When you put the names in quotes, the lookup will be case-sensitive.

jave.web Over a year ago

@Unglückspilz you should put that out as an answer, because that's the generic issue core :)

Afzaal Ahmad Zeeshan Over a year ago

Even if the tables are created without double quotes, these quotes are needed. I created tables in pgAdmin 4 and used .NET Core to insert the data, and it was only possible with the quotation.

Brave Soul · Accepted Answer · 2023-06-05 08:15:14Z

11

Use double quotes to your column names and single quotes to your values

insert into tester3 ("UN0", "UN1") values ( 1, 'jishnu1');

edited Jun 5, 2023 at 8:15

answered Jun 26, 2015 at 11:40

Brave Soul

3,6504 gold badges33 silver badges51 bronze badges

Comments

Sandeep Kumar · Accepted Answer · 2025-02-11 14:52:43Z

-3

I have my story with my database for Qgis + POSTGRES

I tried to update my table with code by Navicate:

UPDATE "MangLuoi"."A_Lam_D310" SET "MangLuoi"."A_Lam_D310"."Nguon"='Lâm'

I recieved the below error:

SQL]UPDATE "MangLuoi"."A_Lam_D310" SET "MangLuoi"."A_Lam_D310"."Nguon"='Lâm'

[Err] ERROR:  column "MangLuoi" of relation "A_Lam_D310" does not exist
LINE 1: UPDATE "MangLuoi"."A_Lam_D310" SET "MangLuoi"."A_Lam_D310"."...

When I tried the following without table name prefix, it worked:

UPDATE "MangLuoi"."A_Lam_D310" SET "Nguon"='Lâm'

Output:

Time: 0.035s
Affected rows: 29

edited Feb 11 at 14:52

Sandeep Kumar

2,5935 gold badges35 silver badges41 bronze badges

answered Aug 14, 2021 at 9:36

Kijutonet Kawasaki

17

1 Comment

Stanley Over a year ago

This answer is difficult to read. Please see Stackoverflow's Style guide.

Collectives™ on Stack Overflow

ERROR: column of relation does not exist PostgreSQL ,Unable to run insert query

3 Answers 3

5 Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related