Re: Can't fix Pgsql Insert Command Issue.

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