时间戳

首页 > 解决方案 > postgresql 唯一约束允许重复

postgresql - postgresql 唯一约束允许重复

问题描述

我有如下用户表

CREATE TABLE public.users
(
    id integer NOT NULL DEFAULT nextval('users_id_seq'::regclass),
    uid uuid DEFAULT (md5(((random())::text || (clock_timestamp())::text)))::uuid,
    createdon timestamp without time zone DEFAULT now(),
    createdby integer,
    modifiedon timestamp without time zone,
    modifiedby integer,
    comments boolean DEFAULT false,
    verified boolean DEFAULT false,
    active boolean DEFAULT true,
    deleted boolean DEFAULT false,
    tags text[] COLLATE pg_catalog."default",
    user_type user_types NOT NULL,
    fullname character varying(100) COLLATE pg_catalog."default" NOT NULL,
    email character varying(84) COLLATE pg_catalog."default" NOT NULL,
    pword character varying(32) COLLATE pg_catalog."default",
    salt character varying(32) COLLATE pg_catalog."default",
    hash text COLLATE pg_catalog."default",
    source character varying(100) COLLATE pg_catalog."default",
    reference character varying(100) COLLATE pg_catalog."default",
    CONSTRAINT users_pkey PRIMARY KEY (id),
    CONSTRAINT email_unique UNIQUE (email)
,
    CONSTRAINT users_createdby_fkey FOREIGN KEY (createdby)
        REFERENCES public.users (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION,
    CONSTRAINT users_modifiedby_fkey FOREIGN KEY (modifiedby)
        REFERENCES public.users (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
)


当我尝试在 pgadmin 上两次插入记录时，电子邮件字段设置为唯一，我收到了错误。
但是，如果通过pg库在我的 nodejs 应用程序上运行相同的查询，则会插入记录

这种误操作的原因是什么？

应用程序中使用的查询对象：

{ text: 'INSERT INTO public.players ( createdby, user_type, fullname, email, pword, reference, source, salt, hash ) \n        VALUES ( $1, $2, $3, $4, $5, $6, $7, $8, $9 ) RETURNING id',
  values: 
   [ null,
     'player',
     'James De Souza',
     'james@desouza.com',
     '4297f44b13955235245b2497399d7a93',
     'organic',
     'on-site',
     '07ecab28a4bab8f1bf63208ac8961053',
     '25571c0618c701087495069cb4e45bf4fb07197e5ff301d963b670a9e033d2044557eb46537cee27a51da8b1fd0c8987b68ad7e8e47f48a06bcb1f44e6d3678d2c875d3dd4311a506a75eabf6fff23b65deba6a202606cc6d6b42abe4b25d136faffed8bd0046620f4e10ef0b974b108b27511a42c150983e268e1f7522ad678f0699848747a9e2f4a0cafc66704915a38966fbc76647678d907ca960533a5dc4de983167fafb7807e583dd5affcc2e14900295c6f396e768a32f106a4c636be78a6df96268216bc9410373fcc2528eb7984e2cb91ae62c3c65660dc477db3c3bfeadfacb214a055a48a1e9ed0c169ee54fcc6e7b24435cb53c3596e19bedbfef2c289ffb784f6fce18b9623253260e17aca5b3d810248ece6c51d810f3b44b1eb95225d5170cde0f3c9fda8ceefd9a287016c785576264f95ee961254bc371fed8671a7497456ce439d7318f21e539ce5940bd2fd73a350fc5d139cbe06bda568663a35488ceb7c62dadf3ee6d5810b5248abe447472b9c294a13c30144271a06e10b6a7f070df5bd7e804b13b1ab541c65de65dc5b85cf3199d7b13431095aff83de6939afc2d72d187597bf8214bf45f356591f7e513e7026322a20beed430966fbd3cbe4ec2c95b54d081c032f5e2ba930019857bb63e7c631668e3f607559b4ffffc1de6c957f687930f2900fb27123aaaf5f55a06844586cee94d10757' ] }

注意：public.players 继承自 public.users

CREATE TABLE public.players (
    "username" character varying(100) UNIQUE DEFAULT concat('player', (random() * 100000000)::int::text),
    "location" int REFERENCES public.list_locations ON DELETE RESTRICT,
    "address" text,
    "bio" text
) INHERITS (public.users);

刚刚意识到唯一约束不适用于继承的表
，这个问题（或其他）有什么解决方案或解决方法吗？

标签： postgresqlunique-constraintnode-postgres

解决方案

推荐阅读