UPSERT syntax error linked to UPDATE in PostgreSQL (python)

Multi tool use
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))
python-3.x postgresql psycopg2 asyncpg
add a comment |
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))
python-3.x postgresql psycopg2 asyncpg
1
where name = excluded.name
– a_horse_with_no_name
Nov 9 '18 at 16:47
For some reason, I'm still getting -- syntax error at or near ""users""
– Life is complex
Nov 9 '18 at 16:54
add a comment |
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))
python-3.x postgresql psycopg2 asyncpg
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))
python-3.x postgresql psycopg2 asyncpg
python-3.x postgresql psycopg2 asyncpg
edited Nov 9 '18 at 18:40
Life is complex
asked Nov 9 '18 at 16:36
Life is complexLife is complex
249212
249212
1
where name = excluded.name
– a_horse_with_no_name
Nov 9 '18 at 16:47
For some reason, I'm still getting -- syntax error at or near ""users""
– Life is complex
Nov 9 '18 at 16:54
add a comment |
1
where name = excluded.name
– a_horse_with_no_name
Nov 9 '18 at 16:47
For some reason, I'm still getting -- syntax error at or near ""users""
– Life is complex
Nov 9 '18 at 16:54
1
1
where name = excluded.name
– a_horse_with_no_name
Nov 9 '18 at 16:47
where name = excluded.name
– a_horse_with_no_name
Nov 9 '18 at 16:47
For some reason, I'm still getting -- syntax error at or near ""users""
– Life is complex
Nov 9 '18 at 16:54
For some reason, I'm still getting -- syntax error at or near ""users""
– Life is complex
Nov 9 '18 at 16:54
add a comment |
2 Answers
2
active
oldest
votes
You need single quotes around the value for name: SET name='TEST'
The double quotes are for table or column names. In your case, you could just remove the double quotes around users
.
After edit:
You should really try your SQL commands in the database console, this has nothing to do with python nor async. It's pure postgresql syntax.
So, the second issue in your query is that you shouldn't specify "users" after UPDATE. It's implied that you're updating the same table. So just DO UPDATE SET...
is good.
Then, you'll get column reference "name" is ambiguous
. You should write DO UPDATE SET name='TEST'
. You already are updating the row where name=excluded.name
. I am not 100% clear on what you're trying to do. So if you insert a row once, it's inserted as usual. If you insert it a second time, the name is replaced with 'TEST'. The excluded keyword allows you to access the attempted insert values. So for example, if you wanted to update the last_access
column when trying to insert an existing name, you would write ON CONFLICT (name) DO UPDATE last_access=excluded.last_access
.
I'm still getting the same error. I'll update my question with more code.
– Life is complex
Nov 9 '18 at 17:15
Concerning my object. I will be updating various tables based on value X existing and value Y being different. If value X doesn't exist then a new record will be added. Hopefully, I can accomplish that without posting another question.
– Life is complex
Nov 10 '18 at 13:37
This on conflict is only intended to solve the conflict. If you need to do more, like update various tables, you should use abefore insert
trigger.
– Eric Darchis
Nov 11 '18 at 22:18
Thanks for the info. I'm in the process of developing another question, because I still have an issue with my UPSERT.
– Life is complex
Nov 12 '18 at 13:00
add a comment |
you can test
replace :
'''INSERT INTO users(name, dob)
VALUES($1, $2)
ON CONFLICT (name)
DO
UPDATE "users"
SET name = 'TEST'
WHERE name = excluded.name '''
by :
"""INSERT INTO users(name, dob)
VALUES($1, $2)
ON CONFLICT (name)
DO
UPDATE
SET name = 'TEST'
WHERE name = excluded.name """
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53229783%2fupsert-syntax-error-linked-to-update-in-postgresql-python%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
You need single quotes around the value for name: SET name='TEST'
The double quotes are for table or column names. In your case, you could just remove the double quotes around users
.
After edit:
You should really try your SQL commands in the database console, this has nothing to do with python nor async. It's pure postgresql syntax.
So, the second issue in your query is that you shouldn't specify "users" after UPDATE. It's implied that you're updating the same table. So just DO UPDATE SET...
is good.
Then, you'll get column reference "name" is ambiguous
. You should write DO UPDATE SET name='TEST'
. You already are updating the row where name=excluded.name
. I am not 100% clear on what you're trying to do. So if you insert a row once, it's inserted as usual. If you insert it a second time, the name is replaced with 'TEST'. The excluded keyword allows you to access the attempted insert values. So for example, if you wanted to update the last_access
column when trying to insert an existing name, you would write ON CONFLICT (name) DO UPDATE last_access=excluded.last_access
.
I'm still getting the same error. I'll update my question with more code.
– Life is complex
Nov 9 '18 at 17:15
Concerning my object. I will be updating various tables based on value X existing and value Y being different. If value X doesn't exist then a new record will be added. Hopefully, I can accomplish that without posting another question.
– Life is complex
Nov 10 '18 at 13:37
This on conflict is only intended to solve the conflict. If you need to do more, like update various tables, you should use abefore insert
trigger.
– Eric Darchis
Nov 11 '18 at 22:18
Thanks for the info. I'm in the process of developing another question, because I still have an issue with my UPSERT.
– Life is complex
Nov 12 '18 at 13:00
add a comment |
You need single quotes around the value for name: SET name='TEST'
The double quotes are for table or column names. In your case, you could just remove the double quotes around users
.
After edit:
You should really try your SQL commands in the database console, this has nothing to do with python nor async. It's pure postgresql syntax.
So, the second issue in your query is that you shouldn't specify "users" after UPDATE. It's implied that you're updating the same table. So just DO UPDATE SET...
is good.
Then, you'll get column reference "name" is ambiguous
. You should write DO UPDATE SET name='TEST'
. You already are updating the row where name=excluded.name
. I am not 100% clear on what you're trying to do. So if you insert a row once, it's inserted as usual. If you insert it a second time, the name is replaced with 'TEST'. The excluded keyword allows you to access the attempted insert values. So for example, if you wanted to update the last_access
column when trying to insert an existing name, you would write ON CONFLICT (name) DO UPDATE last_access=excluded.last_access
.
I'm still getting the same error. I'll update my question with more code.
– Life is complex
Nov 9 '18 at 17:15
Concerning my object. I will be updating various tables based on value X existing and value Y being different. If value X doesn't exist then a new record will be added. Hopefully, I can accomplish that without posting another question.
– Life is complex
Nov 10 '18 at 13:37
This on conflict is only intended to solve the conflict. If you need to do more, like update various tables, you should use abefore insert
trigger.
– Eric Darchis
Nov 11 '18 at 22:18
Thanks for the info. I'm in the process of developing another question, because I still have an issue with my UPSERT.
– Life is complex
Nov 12 '18 at 13:00
add a comment |
You need single quotes around the value for name: SET name='TEST'
The double quotes are for table or column names. In your case, you could just remove the double quotes around users
.
After edit:
You should really try your SQL commands in the database console, this has nothing to do with python nor async. It's pure postgresql syntax.
So, the second issue in your query is that you shouldn't specify "users" after UPDATE. It's implied that you're updating the same table. So just DO UPDATE SET...
is good.
Then, you'll get column reference "name" is ambiguous
. You should write DO UPDATE SET name='TEST'
. You already are updating the row where name=excluded.name
. I am not 100% clear on what you're trying to do. So if you insert a row once, it's inserted as usual. If you insert it a second time, the name is replaced with 'TEST'. The excluded keyword allows you to access the attempted insert values. So for example, if you wanted to update the last_access
column when trying to insert an existing name, you would write ON CONFLICT (name) DO UPDATE last_access=excluded.last_access
.
You need single quotes around the value for name: SET name='TEST'
The double quotes are for table or column names. In your case, you could just remove the double quotes around users
.
After edit:
You should really try your SQL commands in the database console, this has nothing to do with python nor async. It's pure postgresql syntax.
So, the second issue in your query is that you shouldn't specify "users" after UPDATE. It's implied that you're updating the same table. So just DO UPDATE SET...
is good.
Then, you'll get column reference "name" is ambiguous
. You should write DO UPDATE SET name='TEST'
. You already are updating the row where name=excluded.name
. I am not 100% clear on what you're trying to do. So if you insert a row once, it's inserted as usual. If you insert it a second time, the name is replaced with 'TEST'. The excluded keyword allows you to access the attempted insert values. So for example, if you wanted to update the last_access
column when trying to insert an existing name, you would write ON CONFLICT (name) DO UPDATE last_access=excluded.last_access
.
edited Nov 10 '18 at 12:31
answered Nov 9 '18 at 17:05
Eric DarchisEric Darchis
10.5k41840
10.5k41840
I'm still getting the same error. I'll update my question with more code.
– Life is complex
Nov 9 '18 at 17:15
Concerning my object. I will be updating various tables based on value X existing and value Y being different. If value X doesn't exist then a new record will be added. Hopefully, I can accomplish that without posting another question.
– Life is complex
Nov 10 '18 at 13:37
This on conflict is only intended to solve the conflict. If you need to do more, like update various tables, you should use abefore insert
trigger.
– Eric Darchis
Nov 11 '18 at 22:18
Thanks for the info. I'm in the process of developing another question, because I still have an issue with my UPSERT.
– Life is complex
Nov 12 '18 at 13:00
add a comment |
I'm still getting the same error. I'll update my question with more code.
– Life is complex
Nov 9 '18 at 17:15
Concerning my object. I will be updating various tables based on value X existing and value Y being different. If value X doesn't exist then a new record will be added. Hopefully, I can accomplish that without posting another question.
– Life is complex
Nov 10 '18 at 13:37
This on conflict is only intended to solve the conflict. If you need to do more, like update various tables, you should use abefore insert
trigger.
– Eric Darchis
Nov 11 '18 at 22:18
Thanks for the info. I'm in the process of developing another question, because I still have an issue with my UPSERT.
– Life is complex
Nov 12 '18 at 13:00
I'm still getting the same error. I'll update my question with more code.
– Life is complex
Nov 9 '18 at 17:15
I'm still getting the same error. I'll update my question with more code.
– Life is complex
Nov 9 '18 at 17:15
Concerning my object. I will be updating various tables based on value X existing and value Y being different. If value X doesn't exist then a new record will be added. Hopefully, I can accomplish that without posting another question.
– Life is complex
Nov 10 '18 at 13:37
Concerning my object. I will be updating various tables based on value X existing and value Y being different. If value X doesn't exist then a new record will be added. Hopefully, I can accomplish that without posting another question.
– Life is complex
Nov 10 '18 at 13:37
This on conflict is only intended to solve the conflict. If you need to do more, like update various tables, you should use a
before insert
trigger.– Eric Darchis
Nov 11 '18 at 22:18
This on conflict is only intended to solve the conflict. If you need to do more, like update various tables, you should use a
before insert
trigger.– Eric Darchis
Nov 11 '18 at 22:18
Thanks for the info. I'm in the process of developing another question, because I still have an issue with my UPSERT.
– Life is complex
Nov 12 '18 at 13:00
Thanks for the info. I'm in the process of developing another question, because I still have an issue with my UPSERT.
– Life is complex
Nov 12 '18 at 13:00
add a comment |
you can test
replace :
'''INSERT INTO users(name, dob)
VALUES($1, $2)
ON CONFLICT (name)
DO
UPDATE "users"
SET name = 'TEST'
WHERE name = excluded.name '''
by :
"""INSERT INTO users(name, dob)
VALUES($1, $2)
ON CONFLICT (name)
DO
UPDATE
SET name = 'TEST'
WHERE name = excluded.name """
add a comment |
you can test
replace :
'''INSERT INTO users(name, dob)
VALUES($1, $2)
ON CONFLICT (name)
DO
UPDATE "users"
SET name = 'TEST'
WHERE name = excluded.name '''
by :
"""INSERT INTO users(name, dob)
VALUES($1, $2)
ON CONFLICT (name)
DO
UPDATE
SET name = 'TEST'
WHERE name = excluded.name """
add a comment |
you can test
replace :
'''INSERT INTO users(name, dob)
VALUES($1, $2)
ON CONFLICT (name)
DO
UPDATE "users"
SET name = 'TEST'
WHERE name = excluded.name '''
by :
"""INSERT INTO users(name, dob)
VALUES($1, $2)
ON CONFLICT (name)
DO
UPDATE
SET name = 'TEST'
WHERE name = excluded.name """
you can test
replace :
'''INSERT INTO users(name, dob)
VALUES($1, $2)
ON CONFLICT (name)
DO
UPDATE "users"
SET name = 'TEST'
WHERE name = excluded.name '''
by :
"""INSERT INTO users(name, dob)
VALUES($1, $2)
ON CONFLICT (name)
DO
UPDATE
SET name = 'TEST'
WHERE name = excluded.name """
answered Nov 23 '18 at 22:51
Paulo AlvesPaulo Alves
11
11
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53229783%2fupsert-syntax-error-linked-to-update-in-postgresql-python%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
skj 3Et Zy49Vw0Q Q64wtIoF pFTnd3GoY N q9YLhl,lzVUwfHN7njtC6 DZ P,pis05oPBb1NJgYIinsMoKCTjSQZjBY4AMwr,X,Z
1
where name = excluded.name
– a_horse_with_no_name
Nov 9 '18 at 16:47
For some reason, I'm still getting -- syntax error at or near ""users""
– Life is complex
Nov 9 '18 at 16:54