PostgreSQL select current user SELECT session_user, current_user;
Tag: postgresql
PostgreSQL set TIMEZONE
PostgreSQL set TIMEZONE ALTER DATABASE geolands SET timezone TO 'Asia/Ho_Chi_Minh'; SELECT CURRENT_TIMESTAMP; Tham khảo thêm SHOW TIMEZONE; select * from pg_timezone_names
PostgreSQL thống kê | PostgreSQL statistics
PostgreSQL thống kê Create a function for row count create function count_rows_of_table( schema text, tablename text ) returns integer security invoker language plpgsql as $body$ declare query_template constant text not null := ' select count(*) from "?schema"."?tablename" '; query constant […]
PostgreSQL xử lý chuỗi với xpath
Posted on Posted in Công nghệ, Lang thang, Mẹo vặt, Sói's TutorialsLeave a Comment on PostgreSQL xử lý chuỗi với xpath
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ì […]
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 […]