PostgreSQL manual

pdadmin可以数据库分为两部分，Catalog部分是pgsql自身的Function，Schema是业务部分。 所有的业务部分都是基于Catalog，里面有很多功能，table，view，trigger 供你使用。

Install DB

选择 Locale

这个至关重要，如果default 会产生locate 和 ctyle 编码问题。

DML

第一次登录

PostgreSQL 默认的用户是postgres，密码是install时设置的密码

 D:\PostgreSQL94\bin> psql -U postgres
Password for user postgres:

初始化用户

–createdb：允许新角色创建数据库。
–createrole：允许新角色创建其他角色。
–superuser：赋予新角色超级用户权限。
–no-createdb：防止新角色创建数据库（默认）。
–no-createrole：防止新角色创建其他角色（默认）。
–no-superuser：确保新角色不是超级用户（默认）。
-P 或 --pwprompt：创建用户时提示输入密码。
-e 或 --echo：显示执行的 SQL 命令。

createuser test1
createuser --createdb test2
createuser --createrole test3
createuser --superuser test4

createuser --createdb --createrole --superuser admin

登录数据库

psql -U test_admin -d dbname

psql查看用户的权限

 \du
                          角色清單
   角色名稱   |               屬性                | 成員屬於
--------------+-----------------------------------+----------
 mi.y | 超級用戶, 建立角色, 建立 DB, 複製 | {}
 test_admin   |                                   | {}
 test_user    |                                   | {}

创建 User

CREATE USER test_user WITH PASSWORD '*abc123';

删除User

drop user admin1;

删除Role

postgres=# drop role pgrole;

赋值权限

ALTER USER test_admin CREATEDB;

ALTER USER user_name WITH SUPERUSER;

GRANT ALL PRIVILEGES ON DATABASE "EIL" to test_admin;

 ALTER USER test_admin CREATEROLE;

set mi right to test_admin

 grant "mi" to test_admin;

赋予登录权限

alter role admin login

创建数据库

CREATE DATABASE "DBNAME" WITH OWNER = "test_user" ENCODING = 'UTF8' TABLESPACE = pg_default CONNECTION LIMIT = -1 LC_COLLATE = 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8';

删除数据库

bin> dropdb --host="127.0.0.1" --username="test" DBNAME;

清空數據庫

//删除public模式以及模式里面所有的对象
DROP SCHEMA public CASCADE;
//创建public模式
CREATE SCHEMA public;

/l 查看数据库的编码

postgres=# update pg_database set datcollate='en_US.UTF-8', datctype='en_US.UTF-8' where datname='DBNAME';

设置客户端和服务端的encoding

mydb=> SELECT datname::bytea, encoding FROM pg_database;
mydb=> SHOW client_encoding;
mydb=> SHOW server_encoding;

mydb=> \encoding
mydb=> \encoding utf8;
mydb=> \l

查询和修改初始化用户

C:\PostgreSQL93\bin

psql postgres

postgres=# select * from user;

alter user "xxx.yyyy" with password '12aaa';

psql -V 版本

psql -V;
psql (PostgreSQL) 16.0

pg_dump -V 版本

 pg_dump -V;
pg_dump (PostgreSQL) 16.0

备份数据库

远程备份
./pg_dump -v -c postgresql://username:password@192.168.1.1:5432/dbname > d:\test.sql;

version 9.3

–password 下一步需要输入密码

pg_dump.exe --host 192.168.1.1 --port 5432 --username "abc" --no-password  --format custom --blobs --verbose --file "D:\abc.backup" "dbname"

恢复数据库

./psql --username admin --dbname XXX -f "d:\2024db.sql"

pg_restore -c -F t -f your.backup.tar


pg_restore.exe --host 127.0.0.1 --port 5432 --username "test" --dbname "DBNAME" --no-password  --verbose "D:\db.backup"

查询配置文件的location

 SHOW config_file;
  /var/lib/pgsql/data/postgresql.conf

查询Role（User 和Role区别）

CREATE ROLE name LOGIN; 等价于 CREATE USER name;
默认的User可以Login，Role不可以Login

postgres=# select * from pg_roles;

postgres=# select * from pg_authid;

日志

Time Zone

本地時區

log_timezone = 'GMT-8'

日志文件postgresql.conf

log_directory = 'pg_log'		# directory where log files are written,
# can be absolute or relative to PGDATA
绝对位置，或者相对于pg data的位置

log_filename = 'postgresql-%a.log'	# log file name pattern,

pg_reload_conf 重载配置，使修改生效

select pg_reload_conf();

打开日志

log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'

log_file_mode = 0600 敏感保护

log_file_mode = 0600 # the default

The 0600 permission is commonly used for sensitive files that should only be accessible and modifiable by the file’s owner. With this setting, you can only access, view or modify this file by switching to the postgres user or using root privileges. This is a good security measure since PostgreSQL logs often contain sensitive information.

Customizing the log files

Customizing the log files

could not create shared memory segment

HINT: This error usually means that PostgreSQL’s request for a shared memory segment exceeded available memory or swap space. To reduce the request size (currently 10998669312 bytes), reduce PostgreSQL’s shared_buffers parameter (currently 1310720) and/or its max_connections parameter (currently 503).

postgresql.conf 配置的shared_buffers exceed 物理内存，一般是配置物理内存的1/4.

• Memory -
  shared_buffers = 10GB # min 128kB

自身的Function

SHOW data_directory 数据库文件的位置

# SHOW data_directory;
---------------------
/var/lib/pgsql/data (1 row)

log 的location

/var/lib/pgsql/data  数据文件的位置， log文件夹下就是log
/var/lib/pgsql/data/log/postgresql-Thu.log

pg_read_file 读取日志文件

SELECT pg_read_file(‘pg_log/postgresql-.log’, 0, 1000000);
读取最近周日的log file
SELECT pg_read_file(‘pg_log/postgresql-Sun.log’, 0, 1000000);

Replace with the appropriate log file date, and the function will return the content of the log file.

chr(10) chr(13)

chr(10) line feed 换行, chr(13) carriage return 回车

SELECT ih_no,i_address,array_length(string_to_array(ih_dress,chr(10)),1),ih_inate FROM "indr" WHERE iv_no = 'aaa' or ih_no = 'bbbb'

获得字段里有多少个换行。

position 获取字符的第一次出现的位置

position(chr(10) in i_address)

~ 是否包括字符

返回T 或者 F

i_address ~ chr(10)

拼接字符串

'Avoid' || to_char( s_date, 'YYYY-MM' ) AS s_month,

string_agg

PostgreSQL 9.0 or later:

Modern Postgres (since 2010) has the string_agg(expression, delimiter) function which will do exactly what the asker was looking for:
SELECT company_id, string_agg(employee, ', ')
FROM mytable
GROUP BY company_id;

如果是 8.4 版本，可以通過
array_to_string(array_agg(t.cust_no), ', ') 實現

array_agg

合并多行到一列

SELECT t1.user_name, t1.year, ARRAY_TO_STRING(array(SELECT t2.month FROM table t2 WHERE t2.user_name=t1.user_name AND  t2.year=t1.year), ‘,’)  AS monthes, SUM(t1.money) AS total_money FROM table t1 WHERE 1 GROUP BY t1.user_name, t1.year;

case when

		SELECT
			c_code,
		CASE 
			WHEN ctype ='D' THEN 'Detail'
			WHEN ctype ='H' THEN 'header'
			ELSE ctype 
		END AS "ctype",
			ct_desc_en,
		FROM
			table

case when && coalesce

SELECT 

CASE 
WHEN COALESCE(mm.mm_desc_cn,'')='' THEN '111'  
ELSE '222' 
END

FROM main_menu AS mm 

number 精度

float8 长度53 小数点0，其实是Double类型，15位的小数位。

Limit 两种写法

        <if test="topCount!=0">
        LIMIT #{topCount}
        OR
        fetch first #{topCount} rows only
        </if>

Top number

select * from tab    fetch first 10 rows only
或者
select * from tab    LIMIT 10

忽略大小寫

ILIKE 和統一轉成大小寫兩種方式。

        <where>
	        <if test="catCode!=null and catCode.trim()!=''">
	        	And ct_cat_code ILIKE '%'||#{catCode}||'%'
	        </if>
	        <if test="codeVal!=null and codeVal.trim()!=''">
	        	And ct_code_val ILIKE '%'||#{codeVal}||'%'
	        </if>
	        <if test="desc!=null and desc.trim()!=''">
	        	And LOWER(ct_desc_en) LIKE '%'||LOWER(#{desc})||'%'
	        	Or  LOWER(ct_desc_tw) LIKE '%'||LOWER(#{desc})||'%'
	        	Or  LOWER(ct_desc_cn) LIKE '%'||LOWER(#{desc})||'%'
	        </if>
        </where>

授權

GRANT privilege [, …] ON object [, …] TO { PUBLIC | GROUP group | username }

Grant insert privilege to all users on table films:
GRANT INSERT ON films TO PUBLIC;

Grant all privileges to user manuel on view kinds:
GRANT ALL ON kinds TO manuel;

授權admin角色給root用戶

GRANT "ADMIN" TO root 

授權book數據庫給root用戶

GRANT Connect,Create,Temporary ON DATABASE "book" TO "root"

Make a user superuser

ALTER USER myuser WITH SUPERUSER;

ALTER USER username WITH NOSUPERUSER;

set path

D:\DB\PostgreSQL\16\bin

connect to database

–h is host name
-p is port number
-d is database name
-U is for user name

psql -h localhost -p 5432 -d postgres -U postgres

查詢版本信息

 select version();
PostgreSQL 8.4.20 on x86_64-redhat-linux-gnu, compiled by GCC gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-11), 64-bit

export dump file

參考

pg_dump -h 192.168.1.1 -U uesername  dbname > dbname.pgsql

import dump file

./psql -U postgres dbname<d:/db.pgsql

根据column name查找table

SELECT * FROM information_schema.columns WHERE "column_name" ='al_fg'

Business

查找重复行：

如果你想查找表中的重复行，即有相同数据的多个行，可以使用以下查询：

SELECT column1, column2, COUNT()
FROM your_table_name
GROUP BY column1, column2
HAVING COUNT() > 1;

把column1、column2 替换为你要检查的列名，将 your_table_name 替换为你的表名。
将返回重复的行以及它们的重复次数。

去除重复行：

如果你想从表中删除重复的行，你可以使用 DELETE 语句和子查询来执行。注意，在执行删除操作前请务必备份数据。

DELETE FROM your_table_name
WHERE (column1, column2) IN (
SELECT column1, column2
FROM your_table_name
GROUP BY column1, column2
HAVING COUNT(*) > 1
);
这将删除表中具有相同值的重复行，只保留每个组中的一行。

查询去重的结果：

如果你只想查询去重后的结果，可以使用 DISTINCT 关键字来实现：

SELECT DISTINCT column1, column2
FROM your_table_name;

Function

类型转化

Date to String

SUBSTRING(to_char(sd.sd_request_date,'YYYY-MM-DD'),1,2) = '99'

Q && A

max_locks_per_transaction

You might need to increase max_locks_per_transaction.

max_locks_per_transaction = 64  default
max_locks_per_transaction = 1024		# min 10
# (change requires restart)