eComEvo's questions

Estou tentando impedir que alguém solte tabelas em um esquema específico "público", a menos que o usuário seja um, rds_superusermas a função que escrevi está protegendo todos os esquemas.

CREATE OR REPLACE FUNCTION guard_tables()
RETURNS event_trigger
LANGUAGE plpgsql
AS $$
BEGIN
IF TG_TABLE_SCHEMA = 'public' AND (SELECT COUNT(*) FROM pg_roles WHERE pg_has_role(CURRENT_USER, oid, 'member') AND rolname = 'rds_superuser') = 0 THEN
RAISE EXCEPTION 'command % is disabled for this table', tg_tag;
END IF;
END;
$$;

Como faço para que isso funcione?

Estou tentando construir uma função de gatilho para atualizar automaticamente os valores do pedido pai sempre que qualquer um de seus itens for alterado.

Isto é o que eu criei:

CREATE OR REPLACE FUNCTION update_totals() RETURNS void AS $$
    UPDATE orders SET
       total_fees = (SELECT SUM(fees) FROM order_items WHERE order_id = OLD.order_id),
       total_profit = (CASE WHEN total_cost IS NOT NULL THEN total - total_tax - total_cost - (SELECT SUM(fees) FROM order_items WHERE order_id = OLD.order_id) ELSE NULL END)
    WHERE id = OLD.order_id;
$$
LANGUAGE SQL;

CREATE TRIGGER update_totals AFTER INSERT 
OR UPDATE OF fees
ON order_items INITIALLY DEFERRED
FOR EACH ROW
WHEN ( OLD.* IS DISTINCT FROM NEW.* ) EXECUTE PROCEDURE update_totals();

Quando tento executar isso, recebo este erro:

missing FROM-clause for table "old"

Tentei criar uma referência para a tabela original na trigger:

CREATE TRIGGER update_totals AFTER INSERT 
OR UPDATE OF fees
ON order_items
REFERENCING OLD ROW AS old_order
DEFERRABLE INITIALLY DEFERRED
FOR EACH ROW
WHEN ( OLD.* IS DISTINCT FROM NEW.* ) EXECUTE PROCEDURE update_totals();

Mas isso só dá o erro syntax error at or near "REFERENCING".

Como faço para obter o da tabela order_idoriginal ?order_items

Estou tentando mostrar resultados por 12 meses usando esta consulta:

SELECT 
    COUNT(DISTINCT user_id) as customers,
    SUM(amount)*.01 as amount,
    SUM(amount_refunded)*.01 as refunded,
    MONTH(MAX(created_at)) as month
FROM `payments`
WHERE `amount` > 0
AND `status` = 'succeeded'
AND `created_at` BETWEEN NOW() - INTERVAL 2 MONTH AND NOW() - INTERVAL 1 MONTH
AND `user_id` IN (SELECT `id` FROM `users` WHERE `created_at` BETWEEN NOW() - INTERVAL 2 MONTH AND NOW() - INTERVAL 1 MONTH);

Poderia facilmente fazer isso programaticamente usando PHP para gerar uma tabela HTML onde cada linha inclui mostra um mês diferente, incrementando o mês e consultando o banco de dados novamente a cada vez.

Como eu faria isso com esta consulta usando um loop MySQL FOR? Essa seria mesmo a abordagem correta?

Tenho uma orderstabela e preciso calcular quando há uma mudança na contagem média ao longo do tempo. Isso pode ser um aumento ou uma diminuição.

Por exemplo, se geralmente há 1 pedido a cada dois dias, preciso detectar se de repente aumentou para 3 pedidos todos os dias ou diminuiu para 1 pedido a cada 3 dias.

Exemplo de estrutura e dados da tabela:

CREATE TABLE "public"."orders" (
    "id" SERIAL,
    "created_at" timestamp NOT NULL,
    PRIMARY KEY ("id")
);

INSERT INTO "orders" ("created_at")
VALUES
(now() - interval '1 hours'),
(now() - interval '6 hours'),
(now() - interval '12 hours'),
(now() - interval '2 days'),
(now() - interval '4 days'),
(now() - interval '6 days'),
(now() - interval '9 days');

No conjunto de dados acima, suponho que eu queria detectar que, ao longo da última semana, geralmente há apenas 1 pedido a cada 2 dias, mas nas últimas 24 horas esse padrão foi violado com 3 pedidos em um dia.

Resultado desejado:

• Média de pedidos dos 7 dias anteriores por dia (não incluindo as últimas 24 horas): 0,43
• Velocidade média do pedido dos 7 dias anteriores (não incluindo as últimas 24 horas): 1 pedido a cada 2 dias
• Pedidos das últimas 24 horas: 3

Cenário alternativo:

INSERT INTO "orders" ("created_at")
VALUES
(now() - interval '12 hours'),
(now() - interval '2 days'),
(now() - interval '2 days'),
(now() - interval '2 days'),
(now() - interval '3 days'),
(now() - interval '3 days'),
(now() - interval '3 days'),
(now() - interval '4 days'),
(now() - interval '4 days'),
(now() - interval '4 days'),
(now() - interval '5 days'),
(now() - interval '5 days'),
(now() - interval '5 days'),
(now() - interval '6 days'),
(now() - interval '6 days'),
(now() - interval '6 days'),
(now() - interval '7 days'),
(now() - interval '7 days'),
(now() - interval '7 days');

Como alternativa, suponha que eu tenha o conjunto de dados acima. Normalmente, há aproximadamente 3 pedidos todos os dias na última semana, mas nas últimas 24 horas houve apenas 1 pedido.

Resultado desejado:

• Média de pedidos dos 7 dias anteriores (não incluindo as últimas 24 horas): 2,57
• Velocidade média dos pedidos dos 7 dias anteriores (não incluindo as últimas 24 horas): 3 pedidos a cada 1 dia
• Pedidos das últimas 24 horas: 1

Como eu faria isso?

PostgreSQL 9.6

Como eu consultaria uma coluna de matriz JSON como essa para texto em cada um dos valores da matriz e retornaria apenas as linhas que os possuem?

["Mr Smith","Ms Wellington","Mr Anderson"]

Eu tentei isso, mas nenhum retornou resultados quando eu sei que eles existem:

select * from people where names::jsonb ? 'Mr';
select * from people where names::jsonb @> 'Mr';

Eu tenho isso peoplee tagsmesa, assim,

CREATE TABLE people
AS
  SELECT *
  FROM ( VALUES
    (1,'Joe'),
    (2,'Jane')
  ) AS t(id,name);

CREATE TABLE tags
AS
  SELECT * FROM ( VALUES
    (1, 1, 'np'),
    (2, 1, 'yw'),
    (3, 2, 'np')
  ) AS t(id, people_id, tag);

Se eu quiser encontrar tudo o peopleque contém as tags npe na tabela usando uma junção, como eu faria isso de forma eficiente no Postgres 9.6?ywtags

Nesse cenário, devo apenas obter o registro de Joe da peopletabela.

Estou tentando otimizar a estrutura de uma tabela. Para fazer isso, coloquei as colunas na ordem mais otimizada possível e executei esta consulta (simplificada):

INSERT INTO table_new ("id", "some_field", "some_other_field") SELECT "id", "some_field", "some_other_field" FROM table_current;

Então eu simplesmente renomeio a tabela e adiciono todos os mesmos índices e chaves estrangeiras.

ALTER TABLE "table_current" RENAME TO "table_old";
ALTER TABLE "table_new" RENAME TO "table_current";
ALTER TABLE "table_current" ADD PRIMARY KEY ("id") NOT DEFERRABLE INITIALLY IMMEDIATE;

Isso funcionou muito bem no passado, mas neste caso em particular, existem muitas outras tabelas com chaves estrangeiras em cascata nesta tabela.

Portanto, se eu recriar e renomear, todas as outras tabelas ainda farão referência table_oldem suas chaves estrangeiras em vez do novo table_current.

Como faço uma migração de tabela como essa sem ter que recriar as chaves estrangeiras em todas as outras tabelas também?

Preciso me conectar à nossa instância AWS RDS PostgreSQL 9.5 para poder adicionar a citextextensão ao pg_catalogesquema para que fique disponível para todos os esquemas (o PG precede pg_catalogo início do search_pathpor padrão).

Eu sou facilmente capaz de fazer isso na minha máquina local:

> sudo su - postgres
> psql
> \c mydatabase
> CREATE EXTENSION IF NOT EXISTS citext SCHEMA pg_catalog;
> \q
> exit

Se eu fizer login no RDS usando uma conexão direta, como mostram os documentos da AWS, recebo permission denied for schema pg_catalogquando tento CREATE EXTENSION:

psql
   --host=myrdsawshostname
   --port=5432
   --username=myusername
   --password
   --dbname=mydatabase

Esta é a única maneira que conheço de conectar e só consigo conectar como "myusername" e não "postgres".

O que eu estou fazendo errado aqui?

Eu criei várias tabelas em meu esquema público.

Ocasionalmente, preciso executar um seeder para atualizar a userstabela quando realizo testes. Para fazer isso, estou executando TRUNCATE TABLE users RESTART IDENTITY CASCADE.

No entanto, não quero que outras tabelas, exceto aquelas relacionadas à userstabela, sejam afetadas. A única chave estrangeira que faz referência à userstabela está na teamstabela que faz referência usersa owner_id. A teamstabela trunca como esperado.

O que não é esperado é que a attributestabela também seja truncada por TRUNCATE TABLE users RESTART IDENTITY CASCADE.

A attributestabela tem uma chave estrangeira na accountstabela que faz referência à userstabela. No entanto, na attributestabela, TODOS account_idos valores são NULL, portanto, não há nenhuma referência real em nenhum dos registros para um registro específico na accountstabela. Na verdade, no momento da execução da consulta truncada, toda a accountstabela já está vazia.

Como então está TRUNCATE TABLE users RESTART IDENTITY CASCADEfazendo com que minha tabela de atributos seja truncada com base em NULLchaves estrangeiras?