pg8000 creating a table confuses column name with type

Question

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.

Unrelated, but: [don't use the `char` type](https://wiki.postgresql.org/wiki/Don%27t_Do_This#Don.27t_use_char.28n.29) — , Oct 01 '19 at 19:54
The usual way to deal with things like that: print the generated statement and run it manually (e.g. in `psql`) . Then **[edit]** your question and add the error message you get. — , Oct 01 '19 at 19:56
"*not recognizing the syntax*" would mean it doesn't run. If it runs successfully, it **does** recognize the syntax. — , Oct 02 '19 at 05:34

score 0 · Accepted Answer · answered Oct 02 '19 at 14:21

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.

pg8000 creating a table confuses column name with type

1 Answers1