Export Data to a File Using YSQL (the Safe Way)

When exporting data to a file in YSQL I avoid using printable characters (commas, new-lines, pipe symbols, etc.) as field/record separators because we could find the same characters in the exported text fields. I also try to distinguish NULL from “empty strings”.

Below is an example of a command I typically use to export data from a table using YSQL:

				
					
[root@localhost ~]# ysqlsh -c "SELECT * FROM some_data ORDER BY c1;"
 c1 | c2 |     c3
----+----+------------
  1 | A  | 2022-01-02
  3 | c  | 2021-12-23
  2 | B  | 2021-12-30
  4 |    | 2021-06-16
(4 rows)

[root@localhost ~]# ysqlsh -F $'\001' -R $'\002' -P null='(NULL)' -AXtnqc "SELECT * FROM some_data ORDER BY c1;" -o some_data.txt

[root@localhost ~]# cat some_data.txt
1A2022-01-022B2021-12-303c2021-12-234(NULL)2021-06-16
				
			

Have Fun!

One thought on “Export Data to a File Using YSQL (the Safe Way)

Comments are closed.