Re: [GENERAL] Another unexpected behaviour
On Wed, Jul 20, 2011 at 9:58 AM, Rob Richardson rob.richard...@rad-con.comwrote: It seems to me that it is inherently wrong to perform any operation on a database that depends on the order in which records are retrieved, without specifying that order in an ORDER BY clause. The update t1 set f1 = f1 + 1 assumes that the operation will be performed in an order that guarantees that the highest unchanged record will be the next record processed. I don't believe that any database system should be required to support an action like this. RobR I disagree. I think it depends upon all records being modified before any are constraint-checked, which may or may not be a reasonable requirement. If you think of it as a true set operation, it seems like a perfectly reasonable thing to do (increment the value of column N in each of the records of this set). It seems odd that this should work: -- drop unique index -- single update statement -- apply unique index But just single update statement won't. -- Rick Genter rick.gen...@gmail.com
Re: [GENERAL] Slow query with sub-select
On Jul 16, 2011, at 4:14 PM, - - wrote: I would like to count rows in q whose mid does not exist in t. I would write such a query like this: SELECT COUNT(*) FROM q LEFT OUTER JOIN t ON (t.mid = q.mid) WHERE t.mid IS NULL; And I would make sure there was an index on t.mid. (And for 9.2, as I understand it, q.mid as well, since I believe in 9.2 PostgreSQL will be able to compute the result strictly from the indexes without hitting the base tables.) -- Rick Genter rick.gen...@gmail.com
Re: [GENERAL] Error Importing CSV File
I think the COPY is expecting the first line of the file to be a header and it doesn't find a column named 96799 in the table. Try putting a line at the top of the file that looks like this: zip_code,latitude,longitude,city,state,county On Fri, Jul 15, 2011 at 10:10 AM, Susan Cassidy scass...@edgewave.comwrote: There seems to be no reason it should be looking for an integer, if your table definition as shown is correct. You don't have any integers listed. Also, why does it think that the column id is 96799? Stupid question, but are you logged into the right database? Maybe a different db has a different table definition for that table name? Maybe it is using a different file than you think it is? I had to specify the full path to get the COPY to work on my test database. When I created the same table as you specified, and created a test CSV file, it worked fine both with and without quotes. Susan -Original Message- From: pgsql-general-ow...@postgresql.org [mailto: pgsql-general-ow...@postgresql.org] On Behalf Of Bryan Nelson Sent: Friday, July 15, 2011 9:04 AM To: pgsql-general@postgresql.org Subject: [GENERAL] Error Importing CSV File I am having problems importing a CSV file of sample data for testing in a web app. Columns Types --- zip_code - text lattitude - float8 longitude - float8 city - text state - text county - text Some Sample Data From CSV File -- 96799,-7.209975,-170.7716,PAGO PAGO,AS,AMERICAN SAMOA 96941,7.138297,151.503116,POHNPEI,FM,FEDERATED STATES OF MICRO 96942,7.138297,151.503116,CHUUK,FM,FEDERATED STATES OF MICRO COPY Command COPY geo_data FROM 'geo_data2.csv' DELIMITERS ',' CSV; Error Message - ERROR: invalid input syntax for integer: 96799 CONTEXT: COPY geo_data, line 1, column id: 96799 I can't figure out why it keeps choking with invalid syntax for integer since the field was created as text. Any and all help greatly appreciated! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Rick Genter rick.gen...@gmail.com
Re: [GENERAL] Unexpected results when joining on date fields
I don't think you understand what JOIN does. Think of it as a double-nested FOR loop: for each record that has the value on the left side of the JOIN, it will match all records on the right side of the JOIN that meet the ON criteria. For example, if I have two tables: A (i int, j int): i j 1 1 2 1 3 2 4 2 5 3 6 3 and B (k int, j int) k j 10 1 11 1 12 2 13 2 14 3 15 3 Then if I do SELECT COUNT(*) FROM A JOIN B ON A.j = B.j I'll get 12. Each record in A matches 2 records in B on the value of j. Study the following transcript: bash-3.2$ bin/psql -d g2_master Password: psql (8.4.4) Type help for help. g2_master=# CREATE TABLE A (i int, j int); CREATE TABLE g2_master=# CREATE TABLE B (k int, j int); CREATE TABLE g2_master=# INSERT INTO A VALUES (1, 1), (2, 1), (3, 2), (4, 2), (5, 3), (6, 3); INSERT 0 6 g2_master=# INSERT INTO B VALUES (10, 1), (11, 1), (12, 2), (13, 2), (14, 3), (15, 3); INSERT 0 6 g2_master=# SELECT COUNT(*) FROM A JOIN B ON A.j = B.j; count --- 12 (1 row) g2_master=# SELECT * FROM A JOIN B ON A.j = B.j; i | j | k | j ---+---++--- 1 | 1 | 10 | 1 1 | 1 | 11 | 1 2 | 1 | 10 | 1 2 | 1 | 11 | 1 3 | 2 | 12 | 2 3 | 2 | 13 | 2 4 | 2 | 12 | 2 4 | 2 | 13 | 2 5 | 3 | 14 | 3 5 | 3 | 15 | 3 6 | 3 | 14 | 3 6 | 3 | 15 | 3 (12 rows) g2_master=# On Sun, Jul 10, 2011 at 4:58 PM, Tim Uckun t...@basediary.com wrote: I have two tables, traffic and sales. Each one has a date field and lists the traffic and sales broken down by various parameters (multiple rows for each date). If I run select (select count(*) from traffic) as traffic, (select count(*) from sales) as sales; I get the following 49383;167807 if I run select count(*) from traffic t inner join sales s on t.date = s.date I get 24836841. If I change the join to a left join, right join, full join I get the same number of records. So I created a data table which just has the dates in it and ran this query. select count(d.date) from dates d inner join traffic t on t.date = d.date inner join sales s on s.date = d.date And I get the same number 24836841 Same goes for right joins on the above query. Left joins of course give a different answer as there are more dates in the date table than there are in the other tables. I am a bit perplexed by what is happening here. Cheers -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Rick Genter rick.gen...@gmail.com
Re: [GENERAL] Insufficient privileges.
On Thu, Jul 7, 2011 at 10:03 AM, Dave Coventry dgcoven...@gmail.com wrote: I am getting the following error message in my Drupal install. PDOException: SQLSTATE[42501]: Insufficient privilege: 7 ERROR: permission denied for sequence currenttest_id_seq: INSERT INTO currentTest (score) VALUES (:db_insert_placeholder_0); This is a table that I created using the postgres super user. I have tried to grant the drupal user (drupaluser) privileges to the table with: GRANT ALL ON currentTest to drupaluser; but this fails to resolve the issue. Can anyone suggest a way forward? From the message I'd say that the drupal user doesn't have access to the sequence, which is a separate object from the table. -- Rick Genter rick.gen...@gmail.com
Re: [GENERAL] Insufficient privileges.
On Thu, Jul 7, 2011 at 10:26 AM, Dave Coventry dgcoven...@gmail.com wrote: Hi Rick, Thanks for the response. What is the sequence? and how do I grant the privileges needed to insert data into the database? Is it a postgres issue? Yes. I don't know drupal, so I don't know the correct way to fix this. My guess is that something wasn't installed/configured correctly. -- Rick Genter rick.gen...@gmail.com
Re: [GENERAL] DROP TABLE Appears to Fail
Silly question, but did you try it with a semicolon after the drop table? # drop table station_type; I've noticed that if you are in the middle of a statement and issue a \ command, psql ignores the SQL you've typed in and just does the \ command. On Tue, Jun 28, 2011 at 3:34 PM, Rich Shepard rshep...@appl-ecosys.comwrote: I cannot recall issuing a DROP TABLE command from psql that did not work, but seem to have this as a new experience. When I look at the database table list with '\d' I see public | station_type | table| rshepard public | station_type_statype_seq | sequence | rshepard and I want to drop and recreate these with a typo fixed. But, the drop commands do not appear to work: # drop table station_type # \d public | station_type | table| rshepard public | station_type_statype_seq | sequence | rshepard I fail to see what I'm doing incorrectly and would appreciate a clue stick to set me on the proper path. TIA, Rich -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/**mailpref/pgsql-generalhttp://www.postgresql.org/mailpref/pgsql-general -- Rick Genter rick.gen...@gmail.com
Re: [GENERAL] DROP TABLE Appears to Fail
After issuing the \d you are still in the middle of your command. Witness the following copy/paste of a terminal session: bash-3.2$ ./psql Password: psql (8.4.4) Type help for help. postgres=# create table foo (bar int); CREATE TABLE postgres=# drop table foo postgres-# \d List of relations Schema | Name | Type | Owner +--+---+-- public | foo | table | postgres (1 row) postgres-# drop table foo; ERROR: syntax error at or near drop LINE 2: drop table foo; ^ postgres=# drop table foo; DROP TABLE postgres=# This is on 8.4.4. The semicolon is required. On Tue, Jun 28, 2011 at 3:53 PM, Rich Shepard rshep...@appl-ecosys.comwrote: On Tue, 28 Jun 2011, Rick Genter wrote: Silly question, but did you try it with a semicolon after the drop table? Rick, See my answer to Andy: that's incorrect syntax and psql complains. I've noticed that if you are in the middle of a statement and issue a \ command, psql ignores the SQL you've typed in and just does the \ command. But there is no continuation command just 'drop table tablename'. Thanks, Rich -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/**mailpref/pgsql-generalhttp://www.postgresql.org/mailpref/pgsql-general -- Rick Genter rick.gen...@gmail.com
Re: [GENERAL] In a view, how do I cause fields with possible NULLs to be treated as a blank string in a replace operation?
Either use '' as some_type, or use COALESCE(some_type, ''). On Mon, Jun 27, 2011 at 9:53 AM, Asfand Qazi (Sanger Institute) aq2.san...@gmail.com wrote: Hello, So I have am playing with a view to test the feasibility of a technique for storing some data. It basically goes something like this: CREATE VIEW formatted_table AS SELECT name, replace(some_template, '@', some_type) AS some_field FROM some_table; some_template is something like 'foo@bar' or 'foobar' (note the missing template character). some_type is a single letter like 'a' or 'b', or it can be NULL. The above view works fine for rows where some_type is a letter, and some_field ends up as 'fooabar' or whatever. However, when some_type is NULL, some_field ends up as NULL as well. I understand that this is expected behaviour, but how do I cause the view to treat a some_type of NULL as an empty string, so that some_field simply ends up as 'foobar'? Hope that was clear. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Rick Genter rick.gen...@gmail.com
Re: [GENERAL] proper regex_replace() syntax
On Wed, Jun 1, 2011 at 10:22 AM, Geoffrey Myers li...@serioustechnology.com wrote: I want to use regex_replace() to replace characters in multiple records. What I would like to do is this: select regex_replace((select fname from table), 'z', 'Z')); The problem is, the subquery returns more then one row. So, is there a way to do what I'm trying to do? That is, replace the same character in multiple records using regex_replace() ? I think what you want is: SELECT regex_replace(fname, 'z', 'Z') FROM table; This should return a recordset where each row has one column which is the result of regex_replace() on the corresponding row of table. -- Rick Genter rick.gen...@gmail.com
Re: [GENERAL] syntax for updating an aliased table
The UPDATE statement when multiple tables are involved always drives me nuts. I think what you need to do is remove all of the old. from the SET clause and use triple. in the WHERE clause instead of old. - and remove the old table alias from the UPDATE. On Thu, May 26, 2011 at 9:38 AM, Andy Chambers achamb...@mcna.net wrote: I'm confused about the correct syntax for updating an aliased table. I want to update triple from triple_updates where the data is different and tried to use the following update triple old set old.obln = new.obln, old.ointv = new.ointv, old.otime = new.otime, old.oflt = new.oflt, old.ostr = new.ostr, old.oint = new.oint, old.oda = new.oda, old.uasid = new.uasid from triple_update as new where (old.s = new.s and old.g = new.g) and ( old.obln new.obln or old.ointv new.ointv or old.otime new.otime or old.oflt new.oflt or old.ostr new.ostr or old.oint new.oint or old.oda new.oda or old.uasid new.uasid) ...but postgres complains about not having column old in the triple table. Putting an as between triple and old on the first line didn't make any difference. If I leave out the old alias, it complains about the columns being ambiguous. How should the query above be changed to be syntactically correct? Thanks, Andy -- Rick Genter rick.gen...@gmail.com
Re: [GENERAL] Miidpoint between two long/lat points? (earthdistance?)
On Wed, May 25, 2011 at 9:47 AM, Carlo Stonebanks stonec.regis...@sympatico.ca wrote: I need to calculate the long/lat values between a line demarcated by two long/lat points. The points will be very close, but there is the 180 degree problem to consider, so a simple average won’t work. Does anyone know of a function or have a formula that will work using geo long/lat values? I don’t see anything obvious in the earthdistance module. The simplest way to deal with the 180 degree problem is to remember that you can add 360 degrees to a long and get a value that should continue to work. So, assuming West is negative, -175 (175 degrees West) is the same as -175+360 = 185 (185 degrees East). Then you don't have to worry about wraparound. If the result is 180, subtract 360. -- Rick Genter rick.gen...@gmail.com
Re: [GENERAL] how to start a procedure after postgresql started.
On May 23, 2011, at 9:46 PM, jun yang wrote: thanks for the info,i am just not have such deep learn of pg internal, i am on user level,not hacker,so the mail is in pgsql-general,not hacker list. What you are asking to do is not a typical user function. It would be more appropriate for a hacker list. -- Rick Genter rick.gen...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Memcached for Database server
On May 16, 2011, at 10:09 PM, Adarsh Sharma wrote: Dear all, I need to research on Memcache in the next few days. What I want to know is it worth to have memcahed enable in our Mysql/ Postgres Production Servers. We have databases from 20 to 230 GB and it's not the OLTP just a simple OLAP where data is fetched and stored in some meaningful format. What are benefits why we used memcahed? What are the bottlenecks to meet? You need to read about memcached. Memcached is not something you enable. You have to program to it. -- Rick Genter rick.gen...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Memcached for Database server
On May 16, 2011, at 10:31 PM, Adarsh Sharma wrote: Rick Genter wrote: On May 16, 2011, at 10:09 PM, Adarsh Sharma wrote: Dear all, I need to research on Memcache in the next few days. What I want to know is it worth to have memcahed enable in our Mysql/ Postgres Production Servers. We have databases from 20 to 230 GB and it's not the OLTP just a simple OLAP where data is fetched and stored in some meaningful format. What are benefits why we used memcahed? What are the bottlenecks to meet? You need to read about memcached. Memcached is not something you enable. You have to program to it. Thanks Rick, just one question.. At what stage we need memcached what is the purpose of using it. I just want to know whether it is worth to use memcahced or not as per our requirements. I can't tell you; you didn't state your requirements. I recommend that you go to the memcached web site and research it. http://www.memcached.org -- Rick Genter rick.gen...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Growing a live database
I have a PostgreSQL 8.4.4 database that is running 24/7. The drive that the database is on is becoming full and I need to expand it. We are currently doing log-shipping of the WAL files to a slave system to run in a hot standby mode. I have two servers: S1 (currently running as master) and S2 (currently running as slave) My current plan is to do the following: - fail S1 over to S2 by doing pg_ctl stop on S1, then ending the recovery process on S2 and let it come up as the master - add a new larger drive to S1 - swap roles; backup S2 and restore it on S1, then start log shipping from S2 to S1 - let the S1 catch up on the log files from S2 - fail S2 back over to S1 by doing pg_ctl stop on S2, then ending the recovery process on S1 and let it come up as the master - add a new larger drive to S2 - backup S1 and restore it on S2, then start log shipping from S1 to S2 I believe that this accomplishes the goal (increasing available drive space) with a minimum amount of down time. Am I thinking correctly, or have I missed something? -- Rick Genter rick.gen...@gmail.com
Re: [GENERAL] Multiple table relationship constraints
On Thu, May 5, 2011 at 3:20 PM, Jack Christensen ja...@hylesanderson.eduwrote: What is the best way to handle multiple table relationships where attributes of the tables at the ends of the chain must match? Example: CREATE TABLE achievements( achievement_id serial PRIMARY KEY, ... ); CREATE TABLE achievement_versions( achievement_version_id serial PRIMARY KEY, achievement_id integer NOT NULL REFERENCES achievements, ... ); CREATE TABLE achievement_attempts( achievement_attempt_id serial PRIMARY KEY, achievement_version_id integer NOT NULL REFERENCES achievement_versions, ... ); CREATE TABLE actions( action_id serial PRIMARY KEY, ... ) CREATE TABLE achievement_attempt_actions( achievement_attempt_id integer NOT NULL REFERENCES achievement_attempts, action_id integer NOT NULL REFERENCES actions, PRIMARY KEY( achievement_attempt_id, action_id) ); The achievement_attempt_actions table links actions to achievement_attempts. For a link to be valid a number of attributes of actions must match attributes of achievements and achievement_attempts. This means an update to any of these 5 tables could invalidate the chain. How can I eliminate the possibility for this type of erroneous data? I might not be understanding your question, but isn't that what your foreign key references do? For example, you can't update achievement_attempt_id in the achievement_attempt table if there is an achievement_attempt_actions record that refers to it since that would break the reference. (Not that you want to be updating primary key values in the first place...) -- Rick Genter rick.gen...@gmail.com
Re: [GENERAL] Multiple table relationship constraints
On Thu, May 5, 2011 at 3:50 PM, Jack Christensen ja...@hylesanderson.eduwrote: The trick is there are additional attributes of actions and achievements such as a category that must match for the link to be valid. These attributes are not part of the primary key of either record and can and do change. So your data is denormalized? (The category appears in 2 tables?) Don't do that. Create a view that joins your two tables together instead if you need a single entity that contains data from multiple sources. Then you won't have any of the data integrity issues you're worried about. -- Rick Genter rick.gen...@gmail.com
Re: [GENERAL] Multiple table relationship constraints
On Thu, May 5, 2011 at 4:14 PM, Jack Christensen ja...@hylesanderson.eduwrote: It's not denormalized. It is an attribute that both tables have that have to match for it to be a valid link. Here's a contrived example: CREATE TABLE dorms( dorm_id serial PRIMARY KEY, gender varchar NOT NULL, ... ); CREATE TABLE people( person_id serial PRIMARY KEY, gender varchar NOT NULL, ... ); CREATE TABLE room_assignments( person_id integer NOT NULL REFERENCES people, dorm_id integer NOT NULL REFERENCES dorms, ... ); Men should only be assignable to men's dorms and women should only be assignable to women's dorms. On occasion a person's or dorm's gender needs to be updated. I want to make sure that doesn't cause a room assignment to become invalid. In this example, adding gender to room_assignments and using composite foreign keys is fairly straight forward -- but in my actual domain I have 5+ tables and 2+ attributes involved in the relationship. Hm. I think the way I would handle this is to put the business logic for inserting/updating into the room_assignments table into one or more functions and have a special user that owns the tables and owns the functions and declare the functions to be SECURITY DEFINER. Revoke INSERT/UPDATE/DELETE access to the tables from all other users. Then you grant your regular users EXECUTE access to the functions. The functions run as the user that created them, so they will have direct INSERT/UPDATE/DELETE access to the tables while your regular users won't. -- Rick Genter rick.gen...@gmail.com
Re: [GENERAL] pervasiveness of surrogate (also called synthetic) keys
On May 2, 2011, at 10:52 PM, Craig Ringer wrote: SSN? What if they don't live in the US or aren't a citizen? Non-citizens can have SSNs (they have to if they work in the US). -- Rick Genter rick.gen...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Pass age function to extract function
Try removing the keyword interval (i.e., EXTRACT(MINUTE FROM TestRunTime)). Since TestRunTime is a column name, I think if you wanted to cast it as an INTERVAL you'd specify EXTRACT(MINUTE FROM TestRunTime::INTERVAL), but since TestRunTime is already an INTERVAL, the cast is redundant. On Mon, May 2, 2011 at 4:16 PM, Adam Bruss abr...@awrcorp.com wrote: Hello, How can one pass a non string literal interval to the extract function? For example: SELECT starttime, *extract(minute from interval testruntime) as runtime from ( select age(endtime, starttime) as testruntime*, ref_testnames_serial, starttime, endtime, dense_rank() over (order by starttime desc) dr from dtprfrm764.orion_tests where ref_testnames_serial = 389 and pass = true) x WHERE dr = 20 ORDER BY starttime asc Does ‘minute from interval’ only accept string literals? Thanks, Adam Adam Bruss Development Engineer AWR Corporation/Simulation Technology Applied Research 11520 N. Port Washington Rd., Suite 201 Mequon, WI 53092 USA P: 1.262.240.0291 x104 F: 1.262.240.0294 E: abr...@awrcorp.com W: http://www.awrcorp.com -- Rick Genter rick.gen...@gmail.com
[GENERAL] Looking for Silicon Valley/Peninsula/San Francisco users group
I've been using PostgreSQL pretty steadily for the past year and am interesting in joining/attending a users group meeting. I've searched for a users group in the San Francisco/Peninsula/Silicon Valley area (I'm in Redwood City), but all I've found are references to a San Francisco group where the last update/meeting was 2 years ago. Is there such a group in this area? Thanks. -- Rick Genter rick.gen...@gmail.com
