Input validation
Have table that contains employee keys, paired up with work type keys (both foreign keys) and a 3rd column that you enter a billing rate in. Then I have a table where employees enter their work. I need to validate that the employee, work type pair exists, before allowing the new record to be inserted. Any thoughts as to good way to do this? -- "They that would give up essential liberty for temporary safety deserve neither liberty nor safety." -- Benjamin Franklin
Recomended front ends?
I am in the process of defining an application for a very small company that uses Postgresql for the backend DB. This DB will eventually run on a hosted machine. As you imagine all of the employees have Windows machines for their normal work asks. Frankly I am not very strong on Windows. so I am wondering what the consensus is for creating forms and reports? My first though is Libre Office as that is cross platform, and i can test on my development Linux machine. However, i am getting a bit of push-back from the user as he is having issues with installing Libre Office on his computer. he says it does not play well with MS Office. Also we seem to be having some bugs with Libre Office Base in early development. What is the community wisdom here? -- "They that would give up essential liberty for temporary safety deserve neither liberty nor safety." -- Benjamin Franklin
FW: Undelivered Mail Returned to Sender
I apologize for asking, what I suspect will turn out to be a newbie
question, but I have managed to get myself quite confused on this.
I am defining a view as follows
CREATE OR REPLACE view purchase_view as
select
project.proj_no ,
qty ,
mfg_part.mfg_part_no ,
mfg.name as m_name ,
mfg_part.descrip as description ,
(
SELECT
name
FROM
vendor
WHERE
bom_item.vendor_key =
(
SELECT
vendor_key
FROM
mfg_vendor_relationship
WHERE
bom_item.mfg_key = mfg_key
AND
prefered = TRUE
AND
bom_item.project_key = project_key
)
)
as v_name ,
/*
vendor.name as v_name ,
*/
cost_per_unit ,
costing_unit.unit,
need_date ,
order_date ,
recieved_date ,
po_no ,
po_line_item
from
bom_item
right join project on
project.project_key = bom_item.project_key
inner join mfg_part on
mfg_part.mfg_part_key = bom_item.mfg_part_key
inner join vendor on
vendor.vendor_key = bom_item.vendor_key
inner join costing_unit on
costing_unit.costing_unit_key = bom_item.costing_unit_key
inner join mfg on
mfg.mfg_key = bom_item.mfg_key
WHERE bom_item is NOT NULL
ORDER BY
project.proj_no ,
mfg_part
;
Most of the tables are pretty much simple key -> value relationships for
normalization. I can add the create statements to this thread if it adds
clarity.
The exception is:
CREATE TABLE mfg_vendor_relationship (
mfg_vendor_relationship_key_serial integer DEFAULT
nextval('mfg_vendor_relationship_key_serial')
PRIMARY KEY ,
mfg_key integer NOT NULL,
vendor_keyinteger NOT NULL,
project_key integer NOT NULL,
prefered boolean NOT NULL ,
modtime timestamptz DEFAULT current_timestamp ,
FOREIGN KEY (mfg_key) references mfg(mfg_key) ,
FOREIGN KEY (vendor_key) references vendor(vendor_key) ,
FOREIGN KEY (project_key) references project(project_key) ,
CONSTRAINT mfg_vendor_constraint
UNIQUE (
mfg_key ,
vendor_key ,
project_key
)
);
I am down to having a single row in the mfg_vendor_relationship as follows:
mfg_vendor_relationship_key_serial | mfg_key | vendor_key | project_key |
prefered |modtime
+-++-+--+---
164 | 1 | 1 | 2 |
t| 2019-08-10 14:21:04.896619-04
But trying to do a select * from this view returns:
ERROR: more than one row returned by a subquery used as an expression
Can someone please enlighten me as to the error of my ways?
--
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
-- Benjamin Franklin
- End forwarded message -
--
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
-- Benjamin Franklin
Probably a newbie question
Sorry, I got the list address wrong the first time, and when I corected it,
I forget to fix the subject line.
I apologize for asking, what I suspect will turn out to be a newbie
question, but I have managed to get myself quite confused on this.
I am defining a view as follows
CREATE OR REPLACE view purchase_view as
select
project.proj_no ,
qty ,
mfg_part.mfg_part_no ,
mfg.name as m_name ,
mfg_part.descrip as description ,
(
SELECT
name
FROM
vendor
WHERE
bom_item.vendor_key =
(
SELECT
vendor_key
FROM
mfg_vendor_relationship
WHERE
bom_item.mfg_key = mfg_key
AND
prefered = TRUE
AND
bom_item.project_key = project_key
)
)
as v_name ,
/*
vendor.name as v_name ,
*/
cost_per_unit ,
costing_unit.unit,
need_date ,
order_date ,
recieved_date ,
po_no ,
po_line_item
from
bom_item
right join project on
project.project_key = bom_item.project_key
inner join mfg_part on
mfg_part.mfg_part_key = bom_item.mfg_part_key
inner join vendor on
vendor.vendor_key = bom_item.vendor_key
inner join costing_unit on
costing_unit.costing_unit_key = bom_item.costing_unit_key
inner join mfg on
mfg.mfg_key = bom_item.mfg_key
WHERE bom_item is NOT NULL
ORDER BY
project.proj_no ,
mfg_part
;
Most of the tables are pretty much simple key -> value relationships for
normalization. I can add the create statements to this thread if it adds
clarity.
The exception is:
CREATE TABLE mfg_vendor_relationship (
mfg_vendor_relationship_key_serial integer DEFAULT
nextval('mfg_vendor_relationship_key_serial')
PRIMARY KEY ,
mfg_key integer NOT NULL,
vendor_keyinteger NOT NULL,
project_key integer NOT NULL,
prefered boolean NOT NULL ,
modtime timestamptz DEFAULT current_timestamp ,
FOREIGN KEY (mfg_key) references mfg(mfg_key) ,
FOREIGN KEY (vendor_key) references vendor(vendor_key) ,
FOREIGN KEY (project_key) references project(project_key) ,
CONSTRAINT mfg_vendor_constraint
UNIQUE (
mfg_key ,
vendor_key ,
project_key
)
);
I am down to having a single row in the mfg_vendor_relationship as follows:
mfg_vendor_relationship_key_serial | mfg_key | vendor_key | project_key |
prefered |modtime
+-++-+--+---
164 | 1 | 1 | 2 |
t| 2019-08-10 14:21:04.896619-04
But trying to do a select * from this view returns:
ERROR: more than one row returned by a subquery used as an expression
Can someone please enlighten me as to the error of my ways?
--
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
-- Benjamin Franklin
- End forwarded message -
--
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
-- Benjamin Franklin
--
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
-- Benjamin Franklin
Re: Probably a newbie question
On Sat, Aug 10, 2019 at 02:57:14PM -0400, stan wrote: > Sorry, I got the list address wrong the first time, and when I corrected it, > I forget to fix the subject line. > > I apologize for asking, what I suspect will turn out to be a newbie > question, but I have managed to get myself quite confused on this. > > I am defining a view as follows > > > CREATE OR REPLACE view purchase_view as > select > project.proj_no , > qty , > mfg_part.mfg_part_no , > mfg.name as m_name , > mfg_part.descrip as description , > ( > SELECT > name > FROM > vendor > WHERE > bom_item.vendor_key = > ( > SELECT > vendor_key > FROM > mfg_vendor_relationship > WHERE > bom_item.mfg_key = mfg_key > AND > prefered = TRUE > AND > bom_item.project_key = project_key > > ) > ) > as v_name , > /* > vendor.name as v_name , > */ > cost_per_unit , > costing_unit.unit, > need_date , > order_date , > recieved_date , > po_no , > po_line_item > from > bom_item > right join project on > project.project_key = bom_item.project_key > inner join mfg_part on > mfg_part.mfg_part_key = bom_item.mfg_part_key > inner join vendor on > vendor.vendor_key = bom_item.vendor_key > inner join costing_unit on > costing_unit.costing_unit_key = bom_item.costing_unit_key > inner join mfg on > mfg.mfg_key = bom_item.mfg_key > WHERE bom_item is NOT NULL > ORDER BY > project.proj_no , > mfg_part > ; > Thanks to the kind, bright people on this list, I have solved my problem. The basic issue was that my from clause was on the wrong table. Thanks to everyone who spent their time helping me out on this! -- "They that would give up essential liberty for temporary safety deserve neither liberty nor safety." -- Benjamin Franklin
A question about trigger fucntion syntax
Good morning (at least is is morning East Coast USA time). I am trying to create a function to validate an attempted record insert, and I am having a hard time coming up with syntax that is acceptable. Here is the scenario I have a table that has (among other items) employee_key and work_type_key (both integer FOREIGN KEYS). Then I have another table that has the following structure: CREATE TABLE permitted_work ( employee_key integer , work_type_key integer , permit boolean DEFAULT FALSE NOT NULL , modtime timestamptz DEFAULT current_timestamp , FOREIGN KEY (employee_key) references employee(employee_key) , FOREIGN KEY (work_type_key) references work_type(work_type_key) , CONSTRAINT permit_constraint UNIQUE (employee_key , work_type_key) ); What I think I need to do is create a function that is fired on an insert, or update to the 1st table that verifies that there is an existing row in permitted_work that matches the combination of employee_key AND work_type_key AND has the value TRUE in the permit column. First does this seem to be a good way to achieve this constraint? If not, I am open to suggestions as to other ways to address this requirement. If it does, could someone give me a little help with th syntax of the needed function ?? Thanks for your time helping me with this. -- "They that would give up essential liberty for temporary safety deserve neither liberty nor safety." -- Benjamin Franklin
Re: A question about trigger fucntion syntax
On Sun, Aug 11, 2019 at 08:56:13AM -0400, stan wrote: > Good morning (at least is is morning East Coast USA time). > > I am trying to create a function to validate an attempted record > insert, and I am having a hard time coming up with syntax that > is acceptable. > > Here is the scenario I have a table that has (among other items) employee_key > and work_type_key (both integer FOREIGN KEYS). Then I have another table > that has the following structure: > > CREATE TABLE permitted_work ( > employee_key integer , > work_type_key integer , > permit boolean DEFAULT FALSE NOT NULL , > modtime timestamptz DEFAULT current_timestamp , > FOREIGN KEY (employee_key) references > employee(employee_key) , > FOREIGN KEY (work_type_key) references > work_type(work_type_key) , > CONSTRAINT permit_constraint UNIQUE > (employee_key , work_type_key) > ); > > What I think I need to do is create a function that is fired on an insert, > or update to the 1st table that verifies that there is an existing row in > permitted_work that matches the combination of employee_key AND > work_type_key AND has the value TRUE in the permit column. > > First does this seem to be a good way to achieve this constraint? If not, > I am open to suggestions as to other ways to address this requirement. > > If it does, could someone give me a little help with th syntax of the > needed function ?? > > Thanks for your time helping me with this. BTW, here is what I Ave tried. CREATE OR REPLACE FUNCTION check_permission() RETURNS trigger AS $BODY$ BEGIN SELECT permit FROM permitted_work WHERE NEW.employee_key = OLD.employee_key AND NEW.work_type_key = OLD.work_type_key RETURN permit; END; $BODY$ LANGUAGE PLPGSQL; and when I try to insert it I get a syntax error at the RETURN -- "They that would give up essential liberty for temporary safety deserve neither liberty nor safety." -- Benjamin Franklin
Re: A question about trigger fucntion syntax
I trimmed this thread quite a bit. Thanks to the help I have received, I am making progress. I have looked a the recommended documentation, and I believe I am close. I have utilized the "debugging printf" capability here. Here is where I am. I have the following function create. DROP TRIGGER validate_task_trig ON task_instance ; DROP FUNCTION check_permission() ; CREATE FUNCTION check_permission() RETURNS trigger AS $$ DECLARE _permit boolean; BEGIN SELECT permit INTO _permit FROM permitted_work WHERE NEW.employee_key = OLD.employee_key AND NEW.work_type_key = OLD.work_type_key; RAISE NOTICE 'New employee_id % NEW.work_type_key % _permit = %', NEW.employee_key , NEW.work_type_key , _permit ; if NOT _permit THEN RAISE NOTICE 'No permission record'; RAISE EXCEPTION 'No permission record'; ELSE RAISE NOTICE 'Found Permission Record'; END IF; if _permit = FALSE THEN RAISE NOTICE 'Permission Denied'; ELSE RAISE NOTICE 'Permission Granted'; END IF; return NEW; END; $$ LANGUAGE PLPGSQL; CREATE TRIGGER validate_task_trig BEFORE INSERT OR UPDATE ON task_instance FOR EACH ROW EXECUTE FUNCTION check_permission(); Now the issues, currently seems to be that nothing is getting assigned to _permit. Here is the output of a run with 0 records in the permitted_work table. NOTICE: New employee_id 1 NEW.work_type_key 8 _permit = NOTICE: Found Permission Record NOTICE: Permission Granted INSERT 0 1 so it appears that nothing is getting assigned to _permit. Also should I be checking for _permit as NOT NULL in the first if clause? Thanks for all the had holding on this. Brand new application for me. -- "They that would give up essential liberty for temporary safety deserve neither liberty nor safety." -- Benjamin Franklin
[SOLVED] Re: A question about trigger fucntion syntax
On Sun, Aug 11, 2019 at 05:31:13PM -0400, stan wrote: > I trimmed this thread quite a bit. > > Thanks to the help I have received, I am making progress. I have looked a the > recommended documentation, and I believe I am close. I have utilized the > "debugging printf" capability here. Here is where I am. I have the following > function create. > > > DROP TRIGGER validate_task_trig ON task_instance ; > > DROP FUNCTION check_permission() ; > > > CREATE FUNCTION check_permission() > RETURNS trigger AS $$ > DECLARE _permit boolean; > BEGIN > SELECT > permit INTO _permit > FROM > permitted_work > WHERE > NEW.employee_key = OLD.employee_key > AND > NEW.work_type_key = OLD.work_type_key; > RAISE NOTICE 'New employee_id % NEW.work_type_key % _permit = %', > NEW.employee_key , > NEW.work_type_key , > _permit ; > if NOT _permit THEN > RAISE NOTICE 'No permission record'; > RAISE EXCEPTION 'No permission record'; > ELSE > RAISE NOTICE 'Found Permission Record'; > END IF; > if _permit = FALSE THEN > RAISE NOTICE 'Permission Denied'; > ELSE > RAISE NOTICE 'Permission Granted'; > END IF; > > return NEW; > END; > $$ > LANGUAGE PLPGSQL; > > CREATE TRIGGER validate_task_trig BEFORE INSERT OR UPDATE ON task_instance > FOR EACH ROW EXECUTE FUNCTION check_permission(); > > > Now the issues, currently seems to be that nothing is getting assigned to > _permit. Here is the output of a run with 0 records in the permitted_work > table. > > NOTICE: New employee_id 1 NEW.work_type_key 8 _permit = > NOTICE: Found Permission Record > NOTICE: Permission Granted > INSERT 0 1 > > so it appears that nothing is getting assigned to _permit. Also should I be > checking for _permit as NOT NULL in the first if clause? > > Thanks for all the had holding on this. Brand new application for me. For the archive. I have this working, Here is the function that I woulnd up with. DROP TRIGGER validate_task_trig ON task_instance ; DROP FUNCTION check_permission() ; CREATE FUNCTION check_permission() RETURNS trigger AS $$ DECLARE _permit boolean; BEGIN SELECT permit INTO _permit FROM permitted_work WHERE NEW.employee_key = permitted_work.employee_key AND NEW.work_type_key = permitted_work.work_type_key; if _permit IS NULL THEN RAISE EXCEPTION 'No permission record'; ELSE END IF; if _permit = FALSE THEN RAISE EXCEPTION 'Permisson Denied'; END IF; return NEW; END; $$ LANGUAGE PLPGSQL; CREATE TRIGGER validate_task_trig BEFORE INSERT OR UPDATE ON task_instance FOR EACH ROW EXECUTE FUNCTION check_permission(); Thanks to all the people that were instrumental in helping me learn triggers and functions. -- "They that would give up essential liberty for temporary safety deserve neither liberty nor safety." -- Benjamin Franklin
constrain with MATCH full and NULL values in referenced table
I am creating a table that has 2 values in it which are keys pointing to 2 other tables. I need for the UNIQUE combination of these 2 keys to exist in a fourth table. It has been recommended to use a foreign key constraint with the MATCH FULL parameter. Here is my question, does this deal with NULLS in the 4th table? I am concerned that this constraint might fail to reject an entry if one, or both of the 2 key values being inserted in the table are NULLS,. -- "They that would give up essential liberty for temporary safety deserve neither liberty nor safety." -- Benjamin Franklin
Subject: Re: constrain with MATCH full and NULL values in referenced table
Cc: pgsql-general.lists.postgresql@panix.com
Subject: Re: constrain with MATCH full and NULL values in referenced table
User-Agent: Mutt/1.12.1 (2019-06-15)
X-Editor: gVim
On Mon, Aug 12, 2019 at 06:22:54PM +0200, Francisco Olarte wrote:
> Stan:
>
> On Mon, Aug 12, 2019 at 5:11 PM stan wrote:
> >
> > I am creating a table that has 2 values in it which are keys pointing to 2
> > other tables. I need for the UNIQUE combination of these 2 keys to exist in
> > a fourth table. It has been recommended to use a foreign key constraint with
> > the MATCH FULL parameter.
> >
> > Here is my question, does this deal with NULLS in the 4th table? I am
> > concerned that this constraint might fail to reject an entry if one, or both
> > of the 2 key values being inserted in the table are NULLS,.
>
> If you have:
>
> Table TA (a: PK)
> Table TB (b: PK)
> Table TAB( a, b,) PK(A,B), FK(a ref TA), FK(b ref TB)
> Table FOURTH(a,b,...) FK((A,B) ref TAB mach full)
>
> Note TAB cannot have nulls in A,B as it is the PK.
>
> And you insert (null, null) in FOURTH it will be treated as in single
> column, allowed by the fk ( but you may have non null constraints on
> either a or b).
> If you try to insert (a1, null) or (null, b1), it will ber rejected,
> MATCH FULL does not allow null/non-null mix.
>
> OTOH, if you use MATCH SIMPLE the partial-null cases will be not
> checked at all, as if they where not null. As stated in the docs, you
> can use extra single column FK in a and/or b to get them checked in
> TA/TB, and also you can put non-null constraints on either on them.
>
> The exact combo depends on what you are trying to model, which gives
> you what you want. I.e., say I want to:
> 1.- check a,b combos.
> 2.- Allow (a,null) but have it checked against ta.
> 3.- Forbid (null,b)
> 4.- Aloow (null, null)
> You can use MATCH simple FK(a,b) against TAB for (1,4), single column
> FK(a) against TA for(2) and a check constraint (A is not null OR B is
> null , If I'm not confused ) for (3,4).
> ( Note you do not have to check b against tb, because if b is present,
> a is present, a,b is checked against TAB and TAB.b is checked against
> TB ).
>
> (match simple checks 1 and allows 2,3,4, FK(a) checks 2, and the check
> constraint forbids 3)
>
> The DB deals with nulls in many way, you just have to enumerate your
> conditions and elaborate on that.
> Note in this case it FAILS to reject an entry if b is null, because I
> dessigned it that way, but DOES REJECT if a is null and B is not.
>
Thank you.
Testing seems to verify that I have this correct.
I thought I would include what I came up with, so it gets in the archive.
Some fields eliminated for clarity.
The task_instance table is the one the original question was in reference
to.
CREATE TABLE employee (
employee_keyinteger DEFAULT nextval('employee_key_serial')
PRIMARY KEY ,
id varchar(5) NOT NULL UNIQUE ,
first_name varchar NOT NULL,
);
CREATE TABLE work_type (
work_type_key integer DEFAULT nextval('work_type_key_serial')
PRIMARY KEY ,
type smallint UNIQUE ,
descripvarchar UNIQUE ,
modtimetimestamptz DEFAULT current_timestamp
);
CREATE TABLE rate (
employee_key integer NOT NULL,
work_type_key integer NOT NULL,
rate numeric (5, 2) NOT NULL,
descripvarchar ,
modtimetimestamptz DEFAULT current_timestamp ,
FOREIGN KEY (employee_key) references employee(employee_key) ,
FOREIGN KEY (work_type_key) references work_type(work_type_key) ,
CONSTRAINT rate_constraint UNIQUE (employee_key , work_type_key)
);
CREATE TABLE task_instance (
task_instance integer DEFAULT nextval('task_instance_key_serial')
PRIMARY KEY ,
project_keyinteger NOT NULL ,
employee_key integer NOT NULL ,
work_type_key integer NOT NULL ,
hours numeric (5, 2) NOT NULL ,
work_start timestamptz ,
work_end timestamptz ,
modtimetimestamptz DEFAULT current_timestamp ,
descripvarchar ,
FOREIGN KEY (employee_key) references employee(employee_key) ,
FOREIGN KEY (project_key) references project(project_key) ,
FOREIGN KEY (work_type_key) references work_type(work_type_key) ,
FOREIGN KEY (work_type_key , employee_key) REFERENCES rate (work_type_key ,
employee_key) MATCH FULL
);
--
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
-- Benjamin Franklin
- End forwarded message -
--
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
-- Benjamin Franklin
Re: Subject: Re: constrain with MATCH full and NULL values in referenced table
On Mon, Aug 12, 2019 at 10:16:41AM -0700, Adrian Klaver wrote:
> On 8/12/19 10:06 AM, stan wrote:
> > Cc: pgsql-general.lists.postgresql@panix.com
> > Subject: Re: constrain with MATCH full and NULL values in referenced table
> > User-Agent: Mutt/1.12.1 (2019-06-15)
> > X-Editor: gVim
> >
> > On Mon, Aug 12, 2019 at 06:22:54PM +0200, Francisco Olarte wrote:
> > > Stan:
> > >
> > > On Mon, Aug 12, 2019 at 5:11 PM stan wrote:
> > > >
> > > > I am creating a table that has 2 values in it which are keys pointing
> > > > to 2
> > > > other tables. I need for the UNIQUE combination of these 2 keys to
> > > > exist in
> > > > a fourth table. It has been recommended to use a foreign key constraint
> > > > with
> > > > the MATCH FULL parameter.
> > > >
> > > > Here is my question, does this deal with NULLS in the 4th table? I am
> > > > concerned that this constraint might fail to reject an entry if one, or
> > > > both
> > > > of the 2 key values being inserted in the table are NULLS,.
> > >
> > > If you have:
> > >
> > > Table TA (a: PK)
> > > Table TB (b: PK)
> > > Table TAB( a, b,) PK(A,B), FK(a ref TA), FK(b ref TB)
> > > Table FOURTH(a,b,...) FK((A,B) ref TAB mach full)
> > >
> > > Note TAB cannot have nulls in A,B as it is the PK.
> > >
> > > And you insert (null, null) in FOURTH it will be treated as in single
> > > column, allowed by the fk ( but you may have non null constraints on
> > > either a or b).
> > > If you try to insert (a1, null) or (null, b1), it will ber rejected,
> > > MATCH FULL does not allow null/non-null mix.
> > >
> > > OTOH, if you use MATCH SIMPLE the partial-null cases will be not
> > > checked at all, as if they where not null. As stated in the docs, you
> > > can use extra single column FK in a and/or b to get them checked in
> > > TA/TB, and also you can put non-null constraints on either on them.
> > >
> > > The exact combo depends on what you are trying to model, which gives
> > > you what you want. I.e., say I want to:
> > > 1.- check a,b combos.
> > > 2.- Allow (a,null) but have it checked against ta.
> > > 3.- Forbid (null,b)
> > > 4.- Aloow (null, null)
> > > You can use MATCH simple FK(a,b) against TAB for (1,4), single column
> > > FK(a) against TA for(2) and a check constraint (A is not null OR B is
> > > null , If I'm not confused ) for (3,4).
> > > ( Note you do not have to check b against tb, because if b is present,
> > > a is present, a,b is checked against TAB and TAB.b is checked against
> > > TB ).
> > >
> > > (match simple checks 1 and allows 2,3,4, FK(a) checks 2, and the check
> > > constraint forbids 3)
> > >
> > > The DB deals with nulls in many way, you just have to enumerate your
> > > conditions and elaborate on that.
> > > Note in this case it FAILS to reject an entry if b is null, because I
> > > dessigned it that way, but DOES REJECT if a is null and B is not.
> > >
> >
> > Thank you.
> >
> > Testing seems to verify that I have this correct.
> >
> > I thought I would include what I came up with, so it gets in the archive.
> > Some fields eliminated for clarity.
> >
> > The task_instance table is the one the original question was in reference
> > to.
> >
> > CREATE TABLE employee (
> > employee_keyinteger DEFAULT nextval('employee_key_serial')
> > PRIMARY KEY ,
> > id varchar(5) NOT NULL UNIQUE ,
> > first_name varchar NOT NULL,
> > );
> >
> > CREATE TABLE work_type (
> > work_type_key integer DEFAULT nextval('work_type_key_serial')
> > PRIMARY KEY ,
> > type smallint UNIQUE ,
> > descripvarchar UNIQUE ,
> > modtimetimestamptz DEFAULT current_timestamp
> > );
> >
> > CREATE TABLE rate (
> > employee_key integer NOT NULL,
> > work_type_key integer NOT NULL,
> > rate numeric (5, 2) NOT NULL,
> > descripvarchar ,
> > modtimetimestamptz DEFAULT current_timestamp ,
> > FOREIGN KEY (employee_key) references employee(employee_key) ,
> > FOREIGN KEY (work_type_key) references work_type(work_typ
Re: Subject: Re: constrain with MATCH full and NULL values in referenced table
On Mon, Aug 12, 2019 at 10:40:20AM -0700, Adrian Klaver wrote:
> On 8/12/19 10:30 AM, stan wrote:
> > > > The task_instance table is the one the original question was in
> > > > reference
> > > > to.
> > > >
> > > > CREATE TABLE employee (
> > > > employee_keyinteger DEFAULT
> > > > nextval('employee_key_serial')
> > > > PRIMARY KEY ,
> > > > id varchar(5) NOT NULL UNIQUE ,
> > > > first_name varchar NOT NULL,
> > > > );
> > > >
> > > > CREATE TABLE work_type (
> > > > work_type_key integer DEFAULT nextval('work_type_key_serial')
> > > > PRIMARY KEY ,
> > > > type smallint UNIQUE ,
> > > > descripvarchar UNIQUE ,
> > > > modtimetimestamptz DEFAULT current_timestamp
> > > > );
> > > >
> > > > CREATE TABLE rate (
> > > > employee_key integer NOT NULL,
> > > > work_type_key integer NOT NULL,
> > > > rate numeric (5, 2) NOT NULL,
> > > > descripvarchar ,
> > > > modtimetimestamptz DEFAULT current_timestamp ,
> > > > FOREIGN KEY (employee_key) references employee(employee_key) ,
> > > > FOREIGN KEY (work_type_key) references work_type(work_type_key) ,
> > > > CONSTRAINT rate_constraint UNIQUE (employee_key , work_type_key)
> > > > );
> > > >
> > > >
> > > > CREATE TABLE task_instance (
> > > > task_instance integer DEFAULT
> > > > nextval('task_instance_key_serial')
> > > > PRIMARY KEY ,
> > > > project_keyinteger NOT NULL ,
> > > > employee_key integer NOT NULL ,
> > > > work_type_key integer NOT NULL ,
> > > > hoursnumeric (5, 2) NOT NULL ,
> > > > work_start timestamptz ,
> > > > work_end timestamptz ,
> > > > modtimetimestamptz DEFAULT current_timestamp ,
> > > > descripvarchar ,
> > >
> > > Aren't the marked ones below redundant?:
> > >
> > > > FOREIGN KEY (employee_key) references employee(employee_key) ,
> > > ^^
> > > > FOREIGN KEY (project_key) references project(project_key) ,
> > > > FOREIGN KEY (work_type_key) references work_type(work_type_key) ,
> > > ^
> > > > FOREIGN KEY (work_type_key , employee_key) REFERENCES rate
> > > > (work_type_key , employee_key) MATCH FULL
> > >
> > > They are covered above.
> > >
> > > > );
> >
> > OK, looks like learning time for me, again. Which is alwasy a good thing. My
> > thought here was that I needed to specify these on the dreivative table
> > (task_instnce). Are you teaching me that, since these constraints exist on
> > the tables that rate is derived from, I do not need to specify thmm for the
> > rate table?
>
> If I'm following what you are trying to do then:
>
> 1) task_instance is dependent on the information in rate being present for a
> given combination of (work_type_key , employee_key).
>
> 2) If 1) is correct then you cannot create a record in task_instance until a
> record exists in rate.
>
> 3) 2) means you have already established a relationship to employee and
> work_type via rate.
>
Ah subtle.
Makes sense.
In case it is not glaringly obvious to the casual observer, i am just
returning to the database world, after having spent many years in a totaly
unrelated on (power systens for large indutrials, if you are curios).
--
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
-- Benjamin Franklin
"Locking rows"
I have a customer requirement/desire. The system is (among other things) essentially a employee time sheet. The manager wants for an employee to not be able to modify a given row in the table they enter time into once it is committed. I personally see issues with this, but I am willing to try to give him what he wants. If it creates issues we can sort them out, once he sees the issues. The only way I see to do this is to add a column (call it lock). I will then set this up as a default entry with a value of TRUE. The form the employee uses to enter this will, of course, not display this column. The I will create a function that on UPDATE, checks for the presence of the 1 in this row, and rejects the update. Then I will give the manager a form where he can set this flag to FALSE. Looks ugly, and convulsed to me. Is here a more "database native" way to handle this? -- "They that would give up essential liberty for temporary safety deserve neither liberty nor safety." -- Benjamin Franklin
A GROUP BY question
I am trying to write, what is for me, a fairly complex query. It uses JOINS, and also GROUP BY. I have this working with the exception of adding the GROUP BY clause. Is there some reason I cannot add a GROUP BY function to a JOIN? Here is what I have: CREATE OR REPLACE view tasks_view as select project.proj_no , employee.first_name , employee.last_name , employee.id , task_instance.hours , work_type.type, work_type.descrip, rate.rate, employee.hourly_rate , rate.rate * task_instance.hours as result , SUM(rate.rate * task_instance.hours) ^^ from task_instance GROUP BY ^^ project.project_key ^^ join rate on rate.employee_key = task_instance.employee_key AND rate.work_type_key = task_instance.work_type_key inner join employee on rate.employee_key = employee.employee_key inner join work_type on rate.work_type_key = work_type.work_type_key inner join project on project.project_key = task_instance.project_key ORDER BY project.proj_no , employee.id ; The underlined lines are what I added, and I get a syntax error pointing to the join. This works fine without the added lines. -- "They that would give up essential liberty for temporary safety deserve neither liberty nor safety." -- Benjamin Franklin
Re: A GROUP BY question
On Tue, Aug 13, 2019 at 05:54:03AM -0400, stan wrote: > I am trying to write, what is for me, a fairly complex query. It uses JOINS, > and also GROUP BY. I have this working with the exception of adding the > GROUP BY clause. > > Is there some reason I cannot add a GROUP BY function to a JOIN? > > Here is what I have: > > > CREATE OR REPLACE view tasks_view as > select > project.proj_no , > employee.first_name , > employee.last_name , > employee.id , > task_instance.hours , > work_type.type, > work_type.descrip, > rate.rate, > employee.hourly_rate , > rate.rate * task_instance.hours as result , > SUM(rate.rate * task_instance.hours) > ^^ > from > task_instance > GROUP BY > ^^ > project.project_key > ^^ > join rate on > rate.employee_key = task_instance.employee_key > AND > rate.work_type_key = task_instance.work_type_key > inner join employee on > rate.employee_key = employee.employee_key > inner join work_type on > rate.work_type_key = work_type.work_type_key > inner join project on > project.project_key = task_instance.project_key > ORDER BY > project.proj_no , > employee.id > ; > Maybe I have a basic misunderstanding. What I am trying to get is a total cost for each project. This would be calculated by multiplying rate and hours for each row, on a per project base, and then summing all of th products of this multiplication. I did get the following to be accepted from a syntax basis, but it returns rows with the product for each row, and something in the sum column which is the same. DROP view tasks_view ; CREATE OR REPLACE view tasks_view as select project.proj_no , employee.first_name , employee.last_name , employee.id , task_instance.hours , work_type.type, work_type.descrip, rate.rate, employee.hourly_rate , rate.rate * task_instance.hours as result , SUM (rate.rate * task_instance.hours) from task_instance join rate on rate.employee_key = task_instance.employee_key AND rate.work_type_key = task_instance.work_type_key inner join employee on rate.employee_key = employee.employee_key inner join work_type on rate.work_type_key = work_type.work_type_key inner join project on project.project_key = task_instance.project_key GROUP BY project.project_key , employee.first_name , employee.last_name , employee.id , task_instance.hours , work_type.type, work_type.descrip, rate.rate, employee.hourly_rate ORDER BY project.proj_no ; -- "They that would give up essential liberty for temporary safety deserve neither liberty nor safety." -- Benjamin Franklin
Variable constants ?
I need to put a few bossiness constants, such as a labor rate multiplier in an application. I am adverse to hard coding these things. The best plan i have come up with so far is to store them in a table, which would have only 1 row, and a column for each needed constant. Anyone have a better way to do this? Failing a better way is there some way I can limit this table to only allow one row to exist? -- "They that would give up essential liberty for temporary safety deserve neither liberty nor safety." -- Benjamin Franklin
A 3 table join question
First let me say a huge THANK YOU to all the helpful people that hanging out on this. I am changing from one type of work, going back to some database work for a project, as my old job was eliminated. I have made great progress on this, thanks to the time and effort of lots of folks from this list. Now, here is my latest stumbling block. I have three "data streams" that all contribute to the total costs of a project: * labor cost * material cost * expense report cost I have a view that summarizes the burdened cost from each of these 3 streams, and i am trying to create a view that shows the total project cost. Here is the test data from each of the 3 streams: stan=> select * from labor_cost_sum_view ; proj_no | labor_bill_sum | labor_cost_sum -++ 45 | 10810. | 3133.1750 764 | 8712. | 810.7500 789 | 46335.5400 | 7015.5750 (3 rows) stan=> select * from material_cost_sum_view ; proj_no | mtrl_cost -+--- 45 | 5394.6800 764 | 7249.4800 7456 | 4007.3000 (3 rows) stan=> select * from expense_report_cost_sum_view ; proj_no | incured_sum | burdened_cost -+-+--- 45 | 2564.98 | 2564.98 7456 | 1747.11 | 1747.11 (2 rows) And here is the clause for creating the summary table that I presently have: DROP VIEW overall_cost_sum_view ; CREATE view overall_cost_sum_view as select material_cost_sum_view.proj_no as l_proj_vo , labor_cost_sum_view.proj_no as m_proj_vo , expense_report_cost_sum_view.proj_no as x_proj_vo , cast (labor_cost_sum_view.labor_cost_sum as money) as l_burdened_cost, cast (material_cost_sum_view.mtrl_cost as money)as m_burdened_cost, cast (expense_report_cost_sum_view.burdened_cost as money)as x_burdened_cost , cast ( coalesce( labor_cost_sum_view.labor_cost_sum, 0) + coalesce(material_cost_sum_view.mtrl_cost, 0) + coalesce(expense_report_cost_sum_view.burdened_cost, 0) as money) as ttl_cost from labor_cost_sum_view full joinmaterial_cost_sum_view on material_cost_sum_view.proj_no = labor_cost_sum_view.proj_no full joinexpense_report_cost_sum_view on expense_report_cost_sum_view.proj_no = labor_cost_sum_view.proj_no ; Which results in the following: stan=> select * from overall_cost_sum_view ; ---+---+---+-+-+ -+ 45 |45 |45 | $3,133.18 | $5,394.68 | $ 2,564.98 | $11,092.84 764 | 764 | | $810.75 | $7,249.48 | | $8,060.23 | 789 | | $7,015.58 | | | $7,015.58 7456 | | | | $4,007.30 | | $4,007.30 | | 7456 | | | $ 1,747.11 | $1,747.11 (5 rows) As you can see this statement seems to work correctly on the join of the labor and material costs, but fails when I add the expense report stream. What am I doing wrong here? -- "They that would give up essential liberty for temporary safety deserve neither liberty nor safety." -- Benjamin Franklin
Re: A 3 table join question
On Sat, Aug 17, 2019 at 12:24:31AM +1000, rob stone wrote: > Hello, > > On Fri, 2019-08-16 at 07:39 -0400, stan wrote: > > First let me say a huge THANK YOU to all the helpful people that > > hanging out > > on this. > > > > I am changing from one type of work, going back to some database work > > for a > > project, as my old job was eliminated. I have made great progress on > > this, > > thanks to the time and effort of lots of folks from this list. > > > > Now, here is my latest stumbling block. I have three "data streams" > > that all > > contribute to the total costs of a project: > > > > * labor cost > > * material cost > > * expense report cost > > > > I have a view that summarizes the burdened cost from each of these 3 > > streams, and i am trying to create a view that shows the total > > project cost. > > > > Here is the test data from each of the 3 streams: > > > > stan=> select * from labor_cost_sum_view ; > > proj_no | labor_bill_sum | labor_cost_sum > > -+----+ > >45 | 10810. | 3133.1750 > > 764 | 8712. | 810.7500 > > 789 | 46335.5400 | 7015.5750 > > (3 rows) > > > > stan=> select * from material_cost_sum_view ; > > proj_no | mtrl_cost > > -+--- > >45 | 5394.6800 > > 764 | 7249.4800 > > 7456 | 4007.3000 > > (3 rows) > > > > stan=> select * from expense_report_cost_sum_view ; > > proj_no | incured_sum | burdened_cost > > -+-+--- > >45 | 2564.98 | 2564.98 > > 7456 | 1747.11 | 1747.11 > > (2 rows) > > > > And here is the clause for creating the summary table that I > > presently have: > > > > > > DROP VIEW overall_cost_sum_view ; > > > > CREATE view overall_cost_sum_view as > > select > > material_cost_sum_view.proj_no as l_proj_vo , > > labor_cost_sum_view.proj_no as m_proj_vo , > > expense_report_cost_sum_view.proj_no as x_proj_vo , > > cast (labor_cost_sum_view.labor_cost_sum as money) as > > l_burdened_cost, > > cast (material_cost_sum_view.mtrl_cost as money)as > > m_burdened_cost, > > cast (expense_report_cost_sum_view.burdened_cost as money)as > > x_burdened_cost , > > cast ( > > coalesce( labor_cost_sum_view.labor_cost_sum, > > 0) > > + > > coalesce(material_cost_sum_view.mtrl_cost, 0) > > + > > coalesce(expense_report_cost_sum_view.burdene > > d_cost, 0) > > as money) as ttl_cost > > from > > labor_cost_sum_view > > full joinmaterial_cost_sum_view on > > material_cost_sum_view.proj_no = labor_cost_sum_view.proj_no > > full joinexpense_report_cost_sum_view on > > expense_report_cost_sum_view.proj_no = > > labor_cost_sum_view.proj_no > > ; > > > > Which results in the following: > > > > stan=> select * from overall_cost_sum_view ; > > ---+---+---+-+--- > > --+ -+ > > 45 |45 |45 | $3,133.18 > > | $5,394.68 | $ 2,564.98 | $11,092.84 > >764 | 764 | | $810.75 > > | $7,249.48 | | $8,060.23 > >| 789 | | $7,015.58 > > | | | $7,015.58 > > 7456 > > | | | | $4,007.30 > > | | $4,007.30 > >| | 7456 > > | | | $ 1,747.11 | $1,747.11 > > (5 rows) > > > > > > As you can see this statement seems to work correctly on the join of > > the > > labor and material costs, but fails when I add the expense report > > stream. > > > > What am I doing wrong here? > > > > > Your view assumes that all three "streams" contain all the proj_no's > whereas your test data for expense_report_cost_sum_view has no proj_no > = 764. > > How do you know which of the three "streams" contains all proj_no's? > > Maybe you should consider the crosstab code so you end up with > something like this;- > > proj_no | Labour | Material | Expenses > > 45
Re: A 3 table join question
On Fri, Aug 16, 2019 at 12:30:33PM -0700, Ken Tanzer wrote: > On Fri, Aug 16, 2019 at 7:24 AM rob stone wrote: > > > Hello, > > > > On Fri, 2019-08-16 at 07:39 -0400, stan wrote: > > > What am I doing wrong here? > > > > > > > > > Your view assumes that all three "streams" contain all the proj_no's > > whereas your test data for expense_report_cost_sum_view has no proj_no > > = 764. > > > > > Hi. I'm probably missing something, but it seems simpler to either join > with USING, or by COALESCEing the two ID fields in left part of the JOIN > clause (COALESCE(t1.proj_no,t2.proj_no)=t3.proj_no). > > Cheers, > Ken > > CREATE TEMP TABLE t1 (id int, t1_val TEXT); > INSERT INTO t1 VALUES (2,'T1_2'); > INSERT INTO t1 VALUES (5,'T1_5'); > INSERT INTO t1 VALUES (7,'T1_7'); > INSERT INTO t1 VALUES (10,'T1_10'); > > CREATE TEMP TABLE t2 (id int, t2_val TEXT); > INSERT INTO t2 VALUES (3,'T2_3'); > INSERT INTO t2 VALUES (5,'T2_5'); > INSERT INTO t2 VALUES (6,'T2_6'); > INSERT INTO t2 VALUES (10,'T2_10'); > > CREATE TEMP TABLE t3 (id int, t3_val TEXT); > INSERT INTO t3 VALUES (4,'T3_4'); > INSERT INTO t3 VALUES (6,'T3_6'); > INSERT INTO t3 VALUES (7,'T3_7'); > INSERT INTO t3 VALUES (10,'T3_10'); > > SELECT id,t1_val,t2_val,t3_val > FROM > t1 > FULL JOIN t2 USING (id) > FULL JOIN t3 USING (id) > ; > > SELECT COALESCE(t1.id,t2.id,t3.id) AS id,t1_val,t2_val,t3_val > FROM > t1 > FULL JOIN t2 ON (t1.id=t2.id) > FULL JOIN t3 ON (COALESCE(t1.id,t2.id)=t3.id) > ; > > id | t1_val | t2_val | t3_val > +++ > 2 | T1_2 || > 3 || T2_3 | > 4 ||| T3_4 > 5 | T1_5 | T2_5 | > 6 || T2_6 | T3_6 > 7 | T1_7 || T3_7 > 10 | T1_10 | T2_10 | T3_10 > (7 rows) > OK, I am clearly not understanding this yet. Here is what I am trying: select COALESCE( labor_cost_sum_view.proj_no , material_cost_sum_view.proj_no , expense_report_cost_sum_view.proj_no ) AS proj_no , labor_cost_sum_view.labor_cost_sum , material_cost_sum_view.mtrl_cost , expense_report_cost_sum_view.burdened_cost , coalesce( labor_cost_sum_view.labor_cost_sum, 0) + coalesce(material_cost_sum_view.mtrl_cost, 0) + coalesce(expense_report_cost_sum_view.burdened_cost, 0) from labor_cost_sum_view full joinmaterial_cost_sum_view on material_cost_sum_view.proj_no = labor_cost_sum_view.proj_no full joinexpense_report_cost_sum_view on expense_report_cost_sum_view.proj_no = labor_cost_sum_view.proj_no ; Having simplified things a bunch, and removing casts and aliases etc. But here is what I am getting as a result set: proj_no | labor_cost_sum | mtrl_cost | burdened_cost |?column? -++---+---+ 45 | 3133.1750 | 5394.6800 | 2564.98 | 11092.8350 764 | 810.7500 | 7249.4800 | | 8060.2300 789 | 7015.5750 | | | 7015.5750 7456 || 4007.3000 | | 4007.3000 33 || |241.38 | 241.38 7456 || | 1747.11 | 1747.11 (6 row Note that project number 7456 appears in 2 rows. -- "They that would give up essential liberty for temporary safety deserve neither liberty nor safety." -- Benjamin Franklin
A user atribute question
Just starting to expore setting up roles & useres. I ran this statement: GRANT CONNECT ON DATABASE stan TO employee; But yet \du still reports: employee| Cannot login What am I doing wrong? -- "They that would give up essential liberty for temporary safety deserve neither liberty nor safety." -- Benjamin Franklin
How to determine what is preventing deleting a rule
I created some roles that I think are badly named, as my understanding of them increases. I have deleted, what i thought were all the objects referenced by these roles, but I still get an error saying there is one object they reference. How can I find out what this object is? -- "They that would give up essential liberty for temporary safety deserve neither liberty nor safety." -- Benjamin Franklin
Roles versus users
I am creating an application that will need to have access control. There will basically be the groups (roles ?): * normal user (can do insert on a limited sate of tables, and select on a slightly larger set * project manager will have some increased insert and select capabilities * sysadmin will be able to do select and insert on all tables in the schema There will be more than one person in each of these groups. My original intent was to create roles, and assign users to appropriate roles, using inheritance to add increasingly greater capabilities. That is the inheritance would look like this normal user <- project manager <- sysadmin But, I have run up ion a note in the documentation that says that create user is actually a synonym for create role. So, should I just create roles for each user? -- "They that would give up essential liberty for temporary safety deserve neither liberty nor safety." -- Benjamin Franklin
Sorry, real newbie question about connecting to a database
I am developinng an appliction usig Postgresql 11, installed on a UBUTU
18.14 machine.
I ahve vreated a new database to do some testing on restricting access of
specific users/roles to certain data. I have done the following:
REVOKE ALL ON DATABASE pertest FROM employee;
GRANT CONNECT ON DATABASE pertest TO employee;
and I have verifed tht the user employee does exst, I have also doen a few
more GRABTs to allow specific acces. But I cannot conect, or swith to user
employee:
stan@smokey:/etc/postgresql/11/main$ psql -U employee
psql: FATAL: Peer authentication failed for user "employee"
stan=> \l
List of databases
Name| Owner | Encoding | Collate | Ctype | Access privileges
+--+--+-+-+---
pertest | stan | UTF8 | C.UTF-8
| C.UTF-8 | =Tc/stan
+
| | stan=CTc/stan
employee=CTc/stan
Sorrry cut and paste mangled that.
What am I failing to do here?
--
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
-- Benjamin Franklin
Collumn level permissions ?
I do know a way to solve this. I could create a view "B" that is a view of tab;e "A" without column "C" on the select, and give a user permissions on B, but not on A, or at least I think that would work. What I specifically need to do is "hide" one column of a table from a set of users. Any better way to do this? -- "They that would give up essential liberty for temporary safety deserve neither liberty nor safety." -- Benjamin Franklin
Importing from CSV, auto creating table?
I have a situation where we need to import data, as an interim measure, from spreadsheets. I have read up on \copy and COPY, but I do not see that either of these can use the header from a CSV file to define a new table. Am I missing something? Also downloaded something called pgfutter, which I thought would do this, but have not had any success with this. After I (thought) I had figured out the arguments, it just seams to hag forever. -- "They that would give up essential liberty for temporary safety deserve neither liberty nor safety." -- Benjamin Franklin
A question aboout postgresql-server-dev versions
I am using Postgresql version 11 on Ubuntu 18.04. I am considering using an extension called libphonenumber. It needs Postgresql-server-dev to build. I found postgresql-server-dev-all in the list of available packages, BUT when I ask apt-get to lad it, a dependency is postgresql-server-dev-10. Seems wrong, is this OK? -- "They that would give up essential liberty for temporary safety deserve neither liberty nor safety." -- Benjamin Franklin
What is the difference between creating a type, and a domain ?
Subject basically says it all. -- "They that would give up essential liberty for temporary safety deserve neither liberty nor safety." -- Benjamin Franklin
pgcharts on Ubuntu 18.04 ?
If anyone has installed the package for this on 18.04, and gotten it working, I'd like to discuss what you had to do. When I use apt-get to install it, I get a a couple of errors: Warning: The home directory /var/log/pgcharts you specified can't be accessed: No such file or directory Not creating home directory `/var/log/pgcharts' -es a /vr/log directory, and since apt-get is running as root, it should be able to create that directory. In any case, when I try to send my browser to the specified port on localhost after doing an install there is nothing running on that port. I would also be interested in any comments about how well this package works. I am not opposed to building it from source, and working through the install process, if that is needed, if it is useful. "They that would give up essential liberty for temporary safety deserve neither liberty nor safety." -- Benjamin Franklin
Work hours?
I am just starting to explore the power of PostgreSQL's time and date functionality. I must say they seem very powerful. I need to write a function that, given a month, and a year as input returns the "work hours" in that month. In other words something like 8 * the count of all days in the range Monday to Friday) within that calendar month. Any thoughts as to the best way to approach this? -- "They that would give up essential liberty for temporary safety deserve neither liberty nor safety." -- Benjamin Franklin
"storing" a calculated value in plsql function ?
I have created a function (PLSQL) that does a complex select with joins on various tables and views, and returns a table. In the resultant table, I have raw data, and adjusted data. The adjusted data i all adjusted by a common factor, which is calculated in the select. Presently, I calculate this same adjustment factor several times in the select. Is there a way to reference this value, multiple times, once it is calculated? Or would I have to create a 2nd select that calculates this adjustment factor, and stores it in a PLSQL variable< and if I do that, can I reference this stored value in the select? -- "They that would give up essential liberty for temporary safety deserve neither liberty nor safety." -- Benjamin Franklin
SQL equivalint of #incude directive ?
I thought this would be common. But a quick Google only revealed what look to be workarounds. I am defining a bunch of functions, and I would prefer to store them in a separate file, which then gets "source" by the main DB init file. Is there a standard way to do this? -- "They that would give up essential liberty for temporary safety deserve neither liberty nor safety." -- Benjamin Franklin
Returning a table from a function, that requires multiple selects?
I need to encapsulate, what are basically 2 related function calls into a single function. The result of each of th calls is a date type. y current thinking is to return a 2 row table with the 2 dates in it. But, I seem to be having issues getting this to work. Is it possible for a function to return a table with results from multiple queries? -- "They that would give up essential liberty for temporary safety deserve neither liberty nor safety." -- Benjamin Franklin
Re: Returning a table from a function, that requires multiple selects?
On Fri, Aug 30, 2019 at 04:03:15PM -0400, stan wrote: > I need to encapsulate, what are basically 2 related function calls into a > single > function. The result of each of th calls is a date type. > > y current thinking is to return a 2 row table with the 2 dates in it. But, I > seem to > be having issues getting this to work. > > Is it possible for a function to return a table with results from multiple > queries? > Got it working, so yes this can be done. Next I have to figure out how to configure the next function to accept this table. Error message says something about configuring it to accept a record. -- "They that would give up essential liberty for temporary safety deserve neither liberty nor safety." -- Benjamin Franklin
How to get RAISE messges displayed?
OK, I am doing enough of this to start using some debug error messages. I put the following in a function declaration: RAISE notice 'Called with %', $1 ; But, when I call the function I do not see anything. I edited postgresql.conf Like this: client_min_messages = notice #client_min_messages = notice # values in order of decreasing detail: Event though it looked like this should be the default. restarted Postgres with: /etc/init.d/postgresql restart But I still do not see this, or see it in the log when I call this function. Ubuntu 18.04 a Postgresql 11 if that matters. -- "They that would give up essential liberty for temporary safety deserve neither liberty nor safety." -- Benjamin Franklin
Re: How to get RAISE messges displayed?
On Fri, Aug 30, 2019 at 06:22:14PM -0400, stan wrote: > OK, I am doing enough of this to start using some debug error messages. > > I put the following in a function declaration: > > RAISE notice 'Called with %', $1 ; > > But, when I call the function I do not see anything. I edited postgresql.conf > > Like this: > > client_min_messages = notice > #client_min_messages = notice # values in order of decreasing > detail: > > Event though it looked like this should be the default. > > restarted Postgres with: > > /etc/init.d/postgresql restart > > But I still do not see this, or see it in the log when I call this function. > > Ubuntu 18.04 a Postgresql 11 if that matters. > Got it working. Not 100% sure what I had wrong. -- "They that would give up essential liberty for temporary safety deserve neither liberty nor safety." -- Benjamin Franklin
Problems with using function input paramaters
I have been chasing a bug for a bit now. I even wound up completely rewriting
the
function, which in the end turns out to be a good thing, as it is much cleaner
and
easy to read.
I now believe that the bug is in how I am using an input parameter to the
function. Here is the function deceleration:
CREATE FUNCTION
return_previous_month_start_and_end(
integer)
RETURNS interval_dates AS $$
Here is one of the places I am using it:
my_year := ( select
cast(extract(year from
cast(date_trunc('month',
CURRENT_DATE) -
interval ' $1
month - 1 ' day
as date)
) as integer)
) ;
This prints the correct value BTW:
RAISE notice 'Called with %', $1 ;
Nay thoughts as to what I am doing wrong?
--
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
-- Benjamin Franklin
Re: Problems with using function input paramaters
On Sat, Aug 31, 2019 at 10:05:10AM -0400, stan wrote:
> I have been chasing a bug for a bit now. I even wound up completely rewriting
> the
> function, which in the end turns out to be a good thing, as it is much
> cleaner and
> easy to read.
>
> I now believe that the bug is in how I am using an input parameter to the
> function. Here is the function deceleration:
>
> CREATE FUNCTION
> return_previous_month_start_and_end(
> integer)
> RETURNS interval_dates AS $$
>
> Here is one of the places I am using it:
>
>
> my_year := ( select
> cast(extract(year from
> cast(date_trunc('month',
> CURRENT_DATE) -
> interval ' $1
> month - 1 ' day
> as date)
> ) as integer)
> ) ;
> This prints the correct value BTW:
>
> RAISE notice 'Called with %', $1 ;
>
> Nay thoughts as to what I am doing wrong?
>
BTW, this article
https://dba.stackexchange.com/questions/159424/how-to-use-function-parameters-in-dynamic-sql-with-execute
seems to imply that I can use the $1 syntax in a string, but others refer to
using
the COALESCE functionality, but I am uncertain exactly how to use this here.
--
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
-- Benjamin Franklin
echo work alike in SQL
Just started using the RAISE functionality to help in debugging. Tried to add it to an SQL script, only to realize it is a PLPGSQL extension. Is there a way to get a string to appear in the output f an SQL script? Can I do some sort of select? -- "They that would give up essential liberty for temporary safety deserve neither liberty nor safety." -- Benjamin Franklin
pgmodeler ?
I apologize, as this is a bit off topic for this list. pgmodeler got mention in a thread that I started a day or two ago, and it looks like it might be very useful I am running on Ubuntu 18.04 (latest STABLE), and I found that there is a package, which I downloaded. The idea is to import the project I am working on. But, when I try to import it I get an error message about: t says that the column pr.proisaag. I don't want to spend a lot of time trying to get this package to work, if it is a dead end. I am using Postgres 11 if that matters. Has anyone gotten this to work If so, can anyone point me to a quick HOWTO to import my existing project? Thanks in advance. -- "They that would give up essential liberty for temporary safety deserve neither liberty nor safety." -- Benjamin Franklin
Re: pgmodeler ?
On Sun, Sep 01, 2019 at 06:30:23AM -0400, stan wrote: > I apologize, as this is a bit off topic for this list. > > pgmodeler got mention in a thread that I started a day or two ago, and it > looks > like it might be very useful > > I am running on Ubuntu 18.04 (latest STABLE), and I found that there is a > package, > which I downloaded. The idea is to import the project I am working on. > > But, when I try to import it I get an error message about: > > t says that the column pr.proisaag. > > I don't want to spend a lot of time trying to get this package to work, if it > is a > dead end. I am using Postgres 11 if that matters. > > Has anyone gotten this to work If so, can anyone point me to a quick HOWTO to > import my existing project? > This is the specific error message: Could not execute the SQL command. Message returned: ERROR: column pr.proisagg does not exist LINE 1: ...namespace AS ns ON pr.pronamespace = ns.oid WHERE pr.proisag... ^ HINT: Perhaps you meant to reference the column -- "They that would give up essential liberty for temporary safety deserve neither liberty nor safety." -- Benjamin Franklin
Posible off topic ? pgmodeler
Some very helpful folks pointed me to pgmodeler recently. I realize this list may not be the appropriate forum for discussing this, and would welcome pointers to a more appropriate forum. Having said that, this discussion may be a bit more generic. I have seen a number of open source products (Amanda is a good example), where a company sort of "takes over" the project, the company's bossiness plan is generally that of making money on support. Most of these companies have a community edition, which trails development of the product with their enhancements. U understand that this model is acceptable to GPL licensed software. The company supporting pgmodeler seems to have a different model. It looks like they provide a version of the tool that requires a license key, with limited life span. What I am trying to do, at the moment is get a complete understanding of their bossiness model, regarding the source code for this project. Thanks for any input on this. -- "They that would give up essential liberty for temporary safety deserve neither liberty nor safety." -- Benjamin Franklin
help formualting a query
I am having a hard time figuring out to do this, the SQL way. Doing in a procedural way with a foreach would be deasy, but I suspect that a properly formulated SQL query can achieve this. I have a table that contains a series of rows. Each row has a project key, a start date, and an end date. There are multiple rows with different start and end dates for each project. I need to return something that contains, one row per project key, with the min(star date) and max(end date) for ALL the records for that given project. Any suggestions? -- "They that would give up essential liberty for temporary safety deserve neither liberty nor safety." -- Benjamin Franklin
Referncing a calculated column in a select?
I am creating some views, that have columns with fairly complex calculations in them. The I want to do further calculations using the result of this calculation. Right now, I am just duplicating the first calculation in the select fro the 2nd calculated column. There must be a batter way to do this, right? -- "They that would give up essential liberty for temporary safety deserve neither liberty nor safety." -- Benjamin Franklin
How to handle things that change over time?
I am working on a system that will support internal bossiness work for a company. Periodicly things will change in their "world". For example they periodically recalculate the "mark up" of various components, of their bushiness, such as labor and or purchased materials. Presently I am keeping these constants in a table, and I have the constant, and an effective start, and end date for these factors. In this scenario, the number of rows will just grow over time, and by using the proper conditions in our select statement, i can apply the correct factors as they change over time. In another scenario, a column of the employee table is the pay rate. Obviously this will change over time, also. It does not appear to me that this lends itself to this same treatment, as most of the data associated with a given employee, is fairly static, and if I add an entire row, just because the pay rate changed, this looks overly complex. This cannot be the first time this issue has been addressed. What have others done in this scenario?
backing up the data from a single table?
My development methodology is to create scripts that init the db, and load test data, as I make changes. Now, I am starting to move toward a test instance with real data. The end user has provided "real" test data, n spreadsheets. I have the inputing of data from these working OK. (takes a bit of hand work). What I would like to do, is continue to init the "test" database. To make that easy, I would like to be able to "export" individual tables, so I can use the scripts to reload them. I prefer not to have to convert these to my load script format. Is there a way to "export" a single table, that can be easily re "imported"? -- "They that would give up essential liberty for temporary safety deserve neither liberty nor safety." -- Benjamin Franklin
problems importing from csv
I am trying to import some data from spreadsheets. Included in the data sets are US monetary values. These appear in the CSV file, like this: $1.00 The column is defined like this: NUMERIC(5,2) NOT NULL. When I try to import this data using the \copy functionality, i get the following error; stan@smokey:~/pm_db/live_data$ psql < import_employee.sql ERROR: invalid input syntax for type numeric: "$1.00" CONTEXT: COPY employee, line 2, column hourly_rate: "$1.00" How can I work around this? -- "They that would give up essential liberty for temporary safety deserve neither liberty nor safety." -- Benjamin Franklin
Can I wrtie a function that has a BEFORE trigger that is not column name dpendent?
I would like to write a generic function that I can place as a BEFORE trigger on several tables. It would do a max() on the column it was triggered for, and return(max + 1) unless max returns a NULL, in which case it would return one. Yes, I know this looks a lot like a sequence, but normally this value would be provided manually at row input time, I just want to allow for some automated updates. The trick is hat the function needs to work for any numeric column I place it n as a trigger. -- "They that would give up essential liberty for temporary safety deserve neither liberty nor safety." -- Benjamin Franklin
Handling case variatiions on a user defined type?
I have several enumerated types that look like:
CREATE TYPE confidence_level AS ENUM ('HIGH' ,
'MEDIUM' ,
'LOW' ,
'WAG');
I have someone creating data for this project in spreadsheets, and then am
importing the data using \copy.
Problem is the data in the spreadsheets is entered in mixed case.
Is there a way that I can use the type itself, to enforce something like a
to_upper(). Or is there a better way to "human proof" this?
--
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
-- Benjamin Franklin
Re: Handling case variatiions on a user defined type?
On Sun, Sep 15, 2019 at 09:58:45AM -0300, Olivier Gautherot wrote:
> El dom., 15 de septiembre de 2019 09:37, stan escribi??:
>
> > I have several enumerated types that look like:
> >
> > CREATE TYPE confidence_level AS ENUM ('HIGH' ,
> > 'MEDIUM' ,
> > 'LOW' ,
> > 'WAG');
> >
> >
> > I have someone creating data for this project in spreadsheets, and then am
> > importing the data using \copy.
> >
> > Problem is the data in the spreadsheets is entered in mixed case.
> >
> > Is there a way that I can use the type itself, to enforce something like a
> > to_upper(). Or is there a better way to "human proof" this?
> >
> > --
> > "They that would give up essential liberty for temporary safety deserve
> > neither liberty nor safety."
> > -- Benjamin Franklin
> >
>
> What about a trigger on INSERT, which would enforce the case?
That sounds like a good idea, thanks.
--
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
-- Benjamin Franklin
Shouldn;t this trigger be called?
I have defined this function: CREATE FUNCTION fix_customer_types_case() and this trigger: CREATE TRIGGER fix_customer_types_case_trig BEFORE INSERT OR UPDATE ON customer FOR EACH ROW EXECUTE FUNCTION fix_customer_types_case(); and I put a RAISE NOTICE so I cna tell if the function is called. Yet when I do a : \copy to bring data into this table, I do not see the notice. Shouldn't this fucntion be aclled for that? -- "They that would give up essential liberty for temporary safety deserve neither liberty nor safety." -- Benjamin Franklin
Re: Shouldn;t this trigger be called?
On Sun, Sep 15, 2019 at 11:33:09AM -0400, stan wrote: > I have defined this function: > > CREATE FUNCTION fix_customer_types_case() > > and this trigger: > > CREATE TRIGGER fix_customer_types_case_trig BEFORE INSERT OR UPDATE ON > customer > FOR EACH ROW EXECUTE FUNCTION fix_customer_types_case(); > > and I put a RAISE NOTICE so I cna tell if the function is called. Yet when I > do a : > > \copy to bring data into this table, I do not see the notice. > > Shouldn't this fucntion be aclled for that? Sorry, found my mistake. -- "They that would give up essential liberty for temporary safety deserve neither liberty nor safety." -- Benjamin Franklin
FW: Re: Shouldn;t this trigger be called?
Sorry forgot to cc the list On Sun, Sep 15, 2019 at 08:42:02AM -0700, Adrian Klaver wrote: > On 9/15/19 8:33 AM, stan wrote: > > I have defined this function: > > > > CREATE FUNCTION fix_customer_types_case() > > > > and this trigger: > > > > CREATE TRIGGER fix_customer_types_case_trig BEFORE INSERT OR UPDATE ON > > customer > > FOR EACH ROW EXECUTE FUNCTION fix_customer_types_case(); > > > > and I put a RAISE NOTICE so I can tell if the function is called. Yet when I > > do a : > > > > \copy to bring data into this table, I do not see the notice. > > What is the actual command you are using? \COPY customer(name, location, status , c_type , bill_attention , bill_addresse , bill_address_1 , bill_address_2 , bill_city , bill_state , bill_country , ship_attention , ship_addresse , ship_address_1 , ship_address_2, ship_city ,ship_state ) from '/home/stan/pm_db/live_data/ready/customer.csv' DELIMITER ',' CSV HEADER ; and here is the function CREATE FUNCTION fix_customer_types_case() RETURNS trigger AS $$ BEGIN if NEW.c_type IS NOT NULL THEN NEW.c_type := upper(cast( NEW.c_type AS TEXT)); END IF ; if NEW.status IS NOT NULL THEN RAISE NOTICE 'Called With %', NEW.status; NEW.status := upper(cast( NEW.status AS TEXT)); END IF ; RAISE NOTICE 'Left With With %', NEW.status; RAISE NOTICE 'Left With With %', NEW.c_type; return NEW; END; $$ LANGUAGE PLPGSQL; if I do an insert this function is called. However it IS NOT called for the above copy command. How can I fix that? This line from the page you referenced implies this should work, but i must be doing something wrong: COPY FROM will invoke any triggers and check constraints on the destination table. However, it will not invoke rules. -- "They that would give up essential liberty for temporary safety deserve neither liberty nor safety." -- Benjamin Franklin
Re: FW: Re: Shouldn;t this trigger be called?
On Sun, Sep 15, 2019 at 08:59:43AM -0700, Adrian Klaver wrote: > On 9/15/19 8:55 AM, stan wrote: > > > > Sorry forgot to cc the list > > > > On Sun, Sep 15, 2019 at 08:42:02AM -0700, Adrian Klaver wrote: > > > On 9/15/19 8:33 AM, stan wrote: > > > > I have defined this function: > > > > > > > > CREATE FUNCTION fix_customer_types_case() > > > > > > > > and this trigger: > > > > > > > > CREATE TRIGGER fix_customer_types_case_trig BEFORE INSERT OR UPDATE ON > > > > customer > > > > FOR EACH ROW EXECUTE FUNCTION fix_customer_types_case(); > > > > > > > > and I put a RAISE NOTICE so I can tell if the function is called. Yet > > > > when I > > > > do a : > > > > > > > > \copy to bring data into this table, I do not see the notice. > > > > > > What is the actual command you are using? > > > > > > \COPY customer(name, location, status , c_type , bill_attention , > > bill_addresse , bill_address_1 , bill_address_2 , bill_city , bill_state , > > bill_country , ship_attention , ship_addresse , ship_address_1 , > > ship_address_2, ship_city ,ship_state ) from > > '/home/stan/pm_db/live_data/ready/customer.csv' DELIMITER ',' CSV HEADER ; > > > > and here is the function > > > > CREATE FUNCTION fix_customer_types_case() > > RETURNS trigger AS $$ > > BEGIN > > if NEW.c_type IS NOT NULL > > THEN > > NEW.c_type := upper(cast( NEW.c_type AS TEXT)); > > END IF ; > > if NEW.status IS NOT NULL > > THEN > > RAISE NOTICE 'Called With %', NEW.status; > > NEW.status := upper(cast( NEW.status AS TEXT)); > > END IF ; > > RAISE NOTICE 'Left With With %', NEW.status; > > RAISE NOTICE 'Left With With %', NEW.c_type; > > return NEW; > > END; > > $$ > > LANGUAGE PLPGSQL; > > > > if I do an insert this function is called. However it IS NOT called for the > > above copy command. How can I fix that? > > I thought you said it was fixed now. > I discovered that the function was not getting defined, and fixed that. Then I rashly posted to the list that it was fixed, as i was certain that was the only issue. But after I reported that, I tried testing, with he results in this email. Works for INSERT, but does not fire on this \copy command. -- "They that would give up essential liberty for temporary safety deserve neither liberty nor safety." -- Benjamin Franklin
FW: Re: FW: Re: Shouldn;t this trigger be called?
Forgot to cc the list again. Have to look at settings in mutt. > > > > > > Sorry forgot to cc the list > > > > > > On Sun, Sep 15, 2019 at 08:42:02AM -0700, Adrian Klaver wrote: > > > > On 9/15/19 8:33 AM, stan wrote: > > > > > I have defined this function: > > > > > > > > > > CREATE FUNCTION fix_customer_types_case() > > > > > > > > > > and this trigger: > > > > > > > > > > CREATE TRIGGER fix_customer_types_case_trig BEFORE INSERT OR UPDATE > > > > > ON customer > > > > > FOR EACH ROW EXECUTE FUNCTION fix_customer_types_case(); > > > > > > > > > > and I put a RAISE NOTICE so I can tell if the function is called. Yet > > > > > when I > > > > > do a : > > > > > > > > > > \copy to bring data into this table, I do not see the notice. > > > > > > > > What is the actual command you are using? > > > > > > > > > \COPY customer(name, location, status , c_type , bill_attention , > > > bill_addresse , bill_address_1 , bill_address_2 , bill_city , bill_state > > > , bill_country , ship_attention , ship_addresse , ship_address_1 , > > > ship_address_2, ship_city ,ship_state ) from > > > '/home/stan/pm_db/live_data/ready/customer.csv' DELIMITER ',' CSV > > > HEADER ; > > > > > > and here is the function > > > > > > CREATE FUNCTION fix_customer_types_case() > > > RETURNS trigger AS $$ > > > BEGIN > > > if NEW.c_type IS NOT NULL > > > THEN > > > NEW.c_type := upper(cast( NEW.c_type AS TEXT)); > > > END IF ; > > > if NEW.status IS NOT NULL > > > THEN > > > RAISE NOTICE 'Called With %', NEW.status; > > > NEW.status := upper(cast( NEW.status AS TEXT)); > > > END IF ; > > > RAISE NOTICE 'Left With With %', NEW.status; > > > RAISE NOTICE 'Left With With %', NEW.c_type; > > > return NEW; > > > END; > > > $$ > > > LANGUAGE PLPGSQL; > > > > > > if I do an insert this function is called. However it IS NOT called for > > > the > > > above copy command. How can I fix that? > > > > I thought you said it was fixed now. > > > I discovered that the function was not getting defined, and fixed that. Then I > rashly posted to the list that it was fixed, as i was certain that was the > only issue. But after I reported that, I tried testing, with he results in > this email. > > Works for INSERT, but does not fire on this \copy command. > More interesting data. I used vi to correct the incorrect case in the CSV file being imported, and re-ran the \copy command. At this point in time, I did see the messages from notice. I deleted the rows, re-edited back to the incorrect case in the csv file, and the import ((\copy) failed. So, my test tell me that the validity check is done BEFORE an attempt to insert (thus firing the trigger) occurs. Interesting, but not helpful for my application. -- "They that would give up essential liberty for temporary safety deserve neither liberty nor safety." -- Benjamin Franklin
Re: FW: Re: FW: Re: Shouldn;t this trigger be called?
On Sun, Sep 15, 2019 at 12:27:14PM -0700, Adrian Klaver wrote: > On 9/15/19 10:46 AM, stan wrote: > > Forgot to cc the list again. Have to look at settings in mutt. > > > > > > > > > > > > Sorry forgot to cc the list > > > > > > > > > > On Sun, Sep 15, 2019 at 08:42:02AM -0700, Adrian Klaver wrote: > > > > > > On 9/15/19 8:33 AM, stan wrote: > > > > > > > I have defined this function: > > > > > > > > > > > > > > CREATE FUNCTION fix_customer_types_case() > > > > > > > > > > > > > > and this trigger: > > > > > > > > > > > > > > CREATE TRIGGER fix_customer_types_case_trig BEFORE INSERT OR > > > > > > > UPDATE ON customer > > > > > > > FOR EACH ROW EXECUTE FUNCTION fix_customer_types_case(); > > > > > > > > > > > > > > and I put a RAISE NOTICE so I can tell if the function is called. > > > > > > > Yet when I > > > > > > > do a : > > > > > > > > > > > > > > \copy to bring data into this table, I do not see the notice. > > > > > > > > > > > > What is the actual command you are using? > > > > > > > > > > > > > > >\COPY customer(name, location, status , c_type , bill_attention , > > > > > bill_addresse , bill_address_1 , bill_address_2 , bill_city , > > > > > bill_state , bill_country , ship_attention , ship_addresse , > > > > > ship_address_1 , ship_address_2, ship_city ,ship_state ) from > > > > > '/home/stan/pm_db/live_data/ready/customer.csv' DELIMITER ',' CSV > > > > > HEADER ; > > > > > > > > > > and here is the function > > > > > > > > > > CREATE FUNCTION fix_customer_types_case() > > > > > RETURNS trigger AS $$ > > > > > BEGIN > > > > > if NEW.c_type IS NOT NULL > > > > > THEN > > > > > NEW.c_type := upper(cast( NEW.c_type AS TEXT)); > > > > > END IF ; > > > > > if NEW.status IS NOT NULL > > > > > THEN > > > > > RAISE NOTICE 'Called With %', NEW.status; > > > > > NEW.status := upper(cast( NEW.status AS TEXT)); > > > > > END IF ; > > > > > RAISE NOTICE 'Left With With %', NEW.status; > > > > > RAISE NOTICE 'Left With With %', NEW.c_type; > > > > > return NEW; > > > > > END; > > > > > $$ > > > > > LANGUAGE PLPGSQL; > > > > > > > > > > if I do an insert this function is called. However it IS NOT called > > > > > for the > > > > > above copy command. How can I fix that? > > > > > > > > I thought you said it was fixed now. > > > > > > > I discovered that the function was not getting defined, and fixed that. > > > Then I > > > rashly posted to the list that it was fixed, as i was certain that was the > > > only issue. But after I reported that, I tried testing, with he results in > > > this email. > > > > > > Works for INSERT, but does not fire on this \copy command. > > > > > More interesting data. I used vi to correct the incorrect case in the CSV > > file > > being imported, and re-ran the \copy command. At this point in time, I did > > see the messages from notice. I deleted the rows, re-edited back to the > > incorrect case in the csv file, and the import ((\copy) failed. > > > > So, my test tell me that the validity check is done BEFORE an attempt to > > insert (thus firing the trigger) occurs. > > What validity check? > The check to see if it is the type enum. -- "They that would give up essential liberty for temporary safety deserve neither liberty nor safety." -- Benjamin Franklin
Re: FW: Re: FW: Re: Shouldn;t this trigger be called?
On Mon, Sep 16, 2019 at 12:12:34AM -0400, Tom Lane wrote:
> stan writes:
> > On Sun, Sep 15, 2019 at 12:27:14PM -0700, Adrian Klaver wrote:
> >> On 9/15/19 10:46 AM, stan wrote:
> >>> So, my test tell me that the validity check is done BEFORE an attempt to
> >>> insert (thus firing the trigger) occurs.
>
> >> What validity check?
>
> > The check to see if it is the type enum.
>
> Indeed, a trigger cannot fix an input-validity error, because that
> will happen while trying to form the row value that would be passed
> to the trigger. So I guess that when you say "the trigger doesn't
> fire" you really mean "this other error is raised first".
>
> However, I still don't understand your claim that it works the
> way you wanted in an INSERT statement. The enum input function
> is going to complain in either context.
>
> Generally you need to fix issues like this before trying to
> insert the data into your table. You might try preprocessing
> the data file before feeding it to COPY. Another way is to
> copy into a temporary table that has very lax column data types
> (all "text", perhaps) and then transform the data using
> INSERT ... SELECT from the temp table to the final storage table.
>
> regards, tom lane
Thanks for educating me. I thought I had tested and seen that this worked on
an INSERT, but once you told me it does not, I re tested to convince myself
that my test was invalid. let me show you what I was trying to do:
CREATE FUNCTION fix_customer_types_case()
RETURNS trigger AS $$
BEGIN
if NEW.c_type IS NOT NULL
THEN
NEW.c_type := upper(cast( NEW.c_type AS TEXT));
END IF ;
if NEW.status IS NOT NULL
THEN
/*
RAISE NOTICE 'Called With %', NEW.status;
*/
NEW.status := upper(cast( NEW.status AS TEXT));
END IF ;
/*
RAISE NOTICE 'Left With With NEW.status %', NEW.status;
RAISE NOTICE 'Left With With NEW.c_type %', NEW.c_type;
*/
return NEW;
END;
$$
LANGUAGE PLPGSQL;
CREATE TRIGGER fix_customer_types_case_trig BEFORE INSERT OR UPDATE ON customer
FOR EACH ROW EXECUTE FUNCTION fix_customer_types_case();
all of this is to deal with columns defined as this user defined type.
CREATE TYPE activity_status AS ENUM ('ACTIVE' ,
'INACTIVE');
Can you think of a better way to make the system "human data entry friendly"?
--
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
-- Benjamin Franklin
Re: FW: Re: FW: Re: Shouldn;t this trigger be called?
On Mon, Sep 16, 2019 at 12:44:49PM -0700, Adrian Klaver wrote:
> On 9/16/19 11:53 AM, stan wrote:
> > On Sun, Sep 15, 2019 at 09:16:35PM -0700, Adrian Klaver wrote:
> > > On 9/15/19 6:04 PM, stan wrote:
> > > > On Sun, Sep 15, 2019 at 12:27:14PM -0700, Adrian Klaver wrote:
> > > > > On 9/15/19 10:46 AM, stan wrote:
> > > > > > Forgot to cc the list again. Have to look at settings in mutt.
> > > > > >
> > > > > > > > >
> > >
> > > > > What validity check?
> > > > >
> > > >
> > > > The check to see if it is the type enum.
> > > >
> > >
> > > This would get solved a lot quicker if full information was provided:
> > >
> > > 1) Schema of the table.
> > > Including associated triggers
> > >
> > > 2) The actual check code.
> > >
> >
> > OK, please let me know if what I put in my reply to Tom Lane is not
> > sufficient.
> >
>
>
> It was not sufficient, you did not include the table schema or the check
> code.
OK, understood here is the table:
/* Contains one record for each customer */
CREATE TABLE customer (
customer_keyinteger DEFAULT nextval('customer_key_serial')
PRIMARY KEY ,
cust_no smallint NOT NULL UNIQUE ,
namevarchar UNIQUE ,
c_type customer_type ,
locationvarchar ,
bill_address_1 varchar ,
bill_address_2 varchar ,
bill_city varchar ,
bill_state varchar(2) ,
bill_zipus_postal_code NOT NULL DEFAULT '0',
bill_countryvarchar ,
bill_attention varchar ,
bill_addresse varchar ,
ship_address_1 varchar ,
ship_address_2 varchar ,
ship_addresse varchar ,
ship_attention varchar ,
ship_city varchar ,
ship_state varchar(2) ,
ship_zipus_postal_code NOT NULL DEFAULT '0',
office_phone_area_code numeric(3),
office_phone_exchange numeric(3),
office_phone_number numeric(4),
office_phone_extension numeric(4),
cell_phone_area_codenumeric(3),
cell_phone_exchange numeric(3),
cell_phone_number numeric(4),
ship_phone_area_codenumeric(3),
ship_phone_exchange numeric(3),
ship_phone_number numeric(4),
ship_phone_extensionnumeric(4),
fax_phone_area_code numeric(3),
fax_phone_exchange numeric(3),
fax_phone_numbernumeric(4),
status activity_status NOT NULL DEFAULT 'ACTIVE',
modtime timestamptz NOT NULL DEFAULT current_timestamp
);
I am not certain what you mean by the check. As you can see, there is nor
specific check clause. I was referring to the built in check of data being
entered versus the legal values for the user defined type. Make sense?
--
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
-- Benjamin Franklin
Re: FW: Re: FW: Re: Shouldn;t this trigger be called?
On Mon, Sep 16, 2019 at 03:19:27PM -0700, Adrian Klaver wrote: > On 9/16/19 12:55 PM, stan wrote: > > > > On Mon, Sep 16, 2019 at 12:44:49PM -0700, Adrian Klaver wrote: > > > On 9/16/19 11:53 AM, stan wrote: > > > > On Sun, Sep 15, 2019 at 09:16:35PM -0700, Adrian Klaver wrote: > > > > > On 9/15/19 6:04 PM, stan wrote: > > > > > > On Sun, Sep 15, 2019 at 12:27:14PM -0700, Adrian Klaver wrote: > > > > > > > On 9/15/19 10:46 AM, stan wrote: > > > > > > > > Forgot to cc the list again. Have to look at settings in mutt. > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > What validity check? > > > > > > > > > > > > > > > > > > > The check to see if it is the type enum. > > > > > > > > > > > > > > > > This would get solved a lot quicker if full information was provided: > > > > > > > > > > 1) Schema of the table. > > > > > Including associated triggers > > > > > > > > > > 2) The actual check code. > > > > > > > > > > > > > OK, please let me know if what I put in my reply to Tom Lane is not > > > > sufficient. > > > > > > > > > > > > > It was not sufficient, you did not include the table schema or the check > > > code. > > > > OK, understood here is the table: > > > > /* Contains one record for each customer */ > > > > > status activity_status NOT NULL DEFAULT 'ACTIVE', > > modtimetimestamptz NOT NULL DEFAULT current_timestamp > > ); > > > > I am not certain what you mean by the check. As you can see, there is nor > > specific check clause. I was referring to the built in check of data being > > entered versus the legal values for the user defined type. Make sense? > > > > To be clear you are seeing an error because a value of say 'active' is being > rejected before your trigger has a chance to upper case it, correct? > > Also this happens whether you use \copy or manually INSERT the values? That is correct. Sorry this was not clear from the beginning. Any suggestions, including changing the design here, are welcome. -- "They that would give up essential liberty for temporary safety deserve neither liberty nor safety." -- Benjamin Franklin
Re: FW: Re: FW: Re: Shouldn;t this trigger be called?
On Thu, Sep 19, 2019 at 03:54:40PM -0600, Michael Lewis wrote:
> You can also look at citext type to avoid the casting.
Oh, that looks really useful I think I will go back and use that type quite
a bit.
Thanks for pointing it out to me.
>
> customer_keyinteger DEFAULT
> nextval('customer_key_serial') PRIMARY KEY ,
> cust_no smallint NOT NULL UNIQUE ,
> namevarchar UNIQUE ,
>
> Why do you have a surrogate primary key generated by a sequence when you
> have a natural key of either cust_no or name? Why not just declare the
> customer number to be the PK?
At the moment, the customer (who is a small startup) really does not have a
customer number. It is really a place holder at the moment, with the
sequence being the "real" key. For all I know, the customer number may be
alphanumeric. in the final implementation.
> Where does customer number come from anyway?
> Using smallint seems potentially short-sighted on potential future growth,
> but changing the type later should be minimal work as long as you don't
> have this customer_number denormalized many places, or use it as the FKey
> after dropping customer_key surrogate key.
Thanks for your suggestion.
--
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
-- Benjamin Franklin
Re: FW: Re: FW: Re: Shouldn;t this trigger be called?
On Thu, Sep 19, 2019 at 03:54:40PM -0600, Michael Lewis wrote:
> You can also look at citext type to avoid the casting.
>
> customer_keyinteger DEFAULT
> nextval('customer_key_serial') PRIMARY KEY ,
> cust_no smallint NOT NULL UNIQUE ,
> namevarchar UNIQUE ,
>
I am confysed. I am running version 11 which is current I beleive, but when
I try to use this type, I get:
ERROR: type "citext" does not exist
LINE 8: unit citext UNIQUE NOT NULL ,
Do I somehow need to enable this type?
--
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
-- Benjamin Franklin
Re: FW: Re: FW: Re: Shouldn;t this trigger be called?
On Fri, Sep 20, 2019 at 08:52:00PM +1000, Morris de Oryx wrote: > I see that you've already been pointed at citext, but I don't think a CHECK > constraint has been mentioned. In case it hasn't, what about something like > this? > >ADD CONSTRAINT check_activity_status > CHECK (activity_status = 'ACTIVE' OR activity_status = 'INACTIVE'); > > I'm kind of allergic to ENUM...maybe that's just me. But since you're > considering it, maybe it's the perfect time to consider all of your > options. Such as a linked lookup table of defined allowed values (feels > silly with two values), a domain (not entirely fit to purpose), or the > CHECK constraint above. And, yeah, if it's only ever ACTIVE or INACTIVE, > I'd normally make a Boolean named something like active, as Adrian Klaver > mentioned. That's easy to reason about, and it makes it unambiguous that > there are two and only two possible states.. Thanks you. I actually have a number of these cases, and I sullied the simplest one, which just has 2 values. I guess my "C: background is showing here. I do have some similar situations where I did use a table of allowed conditions. I am thinking citext may be the best solution here. I am having an issue getting it to work, though. I don't have to do anything special to enable this type, do I? What I am really trying to do is "human proof" this input :-) -- "They that would give up essential liberty for temporary safety deserve neither liberty nor safety." -- Benjamin Franklin
citext, actually probably using extensions
I was pointed to the citext type to help solve an issue for a project I am working on. I looked at this page: https://nandovieira.com/using-insensitive-case-columns-in-postgresql-with-citext But I must be doing something wrong. As the PG superuser, I did this: postgres=# CREATE EXTENSION IF NOT EXISTS citext WITH SCHEMA public; CREATE EXTENSION and as the superuser it shows up in the \dx display BUT as a normal user: stan=> \dx List of installed extensions Name | Version | Schema | Description -+-++-- plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language (1 row) It does not. What am I doing incorrectly? -- "They that would give up essential liberty for temporary safety deserve neither liberty nor safety." -- Benjamin Franklin
Phone number type extension
Is there any currently supported version of this? I found one using google, but it looks like it was last updated around version 8 of Postgres. -- "They that would give up essential liberty for temporary safety deserve neither liberty nor safety." -- Benjamin Franklin
Thoughts on a cosntraint ?
I have a table that consists of 3 columns. vendor_key mfg_key preferred (boolean) The idea is that a given vendor is the vendor we want to use for each manufacturer for a given project. I need to constrain such that only on row of mfg, vendor and project can be set to TRUE. I would be interested in seeing other peoples approaches on this, also. Any thoughts? -- "They that would give up essential liberty for temporary safety deserve neither liberty nor safety." -- Benjamin Franklin
I messed up and my disk utlization is HUGE
I was going to try to do some testing on very large databases, and I tried to load the Mouse Gerome DB. It failed, and now I know why :-) I filled the disk up. Presently, with virtually nothing in any database the postgres storage location has 43G allocated. The DB was also crashed but i did free up some space and get it started again. How can I shrink this back to a reasonable size? The WAL also seems pretty large. -- "They that would give up essential liberty for temporary safety deserve neither liberty nor safety." -- Benjamin Franklin
Re: I messed up and my disk utlization is HUGE
On Wed, Oct 09, 2019 at 02:59:17PM -0400, melvin6925 wrote: > Have you tried a VACUUM FULL of the db?Sent via the Samsung Galaxy S?? 6, an > AT&T 4G LTE smartphone > Original message ----From: stan Date: 10/9/19 > 14:54 (GMT-05:00) To: melvin6925 Subject: Re: I > messed up and my disk utlization is HUGE On Wed, Oct 09, 2019 at 01:30:33PM > -0400, melvin6925 wrote:> >How can I shrink this back to a>reasonable > size???Have you tried a vacuum full of the db?Sent via the Samsung Galaxy S?? > 6, an AT&T 4G LTE smartphone> Original message From: stan > Date: 10/9/19?? 13:21?? (GMT-05:00) To: > pgsql-general@lists.postgresql.org Subject: I messed up and my disk > utlization is HUGE I was going to try to do some testing on very large > databases, and I tried toload the Mouse Gerome DB. It failed, and now I know > why :-) I filled thedisk up. Presently, with virtually nothing in any > database the postgresstorage location has 43G allocated. The DB was also > crashed but i did freeup some space and get it started again. How can I > shrink this back to areasonable size? The WAL also seems pretty large.-- > "They that would give up essential liberty for temporary safety > deserveneither liberty nor safety." > -- Benjamin FranklinSorry your reply was garbled.-- "They that would > give up essential liberty for temporary safety deserveneither liberty nor > safety." -- Benjamin Franklin I ran vacuumdb from the comand line as postgres , which I beleive does this. Did not take very long to run BTW. -- "They that would give up essential liberty for temporary safety deserve neither liberty nor safety." -- Benjamin Franklin
Recovering disk space
Sorry to repeat this, I am still in trouble on it. I made the mistake of trying to import the mouse gnome database in an effort to do some testing with very large data sets. This import failed, probably because I ran out of disk space. I deleted the dump file, and a few other things, and now I have a bit of disk space. Looks like I see the mad schema both in the postfres db, and in my own stan db/ I did a drop schema cascade on both as the db superuser, and ran vacuum full on both. This command came back almost instantly, which surprises me. However, my 50G disk is still 96% full. How can I recover the disk space I seem to have used u doing this? -- "They that would give up essential liberty for temporary safety deserve neither liberty nor safety." -- Benjamin Franklin
SOLVED Re: Recovering disk space
OOK, after a period of time, I recovered my disk space. On Thu, Oct 10, 2019 at 03:57:00AM -0400, stan wrote: > Sorry to repeat this, I am still in trouble on it. > > I made the mistake of trying to import the mouse gnome database in an > effort to do some testing with very large data sets. > > This import failed, probably because I ran out of disk space. I deleted the > dump file, and a few other things, and now I have a bit of disk space. > Looks like I see the mad schema both in the postfres db, and in my own > stan db/ I did a drop schema cascade on both as the db superuser, and ran > vacuum full on both. This command came back almost instantly, which > surprises me. > > However, my 50G disk is still 96% full. How can I recover the disk space I > seem to have used u doing this? > > > -- > "They that would give up essential liberty for temporary safety deserve > neither liberty nor safety." > -- Benjamin Franklin > > -- "They that would give up essential liberty for temporary safety deserve neither liberty nor safety." -- Benjamin Franklin
SELECT returnig a constant
I used to be able to return a constant value in a SELECT statement in ORACLE. I need to populate a table for testing, and I was going to do so like this: SELECT employee.id , project.proj_no , work_type.type , 'rate' 1 FROM employee CROSS JOIN project CROSS JOIN work_type; This statement works correctly, till I add the last "'rate' 1 line, then it returns a syntax error. How can I do this? -- "They that would give up essential liberty for temporary safety deserve neither liberty nor safety." -- Benjamin Franklin
SOLVED Re: SELECT returnig a constant
On Tue, Oct 15, 2019 at 09:28:51AM -0400, stan wrote: > I used to be able to return a constant value in a SELECT statement in > ORACLE. I need to populate a table for testing, and I was going to do so > like this: > > SELECT > employee.id , > project.proj_no , > work_type.type , > 'rate' 1 > FROM employee > CROSS JOIN project > CROSS JOIN work_type; > > This statement works correctly, till I add the last "'rate' 1 line, then it > returns a syntax error. > > How can I do this? > Turns out this works. SELECT employee.id , project.proj_no , work_type.type , 1 as rate FROM employee CROSS JOIN project CROSS JOIN work_type; ~ -- "They that would give up essential liberty for temporary safety deserve neither liberty nor safety." -- Benjamin Franklin
Inserting multiple rows wtih a SELECt in the values clause
OK, now that figured out how to return the constant, this is the final query I need to run. INSERT into rate ( employee_key , project_key , work_type_key , rate ) VALUES ( ( SELECT employee.employee_key , project.project_key , work_type.work_type_key , 1 as rate FROM employee CROSS JOIN project CROSS JOIN work_type ) ); But this fails. I suspect this may be because the SELECT in the values clause returns multiple rows? -- "They that would give up essential liberty for temporary safety deserve neither liberty nor safety." -- Benjamin Franklin
A question about sequences and backup/restore cycles
I typically design a system with primary keys defined, like this:
CREATE TABLE employee (
employee_key integer DEFAULT nextval('employee_key_serial')
PRIMARY KEY ,
I use scripts to build the database structures and load the data. I am
careful to get the dependencies in the correct order, so that the keys later
structures depend on already exist.
Today I was going over the design for the current project with a friend,
whose expertise i respect. he said that he had issues on a system designed
by an OEM that had dependencies on keys developed from sequences after a
backup/restore cycle,
Will I potentially have these issues? If so, what can I do different to
avoid this being an issue?
--
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
-- Benjamin Franklin
A question about sequnces and pg_restore
On Mon, Oct 21, 2019 at 05:24:37PM -0700, Adrian Klaver wrote:
> On 10/21/19 5:13 PM, stan wrote:
> >
> > I typically design a system with primary keys defined, like this:
> >
> >
> > CREATE TABLE employee (
> > employee_key integer DEFAULT nextval('employee_key_serial')
> > PRIMARY KEY ,
> >
> > I use scripts to build the database structures and load the data. I am
> > careful to get the dependencies in the correct order, so that the keys later
> > structures depend on already exist.
> >
> > Today I was going over the design for the current project with a friend,
> > whose expertise i respect. he said that he had issues on a system designed
> > by an OEM that had dependencies on keys developed from sequences after a
> > backup/restore cycle,
> >
> > Will I potentially have these issues? If so, what can I do different to
> > avoid this being an issue?
>
> It is not clear to me what you are doing:
>
> 1) Are you using pg_dump/pg_restore to populate a database?
> If so it will take care of the dependencies.
>
> 2) Are you using a home built method to populate the database?
> In that case you take responsibility for dependencies.
>
OK, let me elaborate.
During the development/test cycle. I routinely drop all objects in the
database for the project we are in the process of working on. This is my
standard way of doing this, and, I understand the dependencies, and keep
them working correctly with the scripts that recreate the database. This
work fine, as long as all that is in the database is test data.
At a point in time, we start putting real data in the db for continuing
testing, and later it becomes a production database with ONLY real data in
it.
At the 1st of those 2 points, we stop dropping/recreating the database. We
also start making backups using pg_dump at the 1st of these 2 points in
time.
My co-worker says that he had issues restoring a database designed by an
OEM because the design of that database depended on matching keys generated
by sequences on primary table with the keys stored in dependent tables. I
think I can see how this could happen if the keys assigned by the sequences
in the primary changes were recreated during the restore. This would
result in the keys restored in the dependent tables pointing to older, now
incorrect keys.
--
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
-- Benjamin Franklin
A question about building pg-libphonenumber
OK, this seems a stupid question, but I do not see the answer. I downloaded pg-libphonenumber, and am going to try to build it for PG version 11. Looks like the last build was against version 9.6. It builds fine, the issue comes in when I try to do a make install. It wants to install in the appropriate directory tree for 9.6. Should be a simple thing to change, right? But greping around, I cannot seem to find where this is defend. it is NOT in the Makefile. Can someone tell me where to change this to try it against the version of the DB engine I am running? -- "They that would give up essential liberty for temporary safety deserve neither liberty nor safety." -- Benjamin Franklin
Re: A question about building pg-libphonenumber
On Tue, Oct 22, 2019 at 02:19:15PM +0200, Pavel Stehule wrote: > Hi > > ??t 22. 10. 2019 v 14:15 odes??latel stan napsal: > > > OK, this seems a stupid question, but I do not see the answer. > > > > > > I downloaded pg-libphonenumber, and am going to try to build it for PG > > version 11. Looks like the last build was against version 9.6. > > > > It builds fine, the issue comes in when I try to do a make install. It > > wants to install in the appropriate directory tree for 9.6. Should be a > > simple thing to change, right? But greping around, I cannot seem to find > > where this is defend. it is NOT in the Makefile. > > > > Can someone tell me where to change this to try it against the version of > > the DB engine I am running? > > > > The build and installation is controlled by small application - pg_config > > [pavel@nemesis libvterm]$ pg_config --libdir > /usr/local/pgsql/lib > [pavel@nemesis libvterm]$ pg_config --sharedir > /usr/local/pgsql/share > > if you have more versions on your comp, it is important what pg_config is > executed - depends on PATH > Thanks, yes turns out I had the wrong development package installed (I am on Ubuntu). I removed the wrong one, and installed the correct one. BUT, now I am getting this error at the "make install" step: cd '/usr/lib/postgresql/11/lib/bitcode' && /usr/lib/llvm-6.0/bin/llvm-lto -thinlto -thinlto-action=thinlink -o pg_libphonenumber.index.bc pg_libphonenumber/src/error_handling.bc pg_libphonenumber/src/pg_libphonenumber.bc pg_libphonenumber/src/packed_phone_number.bc error: can't create module summary index for buffer: Expected a single module LLVM ERROR: ThinLink didn't create an index /usr/lib/postgresql/11/lib/pgxs/src/makefiles/pgxs.mk:229: recipe for target 'install' failed m Any thoughts on this? -- "They that would give up essential liberty for temporary safety deserve neither liberty nor safety." -- Benjamin Franklin
FW: Re: A question about building pg-libphonenumber
- Forwarded message from Pavel Stehule - Date: Tue, 22 Oct 2019 14:43:17 +0200 From: Pavel Stehule To: stan Cc: pgsql-general Subject: Re: A question about building pg-libphonenumber List-Id: ??t 22. 10. 2019 v 14:37 odes??latel stan napsal: > On Tue, Oct 22, 2019 at 02:19:15PM +0200, Pavel Stehule wrote: > > Hi > > > > ??t 22. 10. 2019 v 14:15 odes??latel stan napsal: > > > > > OK, this seems a stupid question, but I do not see the answer. > > > > > > > > > I downloaded pg-libphonenumber, and am going to try to build it for PG > > > version 11. Looks like the last build was against version 9.6. > > > > > > It builds fine, the issue comes in when I try to do a make install. It > > > wants to install in the appropriate directory tree for 9.6. Should be a > > > simple thing to change, right? But greping around, I cannot seem to > find > > > where this is defend. it is NOT in the Makefile. > > > > > > Can someone tell me where to change this to try it against the version > of > > > the DB engine I am running? > > > > > > > The build and installation is controlled by small application - pg_config > > > > [pavel@nemesis libvterm]$ pg_config --libdir > > /usr/local/pgsql/lib > > [pavel@nemesis libvterm]$ pg_config --sharedir > > /usr/local/pgsql/share > > > > if you have more versions on your comp, it is important what pg_config is > > executed - depends on PATH > > > > Thanks, yes turns out I had the wrong development package installed (I am > on Ubuntu). I removed the wrong one, and installed the correct one. > > BUT, now I am getting this error at the "make install" step: > > cd '/usr/lib/postgresql/11/lib/bitcode' && /usr/lib/llvm-6.0/bin/llvm-lto > -thinlto -thinlto-action=thinlink -o pg_libphonenumber.index.bc > pg_libphonenumber/src/error_handling.bc > pg_libphonenumber/src/pg_libphonenumber.bc > pg_libphonenumber/src/packed_phone_number.bc > error: can't create module summary index for buffer: Expected a single > module > LLVM ERROR: ThinLink didn't create an index > /usr/lib/postgresql/11/lib/pgxs/src/makefiles/pgxs.mk:229: recipe for > target 'install' failed > m > > Any thoughts on this? > I have not a idea what is it, but I found this message https://github.com/PierreSenellart/provsql/issues/9 and there is a patch https://github.com/rdkit/rdkit/issues/2192#issuecomment-445579020 Probably it is problem with some JIT feature flags on Postgres 11 and higher Pavel -- > "They that would give up essential liberty for temporary safety deserve > neither liberty nor safety." > -- Benjamin Franklin > I did manage to get this package to compile and pass the minimal regression tests that are included with it. I would like to submit the modified Makefile that gets it to compile, but i have never done this with github. Can someone point me to a HOWTO to do this, please?
An issue installing an extension
I have 3 machines that _should_ be nearly idnetical. On 2 of them, i have no issues installing an extension I copiled. On one I get the followign error message: icadb=# CREATE EXTENSION IF NOT EXISTS pg_libphonenumber; ERROR: could not load library "/usr/lib/postgresql/11/lib/pg_libphonenumber.so": libphonenumber.so.7: cannot open shared object file: No such file or directory icadb=# ls -l /usr/lib/postgresql/11/lib/pg_libphonenumber.so Yet, root@ica-db:/home/stan/BUILD/pg-libphonenumber-master# ls -l /usr/lib/postgresql/11/lib/pg_libphonenumber.so -rwxr-xr-x 1 root root 182976 Oct 22 16:11 /usr/lib/postgresql/11/lib/pg_libphonenumber.so This appears to be identical to the other machines where this works, root@smokey:/home/stan/BUILD/pg-libphonenumber-master# logout stan@smokey:~/BUILD/pg-libphonenumber-master$ ls -l /usr/lib/postgresql/11/lib/pg_libphonenumber.so -rwxr-xr-x 1 root root 182976 Oct 22 09:47 /usr/lib/postgresql/11/lib/pg_libphonenumber.so What else could be wrong? -- "They that would give up essential liberty for temporary safety deserve neither liberty nor safety." -- Benjamin Franklin
Primary key definition?
I started doing the following to define my primary keys long ago and in a
universe far away:
CREATE TABLE employee (
employee_key integer DEFAULT nextval('employee_key_serial')
PRIMARY KEY ,
WEE ran into a scenario, after a total db restore on a project where we got
errors inserting new data because the keys were duplicated. Looking at a
pg_dump, it appears to me that I now understand why. Although the values f
the keys, and the data structures that reference them look like they will
get restored correctly, it appears to me that the sequences get recreated with
an initial value of 1, which means that on the next insert we will get 1 for
a key, which likely is already used. Looks like this is a different way of
defining this:
CREATE TABLE books (
id SERIAL PRIMARY KEY,
Which has the advantage of not having to manually create the sequences. Will
this also enforce that the "internally created sequence" will be initialized
to a value above the maximum key in use on a pg_restore?
--
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
-- Benjamin Franklin
Re: A question about sequences and backup/restore cycles
On Tue, Oct 22, 2019 at 12:48:41PM -0700, Adrian Klaver wrote:
> On 10/22/19 10:48 AM, stan wrote:
> Please reply to list also:
> Ccing list.
>
>
> >
> > Sorry if my description was not clear.
> >
> > No, we do not mix test, and production data. Let me try to clarify the
> > question. Looking at a pg_dump, I see the following:
> >
> >
> > CREATE SEQUENCE public.customer_key_serial
> > START WITH 1
> > INCREMENT BY 1
> > NO MINVALUE
> > NO MAXVALUE
> > CACHE 1;
> >
> >
> > Yet, in the same pg_dump file I have:
> >
> >
> >
> >
> > COPY public.customer (customer_key, cust_no, name, c_type, location,
> > bill_address_1, bill_address_2, bill_city, bill_state, bill_zip,
> > bill_country, bill_attention, bill_addressee, ship_address_1,
> > ship_address_2, ship_addressee, ship_attention, ship_city, ship_state,
> > ship_zip, office_phone_area_code, office_phone_exchange,
> > office_phone_number, office_phone_extension, cell_phone_area_code,
> > cell_phone_exchange, cell_phone_number, ship_phone_area_code,
> > ship_phone_exchange, ship_phone_number, ship_phone_extension,
> > fax_phone_area_code, fax_phone_exchange, fax_phone_number, status, modtime)
> > FROM stdin;
> > 1 12 Made Up Inc.INDUSTRIAL_CONVEYOR \N 101 Nowhere
> > Ave.\N LaGrangeGA 0 \N \N \N
> > \N \N \N \N \N \N 0 \N \N \N
> > \N \N \N \N \N \N \N \N \N
> > \N \N ACTIVE 2019-09-30 23:55:04.594203+00
> > 2 5 Jimmys Favorite Customer. PLASTICS\N
> > 56 Somewhere St.\N No Such CitySC 0 \N
> > \N \N \N \N \N \N \N \N 0
> > \N \N \N \N \N \N \N \N \N
> > \N \N \N \N \N ACTIVE 2019-09-30
> > 23:55:04.636827+00
> >
> > So it appears to me the customer table is going to get (correctly) populated
> > with the originally generated keys, yet the sequence will want to return a 1
> > the next time it is called, when a new customer gets inserted.
> >
> > Am I missing something here?
> >
>
> Yes something like this, in dump file, for non-serial sequence:
>
> CREATE SEQUENCE public.plant1_p_item_no_seq
> START WITH 1
> INCREMENT BY 1
> NO MINVALUE
> NO MAXVALUE
> CACHE 1;
>
> SELECT pg_catalog.setval('public.plant1_p_item_no_seq', 5509, true);
>
> or for serial sequence:
>
> CREATE SEQUENCE public.avail_headers_line_id_seq
> AS integer
> START WITH 1
> INCREMENT BY 1
> NO MINVALUE
> NO MAXVALUE
> CACHE 1;
>
> ALTER TABLE ONLY public.avail_headers ALTER COLUMN line_id SET DEFAULT
> nextval('public.avail_headers_line_id_seq'::regclass);
>
>
> SELECT pg_catalog.setval('public.avail_headers_line_id_seq', 53, true);
>
> If you want to see how things are run on a higher level do something like:
>
> pg_dump -Fc -d some_db -f db.out
>
> pg_restore -l db_out > db_toc.txt
>
> -l on pg_restore creates a TOC(table of contents) showing the ordering of
> the schema recreation.
>
Oh, excellent.!!
Thanks for the patience to teach me about this.
Does make me wonder what the vendor did to create our issue on their
database.
--
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
-- Benjamin Franklin
A very puzzling backup/restore problem
I have a very confusing isse. I am trying to backup and restre a signle
table .
first I dump the table.
Script started on 2019-10-24 06:29:12-0400
]0;stan@smokey: ~stan@smokey:~$ pg_dump -a --table task_instance >
task_instance.dump
Then I connect to the db, and verify that things are as expected.
]0;stan@smokey: ~stan@smokey:~$ psql
psql (11.5 (Ubuntu 11.5-3.pgdg18.04+1))
Type "help" for help.
[1m[local] stan@stan=[0m> \dt
List of relations
Schema | Name | Type | Owner
+--+---+---
public | biz_constants | table | stan
public | bom_item | table | stan
public | costing_unit | table | stan
public | customer | table | stan
public | earthquake | table | stan
public | employee | table | stan
public | expense_report_instance | table | stan
public | gl_code | table | stan
public | mfg | table | stan
public | mfg_part | table | stan
public | mfg_vendor_relationship | table | stan
public | permitted_work | table | stan
public | phone_number_test | table | stan
public | project | table | stan
public | project_budget_component | table | stan
public | project_cost_category | table | stan
public | rate | table | stan
public | salary | table | stan
public | task_instance | table | stan
public | vendor | table | stan
public | work_type | table | stan
(21 rows)
[1m[local] stan@stan=[0m> \d task_instance
[?1049h[22;0;0t[?1h= Table
"public.task_instance"
Column | Type | Collation | Nullable |
Default
---+--+---+--+--
-
task_instance | integer | | not null |
nextval('task _instance_key_serial'::regclass)
project_key | integer | | not null |
employee_key | integer | | not null |
work_type_key | integer | | not null |
hours | numeric(5,2) | | not null |
work_start| timestamp with time zone | | not null |
work_end | timestamp with time zone | | not null |
modtime | timestamp with time zone | | not null |
CURRENT_TIMES TAMP
lock | boolean | | | true
descrip | character varying| | |
Indexes:
"task_instance_pkey" PRIMARY KEY, btree (task_instance)
"task_constraint" UNIQUE CONSTRAINT, btree (employee_key, work_type_key,
pro ject_key, work_start, work_end)
Foreign-key constraints:
"task_instance_employee_key_fkey" FOREIGN KEY (employee_key) REFERENCES
empl :[K[K[?1l>[?1049l[23;0;0t[1m[local] stan@stan=[0m> \d
permitted_work
[?1049h[22;0;0t[?1h=Table
"public.permitted_work"
Column | Type | Collation | Nullable | Default
---+--+---+--+--
-
employee_key | integer | | not null |
work_type_key | integer | | not null |
permit| boolean | | not null | false
modtime | timestamp with time zone | | not null |
CURRENT_TIMES TAMP
Indexes:
"permit_constraint" UNIQUE CONSTRAINT, btree (employee_key, work_type_key)
Foreign-key constraints:
"permitted_work_employee_key_fkey" FOREIGN KEY (employee_key) REFERENCES
emp loyee(employee_key) ON DELETE RESTRICT
"permitted_work_work_type_key_fkey" FOREIGN KEY (work_type_key) REFERENCES
w ork_type(work_type_key) ON DELETE RESTRICT
Then I delete the rows from the table.
[7m(END)[27m[K[K[?1l>[?1049l[23;0;0t[1m[local] stan@stan=[0m> delete
from task_instance ;
DELETE 31
Then I try to restore from the dump file.
[1m[local] stan@stan=[0m> \i task_instance.dump
SET
SET
SET
SET
SET
set_config
(1 row)
SET
SET
SET
SET
psql:task_instance.dump:55: ERROR: relation "permitted_work" does not exist
LINE 3: permitted_work
^
QUERY: SELECT
permit FROM
permitted_work
WHERE
NEW.employee_key = permitted_work.employee_key
AND
NEW.work_type_key = permitted_work.work_type_key
CONTEXT: PL/pgSQL function public.check_permission() line 4 at SQL statement
COPY task_in
Re: A very puzzling backup/restore problem
On Thu, Oct 24, 2019 at 07:57:14AM -0500, Ron wrote:
> Why did you do "\i task_instance.dump" instead of "pg_restore"?
Ah, that may be the root of my problem. I had in the back of my mind that
the result of a pg_dump was a free standing SQL script, but that my be
exactly what is going on. I will try to use pg_restore, and make certain
that resolves my issue.
In the meantime, I found that if I deleted all the SET lines from the dump
I could restore with psql. But that is probably not the right way to do
this.
>
> On 10/24/19 5:52 AM, stan wrote:
> >
> > I have a very confusing isse. I am trying to backup and restre a signle
> > table .
> >
> > first I dump the table.
> >
> >
> > Script started on 2019-10-24 06:29:12-0400
> > ]0;stan@smokey: ~stan@smokey:~$ pg_dump -a --table task_instance >
> > task_instance.dump
> >
> > Then I connect to the db, and verify that things are as expected.
> >
> > ]0;stan@smokey: ~stan@smokey:~$ psql
> > psql (11.5 (Ubuntu 11.5-3.pgdg18.04+1))
> > Type "help" for help.
> >
> > [1m[local] stan@stan=[0m> \dt
> > List of relations
> > Schema | Name | Type | Owner
> > +--+---+---
> > public | biz_constants | table | stan
> > public | bom_item | table | stan
> > public | costing_unit | table | stan
> > public | customer | table | stan
> > public | earthquake | table | stan
> > public | employee | table | stan
> > public | expense_report_instance | table | stan
> > public | gl_code | table | stan
> > public | mfg | table | stan
> > public | mfg_part | table | stan
> > public | mfg_vendor_relationship | table | stan
> > public | permitted_work | table | stan
> > public | phone_number_test| table | stan
> > public | project | table | stan
> > public | project_budget_component | table | stan
> > public | project_cost_category| table | stan
> > public | rate | table | stan
> > public | salary | table | stan
> > public | task_instance| table | stan
> > public | vendor | table | stan
> > public | work_type| table | stan
> > (21 rows)
> >
> > [1m[local] stan@stan=[0m> \d task_instance
> > [?1049h[22;0;0t[?1h= Table
> > "public.task_instance"
> > Column | Type | Collation | Nullable |
> > Default
> > ---+--+---+--+--
> > -
> > task_instance | integer | | not null |
> > nextval('task _instance_key_serial'::regclass)
> > project_key | integer | | not null |
> > employee_key | integer | | not null |
> > work_type_key | integer | | not null |
> > hours | numeric(5,2) | | not null |
> > work_start| timestamp with time zone | | not null |
> > work_end | timestamp with time zone | | not null |
> > modtime | timestamp with time zone | | not null |
> > CURRENT_TIMES TAMP
> > lock | boolean | | | true
> > descrip | character varying| | |
> > Indexes:
> > "task_instance_pkey" PRIMARY KEY, btree (task_instance)
> > "task_constraint" UNIQUE CONSTRAINT, btree (employee_key,
> > work_type_key, pro ject_key, work_start, work_end)
> > Foreign-key constraints:
> > "task_instance_employee_key_fkey" FOREIGN KEY (employee_key)
> > REFERENCES empl :[K[K[?1l>[?1049l[23;0;0t[1m[local] stan@stan=[0m>
> > \d permitted_work
> > [?1049h[22;0;0t[?1h=Table
> > "public.permitted_work"
> > Column | Type | Collation | Nullable |
> > Default
> > ---+--+---+--+--
> > -
> > employee_key | integer | | not null |
> > work_type_key | integer |
Re: A very puzzling backup/restore problem
On Thu, Oct 24, 2019 at 07:04:11AM -0700, Adrian Klaver wrote:
> On 10/24/19 3:52 AM, stan wrote:
> >
> >
> > I have a very confusing isse. I am trying to backup and restre a signle
> > table .
> >
> > first I dump the table.
>
> Actually you are just dumping the table data.
>
> More below.
> >
> >
> > Script started on 2019-10-24 06:29:12-0400
> > ]0;stan@smokey: ~stan@smokey:~$ pg_dump -a --table task_instance >
> > task_instance.dump
> >
> > Then I connect to the db, and verify that things are as expected.
> >
> > ]0;stan@smokey: ~stan@smokey:~$ psql
> > psql (11.5 (Ubuntu 11.5-3.pgdg18.04+1))
> > Type "help" for help.
> >
> > [1m[local] stan@stan=[0m> \dt
> > List of relations
> > Schema | Name | Type | Owner
> > +--+---+---
> > public | biz_constants| table | stan
> > public | bom_item | table | stan
> > public | costing_unit | table | stan
> > public | customer | table | stan
> > public | earthquake | table | stan
> > public | employee | table | stan
> > public | expense_report_instance | table | stan
> > public | gl_code | table | stan
> > public | mfg | table | stan
> > public | mfg_part | table | stan
> > public | mfg_vendor_relationship | table | stan
> > public | permitted_work | table | stan
> > public | phone_number_test | table | stan
> > public | project | table | stan
> > public | project_budget_component | table | stan
> > public | project_cost_category| table | stan
> > public | rate | table | stan
> > public | salary | table | stan
> > public | task_instance| table | stan
> > public | vendor | table | stan
> > public | work_type| table | stan
> > (21 rows)
> >
> > [1m[local] stan@stan=[0m> \d task_instance
> > [?1049h[22;0;0t[?1h= Table
> > "public.task_instance"
> > Column | Type | Collation | Nullable |
> > Default
> > ---+--+---+--+--
> > -
> > task_instance | integer | | not null |
> > nextval('task _instance_key_serial'::regclass)
> > project_key | integer | | not null |
> > employee_key | integer | | not null |
> > work_type_key | integer | | not null |
> > hours | numeric(5,2) | | not null |
> > work_start| timestamp with time zone | | not null |
> > work_end | timestamp with time zone | | not null |
> > modtime | timestamp with time zone | | not null |
> > CURRENT_TIMES TAMP
> > lock | boolean | | | true
> > descrip | character varying| | |
> > Indexes:
> > "task_instance_pkey" PRIMARY KEY, btree (task_instance)
> > "task_constraint" UNIQUE CONSTRAINT, btree (employee_key,
> > work_type_key, pro ject_key, work_start, work_end)
> > Foreign-key constraints:
> > "task_instance_employee_key_fkey" FOREIGN KEY (employee_key)
> > REFERENCES empl :[K[K[?1l>[?1049l[23;0;0t[1m[local] stan@stan=[0m>
> > \d permitted_work
> > [?1049h[22;0;0t[?1h=Table
> > "public.permitted_work"
> > Column | Type | Collation | Nullable |
> > Default
> > ---+--+---+--+--
> > -
> > employee_key | integer | | not null |
> > work_type_key | integer | | not null |
> > permit| boolean | | not null | false
> > modtime | timestamp with time zone | | not null |
> > CURRENT_TIMES TAMP
> > Indexes:
> > "permit_constraint" UNIQUE CONSTRAINT, btree (employee_key,
> > work_type_key)
> > Foreign-key constraints:
> > "permi
Search path
I just was educated on the security issues of search path. As a result I am going to define a schema for the project we are working on. I set this in postgresql.conf search_path = 'ica , "$user", public' Here is the question. Will this path be in effect for users connecting from MS Access clients? The name of this file makes me think that it only controls instances of psql, but I need this to be effective for all connections to the database. -- "They that would give up essential liberty for temporary safety deserve neither liberty nor safety." -- Benjamin Franklin
Re: A very puzzling backup/restore problem
On Thu, Oct 24, 2019 at 07:40:29AM -0700, Adrian Klaver wrote:
> On 10/24/19 7:32 AM, stan wrote:
> > On Thu, Oct 24, 2019 at 07:04:11AM -0700, Adrian Klaver wrote:
> > > On 10/24/19 3:52 AM, stan wrote:
> > > >
> > > >
> > > > I have a very confusing isse. I am trying to backup and restre a signle
> > > > table .
> > > >
> > > > first I dump the table.
> > >
> > > Actually you are just dumping the table data.
> > >
> > > More below.
> > > >
> > > >
> > > > Script started on 2019-10-24 06:29:12-0400
> > > > ]0;stan@smokey: ~stan@smokey:~$ pg_dump -a --table task_instance >
> > > > task_instance.dump
> > > >
> > > > Then I connect to the db, and verify that things are as expected.
> > > >
> > > > ]0;stan@smokey: ~stan@smokey:~$ psql
> > > > psql (11.5 (Ubuntu 11.5-3.pgdg18.04+1))
> > > > Type "help" for help.
> > > >
> > > > [1m[local] stan@stan=[0m> \dt
> > > >List of relations
> > > >Schema | Name | Type | Owner
> > > > +--+---+---
> > > > public | biz_constants| table | stan
> > > >public | bom_item | table | stan
> > > >public | costing_unit | table | stan
> > > >public | customer | table | stan
> > > >public | earthquake | table | stan
> > > > public | employee | table | stan
> > > >public | expense_report_instance | table | stan
> > > >public | gl_code | table | stan
> > > >public | mfg | table | stan
> > > >public | mfg_part | table | stan
> > > > public | mfg_vendor_relationship | table | stan
> > > >public | permitted_work | table | stan
> > > >public | phone_number_test| table | stan
> > > >public | project | table | stan
> > > >public | project_budget_component | table | stan
> > > >public | project_cost_category| table | stan
> > > >public | rate | table | stan
> > > >public | salary | table | stan
> > > >public | task_instance| table | stan
> > > >public | vendor | table | stan
> > > >public | work_type| table | stan
> > > > (21 rows)
> > > >
> > > > [1m[local] stan@stan=[0m> \d task_instance
> > > > [?1049h[22;0;0t[?1h= Table
> > > > "public.task_instance"
> > > > Column | Type | Collation | Nullable |
> > > > Default
> > > > ---+--+---+--+--
> > > > -
> > > >task_instance | integer | | not null |
> > > > nextval('task _instance_key_serial'::regclass)
> > > >project_key | integer | | not null |
> > > >employee_key | integer | | not null |
> > > >work_type_key | integer | | not null |
> > > >hours | numeric(5,2) | | not null |
> > > >work_start| timestamp with time zone | | not null |
> > > >work_end | timestamp with time zone | | not null |
> > > >modtime | timestamp with time zone | | not null |
> > > > CURRENT_TIMES TAMP
> > > >lock | boolean | | |
> > > > true
> > > >descrip | character varying| | |
> > > > Indexes:
> > > > "task_instance_pkey" PRIMARY KEY, btree (task_instance)
> > > > "task_constraint" UNIQUE CONSTRAINT, btree (employee_key,
> > > > work_type_key, pro ject_key, work_start, work_end)
> > > > Foreign-key constraints:
> > > > "task_instance_employee_key_fkey" FOREIGN KEY (employee_key)
> > > > REFERENCES empl :[K[K[?1l>[?1049l[23;0;0t
SQL pretty pritner?
I have a presentation later in the week, and i plan on printing out some files containing SQL commands. I have used some "pretty printers" in the past for things like Perl scripts. What I am thinking of s something that bolds keywords, handles page breaks, and does some formatting. Development being done in an Ubuntu Linux environment. Anyone have a recommendation? -- "They that would give up essential liberty for temporary safety deserve neither liberty nor safety." -- Benjamin Franklin
11 -> 12 upgrade on Debian Ubuntu
I am in the middle of a project, and it looks like version 12 is now what the Debian/Ubuntu package managers want to update to. I of course, will do this first on a test machine, not the "production", or "develop,met" machines, but I thought i would solicit the group wisdom on this. Are there any things I should watch out for here? Will my data be preserved during this upgrade? We are just beginning to put real data in the "production" instance, and just yesterday, I set up a script to do backups using pg_basebackup. BTW this is the 1st time I have sued this, having used pg_dump in the past. Database is fairly small with just one tablespace if that matters. Thanks for anyone's input. -- "They that would give up essential liberty for temporary safety deserve neither liberty nor safety." -- Benjamin Franklin
Re: 11 -> 12 upgrade on Debian Ubuntu
On Thu, Nov 07, 2019 at 07:52:14AM -0800, Adrian Klaver wrote: > On 11/7/19 7:45 AM, stan wrote: > > I am in the middle of a project, and it looks like version 12 is now what > > the Debian/Ubuntu package managers want to update to. > > This should be a dist-upgrade correct? Correct. > > On a my Ubuntu instance that just installed the Postgres 12 version and > started it(also auto start in start.conf). This was just the template > databases and postgres db. No data was moved over from the 11 instance that > is running. > Thanks, that is helpful to know. > > -- > Adrian Klaver > adrian.kla...@aklaver.com -- "They that would give up essential liberty for temporary safety deserve neither liberty nor safety." -- Benjamin Franklin
Storing a time interval
I need to create a table to store terms and conditions for purchase orders. Some of the attributes of a PO include payment terms. Quite often these will be 2 periods associated with these, the first is a period on which if you pay, you receive a discount, and the 2nd is when payment is due with no discount. I am thinking of storing these two attributes as time internals. What is the wisdom as to how to declare the type of these columns? -- "They that would give up essential liberty for temporary safety deserve neither liberty nor safety." -- Benjamin Franklin
Re: Storing a time interval
On Fri, Nov 08, 2019 at 12:12:59PM -0800, Adrian Klaver wrote: > On 11/8/19 11:57 AM, Michael Lewis wrote: > > You certainly could choose to store as??tstzrange, but why not use two > > fields? > > > > https://www.postgresql.org/docs/current/rangetypes.html > > I would lean more to a composite type: > > https://www.postgresql.org/docs/11/rowtypes.html > > Range types are built around looking for values within the range, whereas > the OP is looking for two discrete values. The two field method you suggest > above also encapsulates that. > Thanks folks. After thinking over both your sugestions, I beleive teh optimum way to do this may look like: CREATE TYPE po_dates AS ( po_isssued_datetimestamptz, discount_last_date timestamptz, net_date timestamptz ); -- "They that would give up essential liberty for temporary safety deserve neither liberty nor safety." -- Benjamin Franklin
Problems modifyiong view
I am trying to add columns to a view using CREATE OR REPLACE VIEW, and I am getting the following error: ERROR: cannot change name of view column "descrip" to "contact_person_1" I suppose I can drop the view, and recreate it, but that seems to indicate that the create or replace functionality is not functioning the way I would expect. Am I missing something here? -- "They that would give up essential liberty for temporary safety deserve neither liberty nor safety." -- Benjamin Franklin
Re: Problems modifyiong view
On Thu, Nov 14, 2019 at 06:31:48AM -0800, Adrian Klaver wrote: > On 11/14/19 5:53 AM, stan wrote: > > I am trying to add columns to a view using CREATE OR REPLACE VIEW, and I am > > getting the following error: > > > > ERROR: cannot change name of view column "descrip" to "contact_person_1" > > > > I suppose I can drop the view, and recreate it, but that seems to indicate > > that the create or replace functionality is not functioning the way I would > > expect. > > > > Am I missing something here? > > > > https://www.postgresql.org/docs/11/sql-createview.html > > "CREATE OR REPLACE VIEW is similar, but if a view of the same name already > exists, it is replaced. The new query must generate the same columns that > were generated by the existing view query (that is, the same column names in > the same order and with the same data types), but it may add additional > columns to the end of the list. The calculations giving rise to the output > columns may be completely different." OK, so I see this is documented behavior. This makes it a "feature", not a "bug" correct :-) Thanks. -- "They that would give up essential liberty for temporary safety deserve neither liberty nor safety." -- Benjamin Franklin
Re: Problems modifyiong view
On Thu, Nov 14, 2019 at 10:12:22AM -0500, Tom Lane wrote: > Adrian Klaver writes: > > On 11/14/19 5:53 AM, stan wrote: > >> I am trying to add columns to a view using CREATE OR REPLACE VIEW, and I am > >> getting the following error: > >> ERROR: cannot change name of view column "descrip" to "contact_person_1" > >> Am I missing something here? > > > https://www.postgresql.org/docs/11/sql-createview.html > > > "CREATE OR REPLACE VIEW is similar, but if a view of the same name > > already exists, it is replaced. The new query must generate the same > > columns that were generated by the existing view query (that is, the > > same column names in the same order and with the same data types), but > > it may add additional columns to the end of the list. The calculations > > giving rise to the output columns may be completely different." > > Yeah, the important point being that you can only add columns at the > *end* of the view, just like you can only add table columns at the > end. The same-names-and-types check is intended to catch simple > mistakes in this area. > > If you actually want to rename an existing view column, use > ALTER TABLE ... RENAME COLUMN ... for that. (We probably ought > to offer an ALTER VIEW spelling of that, but we don't ATM. > ALTER TABLE works though.) Thanks. I am just starting to explore this area at all. I thought I just added a column to a table, and did not realize that it was apended as the last column. Don't see why I care in that case, though. -- "They that would give up essential liberty for temporary safety deserve neither liberty nor safety." -- Benjamin Franklin
mysysconf ?
I am presently running on a Ubuntu 18.04 instance, and as you know Debian/Ubuntu have upgraded to version 12. i have not completed the upgrade yet, so I am in the situation of still having a version 11 server, attaching from vversion 12 psql. I was troubleshooting something a few minutes ago, and wound up looking t the logfiles. I discovered that every time I connect like this I get an error about the msysconf relation not existing. This appears to be harmless, but I was wondering what the story on this is? -- "They that would give up essential liberty for temporary safety deserve neither liberty nor safety." -- Benjamin Franklin
Making "invisible" characters visible ? (psql)
I added a column to an existing table, so there should be nothing (actually the default, I suppose) in this column for all existing rows. However if I do: select new_column from modified_table ; I get the total number of rows in the table, but the lines on the screen have no visible characters. I was expecting these to be nulls, and this select to return nothing. How can i make these "invisible" characters visible? -- "They that would give up essential liberty for temporary safety deserve neither liberty nor safety." -- Benjamin Franklin
Re: Making "invisible" characters visible ? (psql)
On Wed, Nov 20, 2019 at 09:22:02AM -0500, Brian Dunavant wrote: > On Wed, Nov 20, 2019 at 9:16 AM stan wrote: > > > > > How can i make these "invisible" characters visible? > > > > > > > In psql, by default it displays nulls as nothing. You can specify what > they should display as with: > > \pset null '' > > Chances are those are all nulls, and will now display as whatever you set > them to. If they aren't, let us know. That worked, and yes they are NULLs, guess I expected to get 0 rows, as the only column I requested contained ONLY NULL's. basic misunderstanding on my part, i suppose. Thank you for your help. -- "They that would give up essential liberty for temporary safety deserve neither liberty nor safety." -- Benjamin Franklin
Isolation of multiple databse instances provided by a single postgres server
I am working on a fairly small application to use for managing a companies business. I have a "production" instance hosted by one of the cloud providers, and 2 other instances. This is fairly new to me. In the past, I have created applications by keeping a set of scripts that can be used to rebuild the database, and pg_dump to restore the date. Based on some recommendations I am using pg_basebackup to backup the production instance nightly. My background is primarily Oracle. I realize looking at the way pg_basebackup works that multiple database instances, provided by one server are actually stored in the same physical OS files. We have traditionally (in the Postgres world) had a sandbox, that we used for upgrades, and testing development methodologies, and this seems to be supported pretty well by pg_dump. Now that I know "too much" I am concerned about hosting the sandbox on the same Postgres instance. Recognizing that this is a fairly small application, what are wiser folks than I recommendations? Should I run the sandbox from different Postgres server, possibly even on a different machine? Is pg_dump still good way to move the production instance to the sandbox, and perhaps even the other way around? -- "They that would give up essential liberty for temporary safety deserve neither liberty nor safety." -- Benjamin Franklin
Re: Isolation of multiple databse instances provided by a single postgres server
On Wed, Nov 20, 2019 at 04:24:40PM -0600, Ron wrote: > On 11/20/19 4:03 PM, stan wrote: > > I am working on a fairly small application to use for managing a companies > > business. > > > > I have a "production" instance hosted by one of the cloud providers, and 2 > > other instances. This is fairly new to me. In the past, I have created > > applications by keeping a set of scripts that can be used to rebuild the > > database, and pg_dump to restore the date. Based on some recommendations I > > am using pg_basebackup to backup the production instance nightly. My > > background is primarily Oracle. I realize looking at the way pg_basebackup > > works that multiple database instances, provided by one server are actually > > stored in the same physical OS files. > > > > > > We have traditionally (in the Postgres world) had a sandbox, that we used > > for upgrades, and testing development methodologies, and this seems to be > > supported pretty well by pg_dump. > > > > Now that I know "too much" I am concerned about hosting the sandbox on the > > same Postgres instance. > > What specifically do you mean by "instance"??? (I know what it means in the > SQL Server world, and in Postgres all the databases accessible via a single > $PGDATA are called a *cluster*.) Sorry for my incorrect terminology. I am probably confused about the technology here. Let me try to explain what I think I understand. It seems to me that I can have one Postgres "server" running listening on a single port on a single machine. It appears that the data files for this "server" are managed internally by the Postgres server instance, and I have no control of what is stored where in them. In an Oracle world, I can create tablespaces, which have a relationship to OS files, and I can explicitly control what objects are stored in which tablespaces (OS file), thus, for example, when I do a hot backup, I put a specific tablespaces in backup mode, and can then safely copy this OS file (yes I have to properly deal with archive logs). Thus I would be somewhat comfortable have to distinct "instance: provided by that one Oracle "server". It appears to me that, within this one Postgres "instance", there are 2 levels of "isolation", which are database, and schemas. Is this correct? If so, how does this cores pond to physical on disk storage? > > > Recognizing that this is a fairly small application, what are wiser folks > > than I recommendations? > > > > Should I run the sandbox from different Postgres server, possibly even on a > > different machine? Is pg_dump still good way to move the production > > instance to the sandbox, and perhaps even the other way around? > > Running CAT, STG, UAT, DEV, etc on different VMs is certainly one solution, > isolating them from each other. Makes sense. > > OTOH, you can initdb multiple clusters on the same host, accessing them via > different $PGDATA variables and port numbers. That is consistent with what I thought I understood. Thanks for taking time to educate me. -- "They that would give up essential liberty for temporary safety deserve neither liberty nor safety." -- Benjamin Franklin
Help with authentication on Debain/Ubuntu installation
I am trying to set up to do some work with pg_dump, and I would like to be able to connect from my normal user to do this. This is on a Ubunt 18.04 installation. I have added the follwing to pg_hba.conf: hostall all 0.0.0.0/0 md5 hostssl all all 0.0.0.0/0 md5 But when I try to login like this: psql stan postgres -W I get the following error, and yes, I set postgres'es password to something I know: tan@stantest:~$ psql stan postgres -W Password: psql: error: could not connect to server: FATAL: Peer authentication failed for user "postgres" stan@stantest:~$ What am I doing wrong? -- "They that would give up essential liberty for temporary safety deserve neither liberty nor safety." -- Benjamin Franklin
Re: Help with authentication on Debain/Ubuntu installation
On Thu, Nov 21, 2019 at 02:05:09PM +0100, Magnus Hagander wrote: > On Thu, Nov 21, 2019 at 1:46 PM stan wrote: > > > I am trying to set up to do some work with pg_dump, and I would like to be > > able to connect from my normal user to do this. This is on a Ubunt 18.04 > > installation. I have added the follwing to pg_hba.conf: > > > > hostall all 0.0.0.0/0 md5 > > hostssl all all 0.0.0.0/0 md5 > > > > But when I try to login like this: > > > > psql stan postgres -W > > > > I get the following error, and yes, I set postgres'es password to something > > I know: > > > > tan@stantest:~$ psql stan postgres -W > > Password: > > psql: error: could not connect to server: FATAL: Peer authentication > > failed for user "postgres" > > stan@stantest:~$ > > > > What am I doing wrong? > > > > > By default, Debian has a row in pg_hba.conf that enforces peer specifically > for the postgres superuser when connecting over unix sockets (which is a > very good thing from a security perspective). You will have to find that > row further up in the file and comment it out (since the first row that > matches will be used). Thanks, found it, I believe: local all postgres Good to know first match wins, that is different than some other systems I am used to. -- "They that would give up essential liberty for temporary safety deserve neither liberty nor safety." -- Benjamin Franklin
