postgreSQL usage

• Basic usage comparing to mysql

mysql: SHOW TABLES

postgresql: \d

postgresql: SELECT table_name FROM information_schema.tables WHERE table_schema = 'public';

mysql: SHOW DATABASES
postgresql: \l
postgresql: SELECT datname FROM pg_database;

mysql: SHOW COLUMNS
postgresql: \d table
postgresql: SELECT column_name FROM information_schema.columns WHERE table_name ='table';

mysql: DESCRIBE TABLE
postgresql: \d+ table
postgresql: SELECT column_name FROM information_schema.columns WHERE table_name ='table';



• Join usage

Join 2 tables:

SELECT

    A.pka,

    A.c1,

    B.pkb,

    B.c2

FROM

    A

INNER JOIN B ON A .pka = B.fka;

Join 3 tables:
t1 and t3 merger to t2:
SELECT

t1.column1,
t2.column2,
t3.column3
FROM
 table1name t1
INNER JOIN table2name t2 ON t1.id = t2.id
INNER JOIN table2name t2 ON t3.id = t2.id;

t1 merge to t2, then merger to t3:
SELECT

t1.column1,
t2.column2,
t3.column3
FROM
 table1name t1
INNER JOIN table2name t2 ON t1.id = t2.id
INNER JOIN table3name t3 ON t2.id = t3.id;

• Output csv file

When in psql client :
\COPY (SELECT t1.id, t1.ad_id, t2.name, 
              CONCAT(t3.first_name,' ',t3.last_name), 
              t3.email 
       FROM t2name t2 
       INNER JOIN t1namec t1 ON t1.ad_id=t2.ad_id 
       INNER JOIN t3name t3 ON t1.id=t3.id 
       ORDER BY t1.id) 
TO 'new_list.csv' WITH CSV;

When in psql cmd line :
COPY (...)...;


- good postgresql tutorial reference
http://www.postgresqltutorial.com/postgresql-inner-join/