PGSQL 명령어 모음

Connect to postresql psql or psql db_name
Create a user CREATE USER kevin WITH PASSWORD 'my_password';
Create a user from command line createuser kevin
Remove a user from command line dropuser kevin
Select database \c db_name
Show databases select datname from pg_database;
List databases \l
Show Schemas \dn
Show tables \d
Show tables in schema \dt my_schema.*
Show all sequences \ds
Show all views \dv
Show table definition \d table_name
Show privilieges \dp
Show functions \df
Show data types \dT
Describe table \d table_name
Create database Create database db_name
Create database from command line createdb dbname -U user-name -W
Create schema CREATE SCHEMA my_schema
Drop database DROP DATABASE dbname;
Drop database from command line dropdb dbname
Drop database from command line dropdb dbname
Drop Schema DROP SCHEMA my_schema CASCADE
Backup ALL databases pg_dumpall > backup.sql
Backup database pg_dump -U username dbname > outfile.sql
Backup Schema Only pg_dump --schema-only -U username dbname > outfile.sql
Rename database ALTER DATABASE name RENAME TO newname
Auto Increment my_id SERIAL PRIMARY KEY
GRANT update prviliges GRANT UPDATE ON table-name to kevin
GRANT select prviliges GRANT SELECT ON table-name to kevin
GRANT insert prviliges GRANT INSERT ON table-name to kevin
GRANT delete prviliges GRANT DELETE ON table-name to kevin
GRANT rule prviliges GRANT RULE ON table-name to kevin
Add Foreign Key ALTER TABLE my_table ADD FOREIGN KEY(some_id) REFERENCES other_table(some_id) ON DELETE CASCADE
Vacuum vacuumdb --verbose --analyze --all
Import a dump file psql -U username dbname < dbname.sql
Get Version SELECT version();
Quit psql \q ( For aka- who could not work it out )
Dump Functions pg_dump -U postgres -s dbname | awk '/CREATE FUNCTION/,/LANGUAGE/ { print $0;}' > func.txt
Rename Column ALTER TABLE my_table RENAME COLUMN old_name TO new_name
Rename Table ALTER TABLE my_table RENAME TO new_table_name
Change Columns Type ALTER TABLE my_table ALTER COLUMN my_col TYPE numeric(10,2)
Change Default Value ALTER TABLE my_table ALTER COLUMN my_col SET DEFAULT 22
Rename Field ALTER TABLE my_table RENAME old_name TO new_name;
Drop Trigger DROP TRIGGER trigger _name ON table_name