0

I'm doing this (Postgre)SQL excercize for a course i'm following; the objective is fairly simple i suppose:

Insert data into courses using the data provided. Make sure id is system generated.

for reference, this is the query to create said table

%%sql
CREATE TABLE courses (
course_id SERIAL PRIMARY KEY,
course_name VARCHAR(60) NOT NULL,
course_author VARCHAR(40) NOT NULL,
course_status course_status,
course_published_dt TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

and this is the query that i'm trying to run

%%sql
INSERT INTO courses (course_id, course_name, course_author, course_status, 
course_published_dt)
VALUES 
("Programming using Python", "Bob Dillon", "published", "2020-09-30"),
("Data Engineering using Python", "Bob Dillon", "published", "2020-07-15"),
("Data Engineering using Scala", "Elvis Presley", "draft"),
("Programming using Scala", "Elvis Presley", "published", "2020-05-12"),
("Programming using Java", "Mike Jack", "inactive", "2020-08-10"),
("Web Applications - Python Flas", "Bob Dillon", "inactive", "2020-07-20"),
("Web Applications - Java Spring", "Mike Jack", "draft"),
("Pipeline Orchestration - Python", "Bob Dillon", "draft"),
("Streaming Pipelines - Python", "Bob Dillon", "published", "2020-10-05"),
("Web Applications - Scala Play", "Elvis Presley", "inactive", "2020-09-30"),
("Web Applications - Python Django", "Bob Dillon", "published", "2020-06-23"),
("Server Automation - Ansible", "Uncle Sam", "published", "2020-07-05");

when i run it, the message below appears:

(psycopg2.errors.UndefinedColumn) column "Programming using Python" does not exist LINE 3: ("Programming using Python", "Bob Dillon", "published", ...

i really don't get why it takes the first value of that row as the column name... what am i doing wrong? The very same slides i'm following picture queries done the same way!

castoldie
  • 1
  • 2
    Double quotes are used to indicate column names (identifiers). Use single quotes around character literals: 'this is a value, not a column name'. "this is a column name". – Jon Armstrong May 28 '22 at 13:37
  • See the following: https://stackoverflow.com/questions/1992314/what-is-the-difference-between-single-and-double-quotes-in-sql – Jon Armstrong May 28 '22 at 13:42
  • 1
    Note: Some databases can use double quotes for string/character literals. You might find some instructional material using double quotes. But that is not standard behavior. Use single quotes. Example: https://dbfiddle.uk/?rdbms=postgres_14&fiddle=09d696fdbba9d5bfc81e3d939e70f338 – Jon Armstrong May 28 '22 at 13:54
  • Hi @castoldie, does your create table code work ? – VBoka May 28 '22 at 14:11
  • https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-STRINGS –  May 28 '22 at 14:24
  • Remember single/double quotes mean very different things in SQL though they are synonymous and interchangeable in Python. – Parfait May 28 '22 at 14:47
  • just remove `course_id` from you `insert` statement: `INSERT INTO courses (course_name, course_author, course_status, course_published_dt) ...` and use single quotes instead of double quotes – Alexey May 28 '22 at 15:10
  • @JonArmstrong thank you sir!! I had no idea that was a thing – castoldie May 28 '22 at 15:17
  • @Vboka yes it does – castoldie May 28 '22 at 15:17
  • @Alexey course_id is required by the excercize and i don´t have to specify it because it's a SERIAL PRIMARY KEY – castoldie May 28 '22 at 15:17
  • Hi @castoldie can you explain to me what kind of type is course_status ? I can not create table with your code : https://dbfiddle.uk/?rdbms=postgres_14&fiddle=03bc59952f20d0bb767eaacc1d9f3f64 – VBoka May 29 '22 at 07:58
  • sorry @VBoka this cell was not included: CREATE TYPE course_status AS ENUM ('published', 'draft', 'inactive'); – castoldie May 30 '22 at 13:40

0 Answers0