Menu
How can I display normally invisible unicode characters from within psql (the postgres command line client)?
711
Solution: Let's consider a table called spatial_ref_sys having columns srid , auth_name, auth_srid, srtext, and proj4text. SELECT * FROM spatial_ref_sys WHERE srtext LIKE '%\ /%'; Sometimes these ticks are very useful for searching special characters in a database.
One of the interesting features of PostgreSQL database is the ability to handle Unicode characters. In SQL Server, to store non-English characters, we need to use NVARCHAR or NCAHR data type. In PostgreSQL, the varchar data type itself will store both English and non-English characters.
PostgreSQL change column type statement First, specify the name of the table to which the column you want to change after the ALTER TABLE keywords. Second, specify the name of the column that you want to change the data type after the ALTER COLUMN clause.
To see otherwise invisible Unicode in a postgress table, you'll want to use "encode" and "escape" both. And just for fun, the escape function requires a cast to type bytea. Putting it all together:
# CREATE TABLE xxx_test (foo text);
# INSERT INTO xxx_test (foo) values (E'Invis\u200eble €');
# SELECT foo from xxx_test;
Invisble €
# SELECT encode(foo::bytea, 'escape') FROM xxx_test;
Invis\342\200\216ble \342\202\254
# DROP TABLE xxx_test;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With