PostgreSQL can't insert data into table (column does not exist) [duplicate]

Question

PostgreSQL 13, pgAdmin 4.

I have a table "Student" with the following columns:

student_id (text, primary key)
last_name (text)
first_name (text)
major (text)
credit_hours (int)

I'm simply trying to add another student to this table, which I am attempting to do with the query below

INSERT INTO Public."Student" ("student_id", "last_name", "first_name", "major", "credit_hours")
VALUES ("S201217","BAR","FOO","CSC",100);

but when I run this query, I get this output

ERROR:  column "S201217" does not exist
LINE 2: VALUES ("S201217","BAR","FOO","CSC",100);
                ^
SQL state: 42703
Character: 105

For such a basic error, I really must be missing something here, why is this query not able to add a row?

devReddit · Accepted Answer · 2021-07-24 23:59:29Z

5

Double quotes are used to quote columns. Use single quotation instead of double.

INSERT INTO Public."Student" ("student_id", "last_name", "first_name", "major", "credit_hours")
VALUES ('S201217','BAR','FOO','CSC',100);

edited Jul 24, 2021 at 23:59

answered Jul 15, 2021 at 23:41

devReddit

2,9871 gold badge8 silver badges21 bronze badges

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

3 Comments

tanman Over a year ago

This solved the issue lmao, thanks

user330315 Over a year ago

Not only "in PostgreSQL" this is what the SQL standard requires.

devReddit Over a year ago

@tanman, if it answers your query, please consider accepting it.

Andrey · Accepted Answer · 2021-07-16 03:07:19Z

1

DB engine thinks this "S201217" is the column name, do single quotes and you'll be fine

INSERT INTO Public."Student" (student_id, last_name, first_name, major, credit_hours)
VALUES ('S201217','BAR','FOO','CSC',100);

You need "" if you using it for naming such has capitals of a break-in the string, for example, your table, as it starts with capital S it should be in double quotes, for column names no need it. or for example, if doing select

Select now() as 'Time now'

because alias has empty string and capital letter you need to include in ""

edited Jul 16, 2021 at 3:07

answered Jul 16, 2021 at 3:01

Andrey

1,77914 gold badges40 silver badges75 bronze badges

1 Comment

Kai Durai Over a year ago

saved me so much pain, thank you

Collectives™ on Stack Overflow

PostgreSQL can't insert data into table (column does not exist) [duplicate]

2 Answers 2

3 Comments

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Linked

Related