PostgreSQL Basic Operations

Library operations

\h: View an explanation of the SQL command, such as \h Select.
\?: View the list of PSQL commands.
\l: Lists all databases.
\c [database_name]: Connect to a different database.
\d: Lists all tables for the current database.
\d [table_name]: Lists the structure of a table.
\DU: Lists all users.
\e: Opens a text editor.
\conninfo: Lists the current database and connection information.

Log in to Database
Psql-u postgres-w (password)-h 127.0.0.1

Create a database
Create DATABASE Databa_name

Backing Up the database
Pg_dump-h 127.0.0.1-u postgres databasename >/data/backup_name.sql

Import Database
Psql-h localhost-u postgres-d DatabaseName </data/backup_name.sql

To modify a database password
ALTER USER postgres with PASSWORD ' postgres ';

Table Operations

Create a new table
CREATE TABLE user_tbl (name VARCHAR (), signup_date date);

Inserting data
INSERT into USER_TBL (name, signup_date) VALUES (' Zhang San ', ' 2013-12-22 ');

Select Record
SELECT * from USER_TBL;

Update data
UPDATE user_tbl Set name = ' John Doe ' WHERE name = ' Zhang San ';

Deleting records
DELETE from user_tbl WHERE name = ' John Doe ';

Add Field
ALTER TABLE user_tbl ADD email VARCHAR (40);

Update structure
Alter TABLE USER_TBL ALTER COLUMN signup_date SET not NULL;

Rename Field
ALTER TABLE user_tbl RENAME COLUMN signup_date to signup;

Delete Field
ALTER TABLE user_tbl DROP COLUMN email;

Renaming a table
ALTER TABLE user_tbl RENAME to Backup_tbl;

Delete a table
DROP TABLE IF EXISTS backup_tbl;

PostgreSQL Basic Operations