Dave's questions

Estou fazendo login no meu banco de dados Postgres 12 (executando no Windows 10) usando um alias, que resolve para

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

Eu gostaria de encontrar uma maneira de informar ao Postgres qual esquema usar por padrão ao fazer login, seja na linha de comando ou de outra maneira. Eu tentei definir o "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)

Mas assim que eu sair e entrar novamente, a configuração do caminho de pesquisa desapareceu

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)

Como posso preservá-lo ou pelo menos dizer ao psql qual esquema usar ao efetuar login?

Estou usando o Postgres 14 no CentOS 7. Gostaria de executar um comando de cópia de um script bash. Eu tentei primeiro executar o comando na linha de comando

$ 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...

Qual é a maneira certa de escapar do delimitador? Eu tentei mudar minhas aspas simples e duplas, mas sem sucesso.

Estou usando o Postgres 14 no CentOS 7. Gostaria de exportar os dados de uma única tabela, mas não tenho privilégios de superusuário. Sem surpresa, recebo esse erro

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.

Qual é a maneira correta de exportar dados da tabela (para uso com importação posterior) para um usuário que não possui privs de superusuário?

Executando o MySql 5.7.33 em uma instância local do VirtualBox. Eu encaminhei a porta 3306 do servidor virtual para ser acessível localmente. No meu host virtual, tenho isso no meu arquivo /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

Da minha máquina local, posso me conectar à instância do MySql usando

mysql -h 127.0.0.1 -u my_user my_db -p

Mas uma vez que mudo para “localhost”, não consigo mais me conectar

$ 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)

Não tenho certeza qual é o problema. Da minha máquina local (ou seja, não do servidor virtual), isso é retornado para “localhost”…

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

Estou usando o MySql 8. Gostaria de agilizar as pesquisas na minha tabela do formulário

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

e alguém mencionou o uso de pesquisas de texto completo -- https://dev.mysql.com/doc/refman/8.0/en/fulltext-search.html . No entanto, estou tendo um problema com isso. Segue minha tabela...

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    |                |
+----------+--------------+------+-----+---------+----------------+

Eu tentei as instruções abaixo para atualizar minha definição de coluna, mas estou recebendo os seguintes erros ...

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

O que mais preciso fazer para habilitar as otimizações de desempenho que a pesquisa de texto completo supostamente fornece?

Estou no Mac 10.13.6. Reiniciei minha máquina e o Postgres, que normalmente inicia na reinicialização, não estava funcionando. Infelizmente, não me lembro como ou onde o instalei. Consigo localizar instâncias de "pg_ctl"

sudo find / -name "pg_ctl"

retorna

/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/postgresql@9.6/9.6.15/bin/pg_ctl
/usr/local/Cellar/libpq/11.5/bin/pg_ctl
/usr/local/Cellar/postgresql@9.5/9.5.19/bin/pg_ctl

No entanto, preciso informar um diretório de dados para que ele seja iniciado e não consigo descobrir como saber o diretório de dados enquanto o servidor está inativo. Que tipo de arquivos devo procurar para descobrir onde está o diretório de dados?

Estou usando o Postgres 9.5. Vou executar muito esse tipo de consulta

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

então imaginei que precisaria criar um índice funcional na minha tabela para essa operação. Então eu tentei

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...

Minha pergunta é: qual é o tipo de índice mais apropriado que devo usar e, se for o acima, o que mais preciso fazer para que as coisas funcionem corretamente?

Estou usando o Postgres 9.5. Eu tenho uma tabela com uma coluna que registra URLs. Às vezes, a URL tem uma string de consulta e às vezes não. Eu gostaria de extrair o URL, menos qualquer string de consulta, então criei:

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

Isso parece um pouco prolixo e eu queria saber se há uma maneira mais concisa de fazer isso. Minha coluna da tabela é do tipo TEXT.

Acabei de instalar o Postgres 9.6 no CentOS 7. No entanto, quando executo

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

Ainda parece estar apontando para a minha versão antiga. Como faço para desinstalar a versão antiga (ou descobrir como é chamada) sem prejudicar a nova versão?

Acabei de instalar o Postgres 9.2 no CentOS 7. Quero manter a autenticação de pares para todos os meus usuários, exceto um, que gostaria de poder acessar via "psql" através de qualquer conta de usuário. Eu tentei editar meu /var/lib/pgsql/data/pg_hba.conf para ser isso

local   all             all                                     ident
local   all             scale                                   md5

mas ainda não consigo fazer login com o usuário "scale" a menos que esteja na conta desse usuário. O que preciso fazer para configurar a autenticação md5 apenas para este usuário?

Estou usando o Postgres 9.4 no Debian Linux. Criei um banco de dados com um usuário, cindexcom acesso ao banco de dados. No entanto, quando tento fazer login na linha de comando, nem sequer sou solicitado a fornecer uma senha:

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

O que mais preciso fazer para habilitar o usuário? Abaixo você pode ver os privilégios que já configurei:

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 | {}

Usando Postgres 9.5. Tendo alguns problemas ao usar expressões regulares para encontrar linhas correspondentes. eu tentei

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

que não retornou nenhuma linha, mas esta consulta

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

retorna toneladas de linhas. Como estou atrapalhando a expressão regular acima?

Estou usando o Postgres 9.5.4 no Ubuntu 14.04. Tenho meus dados do Postgres em uma partição de disco separada, que está ficando bastante cheia.

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

Quero excluir alguns dados da minha partição, mas isso se tornou um desafio. Em particular, quando executo algumas consultas, recebo resultados como este

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

Quero liberar algum espaço temporário para poder executar consultas e identificar quais dados preciso excluir. Eu tenho algum outro espaço livre em outra partição. Como aponto minha variável pgsql_tmp para que eu possa executar as consultas de que preciso?

Editar:

Como a opção de link simbólico parecia ser a menos invasiva, dei uma chance, configurando coisas assim

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% /

Você pode ver que tenho mais de 16 GB disponíveis na partição para a qual apontei, mas ainda recebo os erros

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

Estou usando o Postgres 9.5. Desejo pesquisar linhas nas quais minha coluna de nome não contém um espaço. Estou um pouco confuso sobre como definir um espaço para você, no entanto. Achei que seria apenas a barra de espaço no meu teclado, então corri:

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

mas depois obtive alguns resultados como este:

 | JASON FALKNER

Isso com certeza parece um espaço para mim, mas provavelmente há algumas outras coisas acontecendo. Existe uma maneira melhor de procurar linhas nas quais minha coluna de nome não contém um espaço?

Usando um regexp, not (name ~ '\s')ainda retornava colunas que pareciam ter um espaço.

Usando:

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

retornou:

\x4a41534f4ec2a0424c414b45

No entanto, ainda não tenho certeza de como uso esses dados para descobrir como filtrar os espaços dos meus resultados.

Eu tentei where not (name ~ '[[:space:]]')'e está retornando "JASON BLAKE" com a mesma sequência de bytes acima, \x4a41534f4ec2a0424c414b45.

Estou usando o Postgres 9.5. Eu tenho uma namecoluna do tipo "variação de caracteres". Gostaria de saber como selecionar linhas onde a coluna começa com um número, seguido por um espaço, seguido por quaisquer outros dados.

Então, eu gostaria de linhas em que a coluna se parecesse com:

12 DAVE
1 abcDEF
12345 LAST

Mas não quando parece:

MYCOLUMN
1TEXT
=END

Estou usando o Postgres 9.5. Quero escrever uma ordem por expressão para uma série de strings concatenadas, mas não tenho certeza se isso é possível ou como fazê-lo. Eu montei o abaixo

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...

Mas como você pode ver, há um erro reclamando sobre a sintaxe. O que estou tentando fazer é possível e, em caso afirmativo, como escrevo uma ordem por cláusula com base em uma concatenação de colunas?

Estou usando o Postgres 9.5. Quero selecionar alguns registros, mas somente se eles tiverem pelo menos uma associação em outra tabela. Eu cheguei a esta consulta:

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

mas não acho que isso seja particularmente eficiente, pois se houver 50.000 associações, acho que todas estão carregadas e depois "distintas", o que parece um pouco ineficiente. Existe uma maneira mais eficiente de verificar o carregamento de um registro, mas apenas se ele tiver uma ou mais associações?

Estou usando o Postgres 9.5 no Mac Sierra. Desejo exportar alguns registros do meu banco de dados local e importá-los para um banco de dados em uma máquina Linux (também executando o PostGres 9.5). Estou usando este comando para exportar dados da minha máquina local…

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

Os dados são exportados em uma série de comandos de cópia. Existe uma maneira de exportar os dados da tabela para que o arquivo tenha um monte de “INSERT ... ON CONFLICT DO NOTHING;” declarações? Existem algumas duplicatas no banco de dados de origem e no banco de dados de destino, mas não quero que isso atrapalhe a importação dos dados não duplicados.

Estou usando Postgre 9.5. Eu quero criar índices que farão consultas como

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

e

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

corra rápido. Então eu criei…

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); 

Isso funciona bem para o primeiro caso, em que posso ver meu índice sendo usado, mas no segundo caso, “Explicar” não mostra meu índice sendo utilizado…

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)

Como faço para que meu índice seja usado no segundo caso?

Estou usando o Postgres 9.5 no Ubuntu 14.04. Gostaria de criar uma função (usuário) que tenha todos os mesmos privilégios e acesso a um banco de dados que uma função existente em meu sistema. Eu pensei que poderia fazer isso criando aquele usuário e, em seguida, concedendo a outra função a ele…

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

mas quando eu faço login nesse usuário, descobri que nem tenho privilégios de login.

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

Qual é a maneira correta de clonar uma função para outro usuário (por exemplo, copiar os privilégios)?