Perguntas[postgresql](coding)

Estou tentando usar a função jsonb_path_exists no PostgreSQL para verificar se um campo JSONB (com lista de objetos) contém um objeto onde uma chave específica tem um valor que pertence a uma determinada lista de valores.

Uma condição simples como esta funciona bem (comparando com um único valor):

SELECT t.id
FROM some_scheme.some_table as t
WHERE jsonb_path_exists(t.jsonb_field, '$[*] ? (@.id == "0e66fef9-0f59-4742-8669-74f41fc26a3f")');

No entanto, quero verificar se o campo id é um dos vários valores possíveis em vez de apenas um. Tentei a seguinte consulta usando o operador "IN":

SELECT t.id
FROM some_scheme.some_table as t
WHERE jsonb_path_exists(t.jsonb_field, '$[*] ? (@.id in ("0e66fef9-0f59-4741-9669-74f41fc26a3f", "1e66fef9-0f59-4741-9669-74f41fc26a3f"))');

e

WHERE jsonb_path_exists(t.jsonb_field, '$[*] ? (@.id in ["0e66fef9-0f59-4741-9669-74f41fc26a3f", "1e66fef9-0f59-4741-9669-74f41fc26a3f"])');

Mas isso resulta em um erro de sintaxe:

ERROR: syntax error in jsonpath near " " SQL state: 42601

Gostaria de saber se o PostgreSQL suporta a verificação de associação em uma lista usando "IN" dentro do JSONPath, ou se há uma maneira alternativa de fazer isso.

Estou tentando especificamente evitar a conexão com o pooler/Supavisor e, em vez disso, quero usar uma conexão direta.

Supabase me dá um nome de host como:db.MY_ID.supabase.co

Não tem IP ipv4, mas consigo encontrar o recorde AAAA:

% dig AAAA db.MY_ID.supabase.co

; <<>> DiG 9.10.6 <<>> AAAA db.MY_ID.supabase.co
;; global options: +cmd
;; Got answer:
;; ->>HEADER<<- opcode: QUERY, status: NOERROR, id: 59725
;; flags: qr rd ra; QUERY: 1, ANSWER: 1, AUTHORITY: 0, ADDITIONAL: 1

;; OPT PSEUDOSECTION:
; EDNS: version: 0, flags:; udp: 512
;; QUESTION SECTION:
;db.MY_ID.supabase.co. IN   AAAA

;; ANSWER SECTION:
db.MY_ID.supabase.co. 30    IN AAAA dead:beef:IPV6:IPV6::IPV6:IPV6:dead:beef

;; Query time: 44 msec
;; SERVER: 192.168.20.1#53(192.168.20.1)
;; WHEN: Sat Mar 08 09:49:28 AEDT 2025
;; MSG SIZE  rcvd: 92

Mas não consigo fazer o psql se conectar a ele nem por domínio nem por IP:

% psql "postgresql://db.MY_ID.supabase.co:5432/postgres?sslmode=require" -U myuser
psql: error: could not translate host name "db.MY_ID.supabase.co" to address: nodename nor servname provided, or not known

% psql "postgresql://[dead:beef:IPV6:IPV6::IPV6:IPV6:dead:beef]:5432/postgres?sslmode=require" -U myuser
psql: error: connection to server at "dead:beef:IPV6:IPV6::IPV6:IPV6:dead:beef", port 5432 failed: No route to host
    Is the server running on that host and accepting TCP/IP connections?

O que estou fazendo errado? Há algo errado aqui no nível do Postgres que eu possa consertar?

Atualização: grawity está correto. Meu roteador não tinha o IPv6 habilitado. Tenho usado endereços IPv6 que apontam para o host local para fins de desenvolvimento, então presumi que tinha o IPv6 funcionando. Mas não o tinha funcionando na Internet, apenas no host local.

Tentei criar um imutável CONCAT(). Para isso, tentei usar as seguintes consultas SQL.

Opção 1

CREATE FUNCTION immutable_concat(VARIADIC text[])
  RETURNS text
  LANGUAGE internal IMMUTABLE PARALLEL SAFE AS 'text_concat';

Opção 2

CREATE FUNCTION immutable_concat(VARIADIC text[])
  RETURNS text
  LANGUAGE sql IMMUTABLE PARALLEL SAFE
  RETURN array_to_string($1, '');

Como resultado, obtenho uma função que funciona bem com texto. Mas como posso criar uma função similar que pegue um array contendo não apenas elementos de texto?

CONCAT()nos permite executar a seguinte consulta SQL:

SELECT concat(CURRENT_DATE, true, false, 1, 'text');

Como resultado, obteremos a seguinte string:

2025-03-07tf1text

Idealmente, eu gostaria de criar uma função que pudesse receber diferentes elementos. Quero dizer o seguinte:

SELECT immutable_concat(CURRENT_DATE, true, false, 1, 'text');

Um processo está tentando ALTERAR uma tabela (para definir o valor padrão da coluna) em um fluxo de trabalho. Ele está sendo bloqueado devido ao processo de auto-vacuum do postgres não liberar um bloqueio (pode estar segurando um ShareUpdateExclusiveLock).

Os seguintes logs são vistos no postgres -

LOG:  automatic vacuum to prevent wraparound of table "my_db.public.event_spl_1740300900": index scans: 0
 pages: 0 removed, 16591962 remain, 0 skipped due to pins, 16300723 skipped frozen
 tuples: 0 removed, 1244397113 remain, 0 are dead but not yet removable, oldest xmin: 1410720248
 buffer usage: 2361602 hits, 6947399 misses, 291262 dirtied
 avg read rate: 3.879 MB/s, avg write rate: 0.163 MB/s
 system usage: CPU: user: 83.69 s, system: 135.74 s, elapsed: 13990.91 s

LOG:  process 113323 acquired AccessExclusiveLock on relation 181282787 of database 180669275 after 5823314.224 ms

Há evidências suficientes aqui para sugerir que o auto-vácuo pode ser o culpado? Se não, qual poderia ser o outro processo de bloqueio?

Ao migrar o banco de dados do Oracle para o PostgreSQL, me deparei com a "Tabela Temporária Global" que preciso migrar de forma equivalente ao PostgreSQL.

O documento diz

Opcionalmente, GLOBAL ou LOCAL podem ser escritos antes de TEMPORARY ou TEMP. Isso atualmente não faz diferença no PostgreSQL e está obsoleto

Tentativa 1: Tabela temporária global do PostgreSQL v2.0

CREATE EXTENSION pgtt;

Erro:

SQL Error [0A000]: ERROR: extension "pgtt" is not available
Detail: Could not open extension control file "C:/Program 
Files/PostgreSQL/17/share/extension/pgtt.control": No such file or directory.
Hint: The extension must first be installed on the system where PostgreSQL is running.

Tentativa 2: Como aproveitar a tabela temporária local para a tabela temporária global no EDB Postgres

Existem vários objetos de banco de dados usados ​​para obter a funcionalidade de tabela temporária global no PostgreSQL.

1. UNLOGGED TABLE
2. CREATE VIEW
3. CREATE FUNCTION
4. CREATE TRIGGER

Pensamentos: Preocupado com o desempenho com essa abordagem.

Qual será o equivalente no PostgreSQL?

Para a tabela de exemplo a seguir, desejo definir classificações para cada grupo, ordenadas por data_de_início_desc, mas quero que o administrador seja sempre definido como classificação 0

Resultado esperado:

SELECT name, start_date, group_no, 
RANK() OVER (PARTITION BY group_no ORDER by start_date DESC) as rnk
FROM Table_Name
ORDER BY rnk;

A consulta acima me dá o seguinte resultado. Existe uma maneira de adicionar uma condição na função rank para definir admin como 0 por padrão e aplicar a função rank a todo o resto.

Também tentei usar a condição case:

SELECT name, start_date, group_no, 
CASE WHEN name = 'Admin' THEN 0 
ELSE RANK() OVER (PARTITION BY group_no ORDER by start_date DESC) 
END as rnk
FROM Table_Name
ORDER BY rnk;

Neste caso, Admin é definido como 0, mas para as linhas subsequentes a classificação começa em 2 em vez de 1.

A outra opção que tenho é usar o seguinte, que funciona, mas existe uma abordagem melhor para isso?

SELECT name, start_date, group_no, 
0 as rnk
FROM Table_Name
WHERE name = 'Admin'
UNION 
SELECT name, start_date, group_no, 
RANK() OVER (PARTITION BY group_no ORDER by start_date DESC) as rnk
FROM Table_Name
WHERE name <> 'Admin'

Gostaria de ler alguns arquivos rtf que recebi de uma fonte externa em uma tabela com Postgres. Estou usando a pg_stat_file()função para verificar se o arquivo existe ou não e pretendo ler seu conteúdo com pg_read_file(). Se o nome do arquivo contiver um trema, a função falha.

SELECT pg_stat_file('C:\datafolder\ctköln.rtf', true);

ERROR:  could not open file "C:\datafolder\ctköln.rtf" for reading: No such file or directory 
SQL state: 58P01

Existe uma opção para ler o arquivo sem alterar seu nome? O sistema subjacente é um servidor Windows.

O conteúdo do arquivo será eventualmente lido em uma tabela.

Eu literalmente tentei todas as 20 principais soluções do Google e do stackoverflow, mas não consigo fazer meu postgres 17 instalar uma extensão chamada 'pgvector'. Quando eu digito 'make' ou 'cl', eu posso ver que eles são adicionados ao meu caminho e a entrada do terminal é capaz de abrir esses arquivos exe. Eu até instalei o WSL como sugerido pelo AI. Eu clonei o repositório para pgvector na minha pasta de documentos, e quando eu chego lá usando o comando Powershell (admin) e executo o comando make, eu recebo esse erro

C:\Usuários\rohit\Documentos\Codificação\pgvector>make cl -Wall -O2 /wd4018 /wd4244 /wd4273 /wd4101 /wd4102 /wd4090 /wd4267 -march=native -ftree-vectorize -fassociative-math -fno-signed-zeros -fno-trapping-math -I. -I./ -IC:/PROGRA~1/PostgreSQL/17/include/server -IC:/PROGRA~1/PostgreSQL/17/include/internal /DWIN32 /DWINDOWS /D__WINDOWS__ /D__WIN32__ /D_CRT_SECURE_NO_DEPRECATE /D_CRT_NONSTDC_NO_DEPRECATE -IC:/PROGRA~1/PostgreSQL/17/include/server/port/win32 -DWIN32_STACK_RLIMIT=4194304 -c -o src/bitutils.o src/bitutils.c Microsoft (R) C/C++ Optimizing Compiler Versão 19.29.30157 para x86 Copyright (C) Microsoft Corporation. Todos os direitos reservados.

trapping-math' bitutils.c C:\Program Files\PostgreSQL\17\include\server\ch(75): erro fatal C1083: Não é possível abrir o arquivo include: 'libintl.h': Nenhum arquivo ou diretório make: *** [: src/bitutils.o] Erro 2

Alguém tem alguma ideia do que eu posso fazer? Eu até tentei usar o método docker image, e mesmo com essa imagem docker em execução, quando eu executo o comando make ainda recebo um erro parecido.

Também instalei o código do VS Studio com as ferramentas C++ instaladas no meu sistema.

Eu tentei usar este comando também:

"C:\Arquivos de programas\Microsoft Visual Studio\2022\BuildTools\VC\Auxiliary\Build\vcvarsall.bat" x64

mas meu sistema não tem essas pastas VC e posteriores. Devo reinstalar meu postgres 17 ou reinstalar o código do vs studio?

Acredito que todos os meus caminhos foram corrigidos, pois não há mais erros como cl.exe não encontrado ou make não encontrado.

Se eu chegar ao psql e executar

ERRO: a extensão "pgvector" não está disponível DETALHE: Não foi possível abrir o arquivo de controle de extensão "C:/Program Files/PostgreSQL/17/share/extension/pgvector.control": Nenhum arquivo ou diretório. DICA: A extensão deve ser instalada primeiro no sistema onde o PostgreSQL está sendo executado.

A parte estranha é que consigo ver meu sistema tendo esse arquivo exato chamado ch no mesmo diretório que o erro acima sugere. Vi no meu antivírus se havia um erro de permissão, mas não havia nenhum.

Seguindo a documentação exata do repositório, consegui executar o comando

cd "C:\Program Files\Microsoft Visual Studio\2022\Community\VC\Auxiliary\Build" e recebo uma mensagem [vcvarsall.bat] Ambiente inicializado para: 'x64'

Depois disso, quando clono um novo repositório para o pgvector, recebo este novo erro:

C:\Usuários\rohit\AppData\Local\Temp\pgvector>nmake /F Makefile.win instalar

Microsoft (R) Program Maintenance Utility Versão 14.43.34808.0 Copyright (C) Microsoft Corporation. Todos os direitos reservados.

    copy vector.dll "C:\Program Files\PostgreSQL\17\lib" Access is denied.
    0 file(s) copied. NMAKE : fatal error U1077: 'copy vector.dll "C:\Program Files\PostgreSQL\17\lib"' : return code '0x1' Stop.

Observe que executei esses comandos no Powershell Executar como administrador e até mesmo no terminal de opções de inicialização do MS VS Code.

Como você pode ver na saída do terminal após executar o nmake, ele mostra que ele foi instalado. Ele até me mostrou instalando centenas de arquivos chamados pgvector, mas quando eu reinicio meu serviço postgres e vejo se meu postgres tem alguma extensão chamada pgvector ele apenas diz cant find anything

Seguindo as instruções, também consegui executar o comando nmake após clonar o repositório pgvector , e ele até mostra que o instalou corretamente (estou supondo?), mas ainda assim, após reiniciar o serviço postgres ou todo o sistema, dentro do psql não consigo usar essa extensão.

C:\pgvector>nmake /F Makefile.win install Microsoft (R) Program Maintenance Utility Versão 14.43.34808.0 Copyright (C) Microsoft Corporation. Todos os direitos reservados.

   cl /nologo /I"C:\Program Files\PostgreSQL\17\include\server\port\win32_msvc" /I"C:\Program

Arquivos\PostgreSQL\17\include\server\port\win32" /I"C:\Arquivos de programas\PostgreSQL\17\include\server" /I"C:\Arquivos de programas\PostgreSQL\17\include" /O2 /fp:fast /c src\hnsw.c /Fosrc\hnsw.obj hnsw.c C:\Arquivos de programas\PostgreSQL\17\include\server\access/tupmacs.h(65): erro C2196: valor do caso '4' já usado C:\Arquivos de programas\PostgreSQL\17\include\server\access/tupmacs.h(197): erro C2196: valor do caso '4' já usado NMAKE: erro fatal U1077: 'cl /nologo /I"C:\Program Arquivos\PostgreSQL\17\include\servidor\porta\win32_msvc" /I"C:\Arquivos de Programas\PostgreSQL\17\include\servidor\porta\win32" /I"C:\Arquivos de Programas\PostgreSQL\17\include\servidor" /I"C:\Arquivos de Programas\PostgreSQL\17\include" /O2 /fp:fast /c src\hnsw.c /Fosrc\hnsw.obj' : código de retorno '0x2' Parar.

Além disso, é realmente tão difícil instalar uma extensão simples do Postgres em um sistema Windows? Todos os sistemas Unix ou Mac podem usar qualquer gerenciador de pacotes, mas os usuários do Windows são forçados a instalar pelo VS Studio e ainda estou preso nisso há 2 dias inteiros.

Acabei de executar o instalador postgresql_17.3_1_windows_x64 do site deles. Concordo com a janela de administração, e então esse erro aparece. Talvez seja por causa do nome de usuário Win - 10 na minha máquina local. De qualquer forma, é isso que recebo:

Isto é o que recebo em [System.Globalization.CultureInfo]::InstalledUICulture:

Nome do LCD DisplayName

1033 en-US Inglês (Estados Unidos)

Vamos fingir que eu tenho 1.000.000 de linhas para inserir dados e não me importo com perda de dados.

Quão mais rápido seria 1 confirmação após 1.000.000 de linhas em vez de 1 confirmação a cada 100.000 linhas?

A mesma pergunta para atualizações e exclusões.

Como posso alterar o tamanho do commit? Ou seja, não confirmar a transação até ter processado XXX linhas?