postgresql使用教程

Database

-- create database
CREATE DATABASE [database_name];
-- delete databse
DROP DATABASE [database_name];

Table

Create Table

create table [table_name](
	in int,
	name varchar(10),
)

Load data from & to database

-- Load data to Database
copy [table_name] from '/path/to/data/[data_name].csv' with DELIMITER AS E'\t';
-- Load data from Database
copy [table_name] to '/path/to/data/[data_name].csv' DELIMITER ',' CSV HEADER;

Explore table

-- see column name of table
select column_name from information_schema.columns where table_name='[table_name]';
-- see cardinality of a table satisfy predicates
select count(*) from [table_name] where [predicate];
-- get 10 random tuples from table
select * from [table_name] order by random() limit 10;
-- get 10 top tuples from table
select * from [table_name] limit 10; 

Role

-- see all roles
\du 
-- see what role are you and what database are you in
\c
-- create new role
create role [new_user];
-- set password for role
alter role [new_user] with password [your password];