I have users table like below
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
)
email field is set to unique
when I try to insert record twice on pgadmin, I got the error.
however, if the same query run over my nodejs app via pg library, records are inserted
what is the reason of this misoperation?
the query object that used in app:
{ 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',
'[email protected]',
'4297f44b13955235245b2497399d7a93',
'organic',
'on-site',
'07ecab28a4bab8f1bf63208ac8961053',
'25571c0618c701087495069cb4e45bf4fb07197e5ff301d963b670a9e033d2044557eb46537cee27a51da8b1fd0c8987b68ad7e8e47f48a06bcb1f44e6d3678d2c875d3dd4311a506a75eabf6fff23b65deba6a202606cc6d6b42abe4b25d136faffed8bd0046620f4e10ef0b974b108b27511a42c150983e268e1f7522ad678f0699848747a9e2f4a0cafc66704915a38966fbc76647678d907ca960533a5dc4de983167fafb7807e583dd5affcc2e14900295c6f396e768a32f106a4c636be78a6df96268216bc9410373fcc2528eb7984e2cb91ae62c3c65660dc477db3c3bfeadfacb214a055a48a1e9ed0c169ee54fcc6e7b24435cb53c3596e19bedbfef2c289ffb784f6fce18b9623253260e17aca5b3d810248ece6c51d810f3b44b1eb95225d5170cde0f3c9fda8ceefd9a287016c785576264f95ee961254bc371fed8671a7497456ce439d7318f21e539ce5940bd2fd73a350fc5d139cbe06bda568663a35488ceb7c62dadf3ee6d5810b5248abe447472b9c294a13c30144271a06e10b6a7f070df5bd7e804b13b1ab541c65de65dc5b85cf3199d7b13431095aff83de6939afc2d72d187597bf8214bf45f356591f7e513e7026322a20beed430966fbd3cbe4ec2c95b54d081c032f5e2ba930019857bb63e7c631668e3f607559b4ffffc1de6c957f687930f2900fb27123aaaf5f55a06844586cee94d10757' ] }
NOTE: public.players is inherited from 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);
just realized that unique constraint not working over inherited table
is there any solution or workaround for this problem(or whatever)?
pgcode?public.players <> public.users