I'm still learning PostgreSQL. During my testing, I have only been using INSERT statement in either psycopg2 and now asyncpg. I now have the need to UPDATE data in my test database, instead of replacing all of it.
I'm currently trying to do a simple replacement test in a testing table, before I move to development table with more data.
I want to replace any $1 name that is in CONFLICT with a name that is already in the table users. I'm trying the query code, which is passed to the DB via asyncpg. I keep getting a syntax errors, so I'm a little lost on how to correct these errors.
What is the proper syntax for this query?
'''INSERT INTO users(name, dob)
VALUES($1, $2)
ON CONFLICT (name)
DO
UPDATE "users"
SET name = 'TEST'
WHERE name = excluded.name '''
UPDATE:
I'm getting this error message when using asyncpg:
asyncpg.exceptions.PostgresSyntaxError: syntax error at or near ""users""
I'm getting this error message when using psycopg2:
psycopg2.ProgrammingError: syntax error at or near ""users""
This is the asyncpg code that I have been using to do the INSERTs:
async def insert_new_records(self, sql_command, data):
print (sql_command)
async with asyncpg.create_pool(**DB_CONN_INFO, command_timeout=60) as pool:
async with pool.acquire() as conn:
try:
stmt = await conn.prepare(sql_command)
async with conn.transaction():
for value in data:
async for item in stmt.cursor(*value):
pass
finally:
await pool.release(conn)
test_sql_command = '''
INSERT INTO users(name, dob)
VALUES($1, $2)
ON CONFLICT (name)
DO
UPDATE "users"
SET name = 'TEST'
WHERE name = excluded.name '''
# The name 'HELLO WORLD' exists in the table, but the other name does not.
params = [('HELLO WORLD', datetime.date(1984, 3, 1)),
('WORLD HELLO', datetime.date(1984, 3, 1))]
loop = asyncio.get_event_loop()
loop.run_until_complete(db.insert_new_records(test_sql_command, params))
where name = excluded.name