Syntax error while creating a table in a postgresql db via Flask-sqlalchemy

Question

I am trying to create a new table in a postgresql db I have in an AWS EC2 instance using flask-sqlalchemy as follows:

def table_create():
    conn = psycopg2.connect(
        dbname='XYZ',
        user='ABC',
        host='ec2-xx-xxx-xxx-xxx.eu-central-1.compute.amazonaws.com',
        password='mypassword')
    cursor = conn.cursor()
    create_table_query = '''CREATE TABLE price_ht
    (ID SERIAL PRIMARY KEY NOT NULL,
    country_code CHAR(2) NOT NULL,
    value float(2) NOT NULL,
    start timestamp with time zone NOT NULL,
    end timestamp with time zone NOT NULL,
    price_type VARCHAR(32) NOT NULL)'''
    cursor.execute(create_table_query)
    cursor.execute("CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;")
    cursor.execute(
        "SELECT create_hypertable('price_ht', 'id', chunk_time_interval=>24)")
    conn.commit()
    print("Table created")

When I run the code, I get the following error:

Exception has occurred: SyntaxError syntax error at or near "end" LINE 6: end timestamp with time zone NOT NULL,

The values stored have to be as follows:

Can anyone please tell me what is the mistake I am doing?

Answer 1

end is a reserved word in Postgresql. It is usually "bad practice" to use these when naming things and in your case it yields a syntax error.

If you really want to use it - you will have to enclose it in ".

CREATE TABLE price_ht
    (ID SERIAL PRIMARY KEY NOT NULL,
    country_code CHAR(2) NOT NULL,
    value float(2) NOT NULL,
    start timestamp with time zone NOT NULL,
    "end" timestamp with time zone NOT NULL,
    price_type VARCHAR(32) NOT NULL)

• https://www.postgresql.org/docs/current/sql-keywords-appendix.html