Postgres commands

postgres.commands.psql

# Connect remotely from command line
psql -h <REMOTE HOST> -p 5432 -U <DB_USER> <DB_NAME>

# USE database;
\c databasename;

# SHOW TABLES;
\dt;

# SHOW COLUMNS FROM tablename
\d+ tablename;

# CREATE TABLE
CREATE TABLE persons (
  id SERIAL NOT NULL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  social_security_number VARCHAR(100) NOT NULL UNIQUE,
  credit_score INT,
  married BOOLEAN NOT NULL,
  birth_date DATE NOT NULL,
  details JSON NOT NULL
);

# INSERT INTO
INSERT INTO persons (
  id,
  name
)
VALUES (
  1,
  'Julia'
)
RETURNING name;

# SELECT
SELECT id, name FROM persons
WHERE
  name = 'Julia';

# UPDATE
UPDATE persons
SET
  name = 'Julianna'
WHERE
  name = 'Julia';

# DELETE
DELETE FROM persons
WHERE
  name = 'Julianna';