Manngo's questions

Eu tenho um cron job diário para despejar meu banco de dados. Parte do crontab inclui o seguinte:

01   01 *   *   *   root    /etc/cron.d/backupDaily.sh

e parte do script de backup contém o seguinte:

cd /data/pgsql/
sudo -u postgres /usr/pgsql-12/bin/pg_dumpall>/data/pgsql/pg.sql

Minhas anotações antigas referem-se a colocar as credenciais em um arquivo como /.pgpass. No entanto, atualizei meu servidor algumas vezes desde o início e parece que não tenho mais esse arquivo.

Alguém pode me dizer como posso escapar disso? Isso sugere que meu postgresusuário não tem uma senha?

Aqui está o que está no meu pg_hba.confarquivo:

#   TYPE    DATABASE        USER    ADDRESS         METHOD
    local   replication     all                     peer
    host    replication     all     127.0.0.1/32    ident
    host    replication     all     ::1/128         ident

    local   all             all                     trust

#   IPv4 local connections:
    host    all             all     127.0.0.1/32    trust
    host    all             all     192.168.0.0/24  trust
    host    all             all     192.168.1.0/24  trust
    host    all             all     192.168.77.0/24 trust

Ao utilizar o método PHP lastInsertId()com PostgreSQL, é necessário saber o nome da sequência.

Se eu defini a coluna usando GENERATED BY DEFAULT AS IDENTITY, sei que posso obter o nome da sequência usando algo como pg_get_serial_sequence('whatever', 'id').

É possível especificar um nome de sequência na CREATE TABLEinstrução?

Eu quero alterar o formato de número e moeda usando algo como:

SET lc_numeric='en_AU.UTF-8'
SET lc_money='en_AU.UTF-8'

Tentei vários formatos para, digamos, holandês, mas nada funcionou. Parece que não tenho essa localidade no meu sistema operacional.

Como posso obter uma lista de localidades disponíveis no PostgreSQL? Não há nada que eu possa encontrar online.

Uso o PostgreSQL há muitos anos e, entre outras coisas, adicionei várias funções escalares definidas pelo usuário.

Claro, se eu soubesse disso na época, deveria ter adicionado minhas próprias funções a um esquema separado para melhor gerenciamento. Em vez disso, minhas funções estão todas misturadas com as padrão.

Existe uma maneira de distinguir as funções padrão para que eu possa organizar melhor minhas próprias funções?

Para fins de demonstração, tenho uma tabela de exemplo com uma varcharcoluna chamada numberstringcom os valores:

-4
32
0
4
16
8
-8
1024

Ao selecionar usando ORDER BY numberstring, acho que usuários diferentes obtêm resultados diferentes.

Alguns recebem:

-4, -8, 0, 1024, 16, 32, 4, 8

enquanto alguns recebem:

0, 1024, 16, 32, -4, 4, -8, 8

De alguma forma, o segundo resultado parece ignorar o sinal de menos, mesmo que os dados sejam classificados como dados de string.

Até onde eu sei, o servidor subjacente é uma instalação padrão do Microsoft SQL Server express, sem nenhuma opção especial. A versão está nos últimos anos, mas usuários com a mesma versão ainda relatam resultados diferentes.

Há algo na configuração que afeta os resultados e como posso visualizar isso no SSMS?

O servidor de banco de dados e o SSMS são configurados na Austrália apenas com os padrões, até onde eu saiba. O tipo de dados é varchar(max).

Eu tenho que confessar que não tenho ideia sobre essa coisa de banco de dados conectável. Tenho experiência em PostgresSQL, MySQL, SQL Server, mas a Oracle me derrotou.

Eu tenho o Oracle 18c rodando em uma imagem do Docker. Eu quero criar um banco de dados conectável chamado bookshop. Seguindo algumas instruções, executei o seguinte:

CREATE PLUGGABLE DATABASE bookshop ADMIN USER pdb_adm IDENTIFIED BY Oradoc_db1
    file_name_convert=('/opt/oracle/oradata/XE/pdbseed','/opt/oracle/oradata/XE/BOOKSHOP');

Em seguida nas instruções, eu corro:

ALTER PLUGGABLE DATABASE xepdb2 OPEN READ WRITE;

exceto que eu não posso. Eu recebo a mensagem:

ORA-01031: privilégios insuficientes.

Eu então tento:

DROP PLUGGABLE DATABASE bookshop;

e recebo a mesma mensagem.

Quando eu tento:

SELECT vp.name, vp.open_mode
FROM v$pdbs vp;

eu recebo

E, finalmente, quando tento descobrir quem eu sou:

SELECT user FROM dual;

eu consigo SYSTEM.

Neste ponto, não tenho ideia de como obter controle sobre esse banco de dados conectável. Preciso mudar para outro usuário? Como?

Tive motivo para alterar o nome do meu computador (está sendo executado em uma máquina virtual). Eu tenho uma instância SQLExpress existente que foi instalada antes da renomeação.

Existem alguns recursos que falham porque novos bancos de dados ainda são criados com o antigo proprietário. Mais especificamente, quando tento criar um novo diagrama de banco de dados, recebo o erro abaixo.

Não foi possível obter informações sobre o grupo/usuário do Windows NT 'OLDNAME\user', código de erro 0x534. (Microsoft SQL Server, Erro: 15404).

Eu usei as seguintes instruções para renomear o servidor:

sp_dropserver 'OLDNAME\SQLEXPRESS';
GO
sp_addserver 'NEWNAME\SQLEXPRESS','local';
GO
--  Restart Server
SELECT @@SERVERNAME;    --  NEWNAME\SQLEXPRESS

No entanto, isso não afetou o proprietário padrão para novos bancos de dados. Eles ainda estão sendo criados com o proprietário como OLDNAME\user, que não existe mais.

Eu sei como alterar o proprietário de um banco de dados existente, após a criação. Mas, como posso fazer com que novos bancos de dados tenham o proprietário correto para começar?

Tenho bastante experiência com outros SQLs (PostgreSQL, Microsoft Server, MySQL, SQLite), mas não tanto em Oracle.

Existe alguma maneira de fazer com que o Oracle aceite literais de data no formato ISO8601. Por exemplo:

CREATE TABLE data (
    id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    name VARCHAR(255),
    when DATE
);
INSERT INTO data(name,when) VALUES ('Fred','2021-10-05');
--  ORA-01861: literal does not match format string

Veja este DBFiddle .

Certamente deve haver como interruptor.

O mesmo se aplicaria à saída de datas: existe uma maneira de garantir datas formatadas em ISO8601?

Estive lendo sobre o .pgpassarquivo, mas não consigo fazê-lo funcionar para mim.

Meu .pgpassarquivo está mais ou menos assim:

127.0.0.1:5432:accounts:fred:p@55w0rd

Eu defino o privilégio para 0600(isso é no CentOS Linux) e tento:

psql

Onde recebo a mensagem:

psql: erro: FATAL: banco de dados "…" não existe

onde …está meu nome de usuário.

Posso me conectar com sucesso se usar:

psql -u … -d accounts

então eu não acho que meu .pgpassarquivo está fazendo seu trabalho.

Como posso fazê-lo funcionar?

Estou bastante familiarizado com DBMS Relacional em geral, mas não com Oracle. Eu tenho o Oracle 18c XE rodando dentro do Docker (no MacOS). Eu me conecto a ele usando um cliente de terceiros (SQLPro Studio no MacOS). Agora quero começar.

Se fosse o MySQL, eu poderia emitir CREATE DATABASE whatever; USE whatever;e prosseguir com as tabelas. O mesmo com o Microsoft SQL Server e algo semelhante com o PostgreSQL. Eu também poderia criar um esquema adicional para manter melhor organizado.

Eu entendo que o Oracle XE fornece apenas um único banco de dados e gostaria de criar um esquema adequado. Neste momento estou no mar. Eu entendo que eu precisaria criar um novo usuário associado ao esquema.

Eu também sei que diferentes RDBMS têm ideias diferentes sobre o que é um banco de dados ou um esquema, então não tenho certeza se essa é a definição ou solução correta.

Não estou tendo sucesso criando um novo usuário ou um novo esquema no meu cliente.

Eu tentei:

CREATE USER fred IDENTIFIED BY 'h3art0fg0ld`;

mas recebo a mensagem:

nome de usuário ou função comum inválido

Eu também li que eu deveria usar algo como c##fred, que funcionou, mas (a) eu realmente não sei o que isso significa e (b) a declaração GRANT connect, resource, create sessions TO c##printsdb;me dá a mensagem “privilégio ausente ou inválido”, então não estou mais longe à frente.

Estando em um contêiner do Docker, não sei se tenho acesso ao SQL*Plus, que alguns tutoriais online presumem.

Como crio e uso um esquema ou o que for necessário para conter uma coleção de tabelas?

Eu tenho um MariaDB 10.2 funcionando em execução no meu servidor CentOS 6. Eu posso me conectar ao banco de dados de outra máquina na LAN.

Por curiosidade, eu queria revisitar como isso foi possível. Tudo o que leio menciona bind-addresso uso no my.cnfarquivo, mas quando examino esse arquivo, não existe tal diretiva. Também não há nada nos arquivos /etc/my.cnf.d/*.cnf.

O acesso à rede é um padrão neste caso ou existe alguma outra diretiva misteriosa?

A maioria dos outros bancos de dados permite gerar valores de tabela usando VALUES. Tanto quanto posso dizer, o SQL Server permite fazer isso com subconsultas:

SELECT * FROM (
    VALUES
        ('Cornelius','Eversoe', '[email protected]'),
        ('Ebenezer','Splodge', '[email protected]'),
        ('Sylvester','Underbar', '[email protected]'),
        ('Cynthia','Hyphen-Smythe', '[email protected]')
) as sq(givenname,familyname,email);

mas não consigo fazê-lo funcionar com CTEs. O melhor que posso fazer é incorporar uma subconsulta dentro de um CTE:

WITH data AS (
    SELECT * FROM (
    VALUES
        ('Cornelius','Eversoe', '[email protected]'),
        ('Ebenezer','Splodge', '[email protected]'),
        ('Sylvester','Underbar', '[email protected]'),
        ('Cynthia','Hyphen-Smythe', '[email protected]')
    ) as sq(givenname,familyname,email)
)
SELECT * FROM data;

Eu prefiro CTEs por causa de sua legibilidade, mas existe uma maneira mais direta de fazer isso? Por exemplo, PostgreSQL, MariaDB (versões de suporte) e SQLite aceitam isso:

WITH data(givenname,familyname,email) AS (
    VALUES
        ('Cornelius','Eversoe', '[email protected]'),
        ('Ebenezer','Splodge', '[email protected]'),
        ('Sylvester','Underbar', '[email protected]'),
        ('Cynthia','Hyphen-Smythe', '[email protected]')
)
SELECT * FROM data;

Ao usar a ntile()função de janela, o principal problema é que ela agrupa arbitrariamente em partes aproximadamente iguais, independentemente do valor real.

Por exemplo com a seguinte consulta:

select
    id,title,price,
    row_number() over(order by price) as row_number,
    rank() over(order by price) as rank,
    count(*) over(order by price) as count,
    dense_rank() over(order by price) as dense_rank,
    ntile(10) over(order by price) as decile
from paintings
order by price;

Obterei 10 grupos aproximadamente do mesmo tamanho, com a forte probabilidade de que pinturas com o mesmo preço acabem em caixas diferentes.

Por exemplo:

┌────┬────────────────────────────────────────────┬───────┬────────────┬──────┬───────┬────────────┬────────┐
│ id │ title                                      │ price │ row_number │ rank │ count │ dense_rank │ decile │
╞════╪════════════════════════════════════════════╪═══════╪════════════╪══════╪═══════╪════════════╪════════╡
│ 11 │ Eyes in the Heat                           │ 10    │ 1          │ 1    │ 1     │ 1          │ 1      │
│ 19 │ Deux fillettes, fond jaune et rouge        │ 11    │ 2          │ 2    │ 2     │ 2          │ 1      │
│ 17 │ Flowers in a Pitcher                       │ 12    │ 3          │ 3    │ 6     │ 3          │ 1      │
│ 5  │ Composition with Red, Yellow and Blue      │ 12    │ 4          │ 3    │ 6     │ 3          │ 2      │
│ 18 │ La lecon de musique (The Music Lesson)     │ 12    │ 5          │ 3    │ 6     │ 3          │ 2      │
│ 9  │ The Adoration of the Magi                  │ 12    │ 6          │ 3    │ 6     │ 3          │ 2      │
│ 29 │ Self-Portrait                              │ 14    │ 7          │ 7    │ 10    │ 4          │ 3      │
│ 25 │ Symphony in White, No. 1: The White Girl   │ 14    │ 8          │ 7    │ 10    │ 4          │ 3      │
│ 30 │ The Anatomy Lecture of Dr. Nicolaes Tulp   │ 14    │ 9          │ 7    │ 10    │ 4          │ 3      │
│ 20 │ Les repasseuses (Women Ironing)            │ 14    │ 10         │ 7    │ 10    │ 4          │ 4      │
│ 1  │ The Birth of Venus                         │ 15    │ 11         │ 11   │ 14    │ 5          │ 4      │
│ 12 │ Femme se promenant dans une foret exotique │ 15    │ 12         │ 11   │ 14    │ 5          │ 4      │
│ 24 │ Portrait of the Painter’s Mother           │ 15    │ 13         │ 11   │ 14    │ 5          │ 5      │
│ 28 │ Jeunes filles au piano                     │ 15    │ 14         │ 11   │ 14    │ 5          │ 5      │
│ 7  │ Portrait de l artiste (Self-portrait)      │ 16    │ 15         │ 15   │ 17    │ 6          │ 5      │
│ 3  │ The Last Supper                            │ 16    │ 16         │ 15   │ 17    │ 6          │ 6      │
│ 13 │ Combat of a Tiger and a Buffalo            │ 16    │ 17         │ 15   │ 17    │ 6          │ 6      │
│ 4  │ The Creation of Man                        │ 17    │ 18         │ 18   │ 19    │ 7          │ 6      │
│ 22 │ Le Chemin de Fer                           │ 17    │ 19         │ 18   │ 19    │ 7          │ 7      │
│ 6  │ Femmes de Tahiti [Sur la plage]            │ 18    │ 20         │ 20   │ 24    │ 8          │ 7      │
│ 21 │ Le Bar aux Folies-Berg                     │ 18    │ 21         │ 20   │ 24    │ 8          │ 7      │
│ 26 │ Lady at the Piano                          │ 18    │ 22         │ 20   │ 24    │ 8          │ 8      │
│ 15 │ Remembrance of a Garden                    │ 18    │ 23         │ 20   │ 24    │ 8          │ 8      │
│ 16 │ 1914                                       │ 18    │ 24         │ 20   │ 24    │ 8          │ 8      │
│ 14 │ Ancient Sound, Abstract on Black           │ 19    │ 25         │ 25   │ 28    │ 9          │ 9      │
│ 8  │ The Large Turf                             │ 19    │ 26         │ 25   │ 28    │ 9          │ 9      │
│ 23 │ On the Beach                               │ 19    │ 27         │ 25   │ 28    │ 9          │ 9      │
│ 2  │ Portrait of Mona Lisa                      │ 19    │ 28         │ 25   │ 28    │ 9          │ 10     │
│ 27 │ On the Terrace                             │ 20    │ 29         │ 29   │ 30    │ 10         │ 10     │
│ 10 │ The She-Wolf                               │ 20    │ 30         │ 29   │ 30    │ 10         │ 10     │
└────┴────────────────────────────────────────────┴───────┴────────────┴──────┴───────┴────────────┴────────┘

Observe que há quatro itens com preço 12, mas dois deles estão no decil 1 e dois deles no decil 2. Eu gostaria de manter esses itens juntos, e não estou preocupado com qual decil.

Incluí outras funções de janela para fazer a comparação.

Parece que ntile()usa o row_number()único e baseia os cortes nisso. Seria mais justo se ele usasse a função rank()ou count(*), pois itens com o mesmo preço acabariam na mesma caixa.

Esse é o comportamento do PostgreSQL e do SQL Server e, presumivelmente, do resto.

A questão é, existe uma maneira de conseguir isso?

Estou ciente da NULLIFfunção que compara dois valores e retorna NULLse eles corresponderem.

Existe uma função que irá comparar dois valores e retornar NULLse eles não corresponderem? Isso é filtrar os outros valores.

Eu sei que posso fazer algo assim usando CASE:

CASE column WHEN value THEN 1 END

Eu também sei que posso escrever uma função.

Talvez haja um truque sutil para usar NULLIFque eu desconheço.

Acho NULLIFgenérico. Estou procurando uma solução que também seja genérica, para que possa ser aplicada a qualquer banco de dados padrão.

Estou ciente da edição Oracle Express que está até a versão 11g, que é bastante datada. No entanto, li que a Oracle está alterando/alterou seu licenciamento de edições mais recentes para que não precisemos mais esperar anos até que uma edição Express mais nova esteja disponível.

Eu ensino SQL para usuários de diferentes origens, alguns dos quais usam Oracle no trabalho. Seria útil apontá-los para um pacote com o qual possam praticar. No entanto, acho o site da Oracle um tanto confuso.

• Quais edições estão atualmente disponíveis gratuitamente para uso pessoal, como treinamento?
• Existe também uma edição gratuita disponível para uma pequena empresa, como com 4 computadores?

Eu tenho duas versões de uma junção múltipla que produzem resultados idênticos. As cinco tabelas são:

customers ← sales ← saleitems → paintings → artists

As setas (espero) mostram a relação entre as tabelas.

Cada tabela tem uma chave primária chamada id, e as tabelas internas têm uma chave estrangeira para outra tabela chamada [othertable]id.

A primeira versão é uma junção clássica com a tabela listada na ordem acima

SELECT
    c.id, c.givenname, c.familyname,
    a.givenname, a.familyname
FROM
    customers c
        JOIN sales s ON c.id=s.customerid
            JOIN saleitems si ON s.id=si.saleid
                JOIN paintings p ON si.paintingid=p.id
                    JOIN artists a ON p.artistid=a.id

;

A segunda versão contém todas as JOINcláusulas primeiro, e as ONcláusulas, na ordem inversa .

SELECT
    c.id, c.givenname, c.familyname,
    a.givenname, a.familyname
FROM
    customers c
        JOIN sales s 
            JOIN saleitems si
                JOIN paintings p
                    JOIN artists a
                    ON p.artistid=a.id
                ON si.paintingid=p.id
            ON s.id=si.saleid
        ON c.id=s.customerid

;

OK, então funciona, mas alguém pode explicar como funciona a segunda versão? Por que a ordem das ONcláusulas deve ser o inverso das JOINcláusulas? É possível ordenar aleatoriamente as cláusulas JOINou ?ON

Eu testei isso no Microsoft SQL e também no PostgreSQL.

O Microsoft SQL Server tem o que considero uma função notavelmente sensata, try_cast()que retorna a nullse a conversão não for bem-sucedida, em vez de gerar um erro.

Isso torna possível usar uma CASEexpressão ou um coalescepara recorrer. Por exemplo:

SELECT coalesce(try_cast(data as int),0);

A questão é, o PostgreSQL tem algo semelhante?

A pergunta é feita para preencher algumas lacunas no meu conhecimento, mas também há o princípio geral de que alguns preferem uma reação menos dramática a alguns erros do usuário. Retornar a nullé mais fácil de se fazer em SQL do que um erro. Por exemplo SELECT * FROM data WHERE try_cast(value) IS NOT NULL;. Na minha experiência, os erros do usuário às vezes são melhor tratados se houver um plano B.

Até onde sei, o SQLite não impõe tipos de dados de tabela definidos — ele tem tipagem dinâmica. Por exemplo, permite inserir strings em campos numéricos ou exceder o comprimento da string.

Se for esse o caso, qual é o sentido de definir tipos de dados na CREATE TABLEinstrução?

Não consigo encontrar nenhuma informação definitiva sobre o que constitui um nome de variável válido em uma função do PostgreSQL.

Estou assumindo que a função é escrita usando plpgsql. Eu sei que posso usar letras, numerais (além do primeiro caractere) e sublinhados. Mais alguma coisa?

Onde isso está documentado?

O SQL Server tem uma peculiaridade com colunas exclusivas opcionais: você não pode ter mais de uma nullem uma tabela.

Por exemplo:

create table employees (
    id int identity(1,1) primary key,
    givenname nvarchar(24) not null,
    familyname varchar(24) not null,
    tfn char(9) unique  --  optional, but unique
);

(Na Austrália, temos um Tax File Number, que é exclusivo para o contribuinte).

O seguinte é uma solução alternativa:

create table employees (
    id int identity(1,1) primary key,
    givenname nvarchar(24) not null,
    familyname varchar(24) not null,
    tfn char(9)
);
CREATE UNIQUE NONCLUSTERED INDEX uq_employees_tfn ON employees(tfn)
WHERE tfn IS NOT NULL;

É possível nomear uma restrição exclusiva e dar a ela a condição acima de maneira semelhante ao primeiro exemplo? Ou seja, é possível incluir a restrição mais complexa em linha com a definição da coluna?