Héctor's questions

Estou enfrentando um problema ao criar um agregado contínuo em timescaledb (com base em visualizações materializadas) quando a coluna de tempo de origem e a nova coluna de tempo têm o mesmo nome abaixo:

CREATE MATERIALIZED VIEW IF NOT EXISTS minute_table
WITH (timescaledb.continuous) AS
SELECT
    time_bucket(INTERVAL '1 minute', my_time_col) AS my_time_col,
    ...
FROM source_table
GROUP BY my_time_col
WITH NO DATA;

Estou tendo o erro a seguir:

ERROR:  continuous aggregate view must include a valid time bucket function

Mas se eu alterar o novo nome da coluna de tempo, como no exemplo abaixo, funcionará:

CREATE MATERIALIZED VIEW IF NOT EXISTS minute_table
WITH (timescaledb.continuous) AS
SELECT
    time_bucket(INTERVAL '1 minute', my_time_col) AS my_time_col_asdf,
    ...
FROM source_table
GROUP BY my_time_col_asdf
WITH NO DATA;

Como posso contornar isso? Eu preciso que a fonte e as novas colunas de tempo tenham o mesmo nome...

Preciso executar CREATE MATERIALIZED VIEW ... WITH DATAa partir de uma função/procedimento, mas estou obtendo arquivos ERROR: CREATE MATERIALIZED VIEW ... WITH DATA cannot be executed from a function.

Existe alguma solução alternativa para isso além de fazer uma função retornar o texto da consulta de criação e copiá-lo e colá-lo para executá-lo?

Parece muito bobo, mas suponho que haja uma boa razão por trás disso ... Enfim, alguma solução alternativa para essa limitação?

Estou executando o postgresql 15.

Se for definido o mesmo parâmetro ./postgresql.confe ./conf.d/something.confqual tem preferência ou qual substitui o outro?

Eu tenho uma matriz de carimbos de data/hora em um bloco de código PLPGSQL: timestamp_array.

Para cada elemento de timestamp_arrayeu quero pegar o próprio elemento ( timestamp_element) e algumas colunas de uma tabela ( my_table) que também tem uma coluna timestamp, mas não quero pegar a coluna timestamp em si, mas sim o elemento timestamp array. A condição é que eu só queira obter uma linha da tabela para cada elemento da matriz, a correspondência mais próxima (no passado) entre os elementos de carimbo de data/hora na matriz e a coluna de carimbo de data/hora da tabela.

O que eu quero alcançar é algo como:

FOR EACH timestamp_element IN ARRAY timestamp_array
LOOP
  EXECUTE $EXE$
    SELECT my_table.some_other_columns...
    FROM my_table
    WHERE my_table.timestamp <= $1
    ORDER BY my_table.timestamp DESC
    LIMIT 1
  $EXE$
  USING timestamp_element
  INTO tmp_array;

  EXECUTE
    INSERT INTO temporary_table
    VALUES ($1, array_to_string($2, ', '))
  USING timestamp_element, tmp_array;
END LOOP;

Não sei se o código anterior está 100% correto, mas é apenas para tentar explicar melhor o que quero alcançar. E, obviamente, o objetivo seria fazer o trabalho com apenas uma consulta, em vez de uma consulta por elemento de matriz.

Para colocar como um caso prático, se eu tiver um my_table:

Com um timestamp_array:

[
  2020-02-15 12:00:00,
  2020-03-22 00:00:00,
  2020-06-14 17:00:00
]

Eu gostaria do seguinte temporary_tablesem usar uma consulta independente para cada elemento da matriz:

Estou escrevendo uma função que em algum passo eu tenho resultado de consulta como o seguinte:

E eu tenho que traduzir para isso:

Com uma linha a menos, e traduzindo os valores acumulados para algum tipo de "taxa", subtraindo a cada linha a anterior e rotulando-a com o timestamp anterior. Como eu poderia conseguir isso?

Obrigado pelo seu tempo.

Em alguma etapa de uma função plpgsql eu preciso armazenar um resultado de consulta 2D em variáveis ​​de matriz.

O código a seguir faz o trabalho para escalares (mas falha com matrizes):

SELECT col_a, col_b
FROM my_table
WHERE col_c = condition
INTO var_a, var_b;

O seguinte faz o trabalho para UMA coluna e UMA variável de matriz, mas não mais do que isso:

SELECT ARRAY(
    SELECT col_a
    FROM my_table
    WHERE col_c > condition
) INTO arr_a;

Como eu poderia armazenar várias linhas de col_a, b, c, d... em suas respectivas variáveis ​​de matriz sem ter que fazer uma consulta separada para cada coluna? Como no primeiro exemplo de código, mas para várias linhas e matrizes.

Estou tentando aprender e entender agrupamentos e como o postgresql compara e ordena strings, mas estou encontrando uma incongruência e não sei o que estou perdendo.

A pergunta:

SELECT datname, datcollate FROM pg_database;

Retorna que todo meu banco de dados usa agrupamento en_GB.UTF-8.

E a consulta:

SELECT table_schema, 
   table_name, 
   column_name,
   collation_name
FROM information_schema.columns
WHERE collation_name is not null
ORDER BY table_schema,
     table_name,
     ordinal_position;

Retorna que todas as colunas têm agrupamento 'C', o que significa (de acordo com minha pesquisa) que nenhum agrupamento é especificado. Então, suponho que o agrupamento de banco de dados seja usado, não é?

EDIT: Meu erro está aqui. A consulta anterior não retorna um resultado para todas as colunas, apenas para aquelas que collation_namenão são nulas, e as colunas com ordenação nula são as que herdam a ordenação de banco de dados. Então, eu estava pensando que as colunas foo.bar tinham Cagrupamento, mas nullna verdade eram.

Bem, a incongruência vem quando executo a consulta:

SELECT "name" FROM foo.bar ORDER BY "name" ASC;

Onde o tipo de dados da coluna "name" é texto e foo.bar é uma combinação schema.table criada pelo usuário. O resultado é semelhante ao humano ordenado alfabeticamente. De a/A a Z/z, não importa se é maiúscula ou minúscula.

Mas se eu executar a seguinte consulta:

SELECT "table_name"
FROM information_schema.tables
WHERE
    "table_name" ~ 'some_pattern'
    AND table_schema = 'foo'
    AND table_type = 'BASE TABLE'
ORDER BY "table_name" ASC;

Ele ordena o resultado de maneira semelhante a uma máquina, comparando byte a byte e, portanto, ordenando maiúsculas antes de minúsculas.

Por que existe essa diferença? Eu vi que o tipo de dados da coluna "table_name" não é "texto", mas "nome", mas não consigo descobrir se tem algo a ver.

Ter o agrupamento de banco de dados definido como en_GB.UTF-8 não deve ser suficiente para comparar o texto de maneira humana?

Obrigado pelo seu tempo.

Estou lendo a documentação do PostgreSQL pela primeira vez e ao enfrentar funções SQL acho que entendi todos os fundamentos, mas ainda não consigo ver a diferença entre SELECT sql_function(...)e SELECT * FROM sql_function(...)e quando usar um ou outro.