Answers for "insert array in postgresql"

postgresql append array

array_append(anyarray, anyelement);

insert record into table postgresql

try:
        connection = psycopg2.connect(**postgres_credentials())
        cursor = connection.cursor()

        records = [(1,'FOO'),(2,'SPAM')]

        placeholders = ','.join(['%s']*len(records)) # => '%s,%s'
        sql = f"""
            INSERT INTO schema.table(id, field)
            VALUES {placeholders}
        """
                       
        # Mogrify helpful to debug command sent to DB bc transforms command into human readable form. 
        # It's not necessary. Could just use executemany, but slower & harder to debug command as SO suggests.
        insert_statement = cursor.mogrify(sql, records)
        # print(insert_statement.decode('utf-8'))

        cursor.execute(insert_statement)
        # cursor.executemany(sql, records) # SLOW bc executes and commits each record one at a time.
        # print(cursor.mogrify(sql, records).decode('utf-8'))

        connection.commit()

    except psycopg2.DatabaseError:
        raise
    finally:
        if not connection.closed:
            cursor.close()
            connection.close()