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

Answer 1

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.

Answer 2

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

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

Answer 3

i have my story with my database for Qgis + POSTGRES DATA i try update my table with code by Navicate UPDATE "MangLuoi"."A_Lam_D310" SET "MangLuoi"."A_Lam_D310"."Nguon"='Lâm' and ERRO : 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"."...

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

IT show ok :)) by Message: Time: 0.035s

Affected rows: 29

Thank for share and answer.