Psycopg2/PostgreSQL 11.9: Syntax error at or near "::" when performing string->date type cast

Question

I am using psycopg2 to create a table partition and insert some rows into this newly created partition. The table is RANGE partitioned on a date type column.

Psycopg2 code:

conn = connect_db()
cursor = conn.cursor()
sysdate = datetime.now().date()
sysdate_str = sysdate.strftime('%Y%m%d')
schema_name = "schema_name"
table_name = "transaction_log"

# Add partition if not exists for current day
sql_add_partition = sql.SQL("""
    CREATE TABLE IF NOT EXISTS {table_partition}
    PARTITION of {table}
    FOR VALUES FROM (%(sysdate)s) TO (maxvalue);
""").format(table = sql.Identifier(schema_name, table_name), table_partition = sql.Identifier(schema_name, f'{table_name}_{sysdate_str}'))
print(cursor.mogrify(sql_add_partition, {'sysdate': dt.date(2015,6,30)}))
cursor.execute(sql_add_partition, {'sysdate': sysdate})

Formatted output of cursor.mogrify():

CREATE TABLE IF NOT EXISTS "schema_name"."transaction_log_20211001"
PARTITION of "schema_name"."transaction_log"
FOR VALUES FROM ('2021-10-01'::date) TO (maxvalue);

Error received:

ERROR:  syntax error at or near "::"
LINE 3: for values FROM ('2021-10-01'::date) TO (maxvalue);

Interestingly enough, psycopg2 appears to be attempting to cast the string '2021-10-01' to a date object with the "::date" syntax, and according to the postgreSQL documentation, this appears to be valid (although there are no explicit examples given in the docs), however executing the statement with both pyscopg2 and in a postgreSQL query editor yields this syntax error. However, executing the following statement in a postgreSQL SQL editor is successful:

CREATE TABLE IF NOT EXISTS "schema_name"."transaction_log_20211001"
PARTITION of "schema_name"."transaction_log"
FOR VALUES FROM ('2021-10-01') TO (maxvalue);

Any ideas on how to get psycopg2 to format the query correctly?

Try converting it to a string in Python and interpolating that. — Laurenz Albe, Oct 01 '21 at 11:24
@LaurenzAlbe That should work. Although I'm curious to know why both the psycopg2 docs and the postgresql docs indicate the syntax is valid despite this syntax error. I'll leave the question open for now. — Otis Sistrunk, Oct 01 '21 at 12:15
The syntax is not valid. You need to supply a constant, not an expression like a type cast. — Laurenz Albe, Oct 01 '21 at 12:17
The reason is from [Create Table](https://www.postgresql.org/docs/11/sql-createtable.html): 'Each of the values specified in the partition_bound_spec is a literal, NULL, MINVALUE, or MAXVALUE. Each literal value must be either a numeric constant that is coercible to the corresponding partition key column's type, or a string literal that is valid input for that type.' Added this as update to my answer. — Adrian Klaver, Oct 01 '21 at 18:08

Adrian Klaver · Accepted Answer · 2021-10-01T18:11:29.673

To follow up on @LaurenzAlbe comment:


sql_add_partition = sql.SQL("""
    CREATE TABLE IF NOT EXISTS {table_partition}
    PARTITION of {table}
    FOR VALUES FROM (%(sysdate)s) TO (maxvalue);
""").format(table = sql.Identifier(schema_name, table_name), table_partition = sql.Identifier(schema_name, f'{table_name}_{sysdate_str}'))
print(cursor.mogrify(sql_add_partition, {'sysdate': '2021-10-01'}))

#OR

sql_add_partition = sql.SQL("""
    CREATE TABLE IF NOT EXISTS {table_partition}
    PARTITION of {table}
    FOR VALUES FROM ({sysdate}) TO (maxvalue);
""").format(table = sql.Identifier(schema_name, table_name), 
table_partition = sql.Identifier(schema_name, f'{table_name}_{sysdate_str}'),
sysdate=sql.Literal('2021-10-01'))
print(cursor.mogrify(sql_add_partition))

#Formatted as

CREATE TABLE IF NOT EXISTS "schema_name"."transaction_log_20211001"
    PARTITION of "schema_name"."transaction_log"
    FOR VALUES FROM ('2021-10-01') TO (maxvalue);

Pass the date in as a literal value instead of a date object. psycopg2 does automatic adaptation of date(time) objects to Postgres date/timestamp types(Datetime adaptation) which is what is biting you.

UPDATE

Per my comment, the reason why it needs to be a literal is explained here Create Table:

Each of the values specified in the partition_bound_spec is a literal, NULL, MINVALUE, or MAXVALUE. Each literal value must be either a numeric constant that is coercible to the corresponding partition key column's type, or a string literal that is valid input for that type.

Psycopg2/PostgreSQL 11.9: Syntax error at or near "::" when performing string->date type cast

1 Answers1