Skip to content

Instantly share code, notes, and snippets.

@spy86

spy86/MySQLvsPostgreSQL.csv

Created November 23, 2021 19:56
Show Gist options
  • Save spy86/621ebcb2b1f591c69095bddbe978fe8d to your computer and use it in GitHub Desktop.
Save spy86/621ebcb2b1f591c69095bddbe978fe8d to your computer and use it in GitHub Desktop.
Download ZIP
Raw
MySQLvsPostgreSQL.csv
Lp. Command MySQL PostgreSQL
1 Connection mysql -p -h host name -P port number -u username DB name $ psql -h hostname -p port number -U username DB name
2 Database list show databases; \l
3 Database switching use DB name \u DB name \c DB name
4 Table list show tables; \d, \dt, \d+, \dt+
5 Check table definition desc table name; \d table name
6 Table CREATE statement confirmation show create table table name pg_dump DB name -U user name -s -t table name
7 Index list SHOW INDEX FROM tbl_name; \d table_name
8 Switching line display select * from t \G \x select * from t;
9 SQL file execution \. \i
10 SQL dump mysqldump pg_dump
11 TSV dump mysqldump -u USER --password = PASS DATABASE_NAME TABLE_NAME -T /tmp
12 TSV import LOAD DATA LOCAL INFILE' $ FILE_NAME' REPLACE INTO TABLE $ TABLE_NAME IGNORE 1 LINES; copy table_name from'/absolute_path/to/data.tsv'(delimiter'', format csv, header true);
13 SQL time measurement Displayed by default \timing on
14 Start log output \T log.txt \o log.txt
15 Log output end \t \o
16 Copy the definition and create a table create table t2 like t1; create table t2 (like t1);
17 Execution plan Prepend EXPLAIN to SQL Prepend EXPLAIN to SQL
18 Check the connection destination server \s \conninfo
19 Check connection show process list; select * from pg_stat_activity;
20 Serial number ID Column name int auto_increment Column name serial
21 Today's date curdate() current_date
22 Current time now() now()
23 Help \h or \? \?
24 Setting file ~ /.my.cnf ~ /.psqlrc
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment