CRUD

April 2021

Table of Contents

Creating a database

CREATE DATABASE analysis;

Creating and removing tables

-- ex1
CREATE TABLE teachers (
    id bigserial,  -- column names are followed by data types
    first_name varchar(25),
    last_name varchar(50),
    school varchar(50),
    hire_date date,
    salary numeric
);

-- ex2
CREATE TABLE products (
    product_no integer,
    name text,
    price numeric
);

-- removing a table
DROP TABLE tablename;

Data Manipulation (CRUD)

Moving data from/to files

COPY table_name
FROM '/home/path/your_file.csv'
WITH (FORMAT CSV, HEADER);

NOTE: if you are using Postgres from a Docker container, make sure the path refers to the relative path within the container.
NOTE2: if you wish to export from a table to a file, change the FROM for a TO.

Querying a Table (Read)

To retrieve data from a table, the table is queried. An SQL SELECT statement is used to do this. The statement is divided into:

a select list (the part that lists the columns to be returned),
a table list (the part that lists the tables from which to retrieve the data), and
an optional qualification (the part that specifies any restrictions).

-- retrieves all columns and all rows (no conditions specified)
SELECT * FROM weather;

-- querying specific columns
SELECT city, temp_lo, temp_hi, prcp, date FROM weather;

-- adding a condition
SELECT * FROM weather
    WHERE city = 'San Francisco' AND prcp > 0.0;

Inserting Data (Create)

When a table is created, it contains no data. Data is conceptually inserted one row at a time. By default, the data values in the records to be inserted should be listed in the order in which the columns appear in the table, separated by commas and between parenthesis:

INSERT INTO products VALUES (1, 'Cheese', 9.99);

Alternatively, column ordering can also be specified:

-- both queries result in the same record being inserted
INSERT INTO products (product_no, name, price) VALUES (1, 'Cheese', 9.99);
INSERT INTO products (name, price, product_no) VALUES ('Cheese', 9.99, 1);

If you don’t have values for all the columns, you can omit some of them. In that case, the columns will be filled with their default values. For example:

INSERT INTO products (product_no, name) VALUES (1, 'Cheese');

Inserting multiple records in the same query

INSERT INTO teachers (first_name, last_name, school, hire_date, salary)
VALUES ('Janet', 'Smith', 'F.D. Roosevelt HS', '2011-10-30', 36200),
       ('Lee', 'Reynolds', 'F.D. Roosevelt HS', '1993-05-22', 65000),
       ('Samuel', 'Cole', 'Myers Middle School', '2005-08-01', 43500),
       ('Samantha', 'Bush', 'Myers Middle School', '2011-10-30', 36200),
       ('Betty', 'Diaz', 'Myers Middle School', '2005-08-30', 43500),
       ('Kathleen', 'Roush', 'F.D. Roosevelt HS', '2010-10-22', 38500);

Updating Data

The modification of data that is already in the database is referred to as updating. You can update individual rows, all the rows in a table, or a subset of all rows. Each column can be updated separately; the other columns are not affected.

To update existing rows, use the UPDATE command. This requires three pieces of information:

The name of the table and column to update
The new value of the column
Which row(s) to update

In general SQL does not provide a unique identifier for rows. Instead, you specify which conditions a row must meet in order to be updated. Only if you have a primary key in the table (independent of whether you declared it or not) can you reliably address individual rows by choosing a condition that matches the primary key.

For example, this command updates all products that have a price of 5 to have a price of 10:

UPDATE products SET price = 10 WHERE price = 5;

First is the key word UPDATE followed by the table name. As usual, the table name can be schema-qualified, otherwise it is looked up in the path. Next is the key word SET followed by the column name, an equal sign, and the new column value. The new column value can be any scalar expression, not just a constant. For example, if you want to raise the price of all products by 10% you could use:

-- this query affects all rows of the price column
UPDATE products SET price = price * 1.10;

You can update more than one column in an UPDATE command by listing more than one assignment in the SET clause. For example:

UPDATE mytable SET a = 5, b = 3, c = 1 WHERE a > 0;

Deleting Data

Just as adding data is only possible in whole rows, you can only remove entire rows from a table. SQL does not provide a way to directly address individual rows. Therefore, removing rows can only be done by specifying conditions that the rows to be removed have to match. If you have a primary key in the table then you can specify the exact row. But you can also remove groups of rows matching a condition, or you can remove all rows in the table at once.

You use the DELETE command to remove rows; the syntax is very similar to the UPDATE command. For instance, to remove all rows from the products table that have a price of 10, use:

DELETE FROM products WHERE price = 10;

-- Warning! The below query deletes ALL the rows in the table
DELETE FROM products;

Rafael Marino