Postgres Load Data and Query

pgcsv is used for load csv data into Postgres.

Input bash in ternimal:

1 2	$ pip install pgcsv $ pgcsv --db postgresql://localhost/database new_table csv_file.csv

With pgcvs default data type would be text.

1	psql -d database

List all current tables:

\d

Getting infomation of a table:

1	\d+ tablename

Checking top 1000 recordes:

1	select * from whole_year limit 1000;

Counting row number:

1	select count(*) from whole;

Combining tables:

1	INSERT INTO table1 SELECT * FROM table2;

Exclud some rows from extra table:

1 2	SELECT * FROM tabA WHERE id not in (SELECT id FROM tabB);

Table rename:

1	ALTER TABLE distributors RENAME TO suppliers;

postgres numeric types

Name	Storage Size	Range
small int	2 bytes	-32768 to +32767
integer	4 bytes	-2147483648 to +2147483647
bigint	8 bytes	-9223372036854775808 to +9223372036854775807
decimal	variable	up to 131072 digits before the decimal point; up to 16383 digits after the decimal point
numeric	variable	up to 131072 digits before the decimal point; up to 16383 digits after the decimal point
double	precision	8 bytes 15 decimal digits precision

Text to numeric

1	ALTER TABLE whole ALTER COLUMN wgs84latitude TYPE numeric(17,15) USING wgs84latitude::numeric(17,15);

numeric{m,d}, where m is the total digits and d is the number of digits after the decimal.

Create new table from exist tables.

1	CREATE TABLE firstSeason AS SELECT * FROM whole WHERE createdatunixtime <= 1396310399;

1	DROP TABLE table1, table2;

IF you have tables A and B, both with colum C, here are the records, which are present in table A but not in B:

SELECT A.*
FROM A
    LEFT JOIN B ON (A.C = B.C)
WHERE B.C IS NULL

To get all the differences with a single query, a full join must be used, like this:

SELECT A.*, B.*
FROM A
    FULL JOIN B ON (A.C = B.C)
WHERE A.C IS NULL OR B.C IS NULL

1	COPY tablename to 'absolute file path.csv' delimiters',';