psycopg2 unable to insert into specific columns

Question

I have a postgres database that I am accessing via Python using the psycopg2 library. The table I am attempting to insert into looks like this:

 Table "public.programmes" Column | Type | Modifiers | Storage | Stats target | Description -------------+-----------------------------+---------------------------------------------------------+----------+--------------+------------- id | bigint | not null default nextval('programmes_id_seq'::regclass) | plain | | title | character varying | not null | extended | | start | timestamp without time zone | not null | plain | | end | timestamp without time zone | not null | plain | | description | character varying | not null | extended | | genre | character varying | not null | extended | | edited | boolean | not null | plain | |

where the id column was created as a bigserial to autoincrement new entries.

Since the id column will autoincrement, I only try to insert data regarding the remaining fields. I create the data as a list and attempt to insert it like this:

def main(): postConn = psycopg2.connect("dbname=TEST host=127.0.0.1 user=user") postCur = postConn.cursor() prog_data = form_programme_data() #prog_data = ['The Next Step', '2016-05-29T10:20:00', '2016-05-29T10:45:00', "2/34. My Boyfriend's Back: Reality-style drama following a group of dancers. A-Troupe meets their new choreographer and votes for open auditions for the nationals team. Also in HD. [S]", 'Lifestyle', False] postCur.execute("""INSERT INTO programmes(title, start, end, description, genre, edited) VALUES (%s, %s, %s, %s, %s, %s);""", prog_data)

And I get thrown this error:

Traceback (most recent call last): File "convert_db.py", line 58, in <module> main() File "convert_db.py", line 32, in main postCur.execute("""INSERT INTO programmes(title, start, end, description, genre, edited) VALUES (%s, %s, %s, %s, %s, %s);""", prog_data) psycopg2.ProgrammingError: syntax error at or near "end" LINE 1: INSERT INTO programmes(title, start, end, description, genre... ^

If I attempt to insert without specifying the column names, it expects a value for the id which I cannot touch. It doesn't complain about the first two column names provided but doesn't like end for some reason.

Any help is appreciated.

Eugene Yarmash · Accepted Answer · 2016-06-20 15:53:15Z

4

Apparently, end is a key word in PostgreSQL. You need to quote it to make it being interpreted as an identifier:

postCur.execute("""INSERT INTO programmes (title, start, "end", description, genre, edited) VALUES (%s, %s, %s, %s, %s, %s);""", prog_data)

answered Jun 20, 2016 at 15:53

Eugene Yarmash

152k44 gold badges346 silver badges391 bronze badges

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

2 Comments

Shiri Over a year ago

Thanks. You and advance512 were right about end being a keyword and I've added quotes and it's worked as intended! Thanks once again.

benelgiac Over a year ago

Holy ... I've just lost half a day to this. Thanks!

advance512 · Accepted Answer · 2016-06-20 15:53:04Z

I am pretty sure that end is a reserved keyword in Postgres. Try changing the column name to a different name - that will definitely solve the issue. Alternatively, the lazy solution might be to change your code:

postCur.execute("""INSERT INTO programmes('title', 'start', 'end', 'description', 'genre', 'edited') VALUES (%s, %s, %s, %s, %s, %s);""", prog_data)

See if it solves your issue and let us know.

Collectives™ on Stack Overflow

psycopg2 unable to insert into specific columns

2 Answers 2

2 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Linked

Related