Sistem database (sys.) - SQL Server

select * from sys.objects
select * from sys.databases
select * from sys.tables
select * from sys.columns
select * from sys.views
select * from sys.triggers

INFORMATION SCHEMA - SQL SERVER

Mencari Nama Field
SELECT * FROM INFORMATION_SCHEMA.COLUMNS

Cek Constraints
SELECT * FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS

List
    INFORMATION_SCHEMA.CHECK_CONSTRAINTS
    INFORMATION_SCHEMA.COLUMN_DOMAIN_USAGE
    INFORMATION_SCHEMA.COLUMN_PRIVILEGES
    INFORMATION_SCHEMA.COLUMNS
    INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
    INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE
    INFORMATION_SCHEMA.DOMAIN_CONSTRAINTS
    INFORMATION_SCHEMA.DOMAINS
    INFORMATION_SCHEMA.KEY_COLUMN_USAGE
    INFORMATION_SCHEMA.PARAMETERS
    INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
    INFORMATION_SCHEMA.ROUTINE_COLUMNS
    INFORMATION_SCHEMA.ROUTINES
    INFORMATION_SCHEMA.SCHEMATA
    INFORMATION_SCHEMA.TABLE_CONSTRAINTS
    INFORMATION_SCHEMA.TABLE_PRIVILEGES
    INFORMATION_SCHEMA.TABLES
    INFORMATION_SCHEMA.VIEW_COLUMN_USAGE
    INFORMATION_SCHEMA.VIEW_TABLE_USAGE
    INFORMATION_SCHEMA.VIEWS

Convert Format Tanggal - SQL Server

Untuk fomat tanggal YYYY-MM-DD
select convert(varchar, cust.tgllhr, 23) tgllhr from nasabah


Untuk lebih lengkap link format tanggalnya klik Di Sini

Store Procedure untuk report informasi hardisk dari suatu server, report by Email - SQL Server

USE [DATABASENYA]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[SendEmailHardiskSize]     
as     
begin        
 SET NOCOUNT ON;     
   
     
DECLARE @XML1 NVARCHAR(MAX)   
DECLARE @XML2 NVARCHAR(MAX)   
 
DECLARE @BODY NVARCHAR(MAX)
DECLARE @TableBODY NVARCHAR(MAX)

DECLARE @IP_Addr VARCHAR(25)
DECLARE @intFlag INT
DECLARE @Count INT
DECLARE @Warna INT
SET @intFlag = 1


DECLARE @TABLE1 TABLE (     
    [IP] varchar(20)     
    ,[Host Name] varchar(30)
    ,[Drive] varchar(30)
    ,[Type] varchar(30)
    ,[Label] varchar(30)
    ,[Format] varchar(30)
    ,[Total Size] varchar(30)
    ,[Free Size] varchar(30)
    ,[Free %] varchar(30)
    ,[Use Size] varchar(30)
    ,[Use %] varchar(30)
    ,[BgRow] INT 
    ) 

select @Count=COUNT(IP_Addr) from SERVER_A@DB_SERVER_A.DB_SERVER_A.dbo.HDD_Inf
    where CONVERT(varchar,tgl,112) = CONVERT(varchar,GETDATE(),112)
        and [typeOf] <> 'CDRom'

    DECLARE SENDHDD CURSOR FOR
        select distinct IP_Addr from SERVER_A@DB_SERVER_A.DB_SERVER_A.dbo.HDD_Inf
            where CONVERT(varchar,tgl,112) = CONVERT(varchar,GETDATE(),112)
                and [typeOf] <> 'CDRom'
            order by IP_Addr;
    OPEN SENDHDD
    FETCH NEXT FROM SENDHDD INTO @IP_Addr
    WHILE (@@FETCH_STATUS = 0)
    BEGIN
        SET @Warna = (@Count-@intFlag) % 2
       
        INSERT INTO @TABLE1   
            select  IP_Addr IP,
            Hostname [Host Name],
            drive [Drive],
            typeOf [Type],
            labels [Label],
            formats [Format],
            totalSize [Total Size],
            freeSize [Free Size],
            freePercent [Free %],
            useSize [Use Size],
            usePercent [Use %],
            @Warna
            from
                SERVER_A@DB_SERVER_A.DB_SERVER_A.dbo.HDD_Inf
                    WHERE IP_Addr=@IP_Addr
                    AND CONVERT(varchar,tgl,112) = CONVERT(varchar,GETDATE(),112)
                    AND [typeOf] <> 'CDRom'                   
        SET @intFlag = @intFlag + 1
    FETCH NEXT FROM SENDHDD INTO @IP_Addr
    END
    CLOSE SENDHDD
        DEALLOCATE SENDHDD
       
SET @XML1 =
        CAST ((SELECT
                case when [BgRow]=0 then '#90EE90' else '#FFDAB9' end AS [@bgcolor],
                td = [IP],'',
                td =  [Host Name],'',
                td =  [Drive],'',
                td =  [Type],'',
                td =  [Label],'',
                td =  [Format],'',
                td =  [Total Size],'',
                td =  [Free Size],'',
                td =  [Free %],'',
                td =  [USE Size],'',
                td =  [Use %],''
                    FROM (select [IP],[Host Name],[Drive],[Type]
                            ,case when [Label] = '' then '-' else [Label] end [Label]
                            ,case when [Format] = '' then '-' else [Format] end [Format]
                            ,case when [Total Size] = '' then '-' else [Total Size] end [Total Size]
                            ,case when [Free Size] = '' then '-' else [Free Size] end [Free Size]  
                            ,case when [Free %] = '' then '-' else [Free %] end [Free %]
                            ,case when [USE Size] = '' then '-' else [USE Size] end [USE Size]
                            ,case when [Use %] = '' then '-' else [Use %] end [Use %]
                            ,[BgRow]
                            from @TABLE1
                            )t FOR XML PATH('tr'), TYPE ) AS NVARCHAR(MAX))

---to search where low space
SET @XML2 = CAST(( select  [IP] AS 'td'     
                    ,'',[Host Name] AS 'td'     
                    ,'',[Drive] AS 'td'
                    ,'',[Type] AS 'td'
                    ,'',case when [Label] = '' then '-' else [Label] end AS 'td'
                    ,'',case when [Format] = '' then '-' else [Format] end AS 'td'
                    ,'',case when [Total Size] = '' then '-' else [Total Size] end AS 'td' 
                    ,'',case when [Free Size] = '' then '-' else [Free Size] end AS 'td' 
                    ,'',case when [Free %] = '' then '-' else [Free %] end AS 'td' 
                    ,'',case when [USE Size] = '' then '-' else [USE Size] end AS 'td'
                    ,'',case when [Use %] = '' then '-' else [Use %] end AS 'td'
                                       
from @TABLE1 
where
Label not like '%VM%'
      AND Label not like '%VIRTUAL%'
      and cast(REPLACE([Free %],'%','') as int) <=10
                               
FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))     
   
---to create row of table low space
set @TableBODY = '
Data Hardisk yang kurang dari 10%
<br> <table border = 1>      
<tr>     
    <th>Site Id</th>
    <th>Site Name</th> 
    <th>Status</th>
    <th>Type</th>
    <th>Label</th>
    <th>Format</th>
    <th>Total Space</th>
    <th>Free Space</th>
    <th>Free %</th>
    <th>Use Space</th>
    <th>Use %</th>
   
</tr>' +  @XML2 +
'</table><br>'


declare @Urgent varchar(5)
if @TableBODY is not null
begin
    set @Urgent= 'High'
end
else set @Urgent= 'Low'

 if @TableBODY  is null
 set @TableBODY='
 Data Hardisk yang kurang dari 10%
 <br> <table border = 1>      
<tr>     
    <th>Site Id</th>
    <th>Site Name</th> 
    <th>Status</th>
    <th>Type</th>
    <th>Label</th>
    <th>Format</th>
    <th>Total Space</th>
    <th>Free Space</th>
    <th>Free %</th>
    <th>Use Space</th>
    <th>Use %</th>
 </tr>
 <tr>     
    <th>-</th>
    <th>-</th> 
    <th>-</th>
    <th>-</th>
    <th>-</th>
    <th>-</th>
    <th>-</th>
    <th>-</th>
    <th>-</th>
    <th>-</th>
    <th>-</th>
 </tr>
 ' +
'</table><br>'      
     
SET @BODY ='<html><body>     
<style>body,th,td {font-family :verdana; font-size:8pt;}     
    </style>  
Berikut ini adalah data HDD Space Report.<br>
Status HDD Storage Space digunakan untuk memonitoring dan memeriksa sisa Hardisk <br>
pada server Toko dan Server HO. Jika Space drive C: atau D: Jumlah Free space nya <br>
kurang dari 10% maka perlu dilakukan pengecekan hardisk agar space-nya cukup.<br>      
Periode : '+     
CONVERT(VARCHAR(11),GETDATE(),106)+     
 '<br>     
<br>
<br>
'
+ @TableBODY +
'  
<br>
Data Hardisk Size Keseluruhan
<br>
<table border = 1>      
<tr>     
    <th>Site Id</th>
    <th>Site Name</th> 
    <th>Status</th>
    <th>Type</th>
    <th>Label</th>
    <th>Format</th>
    <th>Total Space</th>
    <th>Free Space</th>
    <th>Free %</th>
    <th>Use Space</th>
    <th>Use %</th>
   
</tr>'     
SET @BODY = @BODY + @XML1 +'</table>'+     
 '<br><br>     
Email ini dikirimkan oleh System.<br><br>     
Procedure Name : DATABASENYA.[dbo].[SendEmailHardiskSize]     
</body></html>';   
     
--SEND EMAIL     
  EXEC msdb.dbo.sp_send_dbmail     
  @profile_name = 'Report Services',     
  @body = @body,     
  @body_format ='HTML',
  @importance =@Urgent,
  @recipients = 'noname@gmail.com',          
  @subject = 'Report Monitoring Hardisk Size ' ;     
     
end  ;   

GO

Contoh Store Procedure Dengan IF - SQL Server

USE [DATBASESNYA]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[GetUploadMember]
       (@StartMemberNo Varchar (100)
       ,@EndMemberNo Varchar (100)
       ,@StartDate SmallDateTime
       ,@EndDate SmallDateTime)
AS

BEGIN

-- ini jika ingin ngetes ngejalanin SP nya
--set @StartMemberNo = NULL
--set @EndMemberNo = NULL
--set @StartDate = '2010-05-21'
--set @EndDate = '2011-06-30'

--Filter Jika StartMemberNo blank atau tidak diisi
If @StartMemberNo = ''
    Begin
        Set @StartMemberNo = NULL
    End
--Filter Jika EndMemberNo blank atau tidak diisi
If @EndMemberNo = ''
    Begin
        Set @EndMemberNo = NULL
    End

--Filter Jika semua parameter tidak di isi
If @StartMemberNo IS NULL and @EndMemberNo IS NULL and @StartDate IS NULL and @EndDate IS NULL
    Begin
        Set @StartDate= cast(getdate() as Date)
    End

--Filter jika Startmemberno <> '' dan EndMemberno NULL atau ''
If @StartMemberNo <> '' and @EndMemberNo IS NULL
    Begin
        Set @EndMemberNo=@StartMemberNo
    End

--Filter jika Startmemberno NULL atau '' dan EndMemberno <> ''
If @StartMemberNo IS NULL and @EndMemberNo <> ''
    Begin
        Set @StartMemberNo=@EndMemberNo
    End

--Filter jika StartDate <> '' dan EndDate NULL atau ''
If @StartDate <> '' and @EndDate IS NULL
    Begin
        Set @EndDate=@StartDate
    End

--Filter jika StartDate NULL atau '' dan EndDate <> ''
If @StartDate IS NULL and @EndDate <> ''
    Begin
        Set @StartDate=@EndDate
    End

    Select MSR.[Card Number]
          ,CT.[Name]
          ,CT.[E-Mail] [Email]
          ,CT.[Phone No_] [Mobile Phone]
          ,CT.[Address] + ' '+ CT.[Address 2] [Address]
          ,CT.[City]
          ,MSR.[Ending Date]
    From [Nyamnyung Store, PT$MSR Card Link Setup] MSR With (Nolock)
         INNER JOIN [Nyamnyung Store, PT$Contact] CT With (Nolock) on MSR.[Link No_] = CT.[No_]
    Where [Card Number] >= Coalesce(@StartMemberNo,[Card Number])
          AND [Card Number] <= Coalesce(@EndMemberNo,[Card Number])
          AND [Date Created] >= Coalesce(@StartDate,[Date Created])
          AND [Date Created] <= Coalesce(@EndDate,[Date Created])
    Order By [Card Number]

END
GO

Contoh Store Procedure Dengan Dynamic Query - SQL Server

USE [DATABASENYA]
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[GetDataMember]
     (
      @Status Varchar (max)
      ,@StartJoin datetime
      ,@EndJoin datetime
      )
AS
BEGIN
SET NOCOUNT ON;
declare @Query varchar(max)   
declare @filter varchar(max)

if @Status=''set @filter=null


if @Status <>''
begin   
    if @Status='Not Active'
    begin
        set @filter='And [Ending Date]<getdate()'
    end
    else
    if @Status='Active'
    begin
        set @filter='And [Ending Date]>=getdate()'
    end
   
end
else
begin
    set @filter=''
end
   

set @Query= 
    'SELECT
    [CardNumber],
    [Name],
    [Address],
    [City],
    [Post Code],
    [Phone No_],
    [Date Created],
    [Ending Date],
    [status]
FROM
(
    SELECT
    b.[Card Number] as [CardNumber],
    a.[Name],
    a.[Address],
    a.[City],
    a.[Post Code],
    a.[Phone No_],
    a.[Date Created],
    b.[Ending Date],
    (case
        when b.[Ending Date] >= GETDATE() then ''Active''
        else ''Not Active'' 
    end) as [status]
    FROM [Nyamnyung Store, PT$MSR Card Link Setup] b WITH(NOLOCK)
    left join [Nyamnyung Store, PT$Contact] a WITH(NOLOCK)
        on a.[No_]=b.[Link No_]
    where convert(date,a.[Date Created]) >= '''+ convert(varchar(10),@StartJoin,111)+'''
    and convert (date,a.[Date Created]) <='''+convert(varchar(10),@EndJoin,111)+'''
    '+@filter+'
) X
'
--print(@Query)
Exec(@Query);
END

GO

Backup Database - SQL Server

USE [DATABASENYA]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[backupdbase]
AS
    SET NOCOUNT ON;
DECLARE @name VARCHAR(50) -- database name
DECLARE @path VARCHAR(256) -- path for backup files
DECLARE @fileName VARCHAR(256) -- filename for backup
DECLARE @fileDate VARCHAR(20)
DECLARE @FileDay VARCHAR(20) -- used for file name
    -- Insert statements for procedure here
    --BACKUP DATABASE [STOCK_TAKE] TO  DISK = @idcomp WITH NOFORMAT, NOINIT,  NAME = N'STOCK_TAKE-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
SET @path = 'D:\Backup'
SELECT @FileDay= DATENAME(DW,GETDATE())
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)

DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name <>'tempdb'

OPEN db_cursor 
FETCH NEXT FROM db_cursor INTO @name 

WHILE @@FETCH_STATUS = 0 
BEGIN 

       SET @fileName = @path +'\'+ @FileDay +'\'+ @name + '_' + @fileDate + '.BAK'
       BACKUP DATABASE @name TO DISK = @fileName

       FETCH NEXT FROM db_cursor INTO @name 
END 

CLOSE db_cursor 
DEALLOCATE db_cursor
GO

Cek User Proses - SQL Server

select *
from master..sysprocesses
order by cpu desc

--Cek prosesan/cpu tinggi--
select spid, waitresource, cpu, physical_io, memusage, status, hostname, program_name,nt_username, loginame
from master..sysprocesses
order by cpu desc

--Cek user yang diblok user lain--
select spid, blocked, waitresource, program_name, nt_username, loginame
from master..sysprocesses
where spid <> blocked
and blocked > 0

--cek user--
--dbcc inputbuffer(428)
--sp_who2 428

Pembulatan Angka Desimal - SQL Server

select round(5.123456 , 2) from dual ==> 5.12
select round(5.87654 , 2) from dual ==> 5.88

select floor(5.89) from dual ==> 5
select floor(5.12) from dual ==> 5

select ceil(5.89) from dual ==> 6
select ceil(5.12) from dual ==> 6

Menampilkan beberapa karakter tertentu dari belakang - PostgreSQL

Contoh ingin melihat 2 karakter terakhir. jika bukan 2, maka disesuaikan angka -1 dan 2 nya.

select
nama_nasabah,
substring(nama_nasabah,(length(nama_nasabah)-1),2) akhir
from trx_transaksi xx
where nama_nasabah<>''
limit 100

HASILNYA:
nama_nasabah                                       akhir
PT BHAKTI MITRA SELARAS            AS
JUMIRAH                                               AH
REVRA VENI                                         NI
SARNI                                                     NI
RADEN HAJI HAYE TEDJA DJUHAERAH        AH
SUPRIYONO                                          NO

Looping Insert Tanggal Urut atau Bertambah Tiap bulannya - PostgreSQL

Contoh kita ingin update suatu tanggal sesuai dengan contoh tanggal di bawah ini:

--update jwl_deperesiasi
DO $$
    Declare idkrt int;   
        Declare bulanke int;   
        DECLARE idaset int;   
        Declare jwl date;

    cur1 CURSOR for select id, depresiasi_bulan_ke, id_aset_inventaris, jwl_depresiasi from m_kartu_depresiasi where id_aset_inventaris=1530 order by depresiasi_bulan_ke;   
 begin 
OPEN Cur1 ; 
Loop     
    FETCH  next from Cur1 INTO idkrt, bulanke, idaset, jwl;    
    EXIT WHEN NOT FOUND;
    update m_kartu_depresiasi
    set jwl_depresiasi=((select jwl_depresiasi from m_kartu_depresiasi where depresiasi_bulan_ke < bulanke  and id_aset_inventaris=idaset order by depresiasi_bulan_ke desc limit 1) + (interval '1 MONTH'))
    where id_aset_inventaris=idaset and depresiasi_bulan_ke=bulanke and depresiasi_bulan_ke >1;
end loop ;  
CLOSE Cur1; 
END $$;

Import dan Restore database - PostgreSQL

A. Restore /Import versi windows

PostgreSQL SECARA LOCAL
1. C -> Program Files (x86) -> PostgreSQL -> 9.6 -> bin -> psql -U postgres

2. lalu masukkan password


SECARA GLOBAL/SERVER LAIN

1. C -> Program Files (x86) -> PostgreSQL -> 9.6 -> bin -> psql -U postgres -115.75.333.324
2. postgres=# \c bank_aty

(bpr_aty) nama database target

3. bank_aty=# \i C:/backup_data__20170619141626.backup

folder atau file jangan pakai spasi

B. Restore /Import versi LINUX (Ubuntu 16.04)

cara restore pake backup
pg_restore -i -h 10.5.2.30 -p 5432 -U postgres -d data_bank -v "C:/database_kosong.backup"

atau
pg_restore -h 10.5.2.30 -p 5432 -U postgres -d data_bank -v "C:/database_kosong.backup"

jika ada error seperti "FATAL: password authentication failed for user "postgres"
maka di pg_hba.conf ubah seperti ini:
local postgres postgres peer

lalu ALTER USER postgres PASSWORD 'my_postgres_password';

Sumber: https://hariatysimangunsong.blogspot.com/2019/06/cara-import-dan-restore-data-dum.html

Looping Sampai Angka Tertentu - PostgreSQL

DO $$
DECLARE
    x INTEGER;
begin
x :=1;
loop
    insert into zz_test_insert VALUES (x);
    x := x + 1;
    exit when x > 101;
end loop;
end $$;

----
DO $$
DECLARE
    x INTEGER;
begin
x :=1;
FOR x IN 1..100 LOOP
    insert into zz_test_insert VALUES (x);
    x := x + 1;  
end loop;
end $$;

-------------------------------------
DO $$
        DECLARE idkartu INTEGER;
        DECLARE bulan_ke INTEGER;
    Declare harga bigint;    
        Declare jangka int;    
        Declare tglbeli date;
        declare aset record;
 begin
idkartu :=(select COALESCE(max(id),0) idmax from m_kartu_depresiasi) + 1;
 bulan_ke :=1;

loop
  
    select jangka_waktu, id into aset from m_aset_inventaris where id=1592;  
  
    insert into m_kartu_depresiasi
    (id, depresiasi_bulan_ke, id_aset_inventaris,status_depresiasi)    values    (idkartu, bulan_ke, aset.id, false);
  
    idkartu := idkartu + 1;
    bulan_ke := bulan_ke + 1;
    exit when bulan_ke > aset.jangka_waktu;

end loop ;
END $$;

Function backup tabel ke CSV - PostgreSQL

CREATE OR REPLACE FUNCTION "audittable"."backup_logs"()
  RETURNS "pg_catalog"."trigger" AS $BODY$
   
    DECLARE
    filename text :='/home/ukabima/backups/log_backups/loggedtransactions_' || CURRENT_DATE ||'.txt';
   
  BEGIN
     IF ((SELECT count(*) from audittable.tbl_loggedtransactions) >500000) THEN

EXECUTE'COPY audittable.tbl_loggedtransactions TO ''' || filename || '''';

-- COPY audittable.tbl_loggedtransactions TO '/home/ukabima/backups/log_backups/mamanoseng3_loggedtransactions.csv' DELIMITER ',' CSV HEADER ;
       
DELETE FROM audittable.tbl_loggedtransactions;
END IF;
return NULL;
end;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100

Looping - SQL Server

DECLARE @name VARCHAR(50) -- database name
DECLARE @path VARCHAR(256) -- path for backup files
DECLARE @fileName VARCHAR(256) -- filename for backup
DECLARE @fileDate VARCHAR(20) -- used for file name
SET @path = 'C:\Backup\' SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)
DECLARE db_cursor CURSOR FOR
SELECT name FROM MASTER.dbo.sysdatabases WHERE name NOT IN ('master','model','msdb','tempdb')
 OPEN db_cursor FETCH NEXT FROM db_cursor INTO @name WHILE @@FETCH_STATUS = 0
 BEGIN SET @fileName = @path + @name + '_' + @fileDate + '.BAK' BACKUP DATABASE @name TO DISK = @fileName FETCH NEXT FROM db_cursor INTO @name END CLOSE db_cursor DEALLOCATE db_cursor

-----------
 DECLARE @TotalOscars INT
DECLARE @FilmOscars INT
SET @TotalOscars = 0
DECLARE FilmCursor CURSOR
FOR SELECT FilmOscarWins FROM tblFilm
FOR UPDATE OFFilmCumulativeOscars
OPEN FilmCursor
FETCH NEXT FROM FilmCursor INTO @FilmOscars
WHILE @@FETCH_STATUS = 0
BEGIN
SET @TotalOscars += @FilmOscars
UPDATE tblFilm
SETFilmCumulativeOscars = @TotalOscars
WHERE CURRENT OFFilmCursor
FETCH NEXT FROMFilmCursor INTO@FilmOscars
END
CLOSE FilmCursor
DEALLOCATE FilmCursor

Rollback untuk SP - SQL Server

-- CONTOH TABEL HANYA BISA DI ISI DENGAN 5 KARAKTER, MAKA AKAN ROLLBACK JIKA ADA YG DIISI LBH DARI 5 KARAKTER
BEGIN
        BEGIN TRANSACTION
        BEGIN TRY
       
        INSERT INTO [dbo].[TES1]
        VALUES ('ABCDF')
       
        INSERT INTO [dbo].[TES2]
        VALUES ('ABCDF')
       
                COMMIT TRANSACTION
        END TRY
        BEGIN CATCH
            IF @@TRANCOUNT > 0
            BEGIN
                ROLLBACK TRANSACTION
            END
        END CATCH
    END

Trigger - PostgreSQL

CREATE TABLE account (
id serial primary key,
name text,
debt int,
balance int
);

CREATE TABLE account_audit(
id serial primary key,
db_user text NOT NULL default session_user,
operation text,
account_id int,
account_name text,
debt int,
balance int,
created_at timestamp with time zone default current_timestamp
);

-------------
CREATE FUNCTION account_audit_func()
RETURNS TRIGGER
AS $$
BEGIN
IF TG_OP = 'INSERT' THEN
  INSERT INTO account_audit (operation, account_id, account_name, debt, balance) VALUES
       (TG_OP, NEW.*);
  RETURN NEW;
ELSIF TG_OP = 'UPDATE' THEN
   INSERT INTO account_audit (operation, account_id, account_name, debt, balance) VALUES
        (TG_OP, NEW.*);
   RETURN NEW;
ELSIF TG_OP = 'DELETE' THEN
   INSERT INTO account_audit (operation, account_id, account_name, debt, balance) VALUES
        (TG_OP, OLD.*);
   RETURN OLD;
END IF;
END;
$$
LANGUAGE plpgsql;

----
CREATE TRIGGER account_audit_trigger
AFTER INSERT OR UPDATE OR DELETE ON account
FOR EACH ROW EXECUTE PROCEDURE account_audit_func();

----
-- ,lastupdate,aksi,hostname,username)
CREATE OR REPLACE FUNCTION zz_mbpr_triger()
  RETURNS trigger AS
$BODY$
BEGIN
 IF NEW.id_entry <> OLD.id_entry THEN
 INSERT INTO zz_mbpr_hist(id,id_entry,bpr,lastupdate,aksi,hostname,username)
 VALUES(old.id, OLD.id_entry,OLD.last_name,old.bpr,now(),'UPDATE', HOST,USER);
 END IF;

-----
CREATE TRIGGER trg_acc_account_number_UPDATE_DELETE
AFTER UPDATE OR DELETE ON public.acc_account_number
FOR EACH ROW EXECUTE PROCEDURE AuditTable.trg_AuditDML();

Tanggal - PostgreSQL

UBAH FORMAT TANGGAL
CONTOH DI DATABASE FORMATNYA YYYY/MM/DD, KITA UBAH JADI DD/MM/YYYY
select a.nomor_register_aplikasi no_rekening, a.nama_nasabah nama_nasabah,
concat(b.jangka_waktu,' ','B') JK, b.plafon,
TO_CHAR(b.tanggal_pencairan,'DD/MM/YYYY') Tgl_Pencairan, b.provisi from adm_permohonan_kredit a,
adm_simulasi_permohonan_kredit b
where a.id=id_kredit
and a.nomor_register_aplikasi='K060000136'

select TO_CHAR(d_entry, 'YYYY-MM-dd') tgl_mulai from mbl_kolektor_setoran_group
where TO_CHAR(d_entry, 'YYYY-MM-dd')=TO_CHAR(now(), 'YYYY-MM-dd')

CONVERT NOW()
select * from adm_kredit_angsuran where batasan_permohonan_kredit=91898  AND tanggal_jatuh_tempo <= (CAST(NOW() AS DATE))

MENAMBAHKAN TANGGAL
(select a.tanggal,b.id, a.kolektibilitas, a.par, a.baki, a.tgk_pokok, a.tgk_bunga, a.tot_hari  from rpt_history_kol_n_ppap_kredit a
join adm_permohonan_kredit b on a.kredit_id=b.id
where a.tanggal = CAST('2019-06-16' as date) + INTERVAL '1 day'
and b.id_bpr=7 and b.id_bpr_cabang=18)

AMBIL TANGGAL AWAL DAN AKHIR BULAN
select
( ( date_trunc( 'MONTH', CAST ( concat ( '2019-', '09-', '01' ) AS DATE ) ) ) :: DATE ) xx,
((date_trunc( 'MONTH', CAST ( concat ( '2019-', '09-', '01' ) AS DATE ) ) + INTERVAL '1 MONTH - 1 day') :: DATE ) yyy

AMBIL TAHUN, BULAN DAN TANGGAL AJA
select (date_part('year', tanggal_beli)) tanggal from m_aset_inventaris where id in (2659, 2658)
select date_part('month', tanggal_beli), date_part('month', now()) from m_aset_inventaris
select (date_part('day', tanggal_beli)) tanggal from m_aset_inventaris where id in (2659, 2658)

CEK UMUR
select age(timestamp '1995-02-11')

JIKA UPDATE TANGGAL
set jwl_depresiasi=cast(CONCAT(DATE_PART('YEAR',jwl_depresiasi),'-',DATE_PART('MONTH',jwl_depresiasi),'-',tanggal) as date)

MENDAPATKAN JUMLAH HARI DARI SUATU BULAN
SELECT extract(days FROM date_trunc('month', now()) + interval '1 month - 1 day');

SELECT DATE_PART('days', DATE_TRUNC('month', NOW()) + '1 MONTH'::INTERVAL - '1 DAY'::INTERVAL)

GABUNGAN
select
jwl_depresiasi,
DATE_PART('days', DATE_TRUNC('month', jwl_depresiasi) + '1 MONTH'::INTERVAL - '1 DAY'::INTERVAL) jumlah_hari,
DATE_PART('YEAR',jwl_depresiasi) tahun,
DATE_PART('MONTH',jwl_depresiasi) bulan,
CONCAT(DATE_PART('YEAR',jwl_depresiasi),'-',DATE_PART('MONTH',jwl_depresiasi),'-',31) gabungan
from m_kartu_aset_inventaris
where id_aset_inventaris = 2658 order by depresiasi_bulan_ke

FORMAT
--tanggal_jurnal >= '2019-06-01'
--AND tanggal_jurnal < CAST ( '2019-06-30' AS DATE ) + INTERVAL '1 day'
CAST ( tanggal_jurnal AS DATE ) >= ( ( date_trunc( 'MONTH', CAST ( concat ( '2019-', '06-', '01' ) AS DATE ) ) ) :: DATE )
                AND CAST ( tanggal_jurnal AS DATE ) <= ((date_trunc( 'MONTH', CAST ( concat ( '2019-', '06-', date_part( 'days', now()) ) AS DATE ) ) + INTERVAL '1 MONTH - 1 day') :: DATE )

BETWEEN
SELECT
apd.id,
apd.nomor_permohonan no_register,
apd.tanggal_permohonan,
apd.nomor_register no_rek,
apd.nama_nasabah,
mkpd.kode_deposito kode,
apd.nominal saldo,
au.sso_id ao
FROM adm_permohonan_deposito apd
join m_kriteria_produk_deposito mkpd on apd.id_produk_deposito=mkpd.id_kriteria_produk_deposito
join app_user au on apd.account_officer=au.id
where apd.id_bpr=3 and apd.id_bpr_cabang=3
and apd.tanggal_permohonan between cast('2019-07-01' as date) and cast('2019-07-31' as date)

System - PostgreSQL

SELECT * FROM pg_catalog.pg_tables

select * FROM pg_stat_activity ORDER BY query_start desc

String/Karakter - PostgreSQL

select * from acc_account_number  where level=1
and left(nomor_akun,1)='2'

-- HILANGKAN KARAKTER DARI BELAKANG
update m_karyawan
set alamat_tinggal=substring(alamat_tinggal,1,(length(alamat_tinggal)-2))


--- LIHAT 2 KARAKTER TERKAHIR
select
nama_nasabah,
substring(nama_nasabah,(length(nama_nasabah)-1),2) akhir
from trx_transaksi xx
where nama_nasabah<>''
limit 100

HASILNYA:
PT BHAKTI MITRA SELARAS            AS
JUMIRAH                    AH
REVRA VENI                NI
SARNI                    NI
RADEN HAJI HAYE TEDJA DJUHAERAH        AH
SUPRIYONO                NO

Row_Number - PostgreSQL

SELECT *, row_number() over (order by count) as idn
FROM temp_urut

COUNT    URUT    IDN
1    9    1
2    9    2
3    9    3
4    9    4
5    9    5

Menambahkan rownumber berdasarkan order  by
INSERT INTO "temporary".tmp_bukubesar
SELECT
ROW_NUMBER () OVER (

ORDER BY
tanggal_jurnal,
d_entry
),
user_id userid,
kodejurnal,
tanggal_jurnal,
d_entry,
debet,
kredit,
saldoakhir,
nomor_rekening,
keterangan,
nomorakun
FROM

Replace - PostgreSQL

update adm_data_jaminan_permohonan_kredit
set keterangan=REPLACE(keterangan,'²','2')
where keterangan like '%²%'

update adm_permohonan_kredit
set nomor_perjajian_kerdit=REPLACE(nomor_perjajian_kerdit,'@','A')
where nomor_perjajian_kerdit like '%@%'

Alter Database - PostgreSQL

ALTER DATABASE bpr RENAME TO bpr_2

Pivot - PostgreSQL

--- convert kolom to row
---data asli
sandipos0101    sandipos0201    sandipos0202 dst
16            5    100         5    4    83    98    10

-- menjadi
sandi    rasio
0101    16
0201    5
0202    100
0203    5
0401    4
0402    83
0501    98
0502    10

SELECT
   unnest(array['0101', '0201', '0202', '0203', '0401', '0402', '0501', '0502']) AS sandi,
   unnest(array[sandipos0101, sandipos0201, sandipos0202,sandipos0203,sandipos0401,sandipos0402,sandipos0501,sandipos0502]) AS rasio
FROM lapbul_rasio_keuangan_triwulanan
ORDER BY sandi
--- convert kolom to row
select
pen2.id_bpr,
pen2.id_bpr_cabang,
sum(case when pen2.pendidikan='S - 3' then COALESCE(kar2.ttl,0) end) s3,
sum(case when pen2.pendidikan='S - 2' then COALESCE(kar2.ttl,0) end) s2,
sum(case when pen2.pendidikan='S - 1' then COALESCE(kar2.ttl,0) end) s1,
sum(case when pen2.pendidikan='Diploma 3' then COALESCE(kar2.ttl,0) end) d3,
sum(case when pen2.pendidikan='Tanpa Gelar' then COALESCE(kar2.ttl,0) end) slta,
sum(case when pen2.pendidikan='Lainnya ' then COALESCE(kar2.ttl,0) end) Lainnya
from (
select pen.id_bpr, cab.id_bpr_cabang, pen.id, pen.pendidikan from (
select
(select id id_bpr from m_bpr where id=4),
id, pendidikan
from m_pendidikan pen where pen.kode_pendidikan in ('03','04','05','06','99','00')) pen
join m_bpr_cabang cab on pen.id_bpr=cab.id_bpr) pen2
left join (select
id_bpr,
id_bpr_cabang,
kode_pendidikan, count(*) ttl
from m_karyawan where id_bpr=4 and status_karyawan='true'and jenis_karyawan='Tetap' and nama_jabatan not like 'ADMIN%'
group by id_bpr,
id_bpr_cabang, kode_pendidikan) kar2 on pen2.id_bpr=kar2.id_bpr and pen2.id_bpr_cabang=kar2.id_bpr_cabang and pen2.id=kar2.kode_pendidikan
group by pen2.id_bpr,
pen2.id_bpr_cabang

Pivot Gabungkan isi Filed - PostgreSQL

AWALNYA:
111583    Agunan Lainnya
111583    Kendaraan Bermotor

HASILNYA:
111583    Agunan Lainnya,Kendaraan Bermotor

select id_permohonan, ARRAY_TO_STRING(ARRAY_AGG(nama), ',') AS nama from (
select adjpk.id_permohonan, --adjpk.nomor_register_jaminan,
mj.nama
from adm_data_jaminan_permohonan_kredit adjpk
join m_jaminan mj on adjpk.id_jaminan=mj.id
--where id_permohonan=102384
where id_permohonan=111583
order by adjpk.id_permohonan) xx
GROUP BY id_permohonan

Pembulatan Angka - PostgreSQL

    round((case when adjpk.nilai_jaminan<>0 then ((tabkredit.baki*100)/(COALESCE(adjpk.nilai_jaminan,0)))
                        else adjpk.nilai_jaminan
                        end),2) ratio

Coalesce - PostgreSQL

select * from m_bpr where COALESCE(sandi_kantor,'0')='0'

Looping Pakai IF - PostgreSQL

DO $$
  Declare rownumber bigint;   
    Declare debet1 bigint;   
        Declare kredit1 bigint;   
        Declare saldoakhir1 bigint;
        declare tipeakun INTEGER;

    cur1 CURSOR for select row_number, debet, kredit, saldoakhir from "temporary".tmp_bukubesar order by row_number;   
 begin 
OPEN Cur1 ; 
Loop     
    FETCH  next from Cur1 INTO rownumber, debet1, kredit1, saldoakhir1;    
    EXIT WHEN NOT FOUND;
   
    select tipe_akun into tipeakun from acc_account_number where nomor_akun='2.330';
   
    if tipeakun in (0,3) then
    update "temporary".tmp_bukubesar
    set saldoakhir=(select saldoakhir from "temporary".tmp_bukubesar where row_number < rownumber order by row_number desc limit 1) + debet1 - kredit1
    where row_number=rownumber and row_number >1 and iduser = 123;
    end if;
   
    if tipeakun in (1,2) then
    update "temporary".tmp_bukubesar
    set saldoakhir=(select saldoakhir from "temporary".tmp_bukubesar where row_number < rownumber order by row_number desc limit 1) - debet1 + kredit1
    where row_number=rownumber and row_number >1 and iduser = 123;
    end if;
   
end loop ;  
CLOSE Cur1; 
END $$;

Looping Pakai Row_Number dan Concat - PostgreSQL

-- UPDATE NO_KTP AGAR 16 DIGIT, ANGKA TERKAHIR AMBIL DARI ROW_NUMBER
DO $$
DECLARE
    urut VARCHAR;
    NOREG VARCHAR;
        CUR1 CURSOR FOR SELECT "NO_REG", row_number() over (order by "NO_REG") as urutan FROM (   
select "NO_REG","NO_KTP",LENGTH("NO_KTP") AS KTP from "NSB_P") A
WHERE KTP<>16;
BEGIN
OPEN CUR1;
LOOP
    FETCH  next from CUR1 INTO NOREG, urut;   
    EXIT WHEN NOT FOUND;
   
        IF LENGTH(urut)=1 THEN
        UPDATE "NSB_P"
        SET "NO_KTP"=CONCAT('030100000000000',urut)
        WHERE "NO_REG"=NOREG;
    END IF;
   
   
    IF LENGTH(urut)=2 THEN
    UPDATE "NSB_P"
        SET "NO_KTP"=CONCAT('03010000000000',urut)
        WHERE "NO_REG"=NOREG;
    END IF;
       
        IF LENGTH(urut)=3 THEN
    UPDATE "NSB_P"
        SET "NO_KTP"=CONCAT('0301000000000',urut)
        WHERE "NO_REG"=NOREG;
    END IF;
   
    IF LENGTH(urut)=4 THEN
    UPDATE "NSB_P"
        SET "NO_KTP"=CONCAT('030100000000',urut)
        WHERE "NO_REG"=NOREG;
    END IF;
   
        IF LENGTH(urut)=5 THEN
    UPDATE "NSB_P"
        SET "NO_KTP"=CONCAT('03010000000',urut)
        WHERE "NO_REG"=NOREG;
    END IF;


END LOOP;
CLOSE CUR1;
END $$;

Looping Angka Urut - PostgreSQL

DO $$
DECLARE
   iterator INTEGER;  -- we can init at declaration time
     idref integer;
    idsandi varchar;
          cur2 cursor for select id_daftar_ref, sandi from  m_referensi_lapbul_ojk_detail_backup where id is NULL;
BEGIN
    iterator :=19;
    open cur2;
Loop   
    fetch next from cur2 into idref, idsandi;
    EXIT WHEN NOT FOUND;   
 
       UPDATE m_referensi_lapbul_ojk_detail_backup
     set id=iterator
         where id is NULL and id_daftar_ref=idref and sandi=idsandi;
      iterator := iterator + 1;
      -- do stuff
   END LOOP;
         close cur2;
END $$;

-------------------------
DO $$
DECLARE
   iterator INTEGER;  -- we can init at declaration time
BEGIN
    iterator :=1;
   WHILE iterator < 999
   LOOP
     insert into temp_urut (count)
     values (iterator);
      iterator := iterator + 1;
      -- do stuff
   END LOOP;
END $$;


----- update angka urut pakai row_number
with new_numbers as (
   select COUNT,
          row_number() over (order by COUNT) as urutan
   from temp_urut
)
update temp_urut
  set no_urut = nn.urutan
from new_numbers nn
where nn.COUNT = temp_urut.COUNT;

-----
DO $$
DECLARE
   iterator INTEGER;
    urut INTEGER;
     cur1 cursor for select count from temp_urut;
BEGIN
iterator :=1;
OPEN Cur1 ;
Loop   
    FETCH  next from Cur1 INTO urut;   
    EXIT WHEN NOT FOUND;
   
     UPDATE temp_urut
     set no_urut=concat('000',iterator)
         where count=urut;
     iterator := iterator + 1;
  END LOOP;
    CLOSE Cur1; 
END $$;

hasilnya
1    0001
2    0002
3    0003
4    0004
5    0005

Length - PostgreSQL

SELECT * FROM (   
select "NO_REG","NO_KTP",LENGTH("NO_KTP") AS KTP from "NSB_P") A
WHERE KTP<16

Koma - PostgreSQL

update m_kriteria_produk_kredit_biaya
set denda=0.2
where denda=0

--- PADA DATABASE, YANG TAMPIL 0,2
--- KALAU MW SELECT ATAU UPDATE PAKAI 0.2

 --Membuat dua angka di belakang koma (2.50, 3.57 dll)
cast(trunc(cast(aca.suku_bunga as decimal),2) as varchar) AS sukuBunga,

Karakter Enter - PostgreSQL

-- CEK FIELD YANG MENGANDUNG KARAKTER ENTER
select * from m_karyawan where alamat_tinggal LIKE ('%' || chr(10) || '%')

-- HILANGKAN KARAKTER ENTER
update m_karyawan
set alamat_tinggal=substring(alamat_tinggal,1,(length(alamat_tinggal)-2))
where alamat_tinggal LIKE ('%' || chr(10) || '%')

-- HILANGKAN KARAKTER ENTER
update adm_data_jaminan_permohonan_kredit
set alamat_agunan=REPLACE(alamat_agunan,''|| chr(10) ||'','')
where alamat_agunan LIKE ('%' || chr(10) || '%')

Join Update - PostgreSQL

update TEMP_NSB_P_CECK
set "KELURAHAN"=m_kode_pos.kelurahan, "KECAMATAN"=m_kode_pos.kecamatan, "KOTA"=m_kode_pos.Kabupaten
from m_kode_pos
    where TEMP_NSB_P_CECK."KODEPOS"=m_kode_pos.kodepos


UPDATE m_nasabah_perorangan_personal b
SET id_kota_kabupaten_lahir=a.id_kota_kab, id_propinsi_lahir=a.id_propinsi
from temp_data_lahir a
where b.id_nasabah_perorangan=a.id_nasabah_perorangan

Join Like - PostgreSQL

select DISTINCT "NO_REG", "NAMA", "TMP_LAHIR", id_kota_kab,nm_kota_kab, id_propinsi
FROM nsb_all nsb
join m_kota_kab on nm_kota_kab like '%'||nsb."TMP_LAHIR"||'%'
WHERE "TMP_LAHIR"<>''
AND "NO_REG"='060100000468'
ORDER BY "NO_REG", "TMP_LAHIR"

Insert Into - PostgreSQL

insert into m_kode_pos2
(id,id_kelurahan,provinsi,kabupaten,kecamatan,kelurahan,kodepos,id_entry)
select id,id_kelurahan,nm_propinsi,nm_kota_kab,nm_kec,nm_kelurahan,kodepos,'syarif_it' id_entry from temp_kodepos_new

insert into m_kode_pos2
(id,id_kelurahan,provinsi,kabupaten,kecamatan,kelurahan,kodepos,id_entry)
select no,idkelurahan,nm_provinsi,nm_kota_kab,nm_kec,nm_kelurahan,kodepos,'syarif_it' id_entry from temp_kodepos_new2


---- jika tipe data integer dan insertnya tidak ada data maka, di isi dengan null
insert into m_data_direksi_komisaris
values
(1,4,1164,1,'2017-03-03','2020-03-03','S-438/KO.031/2018','2018-04-30','1','2023-12-31',1,'2009-07-25','UPN Veteran',    'Training of trainner solidarity grup landing','2009-08-07','PT UKABIMA',null,null,null,'2','','syarif_it', 'syarif_it', now(), NOW()),
(2,4,1165,2,'2017-03-03','2020-03-03','S-438/KO.031/2018','2018-04-30','1','2023-12-31',1,'2015-09-01','STIE AUB','Satuan pengawas internal','2010-03-02','PT UKABIMA',null,null,null,'1','','syarif_it', 'syarif_it', now(), NOW()),
(3,4,2100,3,'2016-08-23','2019-08-23','S-438/KO.031/2018','2018-04-30','1','2023-12-31',3,'2017-03-03','Universitas Diponegoro','Penyusunan SOP','2014-03-03','PT UKABIMA',1,1,1,'','1','syarif_it', 'syarif_it', now(), NOW());

Index - PostgreSQL

CREATE INDEX adm_kredit_angsuran_tgl_amortisasi_provisi_idx
ON adm_kredit_angsuran (tgl_amortisasi_provisi);

CREATE INDEX adm_permohonan_kredit_tgl_lunas_idx
ON adm_permohonan_kredit (tgl_lunas);

DROP INDEX adm_permohonan_kredit_tgl_lunas_idx;
DROP INDEX adm_kredit_angsuran_tgl_amortisasi_provisi_idx;

--- untuk lap tabungan akrual
CREATE INDEX trx_histori_akrual_tabungan_tanggal_idx
ON trx_histori_akrual_tabungan (tanggal);

Hitung Selisih Angka dengan Setelahnya - PostgreSQL

CREATE TABLE TEMP_ID_IDPK AS
SELECT ID FROM adm_permohonan_kredit ORDER BY ID ASC

CREATE TABLE TEMP_RANGE_IDPK
( AWAL INT, AKHIR INT, SELISIH INT)


DO $$
    Declare ik1 INTEGER;    
    cur1 CURSOR for SELECT * FROM TEMP_ID_IDPK ORDER BY ID ASC;
begin   
OPEN Cur1 ; 
Loop   
    FETCH  next from Cur1 INTO ik1;    
    EXIT WHEN NOT FOUND;
        INSERT INTO TEMP_RANGE_IDPK
        (awal, akhir)
        select ik1, id
        from TEMP_ID_IDPK WHERE ID >(SELECT ID FROM TEMP_ID_IDPK WHERE ID=ik1)
            ORDER BY ID ASC LIMIT 1; 
end loop ;  
CLOSE Cur1; 
END $$;

update TEMP_RANGE_IDPK
set selisih=akhir-awal;


select * from TEMP_RANGE_IDPK
where selisih >100
order by selisih desc

-------------------------
CREATE TABLE TEMP_ID_AKA1 AS
SELECT ID, ROW_NUMBER() OVER (ORDER BY ID) ROWNUMBER FROM adm_kredit_angsuran  ORDER BY ID ASC

CREATE TABLE TEMP_ID_AKA2 AS
SELECT ID, ROW_NUMBER() OVER (ORDER BY ID) ROWNUMBER FROM adm_kredit_angsuran WHERE ID<>1 ORDER BY ID ASC

SELECT *, sum(id1+1) awalid_kosong, sum(id2-1) akhirid_kosong FROM (
SELECT A.ID ID1, B.ID ID2, SUM(B.ID-A.ID) RANGEID FROM TEMP_ID_AKA1 A, TEMP_ID_AKA2 B
WHERE A.ROWNUMBER=B.ROWNUMBER
GROUP BY A.ROWNUMBER,A.ID, B.ID
ORDER BY A.ID ASC) A
WHERE RANGEID>100
group by id1, id2, rangeid
ORDER BY RANGEID DESC

Grant User - PostgreSQL

GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA PUBLIC TO andri_it

Tanggal Hari Ini - PostgreSQL

(SELECT NOW() :: DATE)

select now();

SELECT LOCALTIME

SELECT CURRENT_TIMESTAMP

Create Table - PostgreSQL

-- CREATE TABLE + ADD CONSTRAINT
CREATE TABLE kategori2
(
   masterkey bigserial,
   nama_kategori character varying(255),
   CONSTRAINT kategori_pk PRIMARY KEY (masterkey)
);

INSERT INTO kategori(masterkey, nama_kategori) VALUES (1, 'Smartphone');
INSERT INTO kategori(masterkey, nama_kategori) VALUES (2, 'Laptop');
INSERT INTO kategori(masterkey, nama_kategori) VALUES (3, 'Notebook');
INSERT INTO kategori(masterkey, nama_kategori) VALUES (4, 'Tablet');

Copy Tabel ke HDD

DO $$
    Declare waktu VARCHAR;
    cur1 CURSOR for SELECT (NOW() :: DATE) as VARCHAR;
       
begin   
OPEN Cur1 ; 
Loop   
    FETCH  next from Cur1 INTO waktu;    
    EXIT WHEN NOT FOUND;
    COPY audittable.tbl_loggedtransactions TO 'D:/loggedtransactions_2.csv' DELIMITER ',' CSV;
end loop ;  
CLOSE Cur1; 
END $$;

Convert To atau Cast - PostgreSQL

--- INTEGER TO VARCHAR
select "NILAI", CAST("NILAI" AS VARCHAR) NILAI2 from "KRD_JMN" limit 100

TO_CHAR(apk.tanggal_permohonan, 'YYYYMMDD') tgl_mulai,

--- merubah float menjadi varchar: di database tampil 2,54 diubah agar 2.54
select
'D01' flag,
nama,
alamat,
jenis,
no_identitas,
status_pemegang_saham,
nominal,
cast(presentase_kepemilikan as varchar)
from m_data_pemilik_bpr

Concat - PostgreSQL

concat(b.jangka_waktu,' ','B')
HASILNYA: 24 B

Cek Field Tabel - PostgreSQL

SELECT COLUMN_NAME
FROM information_schema.COLUMNS
WHERE TABLE_NAME = 'krtkrd';

SELECT DISTINCT "table_name"
FROM information_schema.COLUMNS
WHERE TABLE_NAME like 'brs_%';

Case When - PostgreSQL

select a.nomor_register_aplikasi,
c.cicilan_pokok, c.cicilan_bunga, c.tanggal_jatuh_tempo, c.pem_pokok, c.pem_bunga, c.tgl_pembayaran,
(case when c.status_aplikasi=1 then 'Lunas' else 'Belum Lunas' end) status
from adm_permohonan_kredit a, adm_simulasi_permohonan_kredit b, adm_kredit_angsuran c
where a.id=b.id_kredit
and b.id=c.batasan_permohonan_kredit
and c.tanggal_jatuh_tempo >= '2019-06-01'
and a.id_bpr=9
order by a.nomor_register_aplikasi, c.tanggal_jatuh_tempo

---
select (case when a.overdue=0 then 'current'
when a.overdue between 1 and 30 then 'kol1.1'
when a.overdue between 31 and 60 then 'kol1.2'
when a.overdue between 61 and 90 then 'kol1.3'
when a.overdue between 91 and 120 then 'kol2.1'
when a.overdue between 121 and 150 then 'kol2.2'
when a.overdue between 151 and 180 then 'kol2.3'
when a.overdue between 181 and 270 then 'kol3.1'
when a.overdue between 271 and 365 then 'kol3.2'
when a.overdue between 366 and 545 then 'kol4.1'
when a.overdue between 546 and 720 then 'kol4.2'
when a.overdue >=721 then 'kol4.3'
end) koll
from zz_brs_kk_jan a

---- Penjumlahan yang ada nilai menambahkan dan mengurangkan ----
select userid, id_atmr, sum(case when tmpatmr.plusminus = 0 then (nominal*1)
            when tmpatmr.plusminus = 1 then (nominal*(-1)) end)nominalasli,
sum(ppap_khusus) ppap_khusus,
(sum(case when tmpatmr.plusminus = 0 then (nominal*1)
 when tmpatmr.plusminus = 1 then (nominal*(-1)) end)-sum(ppap_khusus)) nominalminusppap,
ma.persen_bobot,
((sum(case when tmpatmr.plusminus = 0 then (nominal*1)
  when tmpatmr.plusminus = 1 then (nominal*(-1)) end)-sum(ppap_khusus))*ma.persen_bobot/100) nilaiatmr
from "temporary".tmp_proses_atmr_awal tmpatmr
left join m_atmr ma on tmpatmr.id_atmr=ma.id
WHERE userid = user_id
group by userid, id_atmr, ma.persen_bobot
order by id_atmr;

Angka Di Belakang Koma - PostgreSQL

SELECT TRUNC(67.456,1) AS "Truncate upto 1 decimal";  -->    67.4

SELECT TRUNC(67.456) AS "Truncate"; --> 67

Alter Table - PostgreSQL

ALTER TABLE temp_urut
ADD NO_URUT INTEGER;

-- ubah type data field
alter table temp_urut
ALTER COLUMN no_urut TYPE varchar(16);

--- add
alter table zz_mbpr_hist
add COLUMN lastupdate TIMESTAMP,
add COLUMN aksi varchar,
add COLUMN hostname VARCHAR,
add COLUMN username VARCHAR;

-- drop
alter table zz_mbpr_hist
drop COLUMN lastupdate,
drop COLUMN aksi,
drop COLUMN hostname,
drop COLUMN username;

-- add constraint
ALTER TABLE kategori2 ADD CONSTRAINT kategori_pk2 PRIMARY KEY (masterkey);

-- Add PK
ALTER TABLE m_sandi_mapping
    ADD CONSTRAINT fk_acc_account_number FOREIGN KEY (id_akun_ebpr) REFERENCES acc_account_number (id);

Gabungan Quey PostreSQL

-- delete history iduser
delete from "temporary".tmp_bukubesar where iduser = 123;


-- tampung data
insert into "temporary".tmp_bukubesar
select ROW_NUMBER () OVER (ORDER BY tanggal_jurnal, kodejurnal),
123, kodejurnal, tanggal_jurnal, debet, kredit, saldoakhir, nomor_rekening, keterangan from (
SELECT '' kodejurnal, (select CAST ( '2019-01-01' AS DATE ) - INTERVAL '1 day' ) tanggal_jurnal, null debet, null kredit,
    (
    SELECT COALESCE
        ( SUM ( acc_end_of_month_detail.end_balanace ), 0 ) AS saldoeom
    FROM
        acc_end_of_month_detail
        JOIN acc_account_number ON acc_end_of_month_detail.nomor_akun = acc_account_number.ID
    WHERE
        acc_account_number.nomor_akun LIKE'2.330%'
        AND acc_end_of_month_detail.ending_date = (
        SELECT COALESCE
            ( MAX ( acc_end_of_month.tgl_eom ), '2000-01-01' )
        FROM
            acc_end_of_month
        WHERE
            acc_end_of_month.tgl_eom < '2019-01-01'
            AND acc_end_of_month_detail.year_month = ( SELECT MAX ( year_month ) FROM acc_end_of_month_detail WHERE acc_end_of_month_detail.year_month < '201901' AND acc_end_of_month_detail.id_bpr = 3 AND acc_end_of_month_detail.id_bpr_cabang = 3 )
            AND acc_end_of_month.id_bpr = 3
            AND acc_end_of_month.id_bpr_cabang = 3
        )
        AND acc_end_of_month_detail.id_bpr = 3
        AND acc_end_of_month_detail.id_bpr_cabang = 3
        ) + (
    SELECT COALESCE
        (
            SUM (
            CASE
                   
                    WHEN acc_account_number.tipe_akun = 0
                    OR acc_account_number.tipe_akun = 3 THEN
                        ( acc_journal_detail.debet_amount - acc_journal_detail.credit_amount ) ELSE ( acc_journal_detail.credit_amount - acc_journal_detail.debet_amount )
                    END
                    ),
                    0
                )
            FROM
                acc_journal_detail
                JOIN acc_account_number ON acc_journal_detail.id_akun = acc_account_number.
                ID JOIN acc_journal ON acc_journal.ID = acc_journal_detail.id_jurnal
            WHERE
                CAST ( tanggal_jurnal AS DATE ) > (
                SELECT COALESCE
                    ( ( MAX ( acc_end_of_month_detail.ending_date ) ), '2000-01-01' )
                FROM
                    acc_end_of_month_detail
                    JOIN acc_account_number ON acc_end_of_month_detail.nomor_akun = acc_account_number.ID
                WHERE
                    acc_account_number.nomor_akun LIKE'2.330%'
                    AND acc_end_of_month_detail.ending_date <= '2019-01-01'
                    AND acc_end_of_month_detail.year_month = ( SELECT MAX ( year_month ) FROM acc_end_of_month_detail WHERE acc_end_of_month_detail.year_month < '201901' AND acc_end_of_month_detail.id_bpr = 3 AND acc_end_of_month_detail.id_bpr_cabang = 3 )
                    AND acc_end_of_month_detail.id_bpr = 3
                    AND acc_end_of_month_detail.id_bpr_cabang = 3
                )
                AND CAST ( tanggal_jurnal AS DATE ) < '2019-01-01'
                AND acc_account_number.nomor_akun LIKE'2.330%'
                AND acc_journal.id_bpr = 3
            AND acc_journal.id_bpr_cabang = 3
    ) saldoakhir,'-' nomor_rekening,'saldo awal' keterangan
    union
    SELECT
    aj.kode_jurnal,
    aj.tanggal_jurnal,
    ajd.debet_amount,
    ajd.credit_amount,
    0 saldoakhir,
    ajd.nomor_rekening,
    aj.deskripsi
FROM
    acc_journal_detail ajd
    JOIN acc_journal aj ON ajd.id_jurnal = aj."id"
    JOIN acc_account_number acn ON ajd.id_akun = acn."id"
WHERE
    aj.id_bpr = 3
    AND aj.tanggal_jurnal >= '2019-01-01'
    AND aj.tanggal_jurnal < CAST ( '2019-08-02' AS DATE ) + INTERVAL '1 day'
    AND acn.nomor_akun LIKE'2.330%'
    AND aj.id_bpr_cabang = 3
ORDER BY tanggal_jurnal) bukubesar
order by tanggal_jurnal, kodejurnal;



--hitung saldoakhir 
DO $$
  Declare rownumber bigint;   
    Declare debet1 bigint;   
        Declare kredit1 bigint;   
        Declare saldoakhir1 bigint;
        declare tipeakun INTEGER;

    cur1 CURSOR for select row_number, debet, kredit, saldoakhir from "temporary".tmp_bukubesar order by row_number;   
 begin 
OPEN Cur1 ; 
Loop     
    FETCH  next from Cur1 INTO rownumber, debet1, kredit1, saldoakhir1;    
    EXIT WHEN NOT FOUND;
   
    select tipe_akun into tipeakun from acc_account_number where nomor_akun='2.330';
   
    if tipeakun in (0,3) then
    update "temporary".tmp_bukubesar
    set saldoakhir=(select saldoakhir from "temporary".tmp_bukubesar where row_number < rownumber order by row_number desc limit 1) + debet1 - kredit1
    where row_number=rownumber and row_number >1 and iduser = 123;
    end if;
   
    if tipeakun in (1,2) then
    update "temporary".tmp_bukubesar
    set saldoakhir=(select saldoakhir from "temporary".tmp_bukubesar where row_number < rownumber order by row_number desc limit 1) - debet1 + kredit1
    where row_number=rownumber and row_number >1 and iduser = 123;
    end if;
   
end loop ;  
CLOSE Cur1; 
END $$;