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.

score 4 · Accepted Answer · answered Jun 20 '16 at 15:53

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 '16 at 15:53

Eugene Yarmash

142,882
41
325
378

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. – Shiri Jun 20 '16 at 16:05
1

Holy ... I've just lost half a day to this. Thanks! – benelgiac Feb 26 '20 at 14:35

score 1 · Answer 2 · answered Jun 20 '16 at 15:53

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.

psycopg2 unable to insert into specific columns

2 Answers2