Basic CRUD operations in SQL
Lets create,read, update and delete customers
The following steps will help you get started as quickly as possible, manipulating SQL data.
Intro
PostgreSQL vs. MariaDB: Features, Performance, Use Cases Lets create a customer or two, Assuming you have Its time to get the hang of CRUD on your fresh database.
So given we need to store customers in our database, we create customers
table, in which we store informations like:
- First name
- Last name
- Age
In theory, First Name and Last Name or Email, are a text, in SQL, there is many text types, but to keep it simple we will use VARCHAR
, think of it as a string
type, but with a predefined max length we provide, 150 in our case is the maximum number of charachters for a first name or last name. And 50 charachters for the email.
As for age, its obviously a numeric type, we will use integer.
This is what we will do:
- Create Table
- With columns/fields in the table (name and data type)
- create a customer read, update, delete.
Create Table
Lets first create a table, with the predefined rows, customer which is auto increment id, set by
-- CREATE TABLE tableName (
-- column name followed by column data type or any conditions to the column
--);
CREATE TABLE customers (
customer_id SERIAL PRIMARY KEY,
first_name VARCHAR(150),
last_name VARCHAR(50),
email VARCHAR(50),
age INT
);
Retrieve all data from customers
To retrieve all data of a column
-- SELECT * FROM tableName
SELECT * FROM customers
If you might have noticed, the customer_id is auto incremented and is set Postgres, it will keep auto-incrementing.
Inserting
Insert a record
Let’s insert a row of data.
-- INSERT INTO tableName (column1, column2, column3.....)
-- VALUES (value1, value2, value3.....)
INSERT INTO customers (first_name, last_name, email, age)
VALUES ('Mohamed', 'ALLAM', 'a@b.com', 30);
Quotes in the data
If you have quotes in your insertion, simply double the one you want to insert, This indicates to Postgres to escape the quotes.
-- To escape quotes we have to double them so, John's, becomes John''s
INSERT INTO customers (first_name, last_name, email, age)
VALUES ('Mohamed''s', 'ALLAM', 'a@b.com', 30);
Insert multiple records
We often want to insert multiple data at once, its easy.
-- INSERT INTO tableName (column1, column2, ....)
-- VALUES
--('value1','value2')
--('value1','value2')
--('value1','value2')
INSERT INTO customers (first_name, last_name)
VALUES
('Mohamed', 'Allam'),
('Jane','Doe'),
('John','Doe');
Returning
If we want to return all the rows and data, we inserted we run:
-- INSERT INTO tableName (column1, column2...)
-- VALUES ('value1', 'value2') returning *;
INSERT INTO customers (first_name, last_name)
VALUES
('John','Doe') returning *;
If however we want to return just a specific row, we return the name of the column:
-- INSERT INTO tableName (column1, column2...)
-- VALUES ('value1', 'value2') returning columnName;
INSERT INTO customers (first_name, last_name)
VALUES
('John','Doe') returning customer_id;
UPDATING
Update a row
-- UPDATE tablename
-- SET columnname = 'new value'
-- WHERE columnname = 'value'
UPDATE customers
SET email = 'yes@aol.com'
WHERE "customer_id" = 3
Update one column and return the rows
-- UPDATE tablename
-- SET columnname = 'new value'
-- WHERE columnname = 'value'
-- RETURNING *;
UPDATE customers
SET email = 'yes@aol.com'
WHERE "customer_id" = 3
returning *;
Update one column and return by column name
-- UPDATE tablename
-- SET columnname = 'new value'
-- WHERE columnname = 'value'
-- RETURNING columnName;
UPDATE customers
SET email = 'yes@aol.com'
WHERE "customer_id" = 3
returning customer_id;
Update Multiple columns
-- UPDATE tablename
-- SET columnname = 'new value', anotherColumn = 'some new value'
-- WHERE columnname = 'value'
UPDATE customers
SET email = 'yes@aol.com', age = 40
WHERE first_name = 'Mohamed'
Update Multiple columns and returning
-- UPDATE tablename
-- SET columnname = 'new value', anotherColumn = 'some new value'
-- WHERE columnname = 'value'
-- RETURNING *;
UPDATE customers
SET email = 'yes@aol.com', age = 40
WHERE first_name = 'Mohamed' returning *;
Update Multiple columns and returning a column name
-- UPDATE tablename
-- SET columnname = 'new value', anotherColumn = 'some new value'
-- WHERE columnname = 'value'
-- RETURNING *;
UPDATE customers
SET email = 'yes@aol.com', age = 40
WHERE first_name = 'Mohamed' returning customer_id;
Update all records in a database
If you update with no where clause, you would update all records in a database, so be careful
-- UPDATE tablename
-- SET columnname = 'new value'
UPDATE customers
SET age = '30'
DELETING
Delete a record
DELETE FROM customers
where customer_id = 3
Delete all records
DELETE FROM customers
UPSERT
So you might have guessed, upsert, is made up of insert and update. This instructs postgres to update row if it already exists, otherwise it will perform an insert.
-- INSERT INTO tableName (column_list)
-- VALUES (value_list)
-- ON CONFLICT target action;
The action is:
-- ON CONFLICT DO NOTHING
-- ON CONFLICT DO UPDATE SET column_1 = value_1
-- WHERE some_column = value
CREATE TABLE t_tags (
id SERIAL PRIMARY KEY,
tag text UNIQUE, --enforce unique case sensitive value
update_date TIMESTAMP DEFAULT NOW()
);
INSERT INTO t_tags (tag) values
('pen'),
('pencil')
returning *;
INSERT INTO t_tags(tag)
VALUES ('pen')
ON CONFLICT (tag)
DO NOTHING;
Mention if we check on the Ids
INSERT INTO t_tags(tag)
VALUES ('pen')
ON CONFLICT (tag)
DO UPDATE SET
tag = EXCLUDED.tag,
update_date = NOW(); -- just to show we excluded the update we made
INSERT INTO t_tags(tag)
VALUES ('pen')
ON CONFLICT (tag)
DO UPDATE SET
tag = EXCLUDED.tag,
update_date = NOW(); -- just to show we excluded the update we made