Transcript

For this example, I've already created a dummy database with a table called users.

We can inspect the table using the "\d" metacommand.

\d users

INSERT

To add a new row to our users table, we use the "insert" command. The "insert" command expects a table name and a comma separated list of values for our column data.

insert into users values ('Bob', 35, 'New York');

By default, if no list of column names is given to the insert command, the values provided are inserted in the columns in their declared order. If we want to change the order, or omit columns, we specify to the insert command the columns we are sending.

insert into users (age, hometown) values (35, 'Houston');

We can also insert multiple rows at once by providing a comma separated list of column values.

insert into users values ('Joe', 42, 'Tampa'), ('Amy', 24, 'Boston');

During insertion, Postgres will try to automatically convert values that are not the intended type for any columns. For an example, let's pass a string to our integer column.

insert into users values ('Sam', '33', 'Detroit');

By querying for our new user, we can see that the age was converted properly to an integer.

select * from users where name = 'Sam';

If postgres isn't able to automatically convert the value for our column, it will throw an error.

insert into users values ('Sam', 'Some age', 'Detroit');

RETURNING

Postgres easily allows us to see the data inserted into our tables with the non-standard "returning" extension. We can use this extension by appending "returning" and the column names we want (or just * for all columns) to our insert command, like so: 

insert into users values ('Sue', 89, 'Phoenix') returning *;

DELETE

To delete rows from our table, we use the "delete" command.

The delete command allows us to use a where clause to select the rows we want to delete. So if we wanted to delete all users named "Sam", we could say: 

delete from users where name = 'Sam';

The "returning" extension that we used earlier can also be used on the delete command, so we can see the rows that were deleted. Let's use this and try deleting all of our users over the age of 50.

delete from users where age > 50;

If our goal is to completely remove all users from our table, we simply omit the where clause from our delete statement.

delete from users;

If we now try to pull data from our table, we can see that it's empty.

select * from users;

Thanks for watching!

Setup

create table users (
  name varchar,
  age integer,
  hometown varchar
);