PostgreSql Five - Plsql 5 (40 Min)
Table Partitioning
CREATE TABLE e_transactions(
order_id varchar(255) NULL,
ordered_at date NULL,
shipped_at date NULL,
order_amount numeric(18, 0) NULL,
customer_id varchar(255) NULL
) PARTITION BY RANGE( ordered_at);
CREATE TABLE orders_2020_07_01
PARTITION OF e_transactions FOR VALUES FROM ('2019-01-01') TO ('2020-01-01');
CREATE TABLE orders_2020_07_02
PARTITION OF e_transactions FOR VALUES FROM ('2020-01-01') TO ('2021-01-01');
INSERT INTO public.e_transactions(
order_id, ordered_at, shipped_at, order_amount, customer_id)
VALUES (1,'2019-02-01','2019-02-01',456,'1'),
(2,'2019-03-03','2019-03-03',631,'1');
INSERT INTO public.e_transactions(
order_id, ordered_at, shipped_at, order_amount, customer_id)
VALUES (1,'2020-02-01','2020-02-01',456,'1'),
(2,'2020-03-03','2020-03-03',631,'1');
select * from e_transactions
select * from orders_2020_07_01
select * from orders_2020_07_02
----------------------------------------------------------------------------
Postgres FDW
Use these queries in a test1 db to connect test2 db
CREATE EXTENSION postgres_fdw;
CREATE SERVER fdw_server
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host 'localhost', dbname 'test2', port '5432');
CREATE USER MAPPING FOR postgres
SERVER fdw_server OPTIONS (user 'postgres', password 'postgres');
IMPORT FOREIGN SCHEMA public FROM SERVER fdw_server INTO public;
SELECT * FROM actor a join candidates c
on a.actor_id = c.candidate_id;
------------------------------------
CREATE TABLE persons (
id SERIAL,
first_name VARCHAR(50),
last_name VARCHAR(50),
dob DATE,
email VARCHAR(255),
PRIMARY KEY (id)
)
Import the file in table - D:\persons.csv
As well export file table ( Writing the file )
------------------------------------
null if
SELECT
NULLIF (1, 1);
-- return NULL
SELECT
NULLIF (1, 0); -- return 1
SELECT
NULLIF ('A', 'B'); -- return A
------------------------------------
cast
SELECT
'100'::INTEGER,
'01-OCT-2015'::DATE;
------------------------------------
SELECT random();
To generate a random number between 1 and 11, you use the following statement:
SELECT random() * 10 + 1 AS RAND_1_11;
--------------------\
Duplicate Value Delete
CREATE TABLE basket(
id SERIAL PRIMARY KEY,
fruit VARCHAR(50) NOT NULL
);
INSERT INTO basket(fruit) values('apple');
INSERT INTO basket(fruit) values('apple');
INSERT INTO basket(fruit) values('orange');
INSERT INTO basket(fruit) values('orange');
INSERT INTO basket(fruit) values('orange');
INSERT INTO basket(fruit) values('banana');
SELECT
id,
fruit
FROM
basket;
SELECT
fruit,
COUNT( fruit )
FROM
basket
GROUP BY
fruit
HAVING
COUNT( fruit )> 1
ORDER BY
fruit;
DELETE FROM
basket a
USING basket b
WHERE
a.id < b.id
AND a.fruit = b.fruit;
SELECT
id,
fruit
FROM
basket;
DELETE FROM
basket a
USING basket b
WHERE
a.id > b.id
AND a.fruit = b.fruit;
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)
To check whether the statement works correctly, let’s verify the data in the basket table:
SELECT
id,
fruit
FROM
basket;
WHERE id IN
(SELECT id
FROM
(SELECT id,
ROW_NUMBER() OVER( PARTITION BY column_1,
column_2
ORDER BY id ) AS row_num
FROM table_name ) t
WHERE t.row_num > 1 );
---------
The information_schema.columns catalog contains the information on columns of all tables.
To get information on columns of a table, you query the information_schema.columns catalog. For example:
SELECT
table_name,
column_name,
data_type
FROM
information_schema.columns
WHERE
table_name = 'city';
----------
Section 4. Roles & Privileges
PostgreSQL represents accounts as roles. Roles that can log in called login roles or users. Roles that contain other roles are called group roles. In this section, you will learn how to manage roles and groups effectively.
Comments
Post a Comment