PostgreSQL Terminal Commands

1
2
3
4
5
6

\c  -- connect to database
\l  -- list all databases
\q  -- quit
\d  -- describe table
\x  -- toggle expanded output
     -- (That displays columns as rows and it's great for long rows)

1
2
3
4
5

-- Just put two dashes for single lines

/* There are also multilines
 * but I feel like I never use them
 */

1
2

CREATE DATABASE practice_pg;
DROP DATABASE practice_pg;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

-- Using auto incrementing primary key
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username text NOT NULL, -- example constraint
    age int,
    is_cool boolean
);

DROP TABLE users;
DROP TABLE IF EXISTS users;

1

TRUNCATE users;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

-- Add column
ALTER TABLE users ADD bio char(50);

-- Modify column type
ALTER TABLE users ALTER COLUMN bio TYPE text;

-- Rename column
ALTER TABLE users RENAME COLUMN bio TO description;

-- Delete column
ALTER TABLE users DROP COLUMN bio;

1
2
3
4
5
6
7

INSERT INTO users (username, age, is_cool) VALUES
('tom', 2, true),
('jerry', 5, false),
('sara', 26, true),
('sam', 58, false),
('bill', 35, true);
-- you can insert multiple comma separated values

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

-- Select all
SELECT * from users;

-- Select specific columns
SELECT username, age, is_cool from users;

-- Using aliases
SELECT username as name, is_cool as "Are they cool?" from users;
-- note that the " are only needed for spaces

-- Conditional selections
SELECT * FROM users WHERE id = 1;
SELECT * FROM users WHERE age > 10 AND is_cool = true;
SELECT * FROM users WHERE age > 10 OR is_cool = true;

1
2
3

SELECT * FROM users ORDER BY age;
SELECT * FROM users ORDER BY age DESC;
SELECT * FROM users ORDER BY age ASC; -- default

1
2

SELECT COUNT(id) FROM users;
SELECT COUNT(id) as "Number of users" FROM users;

1

SELECT * FROM users WHERE id IN (1,4,5);