Working with Databases Part 2: Using the Postgres Interactive Terminal
Once you have PostgreSQL installed, you can create new databases by using the command createdb mydb. mydb will be the name of the database. You can delete a database using dropdb mydb. After creating a database, you can start interacting with it using thePostgreSQL interactive terminal program. You can access this by using the psql command followed by the database name. This will allow you to interact with the database by executing SQL commands.
SQL commands are instructions used to communicate with a database. The Postgres terminal also has internal commands that start with \ e.g. \q to quit, \h for help ,\l to list all databases, \dt to list all tables within a database or\x for expanded display mode.
How to create a table
CREATE TABLE employees (
work_id int,
first_name varchar(80),
last_name varchar(80)
);This creates a new table called employees with columns work_id, first_name and last_name. Each column must be associated with a data type, varchar(80) is a text data type which means that columns first_name and last_name can store arbitrary character strings up to 80 characters in length. There are also number data types such as int which store digits.
The different SQL data types can be found in part 1 of this working with databases series.
To remove table, you can use the SQL command DROPTABLE tablename;.
How to insert a new record into a table
INSERT INTO employees (work_id, first_name, last_name)
VALUES (1234, "Himalee", "Tailor");Using the INSERT INTO statement, you are able to add new records, or rows, to your table. You first specify the column names, then the values to be inserted into your table.
How to delete a record
DELETE FROM employees
WHERE first_name = "Himalee";The DELETE statement allows you to delete previously saved rows from your database based on a condition, the WHERE clause.
How to read from a table
#to select all fields from a table
SELECT * FROM employees;#to select specific fields from a table
SELECT first_name, last_name
FROM employees;
To query a table, or to select data from a table, you can use the SELECT statement. The data you get back from the database is stored in a result table, called the result-set.
How to alter table columns
#to add a new column to a table
ALTER TABLE employees
ADD title varchar(200);#to delete a column from a table
ALTER TABLE employees
DROP COLUMN first_name;
If you have already created a table but you want to add new columns or delete previously added columns, you can use the ALTER TABLE statement.
How to update previously saved rows
UPDATE employees
SET first_name = "Ada", last_name = "LoveLace"
WHERE work_id = 1234;If you want to modify an existing record in a table, you can used the UPDATE statement.
