When creating a postgresql table, the field contains a special character "()", an error was reported when creating the table.
Solution:
Add double quotes to fields containing special characters "" and then create a table
For example:
create table freighttransport_tab ( id serial, date TEXT, "weight(Kilogram)" TEXT )
At this time, the "Weight (kg)" field contains special symbols (). When creating a table, add double quotes to the field in the SQL statement.
Supplement: PostgreSQL removes invisible characters from strings Ultimate method
The data in the test table are as follows:
postgres=# select * from test; street ---------- Chengjiang Street Chengjiang Street (2 Line records)
Then we execute the following sql:
postgres=# select * from test where street='Chengjiang Street'; street ---------- Chengjiang Street (1 Line records)
The problem has been exposed, and there are two records of "Chengjiang Street"! How did you find out one? ?
Next, conduct the following check:
postgres=# select street, length(street) from test; street | length ----------+-------- Chengjiang Street | 5 Chengjiang Street | 4 (2 Line records)
If you encounter this situation, ask if you are confused? ? The same string length is 5 and 4! ! Sun Dog, invisible characters are mixed in.
Maybe someone has come up with a solution at this time, and this method is as follows:
update test set street='Chengjiang Street' where street like '%Chengjiang Street%'; postgres=# start transaction; START TRANSACTION postgres=# update test set street='Chengjiang Street' where street like '%Chengjiang Street%';UPDATE 2 postgres=# select * from test where street='Chengjiang Street'; street ---------- Chengjiang Street Chengjiang Street (2 Line records) postgres=# rollback; ROLLBACK
Haha, everyone is happy, the problem is solved.
But what if there is a lot of this kind of data? ? ?
Are you going to update one by one? ?
Unfortunately, I encountered a lot of this kind of data (because the data was imported from Excel).
Then I found the ultimate solution like this:
First determine which character this invisible character is (generally, if there are invisible characters in a csv file, they should be the same invisible character)
postgres=# select encode(street::bytea,'escape') from test;
encode
--------------------------------------------------------------
\357\273\277\346\276\204\346\261\237\350\241\227\351\201\223
\346\276\204\346\261\237\350\241\227\351\201\223
(2 Line records)
That section\357\273\277 is so dazzling. . . . . .
If you have an enemy, you can consider taking revenge on him in this way
Then determine if the invisible character can be filtered out:
postgres=# select encode(street::bytea,'escape') from test where btrim(street::bytea,E'\\357\\273\\277'::bytea) = 'Chengjiang Street'; encode -------------------------------------------------------------- \357\273\277\346\276\204\346\261\237\350\241\227\351\201\223 \346\276\204\346\261\237\350\241\227\351\201\223 (2 Line records)
I found two records, and using this btrim, it seems that all \357\273\277 in the string were removed.
Next, we will update the table ~
postgres=# update test set street=btrim(street::bytea,E'\\357\\273\\277'::bytea); UPDATE 2
Update succeeded.
View data:
postgres=# select * from test; street ---------------------------- \xe6be84e6b19fe8a197e98193 \xe6be84e6b19fe8a197e98193 (2 Line records)
Haha, Chinese has become hexadecimal.
But don't worry.
postgres=# select convert_from(street::bytea,'UTF8') from test; convert_from -------------- Chengjiang Street Chengjiang Street (2 Line records)
Isn't it enough to turn back to UTF8 encoding? (The garbled question is the easiest of all questions, no one)
Follow this method to update the table:
postgres=# update test set street=convert_from(street::bytea,'UTF8'); UPDATE 2 postgres=# select * from test; street ---------- Chengjiang Street Chengjiang Street (2 Line records)
The normal data is back.
postgres=# select * from test where street='Chengjiang Street'; street ---------- Chengjiang Street Chengjiang Street (2 Line records) postgres=# select street, length(street) from test; street | length ----------+-------- Chengjiang Street | 4 Chengjiang Street | 4 (2 Line records)
The problem of invisible characters has also been solved.
The above is personal experience. I hope you can give you a reference and I hope you can support me more. If there are any mistakes or no complete considerations, I would like to give you advice.