PostgreSql Three- Plsql 3 (20 Min)
PostgreSQL copy table example
CREATE TABLE contacts(
id SERIAL PRIMARY KEY,
first_name VARCHAR NOT NULL,
last_name VARCHAR NOT NULL,
email VARCHAR NOT NULL UNIQUE
);
INSERT INTO contacts(first_name, last_name, email)
VALUES('John','Doe','john.doe@postgresqltutorial.com'),
('David','William','david.william@postgresqltutorial.com');
CREATE TABLE contact_backup
AS TABLE contacts;
SELECT * FROM contact_backup;
id | first_name | last_name | email
----+------------+-----------+--------------------------------------
1 | John | Doe | john.doe@postgresqltutorial.com
2 | David | William | david.william@postgresqltutorial.com
(2 rows)
------------------------------------------
CHECK
DROP TABLE IF EXISTS employees;
CREATE TABLE employees (
id SERIAL PRIMARY KEY,
first_name VARCHAR (50),
last_name VARCHAR (50),
birth_date DATE CHECK (birth_date > '1900-01-01'),
joined_date DATE CHECK (joined_date > birth_date),
salary numeric CHECK(salary > 0)
);
INSERT INTO employees (first_name, last_name, birth_date, joined_date, salary)
VALUES ('John', 'Doe', '1972-01-01', '2015-07-01', 100000);
INSERT INTO employees (first_name, last_name, birth_date, joined_date, salary)
VALUES ('John', 'Doe', '1972-01-01', '2015-07-01', -100000);
-----------------------------------
CREATE TABLE person (
id SERIAL PRIMARY KEY,
first_name VARCHAR (50),
last_name VARCHAR (50),
email VARCHAR (50),
UNIQUE(email)
);
INSERT INTO person(first_name,last_name,email)
VALUES('john','doe','j.doe@postgresqltutorial.com');
INSERT INTO person(first_name,last_name,email)
VALUES('john','doe','j.doe@postgresqltutorial.com');
-----------------------------------
To generate the UUID values based on the combination of computer’s MAC address, current timestamp, and a random value, you use the uuid_generate_v1() function:
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
SELECT uuid_generate_v1();
If you want to generate a UUID value solely based on random numbers, you can use the uuid_generate_v4() function. For example:
SELECT uuid_generate_v4();
------------------------------------------
CREATE TABLE contacts (
contact_id uuid DEFAULT uuid_generate_v4 (),
first_name VARCHAR NOT NULL,
last_name VARCHAR NOT NULL,
email VARCHAR NOT NULL,
phone VARCHAR,
PRIMARY KEY (contact_id)
);
INSERT INTO contacts (
first_name,
last_name,
email,
phone
)
VALUES
(
'John',
'Smith',
'john.smith@example.com',
'408-237-2345'
),
(
'Jane',
'Smith',
'jane.smith@example.com',
'408-237-2344'
),
(
'Alex',
'Smith',
'alex.smith@example.com',
'408-237-2343'
);
SELECT
*
FROM
contacts;
---------------------------------------------------
CREATE TABLE orders (
id serial NOT NULL PRIMARY KEY,
info json NOT NULL
);
INSERT INTO orders (info)
VALUES('{ "customer": "John Doe", "items": {"product": "Beer","qty": 6}}');
INSERT INTO orders (info)
VALUES('{ "customer": "Lily Bush", "items": {"product": "Diaper","qty": 24}}'),
('{ "customer": "Josh William", "items": {"product": "Toy Car","qty": 1}}'),
('{ "customer": "Mary Clark", "items": {"product": "Toy Train","qty": 2}}');
SELECT info FROM orders;
SELECT info -> 'customer' AS customer
FROM orders;
SELECT info -> 'items' ->> 'product' as product
FROM orders
ORDER BY product;
SELECT info ->> 'customer' AS customer
FROM orders
WHERE info -> 'items' ->> 'product' = 'Diaper';
SELECT info ->> 'customer' AS customer,
info -> 'items' ->> 'product' AS product
FROM orders
WHERE CAST ( info -> 'items' ->> 'qty' AS INTEGER) = 2
SELECT
MIN (CAST (info -> 'items' ->> 'qty' AS INTEGER)),
MAX (CAST (info -> 'items' ->> 'qty' AS INTEGER)),
SUM (CAST (info -> 'items' ->> 'qty' AS INTEGER)),
AVG (CAST (info -> 'items' ->> 'qty' AS INTEGER))
FROM orders;
SELECT json_each (info)
FROM orders;
------------------------------------------
select fibonacci(11)
CREATE OR REPLACE FUNCTION fibonacci (n INTEGER)
RETURNS INTEGER AS $$
DECLARE
counter INTEGER := 0 ;
i INTEGER := 0 ;
j INTEGER := 1 ;
BEGIN
IF (n < 1) THEN
RETURN 0 ;
END IF;
LOOP
EXIT WHEN counter = n ;
counter := counter + 1 ;
SELECT j, i + j INTO i, j ;
END LOOP ;
RETURN i ;
END ;
$$ LANGUAGE plpgsql;
------------------------------------------
21.PL/pgSQL WHILE loop example
********** **************
do $$
declare
counter integer := 0;
begin
while counter < 5 loop
raise notice 'Counter %', counter;
counter := counter + 1;
end loop;
end$$;
----------------
CREATE OR REPLACE FUNCTION fibonacci (n INTEGER)
RETURNS INTEGER AS $$
DECLARE
counter INTEGER := 0 ;
i INTEGER := 0 ;
j INTEGER := 1 ;
BEGIN
IF (n < 1) THEN
RETURN 0 ;
END IF;
WHILE counter <= n LOOP
counter := counter + 1 ;
SELECT j, i + j INTO i, j ;
END LOOP ;
RETURN i ;
END ;
------------------------------------------
Indexes
Single-Column Indexes
CREATE INDEX index_name
ON COMPANY (name);
Multicolumn Indexes
CREATE INDEX index_name1
ON company (salary, join_date);
------------------------------------------
PASSWORD ENCRYPT
SELECT *
FROM users
WHERE email = 'joy@mail.com'
AND password = crypt('passwordtext', password);
INSERT INTO users (email, password) VALUES (
'joy@mail.com',
crypt('passwordtext', gen_salt('md5'))
);
------------------------------------------
SELECT & UPDATE
******************
INSERT INTO cm_traceable(c_pckid ,c_rackid,rack_typ, c_zone,pack_nr,bol_no,mrkt,
prod_lotno,nb_vol)
SELECT c_pckid ,c_rackid,rack_typ, c_zone,pack_nr,bol_no,mrkt,prod_lotno,nb_vol
FROM wm_pckinfo where c_rackid=rack_number and c_pckid=label_number;
update cm_traceable set UpdatedBy = updated_by where c_rackid=rack_number and
c_pckid=label_number;
------------------------------------------
PostgreSQL Exception
Handling no_data_found exception example
The following example issues an error because the film with id 2000 does not exist.
do
$$
declare
rec record;
v_film_id int = 2000;
begin
-- select a film
select film_id, title
into strict rec
from film
where film_id = v_film_id;
end;
$$
language plpgsql;
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)
Output:
ERROR: query returned no rows
CONTEXT: PL/pgSQL function inline_code_block line 6 at SQL statement
SQL state: P0002
Code language: Shell Session (shell)
The following example uses the exception clause to catch the no_data_found exception and report a more meaningful message:
do
$$
declare
rec record;
v_film_id int = 2000;
begin
-- select a film
select film_id, title
into strict rec
from film
where film_id = v_film_id;
-- catch exception
exception
when no_data_found then
raise exception 'film % not found', v_film_id;
end;
$$
language plpgsql;
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)
Output:
ERROR: film 2000 not found
CONTEXT: PL/pgSQL function inline_code_block line 14 at RAISE
SQL state: P0001
------------------------------------------
CREATE TABLESPACE
CREATE TABLESPACE ts_primary
LOCATION 'c:\pgdata\primary';
CREATE DATABASE logistics
TABLESPACE ts_primary;
CREATE TABLE deliveries (
delivery_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
order_date DATE,
customer_id INT
);
INSERT INTO deliveries(order_date, customer_id)
VALUES('2020-08-01',1);
ALTER TABLESPACE logistics
RENAME TO dvdrental_raid;
ALTER TABLESPACE dvdrental_raid
OWNER to postgres;
CREATE TABLESPACE demo
LOCATION 'E:\@udemy\@Data base\@TableSpace';
CREATE DATABASE dbdemo
TABLESPACE = demo;
CREATE TABLE test (
ID serial PRIMARY KEY,
title VARCHAR (255) NOT NULL
) TABLESPACE demo;
------------------------------------------
DROP TABLESPACE demo;
DROP DATABASE dbdemo;
ALTER DATABASE dbdemo
SET TABLESPACE = pg_default;
DROP TABLESPACE demo;
----------------------------
Backup And Restore
Back Up
Click Back Up
Give a back up name
Find the success location like this
C:\\Users\\vasan\\OneDrive\\DOCUME~1\\
There find your back up db name that you have given
Restore
Click Restore
Select path and file
Then click restore
---------------------------------------------------------------
Comments
Post a Comment