postgres_cheatsheat.md

Login to PostgreSQL from command line

sudo -u postgres psql postgres

or

psql -u [username] -d [database name]

or

psql

run this for more option

psql --help

CREATE DATABASE

CREATE DATABASE [database_name] ;

LIST ALL DATABASE

\l

or

\list

DELETE DATABASE

DROP DATABASE [database_name] ;

example

DROP DATABASE wolfsbane ;

CREATE TABLE

CREATE TABLE [table_name]
(
  [column] [type]
);

example

CREATE TABLE users
(
  id serial NOT NULL,
  first_name character varying(50),
  last_name character varying(50),
  email character varying(50),
  age INTEGER,
  password character varying(20)
);

Describe a table

\d [table_name]

Show all table

\d

Alter table column

update existing column

ALTER TABLE [table_name]
ALTER COLUME [column_name] TYPE [column_type];

example:

ALTER TABLE users
ALTER COLUMN first_name TYPE character varying(30);

add new column

ALTER TABLE [table_name] ADD [column_name] [column_type];

example

ALTER TABLE users ADD gender character varying(50);

Delete Table

DROP TABLE [table_name];

example

DROP TABLE users;

Postgres services (Linux/MacOS)

sudo service postgresql stop
sudo service postgresql start
sudo service postgresql restart

Import CSV file into postgres

\copy [db_name] FROM '[path]' DELIMITER ',' CSV HEADER;

also

COPY [db_name] FROM '[path]' DELIMITER ',' CSV HEADER;

Additional Resources

• Play around with some exercies
• Sequelize ORM for postgres
• Prisma
• PostgreSQL website