PostgreSQL问答-20230416

原创 Together 2023-04-16

598

本期问答如下：

pg_get_serial_sequence()函数返回空值
PostgreSQL如何查看表的依赖关系
PostgreSQL导出数据到Excel
PostgreSQL修改数据库用户名后，使用原密码还可以登录吗？

Q1.pg_get_serial_sequence()函数返回空值

问题描述

使用pg_get_serial_sequence()函数查看表字段关联的sequence，返回空值。

问题解答

pg_get_serial_sequence()函数返回与列关联的序列名，如果没有与列关联的序列则返回null，第一个输入参数是具有可选模式的表名，第二个参数是列名。因为第一个形参可能是一个schema和table，所以它不被视为双引号标识符，这意味着默认情况下它是小写的，而第二个形参只是一个列名，被视为双引号并保留其大小写。函数返回一个适合传递给序列函数的格式值。可以使用alter sequence owned by 修改或删除改关联。

为什么使用pg_get_serial_sequence()会取到空值？

这取决于表是如何创建的，当使用serial时，序列会自动创建并与表关联

PostgreSQL：

pepsidb=# create table my_table_auto(id serial);
CREATE TABLE
pepsidb=#
pepsidb=# select pg_get_serial_sequence('my_table_auto','id');
   pg_get_serial_sequence
-----------------------------
 public.my_table_auto_id_seq
(1 row)

当手动创建一个序列时，它不会与表关联

pepsidb=# create sequence my_sequence;
CREATE SEQUENCE
pepsidb=# create table my_table_manually(id int default nextval('my_sequence'));
CREATE TABLE
pepsidb=# select pg_get_serial_sequence('my_table_manually', 'id');
 pg_get_serial_sequence
------------------------

(1 row)

使用alter sequence sequence_name owned by tablename.column;关联表字段

pepsidb=# alter sequence my_sequence owned by my_table_manually.id;
ALTER SEQUENCE
pepsidb=# select pg_get_serial_sequence('my_table_manually', 'id');
 pg_get_serial_sequence
------------------------
 public.my_sequence
(1 row)

Q2.PostgreSQL如何查看表的依赖关系

通过PostgreSQL系统表，pg_depend关联pg_rewrite/pg_class/pg_namespace，可以查出表的依赖关系

SELECT
	dependent_ns.nspname || '.' || dependent_view.relname AS dependent_view,
	source_ns.nspname || '.' || source_table.relname AS source_table 
FROM
	pg_depend
	JOIN pg_rewrite ON pg_depend.objid = pg_rewrite.oid
	JOIN pg_class AS dependent_view ON pg_rewrite.ev_class = dependent_view.oid
	JOIN pg_class AS source_table ON pg_depend.refobjid = source_table.oid
	JOIN pg_namespace dependent_ns ON dependent_ns.oid = dependent_view.relnamespace
	JOIN pg_namespace source_ns ON source_ns.oid = source_table.relnamespace 
WHERE
	source_table.relname = 'your_table_name';

例如查看系统表pg_settings

Q3.PostgreSQL导出数据到Excel

pgclimb工具下载地址：https://github.com/lukasmartinelli/pgclimb

chmod +x pgclimb_linux_amd64
mv  pgclimb_linux_amd64   pgclimb

pgclimb语法

[root@pg01 pgsql]# ./pgclimb  --help
NAME:
   pgclimb - Export data from PostgreSQL into different data formats

USAGE:
   pgclimb_linux_amd64 [global options] command [command options] [arguments...]

VERSION:
   0.2

COMMANDS:
     template   Export data with custom template
     jsonlines  Export newline-delimited JSON objects
     json       Export JSON document
     csv        Export CSV
     tsv        Export TSV
     xml        Export XML
     xlsx       Export XLSX spreadsheets
     inserts    Export INSERT statements
     help, h    Shows a list of commands or help for one command

GLOBAL OPTIONS:
   --dbname value, -d value                  database (default: "postgres") [$DB_NAME]
   --host value                              host name (default: "localhost") [$DB_HOST]
   --port value, -p value                    port (default: "5432") [$DB_PORT]
   --username value, -U value                username (default: "postgres") [$DB_USER]
   --ssl                                     require ssl mode
   --password value, --pass value            password [$DB_PASS]
   --query value, --command value, -c value  SQL query to execute [$DB_QUERY]
   --file value, -f value                    SQL query filename
   --output value, -o value                  Output filename
   --help, -h                                show help
   --version, -v                             print the version

示例：

Excel格式

单表导入到excel

-bash-4.2$ ./pgclimb --dbname=pepsidb --output=test.xlsx --query="select * from test" xlsx

多张表导入到一个excel

-bash-4.2$ ./pgclimb --dbname=pepsidb --output=pepsidb.xlsx --query="select * from employees" xlsx  --sheet emp
-bash-4.2$
-bash-4.2$ ./pgclimb --dbname=pepsidb --output=pepsidb.xlsx --query="select * from departments" xlsx  --sheet dep
-bash-4.2$
-bash-4.2$ ./pgclimb --dbname=pepsidb --output=pepsidb.xlsx --query="select * from jobs" xlsx  --sheet job