[TECH] psql , some commands for postgresql

postgres:9.6 (2020-02-03)

Case-1 : Drop the database before importing.

1. root@f8d079b388c2:/tmp# psql -U postgres
2. postgres=# drop database seqdb_prod;
3. postgres=# create database seqdb_prod;
4. postgres=# \q
5. root@f8d079b388c2:/tmp# psql -U postgres seqdb_prod < seqdb_prod_2019-11-20.sql | tee 20191121.log
6. root@f8d079b388c2:/tmp# psql seqdb_prod -U postgres
7. postgres=# SET search_path TO seqdb;
8. postgres=# select * from accounts;

---

1. psql -U postgres seqdb_prod
2. seqdb_prod=# SET search_path TO seqdb;

SET
seqdb_prod=# \dt
                      List of relations
 Schema |           Name            | Type  |      Owner

---

1. psql -U postgres seqdb_prod
2. seqdb_prod=# SET search_path TO seqdb;
3. seqdb_prod=# select collectioninfoid, city, decimallatitude, decimallongitude from collectioninfos;
4. seqdb_prod=# seqdb_prod=# \d collectioninfos;

seqdb_prod=# \d collectioninfos;

Table "seqdb.collectioninfos"
         Column          |            Type             |                                 Modifiers                                  
-------------------------+-----------------------------+----------------------------------------------------------------------------
 collectioninfoid        | integer                     | not null default nextval('collectioninfos_collectioninfoid_seq'::regclass)
 airtemperature          | character varying(10)       | 
 basinname               | character varying(100)      | 
 city                    | character varying(191)      | 
 collector               | character varying(200)      | 
 collectortype           | character varying(50)       | 
 continent               | character varying(50)       | 
 coordinatesystem        | character varying(100)      | 
 coordinates             | character varying(100)      | 
 country                 | character varying(50)       | 
 day                     | character varying(10)       | 
 decimalcoordinatesystem | character varying(100)      | 
 decimallatitude         | real                        | 
 decimallongitude        | real                        | 
 depth                   | character varying(20)       | 
 ecoregions              | character varying(100)      | 
 effort                  | character varying(50)       | 
 elevation               | character varying(50)       | 
 endday                  | character varying(10)       | 
 endmonth                | character varying(10)       | 
 endyear                 | character varying(10)       | 
 eventtime               | character varying(50)       | 
 filtersize              | character varying(50)       | 
 georeferencedby         | character varying(50)       | 
 georeferenceddate       | date                        | 
 gpssource               | character varying(50)       | 
 habitat                 | character varying(50)       | 
 lastmodified            | timestamp without time zone | 
 latitude                | character varying(100)      | 
 longitude               | character varying(100)      | 
 month                   | character varying(10)       | 
 notes                   | text                        | 
 preparations            | character varying(100)      | 
 province                | character varying(50)       | 
 rainfall                | character varying(50)       | 
 rainvolumecollected     | character varying(50)       | 
 region                  | character varying(50)       | 
 riverstreamname         | character varying(100)      | 
 samplesource            | character varying(100)      | 
 samplercollectiondate   | date                        | 
 samplerinstallationdate | date                        | 
 sector                  | character varying(50)       | 
 site                    | character varying(200)      | 
 sitecodes               | character varying(50)       | 
 streamorder             | integer                     | 
 week                    | character varying(50)       | 
 year                    | character varying(10)       | 
 protocolid              | integer                     | 
 decimallatitude_old     | character varying(100)      | 
 decimallongitude_old    | character varying(100)      | 
Indexes:
    "pk_collectioninfos" PRIMARY KEY, btree (collectioninfoid)
    "collectioninfo_city_idx" btree (city)
    "fk_s238mngbvqv10t8lwkgqb81xc" btree (protocolid)
Foreign-key constraints:
    "fk_s238mngbvqv10t8lwkgqb81xc" FOREIGN KEY (protocolid) REFERENCES protocols(protocolid)
Referenced by:
    TABLE "mixedspecimens" CONSTRAINT "fk_a6yywjr2k3p8liflvfcqvijhk" FOREIGN KEY (collectioninfoid) REFERENCES collectioninfos(collectioninfoid)
    TABLE "seqsources" CONSTRAINT "fk_fe5snpi3d9mdpo5jh6vvbu5m7" FOREIGN KEY (collectioninfoid) REFERENCES collectioninfos(collectioninfoid)
    TABLE "specimens" CONSTRAINT "fk_q0hkqo8y5053o592ghgx06rb9" FOREIGN KEY (collectioninfoid) REFERENCES collectioninfos(collectioninfoid)