 create table if not exists profiles
(
  profile_id uuid primary key,
  user_id uuid unique,
  first_name varchar not null,
  last_name varchar not null,
  phone_number varchar,
  address_id uuid,
  date_of_birth date,
  generation varchar not null,
  create_at timestamp default now()
);

create table if not exists addresses
(
  address_id uuid default (gen_random_uuid()) primary key,
  user_id uuid not null,
  address varchar not null,
  create_at timestamp default now()
);

create table if not exists shops
(
  shop_id uuid default (gen_random_uuid()) primary key,
  user_id uuid not null,
  address_id uuid not null,
  name varchar not null,
  is_ban bool default false,
  create_at timestamp default now()
);

create table if not exists product_types
(
  product_type_id serial primary key,
  name varchar not null,
  created_at timestamp default now()
);

create table if not exists products
(
  product_id uuid default (gen_random_uuid()) primary key,
  product_type_id int not null,
  shop_id uuid not null,
  coupon_id uuid, 
  name varchar not null,
  price numeric not null,
  quantity int not null,
  is_ban bool default false,
  create_at timestamp default now()
);

create table if not exists coupons
(
  coupon_id uuid default (gen_random_uuid()) primary key,
  shop_id uuid not null,
  name varchar not null,
  quantity bigint not null,
  is_percent bool not null,
  discount decimal not null,
  is_ban bool default false,
  create_at timestamp default now(),
  expiry_at timestamp not null check(expiry_at > create_at)
);

create table if not exists order_types
(
  order_type_id serial primary key,
  type varchar not null,
  create_at timestamp default now()
);

create table if not exists orders
(
  order_id uuid default (gen_random_uuid()) primary key,
  order_type_id int not null,
  product_id uuid not null,
  user_id uuid not null,
  coupon_id uuid,
  total_product int not null
);

create table if not exists payment_types
(
  payment_type_id serial primary key,
  type varchar not null,
  is_use bool default true,
  create_at timestamp default now()
);

create table if not exists payments
(
  payment_id uuid default (gen_random_uuid()) primary key,
  payment_type_id int not null,
  amount decimal not null,
  is_pay bool default false,
  create_at timestamp default now()
);

create table if not exists transport_types
(
  transport_type_id serial primary key,
  name varchar not null,
  is_use bool default true,
  create_at timestamp default now()
);

create table if not exists transports
(
  transport_id uuid default (gen_random_uuid()) primary key,
  transport_address_id uuid not null,
  transport_type_id int not null,
  coupon_id uuid,
  shop_prepare_at timestamp,
  shop_ready_at timestamp,
  transport_ready_at timestamp,
  transport_complete_at timestamp,
  transport_amount decimal not null
);

create table if not exists histories
(
  history_id uuid default (gen_random_uuid()) primary key,
  transport_id uuid,
  payment_id uuid,
  order_id uuid not null,
  create_at timestamp default now()
);

create schema auth;

create table auth.users
(
  id uuid primary key,
  name varchar
);



alter table profiles
add constraint fk_profiles_user_id foreign key (user_id) references auth.users(id),
add constraint fk_profiles_address_id foreign key (address_id) references addresses(address_id);

alter table addresses
add constraint fk_addresses_user_id foreign key (user_id) references profiles(user_id);

alter table shops
add constraint fk_shops_user_id foreign key (user_id) references auth.users(id),
add constraint fk_shop_address_id foreign key (address_id) references addresses(address_id);

alter table products
add constraint fk_products_product_type_id foreign key (product_type_id) references product_types(product_type_id),
add constraint fk_products_shop_id foreign key (shop_id) references shops(shop_id),
add constraint fk_products_coupon_id foreign key (coupon_id) references coupons(coupon_id);

alter table coupons
add constraint fk_coupons_shop_id foreign key (shop_id) references shops(shop_id);

alter table orders
add constraint fk_orders_product_id foreign key (product_id) references products(product_id),
add constraint fk_orders_user_id foreign key (user_id) references auth.users(id),
add constraint fk_orders_coupon_id foreign key (coupon_id) references coupons(coupon_id),
add constraint fk_orders_order_type_id foreign key (order_type_id) references order_types(order_type_id);

alter table payments
add constraint fk_payments_payment_type_id foreign key (payment_type_id) references payment_types(payment_type_id);

alter table transports
add constraint fk_transports_transport_address_id foreign key (transport_address_id) references addresses(address_id),
add constraint fk_transports_coupon_id foreign key (coupon_id) references coupons(coupon_id),
add constraint fk_transports_transport_type_id foreign key (transport_type_id) references transport_types(transport_type_id);

alter table histories
add constraint fk_histories_transport_id foreign key (transport_id) references transports(transport_id),
add constraint fk_histories_payment_id foreign key (payment_id) references payments(payment_id),
add constraint fk_histories_order_id foreign key (order_id) references orders(order_id);

delete from product_types;

DO $$
DECLARE
    product_types_seq text;
BEGIN
    SELECT pg_get_serial_sequence('product_types', 'product_type_id') INTO product_types_seq;
    
    -- Kiểm tra nếu sequence tồn tại, thực hiện ALTER SEQUENCE
    IF product_types_seq IS NOT NULL THEN
        EXECUTE 'ALTER SEQUENCE ' || product_types_seq || ' RESTART WITH 1';
    END IF;
END $$;

-- Thêm dữ liệu vào bảng
INSERT INTO product_types (name)
VALUES
    ('outfit'),
    ('book'),
    ('food and drink'),
    ('houseware'),
    ('other')
ON CONFLICT DO NOTHING;

insert into shops (user_id, address_id, name)
select '56dd6a9f-7627-4984-95a2-e8775d37e1ac', address_id, 'QH electronic store'
from addresses
where addresses.user_id = '56dd6a9f-7627-4984-95a2-e8775d37e1ac'
on conflict do nothing;

insert into products (product_type_id, shop_id, name, price, quantity)
select 4, shop_id, 'fan 1', 500000, 10 from products union all
select 4, shop_id, 'hairdryer 1', 250000.250, 10 from products
where exists (
  select 1 from products inner join shops on products.shop_id = shops.shop_id
  where shops.user_id = '56dd6a9f-7627-4984-95a2-e8775d37e1ac'
)
and not exists (
  select 1 from products inner join shops on products.shop_id = shops.shop_id and shops.user_id = '56dd6a9f-7627-4984-95a2-e8775d37e1ac' where
  (products.name ILIKE 'fan') or
  (products.name ILIKE 'hairdryer 1')
);

alter table coupons drop constraint fk_coupons_shop_id;
delete from coupons;
alter table coupons add constraint fk_coupons_shop_id foreign key (shop_id) references shops(shop_id);
/*
insert into coupons (shop_id, name, quantity, is_percent, discount, expiry_at)
SELECT shop_id, 'coupon for festival', 10, true, 10.0, cast('2023-07-01 23:59:59'as timestamp) FROM (
  SELECT shop_id FROM shops WHERE name = 'QH electronic store'
) AS subquery
UNION ALL
SELECT shop_id, 'coupon for festival 2', 999, true, 10.0, cast('2023-07-01 23:59:59'as timestamp) FROM (
  SELECT shop_id FROM shops WHERE name = 'QH electronic store'
) AS subquery;
*/
insert into coupons (shop_id, name, quantity, is_percent, discount, expiry_at)
select shop_id, 'coupon for festival', 10, true, 10.0, cast('2023-07-01 23:59:59' as timestamp) from
(
  select shop_id from shops where shops.name = 'QH electronic store'
) as subquery
union all
select shop_id, 'coupon for festival 2', 999, true, 10.0, cast('2023-07-01 23:59:59' as timestamp) from
(
  select shop_id from shops where shops.name = 'QH electronic store'
) as subquery;


insert into shops (user_id, address_id, name)
select '56dd6a9f-7627-4984-95a2-e8775d37e1ac', address_id, 'QH electronic store'
from addresses
where addresses.user_id = '56dd6a9f-7627-4984-95a2-e8775d37e1ac'
on conflict do nothing;

insert into products (product_type_id, shop_id, name, price, quantity)
select 4, shop_id, 'fan 1', 500000, 10 from shops union all
select 4, shop_id, 'hairdryer 1', 250000.250, 10 from shops
where (
  shops.name = 'QH electronic store'
)
/*
and not exists (
  select 1 from products inner join shops on products.shop_id = shops.shop_id and shops.name = 'QH electronic store' where
  (products.name ILIKE 'fan') or
  (products.name ILIKE 'hairdryer 1')
)
*/
;

alter table profiles drop constraint fk_profiles_address_id;
alter table profiles drop constraint fk_profiles_user_id;

alter table addresses drop constraint fk_addresses_user_id;

alter table shops drop constraint fk_shop_address_id;

delete from profiles;
delete from addresses;
deleet from shops;

alter table profiles
add constraint fk_profiles_user_id foreign key (user_id) references auth.users(id),
add constraint fk_profiles_address_id foreign key (address_id) references addresses(address_id);

alter table addresses
add constraint fk_addresses_user_id foreign key (user_id) references profiles(user_id);

alter table shops
add constraint fk_shop_address_id foreign key (address_id) references addresses(address_id);


insert into profiles (user_id, first_name, last_name, phone_number, generation) values
('56dd6a9f-7627-4984-95a2-e8775d37e1ac', 'diep', 'huy', '0942159066', 'nam')
on conflict do nothing;

insert into addresses (user_id, address)
select '56dd6a9f-7627-4984-95a2-e8775d37e1ac', '54, ngo 34/1, phuong Mo Lao, quan Ha Dong, Ha Noi, Viet Nam'
where not exists (
  select 1 from addresses as a where (a.user_id = '56dd6a9f-7627-4984-95a2-e8775d37e1ac' and a.address = '54, ngo 34/1, phuong Mo Lao, quan Ha Dong, Ha Noi, Viet Nam')
);

created 2 years ago

PostgreSQL online editor

Write, Run & Share PostgreSQL queries online using OneCompiler's PostgreSQL online editor and compiler for free. It's one of the robust, feature-rich online editor and compiler for PostgreSQL. Getting started with the OneCompiler's PostgreSQL editor is really simple and pretty fast. The editor shows sample boilerplate code when you choose database as 'PostgreSQL' and start writing queries to learn and test online without worrying about tedious process of installation.

About PostgreSQL

PostgreSQL is a open source relational database system and is also knows as Postgres.

Key Features:

Postgres is not only free and open-source but also it is highly extensible.
Custom Data types and funtions from various programming languaues can be introduced and the good part is compiling entire database is not required.
ACID(Atomicity, Consistency, Isolation, Durability) compliant.
First DBMS which implemented Multi-version concurrency control (MVCC) feature.
It's the default database server for MacOS.
It supports all major operating systems like Linux, Windows, OpenBSD,FreeBSD etc.

Syntax help

1. CREATE

CREATE command is used to create a table, schema or an index.

Syntax:

         CREATE TABLE table_name (
                column1 datatype,
                column2 datatype,
                ....);

2. ALTER

ALTER command is used to add, modify or delete columns or constraints from the database table.

Syntax

ALTER TABLE Table_name ADD column_name datatype;

3. TRUNCATE:

TRUNCATE command is used to delete the data present in the table but this will not delete the table.

Syntax

TRUNCATE table table_name;

4. DROP

DROP command is used to delete the table along with its data.

Syntax

DROP TABLE table_name;

5. RENAME

RENAME command is used to rename the table name.

Syntax

ALTER TABLE table_name1 RENAME to new_table_name1;

6. INSERT

INSERT Statement is used to insert new records into the database table.

Syntax

INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...);

7. SELECT

Select statement is used to select data from database tables.

Syntax:

SELECT column1, column2, ...
FROM table_name;

8. UPDATE

UPDATE statement is used to modify the existing values of records present in the database table.

Syntax

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

9. DELETE

DELETE statement is used to delete the existing records present in the database table.

Syntax

DELETE FROM table_name where condition;