主页 / dba / 问题

问题[postgresql-9.5](dba)

Martin Hope
Franck White
Asked: 2021-04-02 05:11:16 +0800 CST
  • 0

我想从我的表中的一系列值中找到缺失的行。例如，在这张照片documentno 'YD4BC2006008'中是缺失的。

我想通过c_order_id和找到缺失的行documentno。

我试过了，但没有返回预期的结果：

select * from
(SELECT distinct c_order_id, documentno,cast(right(documentno,3) as integer) as no
FROM adempiere.c_order
ORDER BY documentno, c_order_id) as f

在此处输入图像描述

在此示例中，缺少许多行： from'DGPOS2003030'到'DGPOS2003068'。

如何编写查询来检索它们？

在此处输入图像描述

postgresql postgresql-9.5
Martin Hope
FranckT
Asked: 2021-04-02 00:48:45 +0800 CST
  • 0

我的表结构和一些示例数据（测试表），以及我想要的输出（结果）都可以在这里找到。

当我做

SELECT distinct colony, min(date_check), max(date_check)
from test
where protection ='Y'
group by colony

我只按菌落检测 1 个事件。

我应该使用函数还是有其他方法？

ps：如果你想到一个更好的标题来帮助用户，不要犹豫，我在挣扎。

postgresql-9.5 gaps-and-islands
Martin Hope
Slim
Asked: 2021-01-07 13:43:35 +0800 CST
  • 2

问题是编写下面定义的“ADD-VALUE”请求。

对于下表中的每个“ cat ”值，只保留 3 条记录。图这个表：

ID 猫 价值 更新时间
1 猫1 v1 06/01/2021 00:00:01
2 猫1 v2 2021 年 6 月 1 日 00:00:02
3 猫1 v3 06/01/2021 00:00:03 （指针 cat1 在这里）
4 猫2 v1 06/01/2021 00:01:01
5 猫2 v2 06/01/2021 00:01:02 （指针 cat2 在这里）

插入案例：调用“ADD-VALUE(cat= cat2 , value=v3)”将产生粗体结果：

ID 猫 价值 更新时间
1 猫1 v1 06/01/2021 00:00:01
2 猫1 v2 2021 年 6 月 1 日 00:00:02
3 猫1 v3 06/01/2021 00:00:03 （指针 cat1 在这里）
4 猫2 v1 06/01/2021 00:01:01
5 猫2 v2 06/01/2021 00:01:02
6 猫2 v3 06/01/2021 00:01:02 （指针 cat2 现在在这里）

更新案例：调用“ADD-VALUE(cat= cat1 , value=v4)”将产生粗体结果：

ID 猫 价值 更新时间
1 猫1 v4 07/01/2021 00:00:04 （指针 cat1 现在在这里）
2 猫1 v2 2021 年 6 月 1 日 00:00:02
3 猫1 v3 2021 年 6 月 1 日 00:00:03
4 猫2 v1 06/01/2021 00:01:01
5 猫2 v2 06/01/2021 00:01:02 （指针 cat2 在这里）

欢迎任何建议。一个请求中的 UPDATE 或 INSERT 可能是不可能的？我考虑使用 row-num 来计算每个类别的数量。

postgresql postgresql-9.5
Martin Hope
sharadov
Asked: 2020-10-02 13:21:35 +0800 CST
  • 1

我正在尝试使用 pg_uprade 将 9.5.5 升级到 12.3，出现以下错误

新旧 pg_controldata 最大关系段大小无效或不匹配

9.5.5

每个大关系段的块数：8388608

12.3

每个大关系段的块数：131072

8388608 个块相当于 64 GB。

我使用以下选项重新编译了 12.3 版本

./configure --prefix=/opt/pgsql/PostgreSQL-12 --with-segsize=64

pg_controldata 输出 9.5.5

pg_control version number:            942
Catalog version number:               201510051
Database system identifier:           6307574991682851055
Database cluster state:               shut down
pg_control last modified:             Thu 01 Oct 2020 07:15:50 PM EDT
Latest checkpoint location:           2FB/7C000028
Prior checkpoint location:            2FB/78000098
Latest checkpoint's REDO location:    2FB/7C000028
Latest checkpoint's REDO WAL file:    00000001000002FB0000001F
Latest checkpoint's TimeLineID:       1
Latest checkpoint's PrevTimeLineID:   1
Latest checkpoint's full_page_writes: on
Latest checkpoint's NextXID:          0/130082037
Latest checkpoint's NextOID:          182620
Latest checkpoint's NextMultiXactId:  1
Latest checkpoint's NextMultiOffset:  0
Latest checkpoint's oldestXID:        1741
Latest checkpoint's oldestXID's DB:   1
Latest checkpoint's oldestActiveXID:  0
Latest checkpoint's oldestMultiXid:   1
Latest checkpoint's oldestMulti's DB: 1
Latest checkpoint's oldestCommitTsXid:0
Latest checkpoint's newestCommitTsXid:0
Time of latest checkpoint:            Thu 01 Oct 2020 07:15:50 PM EDT
Fake LSN counter for unlogged rels:   0/1
Minimum recovery ending location:     0/0
Min recovery ending loc's timeline:   0
Backup start location:                0/0
Backup end location:                  0/0
End-of-backup record required:        no
wal_level setting:                    hot_standby
wal_log_hints setting:                off
max_connections setting:              1000
max_worker_processes setting:         8
max_prepared_xacts setting:           0
max_locks_per_xact setting:           64
track_commit_timestamp setting:       off
Maximum data alignment:               8
Database block size:                  8192
Blocks per segment of large relation: 8388608
WAL block size:                       8192
Bytes per WAL segment:                67108864
Maximum length of identifiers:        64
Maximum columns in an index:          32
Maximum size of a TOAST chunk:        1996
Size of a large-object chunk:         2048
Date/time type storage:               64-bit integers
Float4 argument passing:              by value
Float8 argument passing:              by value
Data page checksum version:           0

pg_controldata 输出 12.3

pg_control version number:            942
Catalog version number:               201510051
Database system identifier:           6307574991682851055
Database cluster state:               shut down
pg_control last modified:             Thu 01 Oct 2020 07:15:50 PM EDT
Latest checkpoint location:           2FB/7C000028
Prior checkpoint location:            2FB/78000098
Latest checkpoint's REDO location:    2FB/7C000028
Latest checkpoint's REDO WAL file:    00000001000002FB0000001F
Latest checkpoint's TimeLineID:       1
Latest checkpoint's PrevTimeLineID:   1
Latest checkpoint's full_page_writes: on
Latest checkpoint's NextXID:          0/130082037
Latest checkpoint's NextOID:          182620
Latest checkpoint's NextMultiXactId:  1
Latest checkpoint's NextMultiOffset:  0
Latest checkpoint's oldestXID:        1741
Latest checkpoint's oldestXID's DB:   1
Latest checkpoint's oldestActiveXID:  0
Latest checkpoint's oldestMultiXid:   1
Latest checkpoint's oldestMulti's DB: 1
Latest checkpoint's oldestCommitTsXid:0
Latest checkpoint's newestCommitTsXid:0
Time of latest checkpoint:            Thu 01 Oct 2020 07:15:50 PM EDT
Fake LSN counter for unlogged rels:   0/1
Minimum recovery ending location:     0/0
Min recovery ending loc's timeline:   0
Backup start location:                0/0
Backup end location:                  0/0
End-of-backup record required:        no
wal_level setting:                    hot_standby
wal_log_hints setting:                off
max_connections setting:              1000
max_worker_processes setting:         8
max_prepared_xacts setting:           0
max_locks_per_xact setting:           64
track_commit_timestamp setting:       off
Maximum data alignment:               8
Database block size:                  8192
Blocks per segment of large relation: 8388608
WAL block size:                       8192
Bytes per WAL segment:                67108864
Maximum length of identifiers:        64
Maximum columns in an index:          32
Maximum size of a TOAST chunk:        1996
Size of a large-object chunk:         2048
Date/time type storage:               64-bit integers
Float4 argument passing:              by value
Float8 argument passing:              by value
Data page checksum version:           0

并再次运行 pg_upgrade，并得到与上述相同的错误。我在这里想念什么？

postgresql postgresql-9.5
Martin Hope
Amer Bearat
Asked: 2020-07-11 20:56:14 +0800 CST
  • 0

当我将服务器上的 postgres 连接到本地的 postico 时，出现此错误

could not connect to server: Operation timed out
Is the server running on host "http://159.65.65.203/" (92.242.140.2) and accepting
TCP/IP connections on port 5432?

我在任何地方都找不到 postgresql.conf 和 pg_hba.conf 文件，所以我创建了这些文件，如下图所示

在此处输入图像描述

我找到了这个解释，但仍然没有解决我的问题

Ubuntu does not use the standard pg_ctl for postgreSQL. Instead, it 
uses pg_ctlcluster.
That in turn controls the different PostgreSQL clusters. When you do 
an install of a new cluster, pg_ctlcluster is smart enough to put
postgresql.conf & pg_hba.conf into separate dirs.
So to be specific, /usr/lib/tmpfiles.d/postgresql.conf is just a tmp 
file that you can ingnore, /etc/postgresql/9.4/main/postgresql.conf is 
the original version for the 9.4 cluster and
/var/lib/postgresql/9.4/main/postgresql.auto.conf is the actual, live
version of the 9.4 cluster that you need to change to affect the 9.4
cluster. Likewise for the pg_hba.conf.
reference https://www.postgresql.org/message- 
id/CANu8FiygR5CdeqRTiE0YM+BQk6aK+qZyw2frH_FWV27J1exong@mail.gmail.com

这里我如何配置 postgresql.conf

port=5432
listen_addresses='*'

这里我是如何配置 pg_hba.conf

 host    all             all              0.0.0.0/0                       md5
 host    all             all              ::/0                            md5

当我跑步时

Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program 
name
tcp        0      0 0.0.0.0:80              0.0.0.0:*               LISTEN      -
tcp        0      0 0.0.0.0:22              0.0.0.0:*               LISTEN      -
tcp        0      0 127.0.0.1:5432          0.0.0.0:*               LISTEN      
29761/postgres
tcp6       0      0 :::22                   :::*                    LISTEN      -

显然 5432 并非对所有连接都开放。当我使用邮递员进行 api 调用时似乎有效。我目前使用的 mac 防火墙没有打开，我的服务器在 digitalocean 上使用 Ubuntu 16.04.6 (LTS) x64。

这是 postgres 文件的路径

postgres@borroupapi:~/9.5/main$

当我运行 find / -name postgresql.conf 我得到这个

/usr/lib/tmpfiles.d/postgresql.conf

但我找不到登录 postgres 用户的路径

postgresql postgresql-9.5
Martin Hope
Henrik N
Asked: 2020-04-23 01:14:04 +0800 CST
  • 4

由于某些原因，我们想重命名生产数据库中的一些序列。

有没有办法在生产中安全地做到这一点，而不必先关闭与数据库的所有连接？

如果我们在 ID 序列中有间隙（例如，它从 ID 123 跳转到 ID 200 或其他）很好，但我们显然不希望长锁导致延迟/错误，不希望任何重复 ID 和类似的风险。

我们在 Heroku 上使用 PostgreSQL 9.5.21。

我们查看了文档，但仍然不确定跑步的后果

ALTER SEQUENCE old_id_seq RENAME TO new_id_seq;
ALTER TABLE mytable ALTER COLUMN id SET DEFAULT nextval('new_id_seq');

在生产中。我想一个风险是如果mytable在这两个命令之间发生了一些事情。但是如果我们做这样的事情会怎样：

-- Starting at a much higher value than the currently highest ID.
CREATE SEQUENCE new_id_seq START 200;

ALTER TABLE mytable ALTER COLUMN id SET DEFAULT nextval('new_id_seq');

DROP SEQUENCE old_id_seq;

这样做有什么风险？

postgresql postgresql-9.5
Martin Hope
ATN
Asked: 2020-02-08 05:41:57 +0800 CST
  • 1

查询状态为 时是什么意思null？

my_database_name> SELECT pid, datname, usename, application_name, query_start, state, state_change FROM pg_stat_activity;
+-------+------------------+------------------+--------------------------+-------------------------------+---------+-------------------------------+
| pid   | datname          | usename          | application_name         | query_start                   | state   | state_change                  |
|-------+------------------+------------------+--------------------------+-------------------------------+---------+-------------------------------|
| 19093 | my_database_name | my_user_nam      | pgcli                    | 2020-02-07 12:58:54.665447+00 | active  | 2020-02-07 12:58:54.665449+00 |
| 13777 | my_database_name | some_other_user  | DBeaver 6.3.2 - Main     | <null>                        | <null>  | <null>                        |
| 13776 | my_database_name | some_other_user  | DBeaver 6.3.2 - Metadata | <null>                        | <null>  | <null>                        |
| 2036  | <null>           | <null>           |                          | <null>                        | <null>  | <null>                        |
+-------+------------------+------------------+--------------------------+-------------------------------+---------+-------------------------------+
postgresql postgresql-9.5
Martin Hope
user2569831
Asked: 2019-10-18 11:35:55 +0800 CST
  • 0

我有以下查询（PostgreSQL 9.5.19）：

with userlist as (
  select a.ips, 
         array_agg(t.super_user_id) as super_users
  from tmp_table_1 t
    left join unnest(t.ips) as a(ips) on true
  group by a.ips
), shared_users as (
  select u.ips,
         array(select distinct ul.uid
               from userlist u2, unnest(u2.super_users) as ul(uid)
               where u.super_users && u2.super_users
               order by ul.uid) as super_users
  from userlist u
)
select s.super_users, array_agg(distinct s.ips) as ips, 
    (select sum(msg_count) from tmp_table_1 where super_user_id in s.super_users) as sum_msg_count
from shared_users s
group by s.super_users;

我收到以下错误：

ERROR:  syntax error at or near "s"
LINE 38: ...sg_count) from tmp_table_1 where super_user_id in s.super_us...
                                                              ^

似乎我无法s.super_users从子查询中访问。

有谁知道我该怎么做？

这是一个完整的例子： https ://www.db-fiddle.com/f/geB9V6TdHEkF2mSqpd2b4g/1

也许我没有正确地接近这个？

问题

如何将附加列（sum_msg_count、sum_user_count和）添加max_last_date到pubs结果集中？

期望的结果

|super_users                      |  ips                                                        |sum_msg_count| sum_user_count  |   max_last_date                | pubs                        |
|---------------------------------|-------------------------------------------------------------|-------------|-----------------|--------------------------------|-----------------------------|
|{522460771,522460772,522460773}  |  {94.134.88.115,94.134.88.124,94.134.88.125,94.134.88.136}  | 30          |         9       |  "2018-06-30 21:29:48.664000"  |  {ad2,googlecooler,ads,ad1} |
|{522460774,522460775}            |  {127.0.0.1,5.5.5.5}                                        | 20          |         6       |  "2018-06-30 21:29:48.664000"  |  {googlecooler,ad3,ad1}     |
|{522460776}                      |  {8.8.8.8}                                                  | 10          |         3       |  "2018-06-30 21:29:48.664000"  |  {googlecooler,ads}         |
postgresql postgresql-9.5
Martin Hope
user11789512
Asked: 2019-08-13 19:03:10 +0800 CST
  • 0

我有如下示例数据，想看看在这种情况下是否可以使用交叉表函数。

样本数据：

ITEM   | CD | TYPE |PARTS |  PART | CNT |
Item 1 | A  | AVG  |2     |  1    | 10  |
Item 1 | A  | AVG  |2     |  2    | 20  |
Item 1 | B  | AVG  |2     |  1    | 10  |
Item 1 | B  | AVG  |2     |  2    | 20  |
Item 1 | A  | SUM  |2     |  1    | 10  |
Item 1 | A  | SUM  |2     |  2    | 20  |
Item 1 | B  | SUM  |2     |  1    | 10  |
Item 1 | B  | SUM  |2     |  2    | 20  |

预期结果：

ITEM   | CD | AVG_1 | SUM_1 | AVG_2 | SUM_2 |   

Item 1 |  A | 10    | 10    | 20    | 20    |

Item 1 |  B | 10    | 10    | 20    | 20    |

此外，如果部分为 3，则 A 和 B 代码的 AVG 和 SUM 将额外增加一行。这是动态的，用户可以输入此值。如何将多行展平？

在此要求中，项目 1 和代码 A 必须显示第 1 部分总和和平均值，然后显示第 2 部分总和和平均值，这可以根据部分列中的值进行更改。

感谢您对此的帮助！

postgresql postgresql-9.5