Tablolar.md

User Tablosu

create table users(
id serial not null primary key,
username varchar(15) not null UNIQUE,
password text not null,
email text not null UNIQUE,
c_date timestamp default current_timestamp,full_name varchar(40) , 
status_id int not null references status(id),
lastlogin timestamp not null default current_timestamp,
admin_id int not null references admin(id)
);

Status Tablo

create table status(id serial not null primary key,
is_active boolean not null);

Admin Tablo

create table admin(id serial not null primary key,
is_admin boolean not null);

Game Tablosu

create table game(
id serial not null primary key,
name varchar(100) not null UNIQUE,
price real not null CHECK (price > 0),
popularity int not null,
created_date timestamp not null default current_timestamp,
update_date timestamp not null default current_timestamp,
logo varchar(200),
genre_id int not null references genre(id),
company_id int not null references company(id),
platform_id int not null references platform(id)
);

Company Table

create table company(id serial not null primary key,
company_name varchar(40) not null
created_date timestamp default current_timestamp);

Platform Table

create table platform(id serial not null primary key,
platform_name varchar(40) not null
created_date timestamp default current_timestamp);

Sale Tablosu

create table sale(
id serial not null primary key,
user_id int not null references users(id),
game_id int not null references game(id),
c_date timestamp default current_timestamp,
amount real not null CHECK (amount > 0));

Genre Tablosu

create table genre(
id serial not null primary key,
genre_name varchar(40) not null UNIQUE
created_date timestamp default current_timestamp);

Insert İşlemleri

insert into game (name,price,popularity,genre_id) values ('Wow',100,1,1);
insert into game (name,price,popularity,genre_id) values ('Metin2',300,10,2);

OrderBy Kullanımı

Büyükten küçüğe
select * from game order by created_date desc;
Küçükten büyüğe
select * from game order by created_date asc;

İç içe Select

select * from sale where user_id = (select id from users where username = 'utkucan');

NullIf ve Case

select NULLIF('adasd',logo) from game where id=1;
select * , case when balance = 0 then 1000 end from users;

Trigger için Fonksiyon

create or replace function logfunction() returns trigger as $log_trigger$
begin
IF (TG_OP = 'DELETE') THEN
insert into log (username,date,name,operation,price,genre_id,logo,game_id) values (user,now(),OLD.name,'D',OLD.price,OLD.genre_id,OLD.logo,OLD.id);
return OLD;
ELSIF(TG_OP = 'UPDATE') THEN
insert into log (username,date,name,operation,price,genre_id,logo,game_id) values (user,now(),OLD.name,'U',OLD.price,OLD.genre_id,OLD.logo,NEW.id);
return OLD;
ELSIF (TG_OP = 'INSERT') THEN
insert into log (username,date,name,operation,price,genre_id,logo,game_id) values(user,now(),NEW.name,'I',NEW.price,NEW.genre_id,NEW.logo,NEW.id);
return NEW;
end if;
return null;
end;$log_trigger$ language plpgsql;

Trigger

create trigger logt after insert or update or delete on game for each row execute PROCEDURE logfunction();

Postgresql Login

psql -h <host_ip> -U <username> -d game_trnetdb

Show All Connection

select *
from pg_stat_activity
where datname = 'mydatabasename'

Import Db

psql -U USERNAME DBNAME < dbexport.pgsql

Wal Açma-Kapama

SELECT pg_start_backup('archive');
select pg_stop_backup();

Truncate Kullanımı(Tüm Tabloyu silmek (id resetlenir)

TRUNCATE TABLE  table_name;