CODEWITHSUNDEEP
pythonpostgresqlpsycopg2
some_programmer
some_programmer

Reputation: 3528

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

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:

enter image description here

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

Upvotes: 0

Views: 312

Answers (1)

madflow
madflow

Reputation: 8480

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)

Upvotes: 1

Related Questions