
CREATE DATABASE test1 WITH TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'C' LC_CTYPE = 'C';

ALTER DATABASE test1 OWNER TO postgres;

\connect test1





CREATE TABLE loguser (
    luid integer NOT NULL,
    refid bigint NOT NULL,
    ip text NOT NULL,
    manager text
);

ALTER TABLE public.loguser OWNER TO postgres;

CREATE SEQUENCE loguser_luid_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

ALTER TABLE public.loguser_luid_seq OWNER TO postgres;

ALTER SEQUENCE loguser_luid_seq OWNED BY loguser.luid;

SELECT pg_catalog.setval('loguser_luid_seq', 2, true);

CREATE TABLE reference (
    refid integer NOT NULL,
    username text
);


ALTER TABLE public.reference OWNER TO postgres;

CREATE SEQUENCE reference_refid_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

ALTER TABLE public.reference_refid_seq OWNER TO postgres;

ALTER SEQUENCE reference_refid_seq OWNED BY reference.refid;

SELECT pg_catalog.setval('reference_refid_seq', 2, true);

ALTER TABLE ONLY loguser ALTER COLUMN luid SET DEFAULT nextval('loguser_luid_seq'::regclass);

ALTER TABLE ONLY reference ALTER COLUMN refid SET DEFAULT nextval('reference_refid_seq'::regclass);

INSERT INTO loguser VALUES (1, 1, '127.0.0.1', NULL);

INSERT INTO reference VALUES (1, 'user1');

ALTER TABLE ONLY loguser
    ADD CONSTRAINT pkey_loguser PRIMARY KEY (luid);

ALTER TABLE ONLY reference
    ADD CONSTRAINT reference_pkey PRIMARY KEY (refid);

ALTER TABLE ONLY loguser
    ADD CONSTRAINT fkey_refid FOREIGN KEY (refid) REFERENCES reference(refid);

select 1 from reference
where username = 'user1'
and (refid, '127.0.0.1', 'manager1') not in (select refid, ip, manager from loguser);

