ceving's questions

Eu tenho o seguinte código, que gera o erro:

ERRO: não há nenhuma restrição exclusiva correspondente às chaves fornecidas para a tabela referenciada "pessoa"

DROP TABLE IF EXISTS person;
CREATE TABLE person (
  sn        serial     PRIMARY KEY,
  id        int        NOT NULL,
  modified  timestamp,
  name      text
);
CREATE UNIQUE INDEX person_id_key ON person (id) WHERE modified IS NULL;
DROP TABLE IF EXISTS account;
CREATE TABLE account (
  id        int        NOT NULL REFERENCES person(id),
  name      text
);

Existe um índice exclusivo para id:

                                       Table "public.person"
  Column  |            Type             | Collation | Nullable |              Default               
----------+-----------------------------+-----------+----------+------------------------------------
 sn       | integer                     |           | not null | nextval('person_sn_seq'::regclass)
 id       | integer                     |           | not null | 
 modified | timestamp without time zone |           |          | 
 name     | text                        |           |          | 
Indexes:
    "person_pkey" PRIMARY KEY, btree (sn)
    "person_id_key" UNIQUE, btree (id) WHERE modified IS NULL

Por que não é suficiente para a chave estrangeira?

É possível capturar uma exceção no plpgsql:

EXCEPTION WHEN unique_violation THEN

Mas como verificar a violação de uma restrição específica?

O exemplo a seguir cria duas chaves: uma for ae outra for b. Como saber qual deles causou a exceção?

create table t (
  a text unique,
  b text unique
);

create procedure insert_t(a text, b text)
language plpgsql
as $$
begin
  insert into t values (a, b);
  exception when unique_violation then
  raise notice 'is it a or b?';
end
$$;

call insert_t('x', 'foo');
call insert_t('x', 'bar');

select * from t;

Tenho uma tabela especificando tipos para distinguir entre escalares e listas. Quando existe "escalar" o valor é um escalar e quando é nulo é uma lista.

drop table type1 cascade;
create table type1 (
  id     integer primary key,
  scalar "char",
  name   text,
  unique (id, scalar)
);
insert into type1 values
  (1, 'x', 'single'),
  (2, null, 'multi');

Agora tenho uma tabela com valores, que faz referência ao tipo e adiciona uma restrição exclusiva à tupla (tipo, escalar). Isso funciona porque NULL é distinto. Se "escalar" for NULL é possível ter mais de uma linha por "tipo", pois todos os "escalares" são distintos.

drop table value1 cascade;
create table value1 (
  id     integer primary key,
  type   integer,
  scalar "char",
  value  text,
  foreign key (type, scalar) references type1 (id, scalar),
  unique (type, scalar)
);

É possível inserir um único valor:

insert into value1 values
  (11, 1, 'x', 'just one');

Mas não é possível adicionar outro:

insert into value1 values
  (12, 1, 'x', 'another one'); -- ERROR

Mas se o escalar for NULL, é possível ter mais de um:

insert into value1 values
  (21, 2, null, 'first'),
  (22, 2, null, 'second');

Meu problema: é possível fazer referência a tipos que não existem.

Isso funciona, embora não devesse:

insert into value1 values
  (12, 1, null, 'another');

É permitido usar (1, NULL) como tipo, embora na tabela de tipos estejam apenas (1, 'x') e (2, NULL).

Tentei usar match fulla chave estrangeira, mas parece fazer algo diferente. Impede também o uso pretendido de vários valores nulos, porque rejeita vários valores nulos.

Quero permitir vários NULLs se value1.scalarfor type1.scalarNULL. Mas quero rejeitar qualquer NULL se value1.scalarnão type1.scalarfor NULL. Como posso expressar isso?

Eu tentei isso, mas não funciona:

CREATE OR REPLACE FUNCTION answer()
  RETURNS bigint
  LANGUAGE sql AS
$$
SELECT 42
$$;

SET session.answer = select answer();

Eu tentei isso:

create or replace function c3 (arg char(3))
  returns void
  language plpgsql as
$$
begin
  raise notice 'Value: %', arg;
end;
$$;

Mas aceita mais de três caracteres:

select c3('1234');

A resposta a seguir usa SQLSTATE '23000'.

https://dba.stackexchange.com/a/130121/50410

Isso é algo específico para Postgresql ou faz parte do padrão SQL?

Eu tenho uma tabela externa definida com file_fdw, que lê os dados de um arquivo CSV. O Postgresql analisa o arquivo CSV e converte a data, por exemplo, de YYYYMMDDpara YYYY-MM-DD. Isso funciona bem.

Minha pergunta é com que frequência o arquivo CSV é analisado? Ele é analisado em todos os select? Ou fica em cache? E se sim, quanto tempo fica em cache?

Eu tenho uma tabela simples:

create table item (
  lan      text not null,
  disabled date
);

Meu objetivo é garantir exclusividade para a lancoluna em todas as linhas, onde disabledé null.

Eu criei um índice parcial:

create unique index lan_idx on item (lan) where disabled is not null;

Agora tentei adicionar o índice à tabela, mas estou lutando com a sintaxe. (eu peguei o código deste tutorial)

alter table item add constraint lan_idx using index lan_idx;

Isso gera um erro de sintaxe antes de "usar".

Também tentei o seguinte:

alter table item add constraint lan_idx unique using index lan_idx;

Mas isso gera o erro de que uma restrição exclusiva não pode ser criada com um índice parcial.

Alguém pode me dizer o que fiz de errado?