CRUD SQL queries

The following steps will help you get started as quickly as possible, manipulating SQL data.

Intro

Lets create a customer or two, Assuming you have installed and configured, 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
Email
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.

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;

Update a record

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'

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