ERROR: column "username" of relation "public.ae_User" does not exist - Postgresql

Question

I'm learning postgresql and make this table in elephantsql:

CREATE TABLE "public.ae_User" (
    "Id" serial NOT NULL,
    "Name" character varying(30) NOT NULL,
    "Username" character varying(16) NOT NULL UNIQUE DEFAULT 'Guest',
    "Email" character varying(30) NOT NULL,
    "Password" character varying(120) NOT NULL,
    "Salt" character varying(40) NOT NULL,
    "User_type" int NOT NULL DEFAULT '0',
    "Deleted" BOOLEAN NOT NULL DEFAULT 'false',
    CONSTRAINT "ae_User_pk" PRIMARY KEY ("Id")
) WITH (
  OIDS=FALSE
);

But when I try to do an insert into:

INSERT INTO "public"."public.ae_User" (Name, Username, Email, Password, Salt, User_type, Deleted)
VALUES ("Miguel", "adminMiguel", "miguel@gmail.com", "admin", "bla", 3, false)

It returns:

ERROR:  column "username" of relation "public.ae_User" does not exist
LINE 1: INSERT INTO "public"."public.ae_User" (Username, Email, Pass...

score 1 · Accepted Answer · answered Nov 14 '21 at 09:22

You should use a double-quoted in the column name because Postgres is folded column to lowercase.

If the column contains uppercase (and/or other syntax violations) you have to use double-quoted

On other hand for text or varchar data has been passed to the column you have to use single-quoted

INSERT INTO "public"."public.ae_User" ("Name", "Username", "Email", "Password", "Salt", "User_type", "Deleted")
VALUES ('Miguel', 'adminMiguel', 'miguel@gmail.com', 'admin', 'bla', 3, false)

P.S:

In my opinion, better change your structure like below:

CREATE TABLE public."ae_User" (
    "Id" serial NOT NULL,
    "Name" character varying(30) NOT NULL,
    "Username" character varying(16) NOT NULL UNIQUE DEFAULT 'Guest',
    "Email" character varying(30) NOT NULL,
    "Password" character varying(120) NOT NULL,
    "Salt" character varying(40) NOT NULL,
    "User_type" int NOT NULL DEFAULT '0',
    "Deleted" BOOLEAN NOT NULL DEFAULT 'false',
    CONSTRAINT "ae_User_pk" PRIMARY KEY ("Id")
) WITH (
  OIDS=FALSE
);

INSERT INTO public."ae_User" ("Name", "Username", "Email", "Password", "Salt", "User_type", "Deleted")
VALUES ('Miguel', 'adminMiguel', 'miguel@gmail.com', 'admin', 'bla', 3, false)

score 0 · Answer 2 · answered Nov 14 '21 at 09:14

0

your error is in INSERT INTO "public"."public.ae_User" to be replaced by INSERT INTO "public"."ae_User"

answered Nov 14 '21 at 09:14

Edouard

6,577
1
9
20

ERROR: column "username" of relation "public.ae_User" does not exist - Postgresql

2 Answers2