34

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

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

but

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

is working 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 someother column names some of them works fine and some are not working. I am confused. Does PostgreSQL have restriction in column names for which works the 1st 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

Jishnu Prathap
  • 1,955
  • 2
  • 21
  • 37
  • 2
    You can find details about identifier names here: http://www.postgresql.org/docs/current/static/sql-syntax-lexical.html – Ihor Romanchenko Jun 26 '15 at 14:52
  • Very useful for your issue. https://stackoverflow.com/questions/7651417/escaping-keyword-like-column-names-in-postgres – Nadeem Taj Aug 16 '21 at 18:23

3 Answers3

61

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.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • 4
    Weird, the description of the table does not show the double quotes. But yes, this appears to be the correct answer. – Shoe Jun 26 '15 at 11:40
  • 4
    @JishnuPrathap: That's all lower case, that's the standard within PostgreSQL and also works without quotes. – Frank Heikens Jun 26 '15 at 11:52
  • 5
    @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. – Unglückspilz May 06 '16 at 08:43
  • @Unglückspilz you should put that out as an answer, because that's the generic issue core :) – jave.web Sep 03 '19 at 21:49
  • 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. – Afzaal Ahmad Zeeshan Apr 27 '20 at 15:49
10

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

insert into tester3 ("UN0", "UN1") values ( 1, 'jishnu1');
Ameya Deshpande
  • 3,580
  • 4
  • 30
  • 46
-2

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.

Kijutonet Kawasaki
  • 11
  • 3
    This answer is difficult to read. Please see Stackoverflow's [Style guide](https://stackoverflow.com/editing-help). – Stanley Aug 17 '21 at 04:58