Postgres 文本列只允许/转换为小写，没有特殊字符，例如:Ñöáè

问题描述

我有一张表，我希望那 1 列只接受小写字符串，并且没有特殊字符，例如 Ñóáöäë 等...

I have a table and I want that 1 column to only accept lowercase strings and with no special characters such as Ñóáöäë, etc...

无论如何要将此约束添加到列中?

Is there anyway to add this constrain to the column?

CREATE TABLE lawyer (
  id SERIAL PRIMARY KEY,
  name VARCHAR NOT NULL,
  url_name VARCHAR NOT NULL,
  gender VARCHAR(1) check (gender in ('m','f')) NOT NULL
);

我要添加约束的列是 url_name

The column I want to add the constrain is url_name

推荐答案

将所需的字符放入此 translate() 调用的第二个参数中:

Put the desired characters in the second parameter of this translate() call:

CREATE TABLE lawyer (
  id SERIAL PRIMARY KEY,
  name VARCHAR NOT NULL,
  url_name VARCHAR check(translate(url_name, 'abcdefghijklmnopqrstuvwxyz', '') = '') NOT NULL,
  gender VARCHAR(1) check (gender in ('m','f')) NOT NULL
);

insert into lawyer
values (default, 'Adam Smith', 'domain', 'f');

INSERT 0 1

insert into lawyer
values (default, 'Adam Smith', 'dömain', 'f');

ERROR:  new row for relation "lawyer" violates check constraint "lawyer_url_name_check"
DETAIL:  Failing row contains (3, Adam Smith, dömain, f).

或者，您可以创建一个触发器，它可以动态修改值:

---

Alternatively you can create a trigger, which modifies the value on the fly:

create or replace function lawyer_before_insert_or_update()
returns trigger language plpgsql as $$
begin
    new.url_name := lower(new.url_name);
    if translate(new.url_name, 'abcdefghijklmnopqrstuvwxyz', '') <> '' then
        raise exception 'Incorrect url name.';
    end if;
    return new;
end $$;

create trigger lawyer_before_insert_or_update
before insert or update on lawyer
for each row execute procedure lawyer_before_insert_or_update();

insert into lawyer
values (default, 'Adam Smith', 'DOMAIN', 'f')
returning *;

 id |    name    | url_name | gender
----+------------+----------+--------
  4 | Adam Smith | domain   | f
(1 row)

INSERT 0 1

insert into lawyer
values (default, 'Adam Smith', 'dömain', 'f');

ERROR:  Incorrect url name.

这篇关于Postgres 文本列只允许/转换为小写，没有特殊字符，例如:Ñöáè的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！