update cell value in PostgreSQL
When I run the command:
su -s /bin/bash -l postgres -c "psql -d host -c 'UPDATE versi开发者_JAVA技巧on_table SET version_id='1.2' WHERE version_id=CAST ('1.1' AS character varying)'"
It worked well.
But if I changed the version_id to "1.2.1" as:
su -s /bin/bash -l postgres -c "psql -d host -c 'UPDATE version_table SET version_id='1.2.1' WHERE version_id=CAST ('1.1' AS character varying)'"
I got the following error messages:
ERROR: syntax error at or near ".1"
LINE 1: ...M version_table WHERE version_id=CAST (1.2.1 AS char...
It seems more than one dot are not allowed.
How can I get around it?
Thank!
Add: the type of the column is character varying.
You have fighting quotes. You're using single quotes for the shell command:
psql -d host -c '...sql...'
And also for quoting strings inside the SQL:
... version_id='1.2' ...
Try using double quotes for the shell part:
psql -d host -c "UPDATE version_table SET version_id='1.2.1' WHERE version_id=CAST ('1.1' AS character varying)"
Or put the SQL in a separate text file and feed that to psql
via the standard input
psql -d host < sql_in_a_text_file.sql
change the column to some type of character datatype.
精彩评论