PostgreSQL xử lý chuỗi với xpath Tôi có bảng thế này Giờ tôi cần tách theo cấu trúc thế này Với cột định nghĩa cần cực kỳ ngắn gọn, 1 cụm từ thôi càng tốt Cột description trên kia thì […]
Tag: postgresql
PostgreSQL – Terminate Client Connect View
Posted on Posted in Lang thang, Mẹo vặt, Sói's TutorialsLeave a Comment on PostgreSQL – Terminate Client Connect View
PostgreSQL – Terminate Client Connect View To fix FATAL: sorry, too many clients already connection to server https://dothanhlong.org/postgresql-fatal-sorry-too-many-clients-already-connection-to-server/ — View: public.terminate_client_connect — DROP VIEW public.terminate_client_connect; CREATE OR REPLACE VIEW public.terminate_client_connect AS SELECT pg_terminate_backend(pg_stat_activity.pid) AS pg_terminate_backend FROM pg_stat_activity WHERE pg_stat_activity.pid <> […]
[PostgreSQL] FATAL: sorry, too many clients already connection to server
[PostgreSQL] FATAL: sorry, too many clients already connection to server Unable to connect to PostgreSQL server: connection to server at "DB hostname" (DB IP), port DB port failed: FATAL: sorry, too many clients already connection to server at "DB hostname" […]
Make and Update SEQUENCE in PostgreSQL – GeoServer WMST with Image mosaicking plugin
Make and Update SEQUENCE in PostgreSQL – GeoServer WMST with Image mosaicking plugin Create Table, Index, SEQUENCE, COMMENT DROP TABLE IF EXISTS "img_lst"; DROP SEQUENCE IF EXISTS raster.img_lst_fid_seq; CREATE SEQUENCE raster.img_lst_fid_seq INCREMENT 1 MINVALUE 1 MAXVALUE 9223372036854775807 START 1 CACHE […]
PostgreSQL – Column to Json/Jsonb
Posted on Posted in Dữ liệu, Lang thang, Mẹo vặt, Sói's Tutorials, WebLeave a Comment on PostgreSQL – Column to Json/Jsonb
PostgreSQL – Column to Json/Jsonb I have a table coso_nhayen like this row_to_json select row_to_json(row) from (select * from coso_nhayen) row LIMIT 5; to_jsonb SELECT id, to_jsonb(t.*) – 'id' AS data FROM coso_nhayen t LIMIT 5; json_agg SELECT json_agg(t) FROM coso_nhayen t; […]
[Notes] SQL or NoSQL? Why not use both (with PostgreSQL)?
[Notes] SQL or NoSQL? Why not use both (with PostgreSQL)? https://supabase.com/blog/sql-or-nosql-both-with-postgresql It’s a tough decision for any developer starting a new project. Should you store your data in a standard, time-tested SQL database, or go with one of the newer […]
Docker Backup, Restore PostgreSQL Database
Posted on Posted in Công nghệ, Lang thang, Linux, Mẹo vặtLeave a Comment on Docker Backup, Restore PostgreSQL Database
Docker Backup, Restore PostgreSQL Database Single Database PostgreSQL Backup Single Database sudo docker exec -t –user pg_username pg_container_name pg_dump -c -p 5432 -U pg_username "database_name" > "/path/on/your/machine/dump.sql" PostgreSQL Restore Single Database sudo docker exec -t –user pg_username pg_container_name psql -c […]
[PostgreSQL Linux] Remove installed PostgreSQL in opt dir
[PostgreSQL Linux] Remove installed PostgreSQL in opt dir I installed PostgreSQL through the installation file, so the installation directory is /opt/PostgreSQL/10 I tried sudo apt-get –purge remove postgresql* but this did not work for me. In my case uninstaller was […]
PostgreSQL Add User
PostgreSQL Add User CREATE USER user_name WITH PASSWORD 'user_password'; GRANT ALL PRIVILEGES ON DATABASE "database_name" to user_name; GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO user_name; -soiqualang_chentreu-
[PostgreSQL] Create a database from an exist database
[PostgreSQL] Create a database from an exist database Create a database Postgres allows the use of any existing database on the server as a template when creating a new database. I’m not sure whether pgAdmin gives you the option on […]
PostgreSQL – Function to remove accents in string
PostgreSQL – Function to remove accents in string Loại bỏ dấu câu, chuyển câu có dấu thành không dấu trong PostgreSQL Ex: José, Jose, Jósé, Jóse -> Jose Load extension unaccent CREATE EXTENSION unaccent; Run SQL to remove accents in […]
PostgreSQL update value in JSONB
PostgreSQL update value in JSONB Tình cờ mình làm việc, cần update lại giá trị một key trong cột có kiểu là jsonb, thế là mình nghĩ json thì cũng là text thôi, replace là được, và đây là cách […]
Fix OGR2OGR Import – invalid byte sequence for encoding UTF8
Fix OGR2OGR Import – invalid byte sequence for encoding UTF8 Error showing When I use OGR2OGR to import Shapeilfe to PostgreSQL, I got error like this: invalid byte sequence for encoding "UTF8": 0xe2 0x6e 0x20 How to fix invalid byte […]
Force Drop Database PostgreSQL
Force Drop Database PostgreSQL — Check current activity sessions SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = 'nongnghiepdongnai'; — Drop DB DROP DATABASE nongnghiepdongnai;
Xếp hạng các Database Engines theo mức độ phổ biến (tháng 3/2022)
Xếp hạng các Database Engines theo mức độ phổ biến (tháng 3/2022) Nguồn: https://db-engines.com/en/ranking Điều thú vị mình thấy là riêng trong lĩnh vực các Database Engines thì các phần mềm mã nguồn mỡ cũng không hề kém cạnh các […]