[GENERAL] user name and password woes
The dawg that originally set up postgres 8.0 on one of our Windows 2003 servers has left the company, and I have taken it over. I need to use pg_dump on that server. When I try to, I get user Administrator does not exist. Next I try createuser U postgres W administrator P. The response is ERROR: user administrator already exists. I know PG requires a user with the same name as the Windows user and that should be administrator on this server. Oddly I can connect to PG on this server through PGAdmin (through postgres user?). Ultimately I want to create a database with a new name from an existing database; Ive done this from the command line on other servers. Cant, or dont know how to, do it through PGAdmin. Ive also tried to delete and recreate the administrator user from PGAdmin but I still cant use pg_dump. Tried all the passwords my colleagues can think of. Ive searched the forums and tried various things like setting pg_hba.conf to trust temporarily and restarting. Any suggestions greatly appreciated! Thanks in advance. Charles Mortell Advanced Planning Technologies, Inc. Mississippi River port of La Crosse, WI cmortell at apt-cafm dot com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] update record with two-column primary key
Beautiful, Scott. You nailed it. Thanks for the help! -Original Message- From: Scott Marlowe [mailto:[EMAIL PROTECTED] Sent: Monday, November 12, 2007 5:10 PM To: Charles Mortell Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] update record with two-column primary key OK, a followup. The problem is that your where clause in your update rule isn't selective enough, so you're actually trying to update all the rows that match just the one column in your where clause. Here's an example: Note that I've disabled your pk so you can see what's happening: drop table projectdata.data_business_list cascade; CREATE TABLE projectdata.data_business_list ( item_id int4 NOT NULL, business int4 NOT NULL, comments varchar(256) -- , CONSTRAINT data_business_list_pkey PRIMARY KEY (item_id, business) ); CREATE OR REPLACE VIEW projectdata.business_list AS SELECT t.item_id, t.business, t.comments FROM projectdata.data_business_list t; CREATE OR REPLACE RULE update_buslistview AS ON UPDATE TO projectdata.business_list DO INSTEAD UPDATE projectdata.data_business_list SET business = new.business, item_id=new.item_id, comments = new.comments WHERE item_id = old.item_id; -- and business=old.business; insert into projectdata.data_business_list (item_id, business, comments) values (1,2,'abc'), (1,3,'xyz'); UPDATE projectdata.business_list SET business = 13 Where item_id = 1 and business = 2; select * from projectdata.business_list ; You'll see the output is this: item_id | business | comments -+--+-- 1 | 13 | abc 1 | 13 | abc Note that even the comments are the same. However, if we make your where clause in your rule more selective, by removing the ; and -- in the middle of it, and it looks like this: WHERE item_id = old.item_id and business=old.business; and run the query again, we get: select * from projectdata.business_list ; item_id | business | comments -+--+-- 1 |3 | xyz 1 | 13 | abc Now we test it with a real primary key and it also works the same. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] update record with two-column primary key
Using PG 8.0 on Windows, I have a table 'business_list' with a two column primary key. It's a linking table and it's primary keys are the keys from the two tables I am linking: item_id and business. Should I be able to update one of those primary key fields? Here is the SQL: UPDATE projectdata.business_list SET business = 13 Where item_id = 1 and business = 7; It causes the following error: ERROR: duplicate key violates unique constraint data_business_list_pkey I need the 'business' value in the WHERE to locate the proper record to update but it also seems to be preventing the update. Is something wrong or is it just not possible to update a two-column primary key? I didn't see any directly relevant info in the forum or documentation. Should I add a surrogate key? That would be disappointing since the existing 2 column PK does satisfy 3rd Normal Form. Thanks in advance for any suggestions! Charles Mortell Advanced Planning Technologies, Inc. Mississippi River port of La Crosse, WI cmortell at apt-cafm dot com ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] How determine a Views dependents
Fellow Postgressors, I took over an existing PG 8.0 database running on Windows XP. I need to update a view so I know I need to drop the existing view and all the objects that depend on it. The DB is large enough that I can't just look through it with PGAdmin. In an organized way, how can I find out what those dependents are? If I need to use pg_depend, I could use some help in how to do that. Thanks! Charles Mortell Advanced Planning Technologies, Inc. On the Mississippi River at La Crosse, WI cmortell at apt-cafm dot com ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
