Skip to content

Instantly share code, notes, and snippets.

View kad1r's full-sized avatar

Kadir Avcı kad1r

63 followers · 299 following
View GitHub Profile
@kad1r
kad1r / prevent_constraint_violarion_on_delete_for_postgres.sql
Created June 15, 2021 14:47
Postgres delete and constrain violation solution
ALTER TABLE table_name_1 DISABLE TRIGGER ALL;
ALTER TABLE table_name_2 DISABLE TRIGGER ALL;
DELETE FROM table_name_1 where id=xxx;
DELETE FROM table_name_2 where id=xxx;
ALTER TABLE table_name_1 ENABLE TRIGGER ALL;
ALTER TABLE table_name_2 ENABLE TRIGGER ALL;
@kad1r
kad1r / delete_duplicates_postgres.sql
Created April 15, 2021 08:15
Delete duplicate rows in Postgres
delete from table_name a using table_name b
where a.id > b.id;
@kad1r
kad1r / powershell-fix.txt
Created April 14, 2021 09:38
Powershell process exited with code 3221225477
-- process exited with code 3221225477
Open cmd as admin and type diskpart.
Then type list disk.
CTRL + C and close cmd and reopen powershell.
@kad1r
kad1r / for_loop_postgre.sql
Created April 12, 2021 11:17
Postgres for loop
do $$
declare col varchar(100);
begin
for col in select column_name from information_schema.columns where column_name!='id' and table_name='table_name' loop
insert into table_name (columns) values (values);
raise notice 'name: %', replace(col, '(', '');
end loop;
end; $$
@kad1r
kad1r / inactivity_logoff_limit
Created March 17, 2021 17:08
Extend Machine Inactivity Limit
Computer Configuration -> Policies -> Windows Settings -> Security Settings -> Local Policies -> Security Options -> Interactive logon: Machine inactivity limit
@kad1r
kad1r / update_seqs_in_database.sql
Last active February 18, 2021 10:25
Update sequences for all tables on PostgreSQL
DO $$
DECLARE
i TEXT;
BEGIN
FOR i IN (
SELECT 'SELECT SETVAL('
|| quote_literal(quote_ident(PGT.schemaname) || '.' || quote_ident(S.relname))
|| ', COALESCE(MAX(' ||quote_ident(C.attname)|| '), 1) ) FROM '
|| quote_ident(PGT.schemaname)|| '.'||quote_ident(T.relname)|| ';'
FROM pg_class AS S,
@kad1r
kad1r / InactivityTimeoutSecs.txt
Created February 3, 2021 11:45
Windows Server auto logout timeout for inactivity (regedit)
HKLM\SOFTWARE\Microsoft\Windows\CurrentVersion\Policies\System\InactivityTimeoutSecs
@kad1r
kad1r / delete_all_postgre.sql
Last active December 31, 2020 09:11
truncate, delete and restart auto increment for all data from PostgreSql
-- truncate all tables
SELECT 'TRUNCATE ' || input_table_name || ' CASCADE;' AS truncate_query FROM(SELECT table_schema || '.' || table_name AS input_table_name
FROM information_schema.tables
WHERE table_schema NOT IN ('pg_catalog', 'information_schema') AND table_schema NOT LIKE 'pg_toast%') AS information;
-- restart identity columns
SELECT 'TRUNCATE ' || input_table_name || ' RESTART IDENTITY CASCADE;' AS truncate_query FROM(SELECT table_schema || '.' || table_name AS input_table_name
FROM information_schema.tables
WHERE table_schema NOT IN ('pg_catalog', 'information_schema') AND table_schema NOT LIKE 'pg_toast%') AS information;
@kad1r
kad1r / mongodb_compass_filter_examples.txt
Created October 14, 2020 09:06
MongoDb Compass Filter Examples
{ IntegrationDataType: "DELVRY03", "RequestData" : { $regex : ".*4502742347.*" }, TimeStamp: { "$gte": ISODate('2020-08-23T00:00:00'), "$lt": ISODate('2020-09-24T00:00:00') } }
{ IntegrationDataType: "DELVRY03", "RequestData" : { $regex : ".*4502742347.*" }, TimeStamp: { "$gte": ISODate('2020-08-23T00:00:00'), "$lt": ISODate('2020-09-24T00:00:00') } }
@kad1r
kad1r / get_postgre_size.sql
Created October 7, 2020 13:13
Get size informations of db and tables on PostgreSql
-- DB SIZE
SELECT pg_size_pretty(pg_database_size('db_name'));
-- TABLE SIZE
SELECT pg_size_pretty(pg_total_relation_size('table_name'));
-- GET ALL TABLE SIZES
SELECT nspname || '.' || relname AS "relation",
pg_size_pretty(pg_total_relation_size(C.oid)) AS "total_size"
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)