Austin's SWE Notes 😭

    Modifying Data

    Insert

    INSERT INTO table1(column1, column2, …)
VALUES (value1, value2, …);

    The return of SQL is in this format:

    INSERT oid count
    • oid object identifier uses internally in postgres
      • Typically, insert statement will return oid of 0
    • count number of rows that insert successfully

    Returning

    returning to return the value back after insert. Typically the newly inserted row.

    INSERT INTO table1(column1, column2, …) VALUES (value1, value2, …) RETURNING *;

    This will return everything back

    You can also return just the attribute you want

    INSERT INTO table1(column1, column2, …) VALUES (value1, value2, …) RETURNING id;

    postgres@localhost:dvdrental> insert into links(url, name) values ('ozbargain', 'ozbargain') returning *;
+----+-----------+-----------+-------------+-------------+
| id | url       | name      | description | last_update |
|----+-----------+-----------+-------------+-------------|
| 2  | ozbargain | ozbargain | <null>      | <null>      |
+----+-----------+-----------+-------------+-------------+
INSERT 0 1

    Or use ASkeyword follow by the output name

    INSERT INTO table1(column1, column2, …) VALUES (value1, value2, …) RETURNING field AS output_name;

    Note that this only works for 1 field only not * when you use with as

    If you want to insert a single quote ' , you need to use additional single quote to escape it

    postgres@localhost:dvdrental> INSERT INTO links (url, name) VALUES('http://www.oreilly.com','O''Reilly Media') returning *;
+----+------------------------+----------------+-------------+-------------+
| id | url                    | name           | description | last_update |
|----+------------------------+----------------+-------------+-------------|
| 3  | http://www.oreilly.com | O'Reilly Media | <null>      | <null>      |
+----+------------------------+----------------+-------------+-------------+
INSERT 0 1
Time: 0.010s

    To insert a date value, you follow YYYY-MM-DD

    postgres@localhost:dvdrental> INSERT INTO links (url, name, last_update) VALUES('https://www.google.com','Google','2013-06-01') returning *;
+----+------------------------+--------+-------------+-------------+
| id | url                    | name   | description | last_update |
|----+------------------------+--------+-------------+-------------|
| 5  | https://www.google.com | Google | <null>      | 2013-06-01  |
+----+------------------------+--------+-------------+-------------+
INSERT 0 1

    Inserting multiple rows

    INSERT INTO contacts (first_name, last_name, email)
VALUES
    ('Alice', 'Johnson', '[email protected]'),
    ('Charlie', 'Brown', '[email protected]')
RETURNING *;

    Update

    UPDATE table_name
SET column1 = value1,
    column2 = value2,
    ...
WHERE condition;

    we can also use RETURNING * to return the updated rows with new value

    [!important]
    If we dont specify the WHERE cause, it will update to all the rows on the table

    For example this command increases the price of all courses by 5%

    postgres@localhost:dvdrental> update courses set price = price * 1.05;
UPDATE 5

    Update join table

    When updating one table value, we can also query that value based on another table. To do this we need to join the table in the update statement.

    update table1 set
table1.col1 = new_value
from table2 where
table1.col2 = table2.col2

    For example, update the net price based on the segment_id discounts:

    postgres@localhost:dvdrental> update product set net_price = price * (1 - p.discount) from product_segment p where p.id = product.segment_id returning *;

    Delete

    delete from table
where condition;

    For example

    delete from todo
where id = 2
returning *;

    [!danger]
    Doing delete without where will delete the whole table. i.e delete from todo

    Delete join table

    DELETE FROM table1
USING table2
WHERE condition
RETURNING returning_columns;

    For example

    delete from member using denylist where member.phone = denylist.phone;

    Upsert

    allow insert or update an existing row

    INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...)
ON CONFLICT (conflict_column)
DO NOTHING | DO UPDATE SET column1 = value1, column2 = value2, ...;
    • table_name: This is the name of the table into which you want to insert data.
    • (column1, column2, ...): The list of columns you want to insert values into the table.
    • VALUES(value1, value2, ...): The values you want to insert into the specified columns (column1, column2, ...).
    • ON CONFLICT (conflict_column): This clause specifies the conflict target, which is the unique constraint or unique index that may cause a conflict.
    • DO NOTHING: This instructs PostgreSQL to do nothing when a conflict occurs.
    • DO UPDATE: This performs an update if a conflict occurs.
    • SET column = value1, column = value2, ...: This list of the columns to be updated and their corresponding values in case of conflict.

    For example,

    INSERT INTO inventory (id, name, price, quantity)
VALUES (1, 'A', 16.99, 120)
ON CONFLICT(id)
DO UPDATE SET
  price = EXCLUDED.price,
  quantity = EXCLUDED.quantity;

    In here the EXCLUDED will give us access to the new data EXCLUDED.price = 16.99 and EXCLUDED.quantity = 120

    ← Join Tables
    Postgres →