Thread

Re: Can't fix Pgsql Insert Command Issue.

Erik Wienhold <ewie@ewie.name> — 2024-09-24T07:59:00Z

On 2024-09-24 03:42 +0200, Mark Kostevych wrote:
> I tried to get the PID of the session but can't get the response. Same issue.

You need to run SELECT pg_backend_pid() separately before the INSERT.

> Screenshot_2.png<https://hwoodgroup-my.sharepoint.com/:i:/p/mkostevych/EZev3xZoyMRLkJf7RZTA5XwB8H1BcgtH09K9bjmcUx_YvA>
> 
> Create Script.txt<https://hwoodgroup-my.sharepoint.com/:t:/p/mkostevych/ESPFd2CzRRpAt7mzbaiJt1IB3k1z9C67vZAC-RU4njjhLA>
> Create Script(check_items).txt<https://hwoodgroup-my.sharepoint.com/:t:/p/mkostevych/Ef9QlV-ghRZHgCWZaczbSgsBuQGz8ISDxtvv7tQnCi8z6g>
> Cretae Script(employees).txt<https://hwoodgroup-my.sharepoint.com/:t:/p/mkostevych/ET7M1kBp8udBsxQrJH03XGwBUl0Sw9SCjgiIpImCoswKdA>
> 
> As you asked, I attached Create script for three tables(checks,
> check_items, employees). I think we don't have any issue on employees
> table.

Please attach scripts or quote them inline.  External links are not
ideal.

What do these trigger functions on table "checks" do?:

* public.update_universal_location_id()
* public.update_date_parts_checks()

For the archive:

    CREATE TABLE IF NOT EXISTS public.checks
    (
        id character varying COLLATE pg_catalog."default" NOT NULL,
        name character varying COLLATE pg_catalog."default",
        "number" bigint,
        sub_total numeric(8,2),
        tax_total numeric(8,2),
        total numeric(8,2),
        mandatory_tip_amount numeric(8,2),
        open_time timestamp with time zone,
        close_time timestamp with time zone,
        employee_name character varying COLLATE pg_catalog."default",
        employee_role_name character varying COLLATE pg_catalog."default",
        employee_id character varying COLLATE pg_catalog."default",
        employee character varying COLLATE pg_catalog."default",
        guest_count smallint,
        type character varying COLLATE pg_catalog."default",
        type_id smallint,
        taxed_type character varying COLLATE pg_catalog."default",
        table_name character varying COLLATE pg_catalog."default",
        location character varying COLLATE pg_catalog."default",
        zone character varying COLLATE pg_catalog."default",
        autograt_tax numeric(8,2),
        trading_day_id character varying COLLATE pg_catalog."default",
        trading_day date,
        updated_at timestamp with time zone,
        non_revenue_total bigint,
        outstanding_balance numeric(8,2),
        status character varying COLLATE pg_catalog."default",
        revenue_total numeric(8,2),
        comp_total numeric(8,2) DEFAULT 0,
        visible boolean DEFAULT 'true',
        void_total numeric(8,2) DEFAULT 0,
        reason_code character varying COLLATE pg_catalog."default",
        voidcomp_reason_text character varying COLLATE pg_catalog."default",
        voidcomp_type character varying COLLATE pg_catalog."default",
        voidcomp_value numeric,
        parent_category character varying COLLATE pg_catalog."default",
        category_name character varying COLLATE pg_catalog."default",
        month integer,
        day integer,
        year integer,
        universal_location_id integer,
        CONSTRAINT checks_pkey PRIMARY KEY (id),
        CONSTRAINT checks_employee_fkey FOREIGN KEY (employee)
            REFERENCES public.employees (airtable_id) MATCH SIMPLE
            ON UPDATE NO ACTION
            ON DELETE NO ACTION
            NOT VALID
    )
    TABLESPACE pg_default;

    ALTER TABLE IF EXISTS public.checks
        OWNER to postgres;

    GRANT SELECT ON TABLE public.checks TO hwood_read_only;

    GRANT ALL ON TABLE public.checks TO postgres;

    CREATE INDEX IF NOT EXISTS checks_location
        ON public.checks USING btree
        (location COLLATE pg_catalog."default" ASC NULLS LAST)
        TABLESPACE pg_default;

    CREATE INDEX IF NOT EXISTS idx_checks_location
        ON public.checks USING btree
        (location COLLATE pg_catalog."default" ASC NULLS LAST)
        TABLESPACE pg_default;

    CREATE INDEX IF NOT EXISTS idx_checks_trading_day
        ON public.checks USING btree
        (trading_day ASC NULLS LAST)
        TABLESPACE pg_default;

    CREATE INDEX IF NOT EXISTS idx_checks_trading_day_location
        ON public.checks USING btree
        (trading_day ASC NULLS LAST, location COLLATE pg_catalog."default" ASC NULLS LAST)
        TABLESPACE pg_default;

    CREATE TRIGGER set_universal_location_id_checks
        AFTER INSERT OR UPDATE
        ON public.checks
        FOR EACH ROW
        EXECUTE FUNCTION public.update_universal_location_id();

    CREATE TRIGGER update_date_part_checks_trigger
        BEFORE INSERT OR UPDATE
        ON public.checks
        FOR EACH ROW
        EXECUTE FUNCTION public.update_date_parts_checks();


    CREATE TABLE IF NOT EXISTS public.check_items
    (
        id character varying COLLATE pg_catalog."default" NOT NULL,
        check_id character varying COLLATE pg_catalog."default" NOT NULL,
        name character varying COLLATE pg_catalog."default",
        date timestamp with time zone,
        item_id character varying COLLATE pg_catalog."default" NOT NULL,
        quantity bigint,
        price numeric(8,2),
        pre_tax_price numeric(8,2),
        regular_price numeric(8,2),
        cost numeric(8,2),
        tax numeric(8,2),
        comp_total numeric(8,2),
        comp_tax numeric(8,2),
        parent_category character varying COLLATE pg_catalog."default",
        category character varying COLLATE pg_catalog."default",
        CONSTRAINT check_items_pkey PRIMARY KEY (id),
        CONSTRAINT check_item_fkey FOREIGN KEY (check_id)
            REFERENCES public.checks (id) MATCH SIMPLE
            ON UPDATE NO ACTION
            ON DELETE NO ACTION,
        CONSTRAINT item_fkey FOREIGN KEY (item_id)
            REFERENCES public.items (item_id) MATCH SIMPLE
            ON UPDATE NO ACTION
            ON DELETE NO ACTION
            NOT VALID
    )
    TABLESPACE pg_default;

    ALTER TABLE IF EXISTS public.check_items
        OWNER to postgres;

    GRANT SELECT ON TABLE public.check_items TO hwood_read_only;

    GRANT ALL ON TABLE public.check_items TO postgres;


    CREATE TABLE IF NOT EXISTS public.employees
    (
        pos_id integer NOT NULL,
        first character varying COLLATE pg_catalog."default",
        last character varying COLLATE pg_catalog."default",
        email character varying COLLATE pg_catalog."default",
        mobile character varying COLLATE pg_catalog."default",
        location character varying COLLATE pg_catalog."default",
        paycom_code character varying COLLATE pg_catalog."default",
        r365_code integer,
        role character varying COLLATE pg_catalog."default",
        reg_rate numeric(4,2),
        employee_id character varying COLLATE pg_catalog."default",
        airtable_id character varying COLLATE pg_catalog."default" NOT NULL,
        role_id numeric,
        "paycorIdProfileId" character varying COLLATE pg_catalog."default",
        active boolean,
        role_name character varying COLLATE pg_catalog."default",
        CONSTRAINT employees_pkey PRIMARY KEY (airtable_id)
    )
    TABLESPACE pg_default;

    ALTER TABLE IF EXISTS public.employees
        OWNER to postgres;

    GRANT SELECT ON TABLE public.employees TO hwood_read_only;

    GRANT ALL ON TABLE public.employees TO postgres;

    CREATE INDEX IF NOT EXISTS idx_employees_airtable_id
        ON public.employees USING btree
        (airtable_id COLLATE pg_catalog."default" ASC NULLS LAST)
        TABLESPACE pg_default;

    CREATE INDEX IF NOT EXISTS idx_employees_email
        ON public.employees USING btree
        (email COLLATE pg_catalog."default" ASC NULLS LAST)
        TABLESPACE pg_default;

-- 
Erik