ubuntu

sudo -i -u postgres

psql

#data #analitic #dataanalitic

to_timestamp(c.created_at/1000)::date as createdAt

#timestamptodata #timestamp

wrong code

sum(case when '1970-01-01'::date+date_start>= date_trunc('year', now()) then 1 else 0 end) as new_companies,

TRUE

count(distinct column) filter ( where date_start >= EXTRACT(DAYS FROM (date_trunc('year', now()) - '1970-01-01')) ) as new_companies,

this is also will be wrong

'1970-01-01'::date+date_start>= date_trunc('year', now())

will be true

date_start >= EXTRACT(DAYS FROM (date_trunc('year', now()) - '1970-01-01')

you can't change column checking time, if you will do like this code worked slowly

if you need get value by dynamic column name , you can use like this

$column_name = column_name;

DB::connection('archive')->select(
"SELECT
sum(case when {$data_moth}->>'positions' is not null then ({$data_moth}->>'positions')::int else 0 end) as enst
from table c
);

#dynamic #dynamic_column #dynamic_column_value

use where in dynamic values

$project_ids = implode(',', [1,2,3,4,54]);

DB::connection('archive')->select(
"SELECT
sum(case when table.column in ({$project_ids}) then pl.positions_plan else 0 end) as plan,
from table c
);

#dynamic #dynamic_wherein #wherein

update with other columns

update table1 t1
set column = t2.column
from table2 t2
where "your condation";

#update #update_with #update_other_column #update_other

https://stackoverflow.com/questions/7869592/how-to-do-an-update-join-in-postgresql

SELECT COUNT(DISTINCT count) gender_count,
    COUNT(DISTINCT CASE WHEN gender = 'male' THEN count END) male_count,  
    COUNT(DISTINCT CASE WHEN gender = 'female' THEN count END) female_count
FROM example_dataset

#mysql #distinct #condition

Update with limit
_____________________________

with update_ids as (
select id
from table_name
where {your condition}
limit 100
)

update table_name t
set
name = 'Test update with limit'
from update_ids
where t.id = update_ids.id;
_______________________________

#update #update_with_limit #update_limit

Stream restart учун

- сервисларни учириш керак
- sudo systemctl stop kafka-connect.service
- sudo systemctl stop kafka.service
- sudo systemctl stop zookeeper.service

- слотларни учириш керак
- select * from pg_replication_slots;
- select pg_drop_replication_slot('slotname');

- kafka logs учириш
- sudo rm -rf /tmp/kafka-logs

- connect offsets учириш (reset connect)
- sudo rm /tmp/connect.offsets

- ески данныйларни учириш
- truncate / new schema

- сервисларни старт килиш
- sudo systemctl start zookeeper.service
- sudo systemctl start kafka.service
- sudo systemctl start kafka-connect.service


#restart #resetkafka #kafka

https://www.postgresqltutorial.com/postgresql-extract/

#EXTRACT #dateformat

Convert Month Number to Month Name in PostgreSQL

SELECT TO_CHAR(
TO_DATE (12::text, 'MM'), 'Month'
) AS "Month Name";

read more ->https://database.guide/convert-month-number-to-month-name-in-postgresql/

#month #date #mont_number

select
FORMAT('{"positions":%s,"rates":%s,"persons":%s}',v1,v2,jv3)::json as v_json
from table_name

#format #format_json #json

Create index for jsonb column

CREATE INDEX vacancies_additional_info_reserved_data_reserved_types_gin ON vacancies USING GIN ((column -> 'item_name'));

#json #jsonb #index

select p.inps,
array_to_json(ARRAY(select rows
from (select p2.from_id, p2.inps, p2.doc_number, p2.doc_seria, p2.fio
from xalq_bank.persons p2
where p.inps = p2.inps) as rows)) as data
from xalq_bank.persons p
group by p.inps
having count(p.inps) > 1

#json_column #json array_to_json

pg_dump -h ip_add -p 5432 -d db_name -U user_name -W --schema='public' --table='public.requests' -O -Z 9 > requests.sql.gz

#dump #db_dump

SELECT query,
round(total_time::numeric, 2) AS total_time,
calls,
round(mean_time::numeric, 2) AS mean,
round((100 * total_time /
sum(total_time::numeric) OVER ())::numeric, 2) AS percentage_cpu
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 20;

#bigqueries #query #more_run_time #take_more_time

explain analyze SELECT ........

#analyze #analyze_query

Compare Arrays

#to check if elements of array['b', 'c', 'd'] contain elements of array['b', 'c']
postgres=# select array['b', 'c', 'd'] @> array['b', 'c'] as contains;
contains
----------
t

# to check if array[1, 2, 4] is contained by array[4, 5, 2, 1]
postgres=#select array[1, 2, 4] <@ array[4, 5, 2, 1] as is_contained_by;
is_contained_by
-----------------
t