Dave's questions

我正在使用别名登录我的 Postgres 12（在 Windows 10 上运行）数据库，该别名解析为

PGPASSWORD=$DB_PASSWORD psql -U${DB_USER} $DB_NAME

我想找到一种方法来告诉 Postgres 在登录时默认使用哪个模式，无论是在命令行还是其他方式。我尝试设置“search_path”...

> login_to_my_db
psql (12.11)
WARNING: Console code page (437) differs from Windows code page (1252)
         8-bit characters might not work correctly. See psql reference
         page "Notes for Windows users" for details.
Type "help" for help.

my_db_name=> show search_path;
   search_path
-----------------
 "$user", public
(1 row)


my_db_name=> set search_path = 'my_schema';
SET
my_db_name=> show search_path;
 search_path
-------------
 my_schema
(1 row)

但是一旦我注销并重新登录，搜索路径设置就消失了

my_db_name=> \q
> login_to_my_db
psql (12.11)
WARNING: Console code page (437) differs from Windows code page (1252)
         8-bit characters might not work correctly. See psql reference
         page "Notes for Windows users" for details.
Type "help" for help.

my_db_name=> show search_path;
   search_path
-----------------
 "$user", public
(1 row)

我如何保存它或至少告诉 psql 登录时使用什么模式？

我在 CentOS 7 上使用 Postgres 14。我想从 bash 脚本运行复制命令。我首先尝试在命令行上运行命令

$ PGPASSWORD=$DB_PASS psql -U $DB_USER -d $DB_NAME -c 'COPY myapp_currencyprice to STDOUT WITH (DELIMITER ",", FORMAT CSV, HEADER) \g /tmp/prices.csv'
ERROR:  syntax error at or near "\"
LINE 1: ...o STDOUT WITH (DELIMITER ",", FORMAT CSV, HEADER) \g /tmp/pr...

逃避分隔符的正确方法是什么？我试过改变我的单引号和双引号，但无济于事。

我在 CentOS 7 上使用 Postgres 14。我想导出单个表的数据，但我没有超级用户权限。不出所料，我收到了这个错误

myapp=> COPY myapp_currencyprice to '/tmp/prices.csv' DELIMITER ',' CSV HEADER;
ERROR:  must be superuser or a member of the pg_write_server_files role to COPY to a file
HINT:  Anyone can COPY to stdout or from stdin. psql's \copy command also works for anyone.

对于没有超级用户权限的用户，导出表数据（用于稍后导入）的正确方法是什么？

在本地 VirtualBox 实例上运行 MySql 5.7.33。我已从虚拟服务器转发端口 3306，以便在本地访问。在我的虚拟主机上，我的 /etc/mysql/my.cnf 文件中有这个...

[client]
#password = your_password
port = 3306
socket = /var/run/mysqld/mysqld.sock

[mysqld]
port = 3306
bind-address = 0.0.0.0
datadir = /var/lib/mysql
socket = /var/run/mysqld/mysqld.sock
pid-file = /var/run/mysqld/mysqld.pid

从我的本地机器，我可以使用连接到 MySql 实例

mysql -h 127.0.0.1 -u my_user my_db -p

但是一旦我更改为“localhost”，我就无法再连接

$ mysql -h localhost -u my_user my_db -p
Enter password: 
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

不确定是什么问题。从我的本地机器（即不是虚拟服务器），这是为“localhost”返回的……

$ host localhost
localhost has address 127.0.0.1
localhost has IPv6 address ::1

我正在使用 MySql 8。我想加快对表格的搜索速度

select * FROM dirctory_coop where name like '%mystr%';

有人提到使用全文搜索 - https://dev.mysql.com/doc/refman/8.0/en/fulltext-search.html。但是，我对此有疑问。下面是我的表...

mysql> desc directory_coop;
+----------+--------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default | Extra          |
+----------+--------------+------+-----+---------+----------------+
| id       | int          | NO   | PRI | NULL    | auto_increment |
| name     | varchar(250) | NO   |     | NULL    |                |
| enabled  | tinyint(1)   | NO   |     | NULL    |                |
| phone_id | int          | YES  | MUL | NULL    |                |
| email_id | int          | YES  | MUL | NULL    |                |
| web_site | longtext     | NO   |     | NULL    |                |
+----------+--------------+------+-----+---------+----------------+

我尝试了以下语句来更新我的列定义，但出现以下错误...

mysql> alter table directory_coop modify column name FULLTEXT;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FULLTEXT' at line 1
mysql> alter table directory_coop modify name FULLTEXT;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FULLTEXT' at line 1

我还需要做什么来启用全文搜索应该提供的性能优化？

我在 Mac 10.13.6 上。我重新启动了我的机器，但通常在重新启动时启动的 Postgres 没有运行。可悲的是，我不记得我是如何或在哪里安装它的。我能够找到“pg_ctl”的实例

sudo find / -name "pg_ctl"

返回

/usr/local/bin/pg_ctl
/usr/local/Cellar/postgresql/9.6.1/bin/pg_ctl
/usr/local/Cellar/postgresql/9.5.0/bin/pg_ctl
/usr/local/Cellar/postgresql/9.6.15/bin/pg_ctl
/usr/local/Cellar/[email protected]/9.6.15/bin/pg_ctl
/usr/local/Cellar/libpq/11.5/bin/pg_ctl
/usr/local/Cellar/[email protected]/9.5.19/bin/pg_ctl

但是，我需要告诉它一个数据目录才能启动它，而且我不知道在服务器关闭时如何知道数据目录。我应该寻找什么样的文件来确定数据目录在哪里？

我正在使用 Postgres 9.5。我将运行大量此类查询

SELECT * 
FROM mydb_article 
where regexp_replace(url, '\?.*$', '') = :url_wo_query_string

所以我想我需要在我的表上为该操作创建一个功能索引。所以我试过了

mydb=> CREATE INDEX my_article_idx ON mydb_article regexp_replace(url, '\?.*$', '');
ERROR:  syntax error at or near "regexp_replace"
LINE 1: CREATE INDEX my_article_idx ON mydb_article regexp_repl...

我的问题是，我应该使用的最合适的索引类型是什么？如果是上述类型的索引，我还需要做些什么才能使事情正常进行？

我正在使用 Postgres 9.5。我有一个表，其中有一列记录 URL。有时 URL 有查询字符串，有时没有。我想提取 URL，减去任何查询字符串，所以我想出了：

select substring(url, 0, case position('?' in url) when 0 then length(url)+1 else position('?' in url) end) 
from article;

这似乎有点罗嗦，我想知道是否有更简洁的方法来做到这一点。我的表格列是类型TEXT。

我刚刚在 CentOS 7 上安装了 Postgres 9.6。但是当我运行

[root@server tmp]# /usr/bin/psql --version
psql (PostgreSQL) 9.2.23

它似乎仍然指向我的旧版本。如何在不损害新版本的情况下卸载旧版本（或找出它的名称）？

我刚刚在 CentOS 7 上安装了 Postgres 9.2。我想为我的所有用户保留对等身份验证，除了一个我希望能够通过任何用户帐户通过“psql”访问的用户。我尝试将 /var/lib/pgsql/data/pg_hba.conf 编辑为这个

local   all             all                                     ident
local   all             scale                                   md5

但我仍然无法使用用户“scale”登录，除非我在该用户的帐户中。我需要做什么才能只为这个用户设置 md5 身份验证？

我在 Debian Linux 上使用 Postgres 9.4。我用一个用户创建了一个数据库，cindex可以访问该数据库。然而，当我尝试在命令行登录时，我什至没有提示输入密码：

myuser@myuserserver:~ $ psql -Ucindex cindex
psql: FATAL:  Peer authentication failed for user "cindex"

我还需要做什么才能启用用户？下面你可以看到我已经设置的权限：

postgres@myuserserver:~$ psql
psql (9.4.13)
Type "help" for help.

postgres=# GRANT SELECT ON ALL TABLES IN SCHEMA cindex TO cindex;
GRANT
postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 cindex    | postgres | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 | =T/postgres          +
           |          |          |             |             | postgres=CTc/postgres+
           |          |          |             |             | cindex=c/postgres
 postgres  | postgres | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 |
 template0 | postgres | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(4 rows)

postgres=# \du
                             List of roles
 Role name |                   Attributes                   | Member of
-----------+------------------------------------------------+-----------
 cindex    |                                                | {}
 postgres  | Superuser, Create role, Create DB, Replication | {}

使用 Postgres 9.5。使用正则表达式查找匹配行时遇到一些问题。我试过了

select * FROM my_object_times t where t.name ~ '/\(.*\)\s*$/'
...
(0 rows)

没有返回任何行，但是这个查询

select * FROM my_object_times t where t.name like '%(%)';

返回大量行。我怎么弄乱了上面的正则表达式？

我在 Ubuntu 14.04 上使用 Postgres 9.5.4。我的 Postgres 数据位于一个单独的磁盘分区上，该分区已满。

myuser@myproject:~$ df -h /mnt/volume-nyc1-01/
Filesystem Size Used Avail Use% Mounted on
/dev/sda 99G 93G 413M 100% /mnt/volume-nyc1-01

我想从我的分区中删除一些数据，但这变得具有挑战性。特别是，当我运行一些查询时，我得到这样的结果

myproject_production=> select count(*) FROM my_object_times rt1, my_object_times rt2 where rt1.my_object_id = rt2.my_object_id and rt1.name = rt2.name and rt1.time_in_ms = rt2.time_in_ms and rt1.id > rt2.id;;
ERROR:  could not write block 52782 of temporary file: No space left on device

我想释放一些临时空间，以便我可以运行查询并确定我需要删除哪些数据。我在另一个分区上有一些其他可用空间。如何将我的 pgsql_tmp 变量指向那里以便我可以运行我需要的查询？

编辑：

由于符号链接选项似乎是侵入性最小的，我试了一下，设置这样的东西

myuser@myproject:~$ sudo ls -al /mnt/volume-nyc1-01/postgresql/9.5/main/base/pgsql_tmp
lrwxrwxrwx 1 root root 14 Apr 10 18:01 /mnt/volume-nyc1-01/postgresql/9.5/main/base/pgsql_tmp -> /opt/pgsql_tmp
myuser@myproject:~$ cd /opt
myuser@myproject:/opt$ df -h /
Filesystem                                              Size  Used Avail Use% Mounted on
/dev/disk/by-uuid/050e1e34-39e6-4072-a03e-ae0bf90ba13a   40G   24G   15G  62% /

您可以看到我指向的分区上有超过 16GB 的可用空间，但仍然出现错误

ERROR:  could not write block 1862514 of temporary file: No space left on device

我正在使用 Postgres 9.5。我想搜索我的姓名列不包含空格的行。不过，我对如何为您定义空间有点模糊。我以为这只是我键盘上的空格键，所以我跑了：

.... where name not like '% %';

但后来我得到了一些这样的结果：

 | JASON FALKNER

这对我来说确实像是一个空间，但可能还有其他一些事情正在发生。有没有更好的方法可以扫描我的名称列不包含空格的行？

使用正则表达式，not (name ~ '\s')仍然返回看起来像有空格的列。

使用：

select cast(name as bytea) ... where name not like like '% %';

回来：

\x4a41534f4ec2a0424c414b45

但是，我仍然有点不清楚如何使用这些数据来找出如何从我的结果中筛选空间。

我试过where not (name ~ '[[:space:]]')'了，它返回“JASON BLAKE”，上面的字节序列相同，\x4a41534f4ec2a0424c414b45.

我正在使用 Postgres 9.5。我有一name列类型为“字符变化”的列。我想知道如何选择列以数字开头的行，然后是空格，然后是任何其他数据。

所以我想要列看起来像的行：

12 DAVE
1 abcDEF
12345 LAST

但不是当它看起来像：

MYCOLUMN
1TEXT
=END

我正在使用 Postgres 9.5。我想为一系列连接的字符串编写一个表达式顺序，但我不确定这是否可能或如何去做。我拼凑了下面

myproject=> SELECT  "my_objects".* 
            FROM "my_objects" 
              left join addresses on my_objects.address_id = addresses.id 
            WHERE (my_objects.name ILIKE '%my_object%' 
              AND EXISTS (SELECT * 
                          FROM my_object_times 
                          WHERE my_object_times.my_object_id = my_objects.id)) 
            order by lower(addresses.city) || "," || addresses.state_id; 

ERROR:  column "," does not exist 
LINE 1: ...id = my_objects.id)) order by lower(addresses.city) || "," || add...

但是正如您所看到的，有一个抱怨语法的错误。我正在尝试做的事情是否可行，如果可以，我如何根据列的串联编写 order by 子句？

我正在使用 Postgres 9.5。我想选择一些记录，但前提是它们在另一张表中至少有一个关联。我想出了这个查询：

select distinct m.name, m.id 
from machines m, parts rt 
where m.id = rt.machine_id 
  and m.name like '%Name%';

但我认为这不是特别有效，因为如果有 50000 个关联，我认为它们都被加载然后“区分”通过，这似乎有点低效。有没有一种更有效的方法来检查加载记录，但前提是它有一个或多个关联？

我在 Mac Sierra 上使用 Postgres 9.5。我想从我的本地数据库中导出一些记录并将它们导入到 Linux 机器上的数据库中（也运行 PostGres 9.5）。我正在使用此命令从本地机器导出数据……

localhost:myproject davea$ pg_dump -U myproject mydb -a -t table1 -t table2 -t table3 > /tmp/pgdata.sql

数据以一系列复制命令导出。有没有办法导出表数据，使文件有一堆“INSERT ... ON CONFLICT DO NOTHING;” 陈述？原始数据库和目标数据库中有一些重复，但我不希望这会破坏非重复数据的导入。

我正在使用 Postgres 9.5。我想创建索引来进行查询

select * FROM my_object_times where name like ‘Dave %’;

和

select * FROM my_object_times where name like '% LastName';

奔驰。所以我创造了……

CREATE INDEX my_object_times_name_idx ON my_object_times (name text_pattern_ops);
CREATE INDEX my_object_times_rev_name_idx ON my_object_times (reverse(name) text_pattern_ops); 

这在第一种情况下工作正常，我可以看到我的索引被使用，但在第二种情况下，“Explain”没有显示我的索引被使用......

my_db=> explain select * FROM my_object_times where name like '% LastName';
                            QUERY PLAN                            
------------------------------------------------------------------
 Seq Scan on my_object_times  (cost=0.00..136594.69 rows=51 width=221)
   Filter: ((name)::text ~~ '% LastName'::text)

在第二种情况下如何使用我的索引？

我在 Ubuntu 14.04 上使用 Postgres 9.5。我想创建一个角色（用户），该角色（用户）具有与系统中现有角色相同的所有权限和对数据库的访问权限。我想我可以通过创建该用户然后将另一个角色授予他来实现这一目标......

postgres=# create role myuser;
CREATE ROLE
postgres=# GRANT rails TO myuser;
GRANT ROLE

但是当我登录到该用户时，结果发现我什至没有登录权限。

myuser@myproject:~$ psql mydb_production
psql: FATAL:  role "myuser" is not permitted to log in

将角色克隆到另一个用户（例如复制权限）的正确方法是什么？