[SOLVED] pg8000 creating a table confuses column name with type

pg8000 creating a table confuses column name with type

I am using pg8000 for Postgres api in my python package. I created a function that is to create a table is no such table exists. The function is below:

    def create_tables(self, cur, tables):

       for i, table in enumerate(tables):
            name = 'important_' + table
            query = '''CREATE TABLE IF NOT EXISTS {} (
                        {}  CHAR(9),
                        {}  DATE,
                        {}  DECIMAL,
                        {}  TIME,
                        {}  TIME,
                        {}  NUMERIC,
                        {}  CHAR,
                        {}  CHAR,
                        {}  DECIMAL 
                    )
                '''.format(name, "key", "date", "cycle_sequence", "sent_time","processed_time", "amount", "sender", "receiver", "jumbo")

            cur.execute(query)

The query runs without error (in program and an online fiddle here) except that the table column names are for example: "key CHAR(9)", "date DATE", etc. So the query is executing but not recognizing the syntax. But the query syntax is correct according to this. Any tips?

NOTE 1: There is a similar question here but I do not have similar symptoms because I do not get a syntax error. My query is successful.

Solution

This seems odd to me but the 'fix' was to remove the space before the '(' in the column specification portion of the query as below. With the space the (name, type) tuples are interpreted as a single string, without the space they are correctly interpreted as column name and type.

query = '''CREATE TABLE IF NOT EXISTS {} (...

query = '''CREATE TABLE IF NOT EXISTS {}(...

For the record I'm using: PyCharm 2019.2.3 (Community Edition) with Anaconda 1.9.7. The db api is the 'pg8000' package.