How to Insert and Delete Rows in PostreSQL tables
This PG Casts episode is sponsored by Hashrocket, a consultancy specializing in PostgreSQL; learn more at https://hashrocket.com. To see more PG Casts videos, visit our YouTube channel or https://www.pgcasts.com
Transcript:
Hey everyone, today we're going to look at how to insert and delete rows in Postgres.
---
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.
```sql
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.
```sql
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.
```sql
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.
```sql
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.
```sql
select * from users where name = 'Sam';
```
If postgres isn't able to automatically convert the value for our column, it will throw an error.
```sql
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:
```sql
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:
```sql
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.
```sql
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.
```sql
delete from users;
```
If we now try to pull data from our table, we can see that it's empty.
```sql
select * from users;
```
Thanks for watching!
Watch video How to Insert and Delete Rows in PostreSQL tables online without registration, duration hours minute second in high quality. This video was added by user PG Casts by Hashrocket 11 July 2019, don't forget to share it with your friends and acquaintances, it has been viewed on our site 4,068 once and liked it 19 people.