主页 / user-8641

jgrocha's questions

Martin Hope
jgrocha
Asked: 2021-06-02 09:26:34 +0800 CST
  • 6

对于serialPostgresql < 10 中使用的列，我们按其名称管理序列。我们能够通过以下方式重置序列：

SELECT setval('table_id_seq', (SELECT MAX(id) FROM table));

从版本 10 开始，使用标识列，无需使用序列名称。那很好。

ALTER TABLE table ALTER COLUMN id RESTART WITH 1000;

如何在max(id)不知道序列名称的情况下将标识列设置为？

据我从ALTER TABLE 语法中可以看出，没有办法让子查询来计算序列的开始。

我想写一些类似的东西：

ALTER TABLE table ALTER COLUMN id RESTART WITH (SELECT MAX(id) FROM table);
postgresql identity
Martin Hope
jgrocha
Asked: 2020-03-08 10:53:10 +0800 CST
  • 1

问题

我正在尝试重塑包含许多列的表格。我正在尝试独立于特定表进行操作，因此我正在尝试对任何表进行操作。

让我们使用一个非常简单的表格foo。

CREATE TABLE foo (id int, a text, b text, c text);
INSERT INTO foo VALUES (1, 'ant', 'cat', 'chimp'), (2, 'grape', 'mint', 'basil');

select * from foo;

| id|  a  |  b |  c  |
|---|-----|----|-----|
|  1| ant | cat|chimp|
|  2|grape|mint|basil|

我想将 columna和b行转换c为行。

此查询有效（对于此特定表）：

SELECT id,
       unnest(array['a', 'b', 'c']) AS colname,
       unnest(array[a, b, c]) AS colvalue
FROM foo;

|id|colname|colvalue|
|--|-------|--------|
| 1|  a    |  ant   |
| 1|  b    |  cat   |
| 1|  c    | chimp  |
| 2|  a    | grape  |
| 2|  b    | mint   |
| 2|  c    | basil  |

但我想让它适用于任何有很多列的表。

我已经做了什么

要获取我想要在行中转换的所有列，我可以使用：

SELECT column_name FROM information_schema.columns WHERE table_schema = 'public' AND table_name   = 'foo' and column_name ~ '^[a-z]$';

因此，使用前面的查询，我可以执行以下操作：

WITH tablecolumns AS (SELECT array_agg( column_name ) as cols FROM information_schema.columns WHERE table_schema = 'public' AND table_name = 'foo' and column_name ~ '^[a-z]$') 
select id, 
       unnest( tablecolumns.cols ) AS colname,
       unnest( array[a, b, c] ) AS colvalue
FROM foo, tablecolumns;

但我无法array[a, b, c]用动态的东西代替。如果我使用：

WITH tablecolumns AS (SELECT array_agg( column_name ) as cols FROM information_schema.columns WHERE table_schema = 'public' AND table_name = 'foo' and column_name ~ '^[a-z]$') 
select id, 
       unnest( tablecolumns.cols ) AS colname,
       unnest( tablecolumns.cols ) AS colvalue
FROM foo, tablecolumns;

结果不是列的值，而只是列的名称。

问题

我怎样才能unnest得到列的值？

postgresql
Martin Hope
jgrocha
Asked: 2019-11-27 15:21:54 +0800 CST
  • 1

我希望有几个用户能够创建模式和表。这个模式（和表）应该对所有人可用。我想设置数据库，并设置所有权限（以及使用 ALTER DEFAULT PRIVILEGES 的未来权限）。用户之后不必更改任何权限。

这些模式是使用客户端软件创建的，在这种情况下是 QGIS。因此，用户只能发出CREATE SCHEMA newschemaname.

问题是，即使用户属于同一组，他们也无法看到彼此的模式。

无法访问其他人的架构

我正在使用两个角色进行测试。用户admin1和admin2，都属于grp_admin：

CREATE ROLE grp_admin NOLOGIN;
-- one user should be able to create db and extensions; these permissions are not inherited
CREATE ROLE admin1 WITH PASSWORD 'test2k19' LOGIN inherit SUPERUSER CREATEDB CREATEROLE; 
GRANT grp_admin TO admin1;
CREATE ROLE admin2 WITH PASSWORD 'test2k19' LOGIN inherit; 
GRANT grp_admin TO admin2;

作为admin1，我创建了一个新数据库，并授予了一些权限（以及未来对象的权限）：

CREATE DATABASE people;
\c people

-- To create schemas and tables
GRANT ALL PRIVILEGES ON DATABASE people TO grp_admin;

ALTER DEFAULT PRIVILEGES GRANT select, INSERT, UPDATE, DELETE, TRUNCATE ON TABLES TO grp_admin;
ALTER DEFAULT PRIVILEGES GRANT USAGE, SELECT, UPDATE ON SEQUENCES TO grp_admin;
ALTER DEFAULT PRIVILEGES GRANT EXECUTE ON FUNCTIONS TO grp_admin;

如果，作为用户，admin1我创建了一个新的模式和表，另一个admin2人无权访问它：

create schema archive;
create table archive.vip ( thename varchar);
insert into archive.vip values ('Fernão de Magalhães');

作为admin2：

select * from archive.vip;
ERROR:  42501: permission denied for schema archive
LINE 1: select * from archive.vip;

能够访问其他人的架构

如果我authorization grp_admin在创建架构时使用，它可以工作。作为用户admin1：

create schema future authorization grp_admin;
create table future.vip ( thename varchar);
insert into future.vip values ('Robot 5126');

作为用户admin2：

select * from future.vip;
  thename   
------------
 Robot 5126

问题

由于用户正在使用 QGIS（客户端应用程序）创建模式，因此仅发布CREATE SCHEMA newschemaname，用户无法相互共享模式和表。

我怎样才能让第二次admin2访问admin1创建的模式？（在创建每个架构后不发出 GRANT 权限）。

postgresql
Martin Hope
jgrocha
Asked: 2018-03-31 11:26:04 +0800 CST
  • 1

对于给定的表，我想在我的数据库中拥有所有以前记录的副本。我只在表中插入数据。

我有一个额外的列seq来知道哪个是活动记录。

所以，我seq = 0用于当前记录。每seq < 0一张唱片都是旧唱片。

这是表格（我省略了一些列以使其更简单）

CREATE TABLE public.machine (
    id character varying(25) NOT NULL,
    seq integer NOT NULL,
    vendor character varying(50),
    model character varying(50),
    CONSTRAINT pk_machine PRIMARY KEY (id, seq) );

让我展示一些记录：

"cat-1" 0   "Caterpillar"   "D3K2 TIER 4 FINAL"
"cat-1" -1  "Caterpillar"   "D3K2 TIER 4 FINAL"
"cat-1" -2  "Caterpillar"   "D3K2 TIER 4 FINAL"
"cat-1" -3  "Caterpillar"   "D3K2 TIER 4 FINAL"
"cat-1" -4  "Caterpillar"   "D3K2 TIER 4 FINAL"
"cat-1" -5  "Caterpillar"   "D3K2 TIER 4 FINAL"

如果我需要插入一条新记录，我会这样做：

UPDATE machine SET seq = seq-1 where id = 'cat-1';

接着：

insert into machine (id, seq, vendor, model) values ( 'cat-1', 0, 'Caterpillar', 'D3K2 TIER 4 FINAL');

但他的不起作用。有时，更新失败并出现以下错误：

ERROR:  duplicate key value violates unique constraint "pk_machine"
DETAIL:  Key (id, seq)=(cat-2, 0) already exists.

如果更新每次都逐行更改，那么它可能会中断。如果以正确的顺序进行更新（从最小数量开始），则更新将起作用。

如何通过 seq 降序进行更新顺序？

我为此写了一个触发器，但它有时会失败。

CREATE TRIGGER make_history
    BEFORE INSERT
    ON public.machine
    FOR EACH ROW
    EXECUTE PROCEDURE public.history();

CREATE OR REPLACE FUNCTION history() RETURNS TRIGGER AS $$
BEGIN
    UPDATE machine SET seq = seq-1 where id = NEW.id;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;
postgresql