Leon's Blogging

Coding blogging for hackers.

Postgres

| Comments

install

1

brew install postgresql

Initdb

1

initdb /usr/local/var/postgres -E utf8

Connect postgresql

1

psql

Create DB

1

createdb <dbname>

or

1
2
3
4
5
6

-- into postgres
psql
-- show all database
/l
-- create database
CREATE DATABASE dbname;

Connect to database

1

psql <dbname>

or

1
2
3

psql
\l
\c <dbname>

Show

1
2
3
4
5
6
7
8

\l -- List databases
\dt *.* -- List tables from all schemas
\dt -- List current path tables
\dn -- List Schema
\di -- List Index
\dt+ -- List Table
\d+ <tablename> -- List Column
SELECT * FROM <tablename> -- List Record

Dump db

1

pg_dump <dbname> <filename>

Import db

1

psql <filename> < <dbname>

Connect to a remote database

1

psql -h <host> -p <port> -U <username>

Create User

1
2
3
4
5
6
7
8
9
10
11
12

createuser testuser

-- or

--show users
\du+

CREATE USER <username>;
GRANT ALL PRIVILEGES ON DATABASE <dbname> TO <username>;
GRANT ALL PRIVILEGES ON TABLE <tablename> TO <username>;
-- Allow user1 to set role as user2
GRANT <username2> to <username1>;

other

1

SELECT * FROM trends WHERE  user_id = ANY('{1, 2, 3}'::text[]) ORDER BY array_position('{1, 2}'::text[], user_id), updated_at DESC, metric_value DESC LIMIT 30

Tool

  • pgcli - Pgcli is a command line interface for Postgres with auto-completion and syntax highlighting.
  • Postico - GUI client on mac
  • goose - Goose database migration tool
  • postgres-cheatsheet

Comments