Existe uma maneira de construir um jsonb com apenas um valor numérico?
Por exemplo,
SELECT pg_typeof(('{"a":1}'::jsonb) -> 'a');
indica que ('{"a":1}'::jsonb) -> 'a'possui o tipo jsonb e contém apenas um valor numérico 1.
Mas como crio um jsonb diretamente1 nele sem construir e destruir um objeto?
A conversão direta de tipo não parece funcionar:
# SELECT 1::jsonb;
ERROR: cannot cast type integer to jsonb
LINE 1: SELECT 1::jsonb;
Não costumo ver a cláusula ON usada com as junções LATERAL (PostgreSQL 11+). Por exemplo, a documentação oficial tem este exemplo:
Um exemplo trivial de LATERAL é
SELECT * FROM foo, LATERAL (SELECT * FROM bar WHERE bar.id = foo.bar_id) ss;
Isso não é especialmente útil, pois tem exatamente o mesmo resultado que o mais convencional
SELECT * FROM foo, bar WHERE bar.id = foo.bar_id;
A partir do exemplo, a junção convencional equivalente tem uma cláusula ON (escrita usando WHERE, WHERE bar.id = foo.bar_id), mas para a LATERAL JOIN, a condição de junção parece ser "internalizada".
Sinto que este exemplo não está sozinho. Vejo muitos usos de junção lateral sem uma cláusula ON, mas não vejo muito uso com uma cláusula ON. Conceitualmente, não está claro se uma cláusula ON é necessária para uma junção lateral, pois cada conjunto de valores dependentes é associado apenas às linhas das quais eles dependiam. Usando o próximo exemplo da mesma documentação para ilustrar:
Por exemplo, supondo que vertices(polygon) retorne o conjunto de vértices de um polígono, poderíamos identificar vértices próximos de polígonos armazenados em uma tabela com:
SELECT p1.id, p2.id, v1, v2
FROM polygons p1, polygons p2,
LATERAL vertices(p1.poly) v1,
LATERAL vertices(p2.poly) v2
WHERE (v1 <-> v2) < 10 AND p1.id != p2.id;
Aqui, o conjunto de vértices gerado para cada polígono p1.polyestá associado apenas a esse polígono, não a nenhum outro polígono. Não parece ser necessário especificar a correlação entre o resultado calculado e o polígono original usando uma cláusula ON. A "condição de junta" parece estar implicada na dependência dos pilares na junta lateral.
Não consegui encontrar confirmação na documentação vinculada se uma cláusula ON é necessária ou mesmo permitida para um LATERAL JOIN. Daí esta pergunta:
Um LATERAL JOIN requer/permite uma condição de junção (uma cláusula ON)?
Esta pergunta está relacionada ao armazenamento PostgreSQL TOAST e à pergunta GIS.SE: A compactação TOAST deve ser desativada para PostGIS?
Basicamente, eu queria saber se existe alguma garantia de complexidade de tempo constante (O(1)) para o acesso aleatório dos elementos do array?
Ou seja, para obter arr[n], o número de etapas do pior caso é necessário uma constante (ou seja, O(1)) ou outra coisa ( O(log n)etc.)?
Estou perguntando porque em certos formulários de dados, como linhas PostGIS ou rasters, os dados principais são logicamente uma matriz (de coordenadas). Sabe-se que o tempo de acesso a esses dados pode ser extremamente longo, uma vez que a quantidade de dados ultrapassa um determinado limite (como 500 pontos). Uma possível razão é que os dados de tais tamanhos são transferidos para o armazenamento TOAST e potencialmente os dados podem ser compactados (por exemplo, com o mainarmazenamento). Não está claro como o PostgreSQL pode prever a localização aproximada de um elemento e ainda oferecer tempo de acesso aleatório.
O tempo de acesso para arrays na maioria das linguagens de programação é constante ( O(1)). E esse é o ponto de usar uma matriz. Só por curiosidade:
Os arrays do PostgreSQL têm tempos de acesso constantes? (quando e quando não?)
Minha impressão é que cada tabela do PostgreSQL tem um tipo composto correspondente associado a ela. Mas parece que a coluna de identidade do PostgreSQL não pode ser usada em uma CREATE TYPEdeclaração:
=> CREATE TYPE _t_type AS (id bigint GENERATED ALWAYS AS IDENTITY);
ERROR: syntax error at or near "GENERATED"
LINE 1: CREATE TYPE _t_type AS (id bigint GENERATED ALWAYS AS IDENTI...
^
, enquanto ele pode ser usado em um CREATE TABLE:
=> CREATE TABLE _t_table (id bigint GENERATED ALWAYS AS IDENTITY);
CREATE TABLE
Existe uma razão fundamental pela qual as colunas de identidade não podem ou não devem ser usadas na CREATE TYPEinstrução?
(Isso é com o PostgreSQL 14 beta1).
De acordo com a documentação do PostgreSQL no construtor ARRAY :
Por padrão, o tipo de elemento array é o tipo comum das expressões de membro, determinado usando as mesmas regras das construções UNION ou CASE (consulte a Seção 10.5). Você pode substituir isso lançando explicitamente o construtor de matriz para o tipo desejado, por exemplo :
SELECT ARRAY[1,2,22.7]::integer[];
array
----------
{1,2,23}
(1 row)
Isso funciona para converter elementos em inteiros e para tipos simples:
SELECT ARRAY[ 'name', 1]::text[];
array
----------
{name,1}
(1 row)
Mas a conversão não parece funcionar para elementos complexos, como elementos de matriz:
SELECT ARRAY[ 'name', 1, ARRAY['world']]::text[];
ERROR: malformed array literal: "name"
LINE 1: SELECT ARRAY[ 'name', 1, ARRAY['world']]::text[];
^
DETAIL: Array value must start with "{" or dimension information.
Minha pergunta é:
Existe uma maneira de fazer a conversão automática funcionar text[]?
(ou seja, faça a conversão acima funcionar sem ter que escrever SELECT ARRAY[ 'name'::text, 1::text, ARRAY['world']::text];)
Eu só estou querendo saber como escrever uma função wrapper no PostgreSQL (a partir de 12.x) para uma heterogeneousfunção VARIADIC como jsonb_build_object?
A maioria dos exemplos SQL de funções VARIADIC que posso encontrar não são heterogêneos. A parte VARIADIC é essencialmente uma matriz de elementos do mesmo tipo. Mas eu preciso escrever um wrapper para jsonb_build_objectfazer algum pós-processamento. Aqui, os tipos de parâmetros na parte variável (lista de valores-chave) não são os mesmos.
Atualmente, escrevo:
my_func(jsonb_build_object(k1, v1, k2, v2, ...))
Mas esse clichê está ficando chato...
Qual é a maneira de esforço mínimo de empacotar uma extensão do PostgreSQL em um .debarquivo para o Ubuntu (a partir de 18.04)?
Eu fiz algumas pesquisas, mas encontrei muitas sugestões diferentes (algumas desatualizadas).
Suponha que eu queira empacotar uma extensão postgres simples, como a primeira e a última funções agregadas .
Esta resposta do StackOverflow sugere que se deve primeiro criar uma estrutura de diretórios manualmente da seguinte forma:
ProgramName-Version/
ProgramName-Version/DEBIAN
ProgramName-Version/DEBIAN/control
ProgramName-Version/usr/
ProgramName-Version/usr/bin/
ProgramName-Version/usr/bin/your_script
e dpkg -b /path/to/the/ProgramName-Versiondepois de definir as permissões de arquivo corretas.
Outros sugerem usar dh-make , e o Ubuntu Packaging Guid sugere um fluxo de trabalho que usa alguma cadeia de ferramentas baseada em bzr.
Mas acho que o PostgreSQL tem sua própria infraestrutura de compilação para extensões chamadas PGXS (a partir do PostgreSQL 12.x). Achei o PGXS muito fácil de usar, porque não preciso me preocupar com a estrutura de pastas e copiarei os make installarquivos para os diretórios corretos do sistema, independentemente da versão do PostgreSQL.
Existe uma maneira de combinar a abordagem manual acima e a abordagem PGXS para criar automaticamente uma extensão do PostgreSQL como um .debarquivo?
Como expressar o maior valor de ponto flutuante (para FLOAT8) no PostgreSQL?
Preciso substituir 'Infinity'::FLOAT8durante a conversão para jsonbvalores de campo, pois json não permite valores NaN e infinito .
C++ tem numeric_limits<double>::max(). Existe algo equivalente no PostgreSQL?
(Estou usando o PostgreSQL 11/12 mais recente).
Eu tenho uma função com 5 parâmetros,
p1, e p2são obrigatóriosp3, p4e p5são opcionais e têm um DEFAULTconjuntoA função fica assim,
CREATE FUNCTION test_func(
p1 int,
p2 int,
p3 int DEFAULT 3,
p4 int DEFAULT 4,
p5 int DEFAULT 5
)
RETURNS int AS
$$
SELECT p5;
$$ LANGUAGE sql;
Eu posso invocá-lo assim,
SELECT test_func(1,2,3,4,5);
test_func
-----------
5
Mas eu gostaria de permitir que p3 e p4 fossem padronizados e invocá-lo assim,
SELECT test_func(1,2,5);
Existe uma maneira de especificar o valor para o último parâmetro opcional ( p5) sem dar valores aos anteriores ( p3, p4)? Não quero codificar valores padrão para p3, p4na chamada para test_func, porque talvez precise alterar seus valores padrão no futuro.
Existe uma maneira de expressar uma relação vazia no SQL sem realmente criar uma tabela vazia?
Para fins de teste, quero encontrar uma maneira de testar algo como:
SELECT count(*) FROM (...) my_empty_table;
onde ...gera uma tabela vazia em linha. (espero receber 0como resposta)
Isso é possível?
Tentei sem sucesso:
SELECT count(*) FROM (VALUES) t;
SELECT count(*) FROM () t;
, que dão erros de sintaxe.
e
=> SELECT count(*) FROM (SELECT) t;
count
-------
1
(1 row)
(Estou usando principalmente SQL, mas também estou curioso para saber se existe uma maneira compatível com o padrão de escrever isso).
Para a seguinte consulta
SELECT sum(c)
FROM (SELECT 1 c WHERE false) t;
Eu pensei que leva a soma de zero números e, portanto, deve retornar 0. (Da mesma forma, espero, por exemplo, uma função de agregação de matriz para retornar uma matriz vazia em elementos zero).
No entanto, está retornando a NULL(usando PostgreSQL 11).
=> SELECT sum(c) FROM (SELECT 1 c WHERE false) t;
sum
-----
(1 row)
Isso não faz sentido para mim logicamente.
Por que a soma dos números zero (para qualquer tipo numérico) não é zero? Este padrão de comportamento de retorno NULL?
Ou talvez eu esteja perdendo alguma coisa. Algum motivo para fazer a soma NULLem alguns casos de canto?
Na seguinte declaração (PostgreSQL 11):
=> SELECT c cost FROM tt;
ERROR: syntax error at or near "cost"
LINE 1: SELECT c cost FROM tt;
Eu recebo um erro. Adicionar parênteses ao redor da expressão de campo não ajuda ( SELECT (c) cost FROM tt;). Mas adicionar a ASpalavra-chave corrige isso.
=> SELECT c AS cost FROM tt;
cost
------
1
...
Eu percebo que costé uma palavra-chave, mas minha impressão é que a ASpalavra-chave é opcional.
Do ponto de vista da linguagem, por que a palavra- ASchave é necessária (ou útil) aqui? O comportamento do PostgreSQL aqui é padrão ou documentado em algum lugar?
Existem outros casos em que a ASpalavra-chave é necessária?
Estou escrevendo um script no qual preciso analisar o nome de uma tabela (em regclass). A análise (com parse_ident()) funciona até agora. No entanto, o script falha quando a tabela está no publicesquema porque o PostgreSQL (10.3) remove automaticamente o nome do esquema.
Por exemplo, se uma tabela ttestiver em um não- publicesquema ex, o valor de texto de regclassé o mesmo que o original:
=> select 'ex.tt'::regclass::text;
text
-------
ex.tt
Quando está em public, o nome do esquema é perdido:
=> select 'public.tt'::regclass::text;
text
------
tt
Existe uma maneira de desabilitar esse comportamento ou converter textsem perder o nome do esquema?
Existe um operador no PostgreSQL para testar se um elemento está em um array?
Atualmente, estou fazendo esses testes de uma maneira um pouco complexa, primeiro construindo uma matriz singleton para o elemento e, em seguida, usando o <@operador entre matrizes.
SELECT ARRAY[1] <@ ARRAY[1,2,3];
( SELECT 1 <@ ARRAY[1,2,3];não funciona).
Existe uma maneira mais sucinta/clara?
Eu queria saber se existe uma maneira estabelecida de testar se duas tabelas/relações do PostgreSQL têm a mesma estrutura. Em outras palavras, como testar se eles são compatíveis entre si no sentido de que posso realizar operações de conjunto como UNION ALLe EXCEPTneles?
Pesquisei no DBA.SE e em outros lugares e só consigo encontrar perguntas sobre descobrir se o conteúdo de duas tabelas é diferente (por exemplo, Verificar se duas tabelas têm conteúdo idêntico no PostgreSQL ), ou quando a compatibilidade é conhecida (por exemplo , Comparar duas tabelas com a mesma estrutura, mas com números de membros diferentes ). Mas estou interessado em verificar a compatibilidade da estrutura da tabela .
Estou usando o PostgreSQL 10.3, mas as formas compatíveis com o padrão certamente são preferíveis.
Como posso escrever uma consulta para verificar se uma tabela do PostgreSQL (10.x) é uma tabela temporária?
Eu olhei para a pg_classtabela do sistema, mas não consegui encontrar nenhuma coluna sobre se a relação é temporária.
SELECT * FROM pg_class WHERE False;
relname | relnamespace | reltype | reloftype | relowner | relam | relfilenode | reltablespace | relpages | reltuples | relallvisible | reltoastrelid | relhasindex | relisshared | relpersistence | relkind | relnatts | relchecks | relhasoids | relhaspkey | relhasrules | relhastriggers | relhassubclass | relrowsecurity | relforcerowsecurity | relispopulated | relreplident | relispartition | relfrozenxid | relminmxid | relacl | reloptions | relpartbound
---------+--------------+---------+-----------+----------+-------+-------------+---------------+----------+-----------+---------------+---------------+-------------+-------------+----------------+---------+----------+-----------+------------+------------+-------------+----------------+----------------+----------------+---------------------+----------------+--------------+----------------+--------------+------------+--------+------------+--------------
(0 rows)
Estou tentando escrever uma função para obter o nome da função associada a uma determinada regprocedurevariável, da seguinte forma:
CREATE OR REPLACE FUNCTION get_funcname(_fn regprocedure)
RETURNS text AS
$func$
SELECT proname::text
FROM pg_catalog.pg_proc AS p
JOIN pg_catalog.pg_namespace AS ns
ON p.pronamespace = ns.oid
WHERE p.oid = _fn;
$func$ LANGUAGE sql;
espero receber my_funcem troca
SELECT get_funcname('my_func(text, variadic text[])');
No entanto, o acima está me dando o erro:
ERROR: syntax error at or near "variadic"
LINE 1: SELECT get_funcname('my_func(text, variadic text[])');
^
CONTEXT: invalid type name "variadic text[]"
Alguém pode ajudar a explicar como corrigir a função ou sintaxe?
No PostgreSQL, existem Set-Returning-Functions (SRF). Mas eu estou querendo saber se existe também uma maneira de passar uma relação desconhecida em uma função? Em outras palavras, é possível escrever Set-Taking-Functions (por assim dizer), além de passar o nome do conjunto de tuplas?
Obviamente, existe um SQL dinâmico onde se pode passar um nome de tabela. Com pgRouting , pode-se até usar uma string inteira para passar o conjunto de tuplas para a função:
SELECT * FROM shortest_path('
SELECT gid AS id,
start_id::int4 AS source,
end_id::int4 AS target,
shape_leng::float8 AS cost
FROM network',
1,
5110,
false,
false);
Mas a string SQL no parâmetro não parece muito segura. A outra possibilidade é armazenar o resultado em uma tabela temporária primeiro e passar o nome da tabela temporária, o que parece ser contra a ideia de programação baseada em conjuntos em SQL.
Existem maneiras alternativas de passar um conjunto para uma função?
Eu só estou querendo saber se existe um método estabelecido para testar se uma string pode ser usada como um identificador PostgreSQL sem aspas ? (sem aspas porque quase qualquer string pode ser um identificador entre aspas).
Pergunto porque como mostrado em uma pergunta anterior ( Como citar nome de tabela qualificado com função format()? ), há momentos em que eu precisaria especificar um identificador (como o nome de uma tabela a ser criada) que não ainda existem, como valores de string ( text) em vez de um tipo mais seguro, como regclass. Citar a string/nome pode ser problemático, como mostrado lá e provavelmente em outros lugares. Sem citar, é suscetível à injeção de SQL.
Eu acho que se alguém programar com força suficiente, uma função de análise de string pode ser escrita em última análise. Só queria verificar se existem soluções existentes.
Relacionado:
Quais são os formatos válidos de um nome de esquema PostgreSQL?
A função PARSENAME() é o oposto de QUOTENAME() (sql-server)
Ao atribuir o resultado de uma função ou expressão a uma variável em PL/pgSQL, quando é necessário usar uma SELECTinstrução (subconsulta)?
Isso pode ser apenas meu equívoco do SQL. Eu pensei que tudo deveria estar dentro de um (SELECT ...)(mesmo que envolvesse apenas calcular um número), por exemplo
$$
DECLARE parts text[];
BEGIN
parts := (SELECT string_to_array(my_str,'_') );
...
$$
mas acabei de descobrir que funciona sem o SELECT:
parts := string_to_array(my_str,'_');
(É quase como se eu pudesse usar PL/pgSQL como se fosse Pascal.)
Em geral, que tipo de expressões podem ser usadas diretamente sem SELECTcalcular valores ?