[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
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 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
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] Multiple table relationship constraints
On Thu, May 5, 2011 at 3:20 PM, Jack Christensen wrote: > 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 wrote: > 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 wrote: > 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
[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] 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
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] 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] 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 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] proper regex_replace() syntax
On Wed, Jun 1, 2011 at 10:22 AM, Geoffrey Myers 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] 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] 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 wrote: > 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-general<http://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 wrote: > 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 '. > > Thanks, > > > Rich > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/**mailpref/pgsql-general<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 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 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] 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 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] 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 wrote: > 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] 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] Another unexpected behaviour
On Wed, Jul 20, 2011 at 9:58 AM, Rob Richardson
wrote:
> 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
