gmane.comp.db.postgresql.sql http://blog.gmane.org/gmane.comp.db.postgresql.sql hourly 1 1901-01-01T00:00+00:00 Gmane http://gmane.org/img/gmane-25t.png http://gmane.org How to right justify text in psql? http://comments.gmane.org/gmane.comp.db.postgresql.sql/29854 <pre>I am running postgresql 9.2. I am assuming it would be a function of psql to right justify text, but I can't find any way to do this. Is there a way to right justify just one text column? Thanks Brian </pre> Brian Sherwood 2013-05-17T15:26:58 array_agg() with join question http://comments.gmane.org/gmane.comp.db.postgresql.sql/29850 <pre>To summarize my question at the top, why is it that when I did the JOIN, the array_agg results reversed order? I had a function that ran the following query: SELECT timeslot, pollgrpid, array_agg(outval) FROM (SELECT * FROM rrd.a_current WHERE pollgrpid=8 ORDER BY timeslot, dsnum) AS foo WHERE timeslot &gt;= now() - '1 hour'::interval AND timeslot &lt;= now() + '1 hour'::interval GROUP BY timeslot, pollgrpid ORDER BY timeslot; timeslot | pollgrpid | array_agg ------------------------+-----------+---------------------------- 2013-05-14 08:58:00-04 | 8 | {1,1,0.00125217437744141} 2013-05-14 09:00:00-04 | 8 | {1,1,0.000999927520751953} 2013-05-14 09:02:00-04 | 8 | {1,1,0.00318312644958496} 2013-05-14 09:04:00-04 | 8 | {1,1,0.000761985778808594} 2013-05-14 09:06:00-04 | 8 | {1,1,0.000777959823608398} 2013-05-14 09:08:00-04 | 8 | {1,1,0.101096868515015} 2013-05-14 09:10:00-04 | 8 | {1,1,0.86168384552002} 2013-05-14 09:12:00-04 | 8 | {1,1,0.00656795501708984} 2013-05-14 09:14:00-04 | 8 | {1,1,0.102259159088135} 2013-05-14 09:16:00-04 | 8 | {1,1,0.000636100769042969} I wanted to include missing timestamps in my results, so I joined it with generate_series. SELECT timeslot, pollgrpid, array_agg(outval) FROM ( SELECT generate_series(rrd_timeslot('avail', now() - '58 minutes'::interval), now() + '1 hour'::interval, '2 minutes') AS timeslot ) AS bar LEFT JOIN (SELECT * FROM rrd.a_current WHERE pollgrpid=8 AND timeslot &gt;= now() - '1 hour'::interval AND timeslot &lt;= now() + '1 hour'::interval ORDER BY timeslot, dsnum) AS foo USING(timeslot) GROUP BY timeslot, pollgrpid ORDER BY timeslot; timeslot | pollgrpid | array_agg ------------------------+-----------+---------------------------- 2013-05-14 09:02:00-04 | 8 | {0.00318312644958496,1,1} 2013-05-14 09:04:00-04 | 8 | {0.000761985778808594,1,1} 2013-05-14 09:06:00-04 | 8 | {0.000777959823608398,1,1} 2013-05-14 09:08:00-04 | 8 | {0.101096868515015,1,1} 2013-05-14 09:10:00-04 | 8 | {0.86168384552002,1,1} 2013-05-14 09:12:00-04 | 8 | {0.00656795501708984,1,1} 2013-05-14 09:14:00-04 | 8 | {0.102259159088135,1,1} 2013-05-14 09:16:00-04 | 8 | {0.000636100769042969,1,1} 2013-05-14 09:18:00-04 | 8 | {0.000638008117675781,1,1} 2013-05-14 09:20:00-04 | 8 | {0.174574136734009,1,1} 2013-05-14 09:22:00-04 | 8 | {0.1006920337677,1,1} 2013-05-14 09:24:00-04 | 8 | {0.00069117546081543,1,1} 2013-05-14 09:26:00-04 | 8 | {0.114289045333862,1,1} 2013-05-14 09:28:00-04 | 8 | {0.116230010986328,1,1} 2013-05-14 09:30:00-04 | 8 | {0.0349528789520264,1,1} The array_agg results are reversed. I had to ODER BY timeslot, dsnum desc on the right of the join to make it match. I am curious as to why this happened. I am running 9.2.4. Thanks, Woody iGLASS Networks www.iglass.net </pre> George Woodring 2013-05-14T14:08:56 transaction isolationa level - SERIALIZABLE http://comments.gmane.org/gmane.comp.db.postgresql.sql/29847 <pre>Hi list, I have some problems with SERIALIZABLE isolation level, namely my users are plagued with concurrency errors. As of postgres 9.1 (which I'm running) there has been a change to SERIALIZABLE logic, unfortunately my application has not been updated to work with the new logic. I don't have an access to it's code and the only thing I can do is to report the issue to the authors. But before I do it, since I don't actually need SERIALIZABLE for my use, is it possible to have transactions always run in default READ COMMITTED mode, regardless of application level SET SESSION CHARACTERISTICS AS TRANSACTION command ... ? (like e.g in postgres 8.1 where SERIALIZABLE = READ COMMITED) regards mk </pre> Marcin Krawczyk 2013-05-13T09:22:16 Order of execution http://comments.gmane.org/gmane.comp.db.postgresql.sql/29843 <pre>I have an UPDATE query which performs several opertions in one table. UPDATE table01 SET field1 = (query1 may contain any field), field2 = (query1 may contain any field), field3 = (query1 may contain any field) WHERE (condition) query1, query2 and query3 perform a logic to determine which records will be updated. Does field1 updates first, then field2 and, lastly, field3? or What is the order in which updates are executed? If I need fields to be updated in a certain order, should I use 3 UPDATE commands instead? Respectfully, Jorge Maldonado </pre> JORGE MALDONADO 2013-05-06T00:04:18 Correct implementation of 1:n relationship with n>0? http://comments.gmane.org/gmane.comp.db.postgresql.sql/29834 <pre>It hit me today that a 1:n relationship can't be implemented just by a single foreign key constraint if n&gt;0. I must have been sleeping very deeply not to notice this. E.g. if there is a table "list" and another table "list_item" and the relationship can be described as "every list has at least one list_item" (and every list_item can only be part of one list, but this is trivial). A "correct" solution would require (at least?): 1. A foreign key pointing from each list_item to its list 2. Another foreign key pointing from each list to one of its list_item. But this must be a list_item that itself points to the same list, so just a simple foreign key constraint doesn't do it. 3. When a list has more than one list_item, and you want to delete the list_item that its list points to, you have to "re-point" the foreign key constraint on the list first. Do I need to use stored proceures then for all insert, update, delete actions? (4. Anything else that I've not seen?) Is there a "straight" (and tested) solution for this in PostgreSQL, that someone has already implemented and that can be re-used? No, I definitely don't want to get into programming PL/PgSQL myself. especially if the solution has to warrant data integrity under all circumstances. Such as concurrent update, insert, delete etc. TIA, Sincerely, Wolfgang </pre> Wolfgang Keller 2013-04-30T14:39:05 execute http://comments.gmane.org/gmane.comp.db.postgresql.sql/29830 <pre> Hi every one, I'm trying to do the follow: declare aCad text[][] begin ... execute 'aCad:=aCad array[['||var1||','||var2||']]' ... end; language plpgsql but I get the follow error: sintax erro near to "aCad" aCad := aCad || array[rCad.cmp,rSql.codfor] ^ I guess I should not use "execute" besides of insert, update, delete ... but how could I do in this case ? Thanks every one. </pre> Mauricio Cruz 2013-04-25T16:17:06 Not Able to Delink the old tables http://comments.gmane.org/gmane.comp.db.postgresql.sql/29829 <pre>Hi, When i tried to delink the old tables i'm getting the following error NOTICE: SELECT COUNT(*) AS count from pg_tables where schemaname='fr' and tablename='check_deny_2013_04_17' CONTEXT: PL/pgSQL function "delink_partitions_tables" line 16 at IF NOTICE: Lock SqlLOCK TABLE fr.check_deny_2013_04_17 NOWAIT ; NOTICE: -20000 delink_partitions_tables (1 row) But in the pg_stat_activity there is no locked process &amp; there is no process running related to this process... Thanks in Advance :) SHABEER. </pre> shabeer ece 2013-04-23T08:01:51 check for overlapping time intervals http://comments.gmane.org/gmane.comp.db.postgresql.sql/29826 <pre>Hi, I am on postgresql 9.1 and use at table like CREATE TABLE timetable( tid INTEGER PRIMARY KEY, gid INTEGER REFERENCES groups(gid), day DATE, s TIME NOT NULL, --- start e TIME NOT NULL, --- end CHECK (e &gt; s)); Now, i need a constraint to prevent overlapping timeintervals in this table. For this, i use a trigger: CREATE OR REPLACE FUNCTION validate_timetable() RETURNS trigger AS $$ BEGIN IF TG_OP = 'INSERT' THEN IF EXISTS( SELECT * FROM timetable WHERE gid = NEW.gid AND day = NEW.day AND s &lt; NEW.e AND e &gt; NEW.s) THEN RAISE EXCEPTION 'overlapping intervals'; END IF; ELSIF TG_OP = 'UPDATE' THEN IF EXISTS( SELECT * FROM timetable WHERE gid = NEW.gid AND day = NEW.day AND tid &lt;&gt; OLD. tid AND s &lt; NEW.e AND e &gt; NEW.s) THEN RAISE EXCEPTION 'overlapping intervals'; END IF; END IF; RETURN NEW; END; $$ LANGUAGE plpgsql; CREATE TRIGGER validate_timetable BEFORE INSERT OR UPDATE ON timetable FOR EACH ROW EXECUTE PROCEDURE validate_timetable(); Is there a simpler way to check for overlapping timeintervals? I ask this question, because i have more similar tables with similar layout and would have to write similar functions again and again. Thank you for any hints Wolfgang </pre> Wolfgang Meiners 2013-04-22T10:19:17 Table indexes in a SELECT with JOIN´s http://comments.gmane.org/gmane.comp.db.postgresql.sql/29824 <pre>Let´s suppose that I have a SELECT statement that joins more than one table and such a statement is order by fields that belong not only to the table in the FROM but also by fields in the tables that are part of the JOIN´s. How does indexes should be considered in a case like this? For example: SELECT artist_name, author_name, producer_name, song_name FROM tbl_songs INNER JOIN tbl_artists ON tbl_artists.artistid = tbl_songs_artistid INNER JOIN tbl_authors ON tbl_authors.authorid = tbl_songs_authorid INNER JOIN tbl_producers ON tbl_producers.producerid = tbl_songs_producerid ORDER BY song_name Respectfully, Jorge Maldonado </pre> JORGE MALDONADO 2013-04-20T16:32:00 SQL query with Overlapping date time ranges http://comments.gmane.org/gmane.comp.db.postgresql.sql/29822 <pre>I have a temporary table with columns zone_name, nodeid, nodelabel, nodegainedservice, nodelostservice Zone1, 3, Windows-SRV1, "2012-11-27 13:10:30+08", "2012-11-27 13:00:40+08" Zone1, 5, Windows-SRV2, "2012-12-20 13:10:30+08", "2012-12-18 13:00:40+08" .... ....Many zones and many nodes and same nodes with gained service and lost service many times.`nodegainedservice` meaning node has come alive and `nodelostservice` meaning node has gone down.How could I make a query to fetch each zone availability in a period?e.g., Zone1 have Windows-SRV1, Windows-SRV2. Find how many times and how long both servers are down at the same time or Zone1 is down.Please use the below sample data zonename, nodeid, nodelabel, noderegainedservice, nodelostserviceZone127Srv12013-02-21 10:04:56+08 2013-02-21 09:48:48+08Zone127Srv12013-02-21 10:14:01+082013-02-21 10:09:27+08Zone127Srv12013-02-21 10:26:29+082013-02-21 10:24:20+08Zone1 27Srv12013-02-21 11:27:24+082013-02-21 11:25:15+08Zone127Srv1 2013-02-28 16:24:59+082013-02-28 15:52:59+08Zone127Srv12013-02-28 16:56:19+082013-02-28 16:40:18+08Zone127Srv12013-02-28 17:09:28+08 2013-02-28 16:58:38+08Zone127Srv12013-02-28 17:39:50+082013-02-28 17:29:47+08Zone127Srv12013-03-01 09:39:36+082013-02-28 19:12:26+08Zone1 27Srv12013-03-01 13:35:07+082013-03-01 12:10:03+08Zone127Srv1 2013-03-04 11:04:14+082013-03-04 10:48:07+08Zone127Srv12013-03-06 16:36:56+082013-03-06 16:33:10+08Zone127Srv12013-03-13 13:54:11+08 2013-03-06 16:43:51+08Zone127Srv12013-03-14 11:43:28+082013-03-13 19:09:31+08Zone127Srv12013-03-18 18:38:16+082013-03-15 18:55:31+08Zone1 27Srv12013-03-22 11:18:57+082013-03-22 09:53:38+08Zone127Srv1 2013-03-28 16:48:27+082013-03-26 10:23:47+08Zone127Srv12013-04-04 10:33:24+082013-04-04 10:32:51+08Zone127Srv12013-04-04 11:48:54+08 2013-04-04 11:48:23+08Zone127Srv12013-04-08 19:01:34+082013-04-08 19:01:03+08Zone127Srv12013-04-08 19:37:05+082013-04-08 19:31:38+08Zone1 27Srv12013-04-08 21:48:07+082013-04-08 21:47:35+08Zone127Srv1 2013-04-08 21:54:02+082013-04-08 21:52:29+08Zone127Srv12013-04-10 09:33:53+082013-04-10 09:32:34+08Zone127Srv12013-04-10 12:01:01+08 2013-04-10 12:00:30+08Zone127Srv12013-04-10 14:57:25+082013-04-10 14:56:53+08Zone127Srv12013-04-10 16:25:50+082013-04-10 16:24:31+08Zone1 27Srv12013-04-10 16:57:02+082013-04-10 16:56:19+08Zone127Srv1 2013-04-10 17:17:37+082013-04-10 17:15:18+08Zone127Srv12013-04-11 21:35:43+082013-04-11 21:31:50+08Zone139Srv22013-04-05 13:15:53+08 2013-04-05 12:26:04+08Zone139Srv22013-04-05 13:23:10+082013-04-05 13:21:14+08Zone139Srv22013-04-05 13:35:23+082013-04-05 13:33:32+08Zone1 39Srv22013-04-05 15:17:25+082013-04-05 14:25:51+08Zone139Srv2 2013-04-07 16:49:56+082013-04-05 17:43:01+08Zone139Srv22013-04-09 22:32:19+082013-04-07 20:00:44+08Zone139Srv22013-04-09 22:38:02+08 2013-04-09 22:37:40+08Zone139Srv22013-04-10 11:16:21+082013-04-10 11:13:32+08Zone139Srv22013-04-10 16:15:37+082013-04-10 15:44:05+08Zone1 39Srv22013-04-10 16:23:07+082013-04-10 16:20:59+08Zone139Srv2 2013-04-10 16:48:46+082013-04-10 16:33:29+08Zone139Srv22013-04-10 17:19:11+082013-04-10 17:04:10+08Zone139Srv22013-04-11 21:39:21+08 2013-04-11 21:28:51+08Zone139Srv22013-04-11 22:05:02+082013-04-11 21:49:44+08Zone139Srv22013-04-15 14:02:11+082013-04-12 16:41:48+08Zone1 39Srv22013-04-17 00:00:00+082013-04-15 20:50:40+08Zone129Srv3 2013-03-12 17:20:02+082013-03-12 17:16:49+08Zone129Srv32013-03-12 18:08:30+082013-03-12 17:55:43+08Zone113Srv42013-01-09 17:23:59+08 2013-01-09 17:19:13+08Zone113Srv42013-01-10 16:54:27+082013-01-10 16:53:48+08Zone113Srv42013-01-10 16:59:55+082013-01-10 16:56:56+08Zone1 13Srv42013-01-10 17:07:10+082013-01-10 17:04:11+08Zone113Srv4 2013-01-10 17:13:54+082013-01-10 17:10:42+08Zone113Srv42013-01-16 10:31:45+082013-01-15 14:47:25+08Zone113Srv42013-01-24 17:52:35+08 2013-01-24 17:20:31+08Zone113Srv42013-01-28 17:24:25+082013-01-28 16:53:10+08Zone113Srv42013-02-18 12:16:45+082013-02-18 12:10:05+08Zone1 13Srv42013-02-18 15:00:26+082013-02-18 14:12:04+08Zone113Srv4 2013-02-18 17:11:10+082013-02-18 17:00:58+08Zone113Srv42013-02-21 10:14:24+082013-02-21 10:13:45+08Zone113Srv42013-02-25 14:29:39+08 2013-02-25 13:44:50+08Zone113Srv42013-02-26 10:40:08+082013-02-26 10:19:33+08Zone113Srv42013-03-04 11:37:34+082013-03-04 11:00:56+08Zone1 13Srv42013-04-10 16:25:27+082013-04-10 16:24:07+08Zone113Srv4 2013-04-10 17:17:39+082013-04-10 17:14:40+08Zone113Srv42013-04-11 21:39:05+082013-04-11 21:28:22+08 -- View this message in context: http://postgresql.1045698.n5.nabble.com/SQL-query-with-Overlapping-date-time-ranges-tp5752610.html Sent from the PostgreSQL - sql mailing list archive at Nabble.com.</pre> chinnaobi 2013-04-19T03:41:06 Primary DB stuck becuase of unavailable standby (synchronized streaming) - please help http://comments.gmane.org/gmane.comp.db.postgresql.sql/29819 <pre>Hi, I am using Postgres DB with stand by database, configured with streaming in synchronized mode (each commit on primary DB waits for commit on secondary DB). Sometimes we suffer from network issues and as consequences, secondary machine is not available. In these situations, our application is stuck and do not respond anymore, since no confirmation comes from the secondary Postgres (obviously... it is down or unavailable....). Is there any parameter that I can use, to enable recovering from hanging on until someone of us re-establish connection to secondary DB? Maybe some configuration parameter that sets the time interval for being stuck because of unavailable secondary Postgres? Please help, Thanks, Yuval Sofer BMC Software CTM&amp;D Business Unit DBA Team 972-52-4286-282 yuval_sofer&lt; at &gt;bmc.com&lt;mailto:yuval_sofer&lt; at &gt;bmc.com&gt; </pre> Sofer, Yuval 2013-04-18T15:41:22 ALTER USER abc PASSWORD - what's going on ??? http://comments.gmane.org/gmane.comp.db.postgresql.sql/29813 <pre>Hi, I can't change the pass for my user. When I invoke ALTER USER abc PASSWORD newpassword it changes ok but then I can't login with my newpassword... It says authetication failed. My pg_hba.conf has md5 entry for the ip I connect from. It used to work ok, but I accidentaly changed the password for the user abc from pgAdmin and now I can't change it back to what it was. What's going on ? regards mk </pre> Marcin Krawczyk 2013-04-18T10:59:49 copy from csv, variable filename within a function http://comments.gmane.org/gmane.comp.db.postgresql.sql/29811 <pre>Hello, i have try the following: </pre> basti 2013-04-18T07:26:09 building psycopg2 http://comments.gmane.org/gmane.comp.db.postgresql.sql/29810 <pre>Hello, I'm trying to put together a fully contained runtime environment and am having trouble with fully qualified path names to dynamic library being included in _psycopg2.so (I'm on a Mac). See below: ^&lt; at &gt;^L^&lt; at &gt;^&lt; at &gt;^&lt; at &gt;`^&lt; at &gt;^&lt; at &gt;^&lt; at &gt;^X^&lt; at &gt;^&lt; at &gt;^&lt; at &gt;^B^&lt; at &gt;^&lt; at &gt;^&lt; at &gt;^&lt; at &gt;^E^E^&lt; at &gt;^&lt; at &gt;^&lt; at &gt;^E^&lt; at &gt;/Users/tburnett/Development/Lab7/main/alpha/build/BPD/lib/libpq.5.dylib^&lt; at &gt;^L^&lt; at &gt;^&lt; at &gt;^&lt; at &gt;h^&lt; at &gt;^&lt; at &gt;^&lt; at &gt;^X^&lt; at &gt;^&lt; at &gt;^&lt; at &gt;^B^&lt; at &gt;^&lt; at &gt;^&lt; at &gt;^&lt; at &gt;^&lt; at &gt;^A^&lt; at &gt;^&lt; at &gt;^&lt; at &gt;^A^&lt; at &gt;/Users/tburnett/Development/Lab7/main/alpha/build/BPD/lib/libssl.1.0.0.dylib^&lt; at &gt;^&lt; at &gt;^&lt; at &gt;^&lt; at &gt;^L^&lt; at &gt;^&lt; at &gt;^&lt; at &gt;h^&lt; at &gt;^&lt; at &gt;^&lt; at &gt;^X^&lt; at &gt;^&lt; at &gt;^&lt; at &gt;^B^&lt; at &gt;^&lt; at &gt;^&lt; at &gt;^&lt; at &gt;^&lt; at &gt;^A^&lt; at &gt;^&lt; at &gt;^&lt; at &gt;^A^&lt; at &gt;/Users/tburnett/Development/Lab7/main/alpha/build/BPD/lib/libcrypto.1.0.0.dylib^&lt; at &gt;^L^&lt; at &gt;^&lt; at &gt;^&lt; at &gt;8^&lt; at &gt;^&lt; at &gt;^&lt; at &gt;^X^&lt; at &gt;^&lt; at &gt;^&lt; at &gt;^B^&lt; at &gt;^&lt; at &gt;^&lt; at &gt;^&lt; at &gt;^C©^&lt; at &gt;^&lt; at &gt;^&lt; at &gt;^A^&lt; at &gt;/usr/lib/libSystem.B.dylib^&lt; at &gt;^&lt; at &gt;^&lt; at &gt;^ How can I remove the fully qualified paths so that I can find them through DYLD_LIBRARY_PATH? Thanks. Thomas. </pre> Thomas Burnett 2013-04-16T18:42:08 Peer-review requested of soft-delete scheme http://comments.gmane.org/gmane.comp.db.postgresql.sql/29809 <pre> Hello, I'm working on designing a soft-delete scheme for our key entity-- there are 17 other tables that reference our key table via RI. Let's call the table "foo". I understand there are a couple common design patterns for soft-deletes: 1. Use a trigger to move the rows to a "tombstone table". 2. Add an "deleted flag" to the table. The "tombstone table" approach is out for us because all the RI. The "deleted flag" approach would be a natural fit for us. There's already a "state" column in the table, and there will only be a small number rows in the "soft-deleted" state at a time, as we'll hard-delete them after a few months. The table has only about about 10,000 rows in it anyway. My challenge is that I want to make very hard or impossible to access the soft-deleted rows through SELECT statements. There are lots of selects statements in the system. My current idea is to rename the "foo" table to something that would stand-out like "foo_with_deleted_rows". Then we would create a view named "foo" that would select all the rows except the soft-deleted views. I think that would make it unlikely for a developer or reviewer to mess up SELECTs involving the statement. Inserts/Updates/Delete statements against the table are view, and coud reference the underlying table directly. Is this sensible? Is there another approach to soft-deletes I should be considering? Thanks! Mark </pre> Mark Stosberg 2013-04-16T16:24:00 pivot query with count http://comments.gmane.org/gmane.comp.db.postgresql.sql/29805 <pre>The following is my code and results: select '1' "num_ads", (case when r.region_code = 1000 then ( select count(*) from ( select userid from user_event_stg2 where userid in ( select userid from user_region where region_code = 1000) and messagetype = 'impression' group by userid having count(userid) = 1) as foo) else 0 end) as "NorthEast", (case when r.region_code = 2000 then ( select count(*) from ( select userid from user_event_stg2 where userid in ( select userid from user_region where region_code = 2000) and messagetype = 'impression' group by userid having count(userid) = 1) as foo) else 0 end) as "NorthWest", (case when r.region_code = 3000 then ( select count(*) from ( select userid from user_event_stg2 where userid in ( select userid from user_region where region_code = 3000) and messagetype = 'impression' group by userid having count(userid) = 1) as foo) else 0 end) as "SouthEast", (case when r.region_code = 4000 then ( select count(*) from ( select userid from user_event_stg2 where userid in ( select userid from user_region where region_code = 4000) and messagetype = 'impression' group by userid having count(userid) = 1) as foo) else 0 end) as "SouthWest", (case when r.region_code = 5000 then ( select count(*) from ( select userid from user_event_stg2 where userid in ( select userid from user_region where region_code = 5000) and messagetype = 'impression' group by userid having count(userid) = 1) as foo) else 0 end) as "Middle of Nowhere" from user_region u, region r where u.region_code = r.region_code group by r.region_code; num_ads | NorthEast | NorthWest | SouthEast | SouthWest | Middle of Nowhere ---------+-----------+-----------+-----------+-----------+------------------- 1 | 0 | 0 | 3898 | 0 | 0 1 | 3895 | 0 | 0 | 0 | 0 1 | 0 | 3873 | 0 | 0 | 0 1 | 0 | 0 | 0 | 3915 | 0 How can I get this output on to a single line? num_ads | NorthEast | NorthWest | SouthEast | SouthWest | Middle of Nowhere ---------+-----------+-----------+-----------+-----------+------------------- 1 | 3895 | 3873 | 3898 | 3915 | 0 Thanks. </pre> Tony Capobianco 2013-04-13T00:28:33 pivot query with count http://comments.gmane.org/gmane.comp.db.postgresql.sql/29804 <pre>The following is my code and results: select '1' "num_ads", (case when r.region_code = 1000 then ( select count(*) from ( select userid from user_event_stg2 where userid in ( select userid from user_region where region_code = 1000) and messagetype = 'impression' group by userid having count(userid) = 1) as foo) else 0 end) as "NorthEast", (case when r.region_code = 2000 then ( select count(*) from ( select userid from user_event_stg2 where userid in ( select userid from user_region where region_code = 2000) and messagetype = 'impression' group by userid having count(userid) = 1) as foo) else 0 end) as "NorthWest", (case when r.region_code = 3000 then ( select count(*) from ( select userid from user_event_stg2 where userid in ( select userid from user_region where region_code = 3000) and messagetype = 'impression' group by userid having count(userid) = 1) as foo) else 0 end) as "SouthEast", (case when r.region_code = 4000 then ( select count(*) from ( select userid from user_event_stg2 where userid in ( select userid from user_region where region_code = 4000) and messagetype = 'impression' group by userid having count(userid) = 1) as foo) else 0 end) as "SouthWest", (case when r.region_code = 5000 then ( select count(*) from ( select userid from user_event_stg2 where userid in ( select userid from user_region where region_code = 5000) and messagetype = 'impression' group by userid having count(userid) = 1) as foo) else 0 end) as "Middle of Nowhere" from user_region u, region r where u.region_code = r.region_code group by r.region_code; num_ads | NorthEast | NorthWest | SouthEast | SouthWest | Middle of Nowhere ---------+-----------+-----------+-----------+-----------+------------------- 1 | 0 | 0 | 3898 | 0 | 0 1 | 3895 | 0 | 0 | 0 | 0 1 | 0 | 3873 | 0 | 0 | 0 1 | 0 | 0 | 0 | 3915 | 0 How can I get this output on to a single line? Thanks. </pre> Tony Capobianco 2013-04-12T21:14:28 Clarity on how LOCK interacts with INHERIT http://comments.gmane.org/gmane.comp.db.postgresql.sql/29802 <pre>Hi, The following when run in PostgreSQL 9.2.x seems to tell that if I have two tables A and B, such that if B inherits A, then, even if I don't have any rights on A, I can get an ACCESS EXCLUSIVE LOCK on Table A via Table B. This isn't necessarily wrong, I just want be sure that this is what PostgreSQL allows us to do. Sample SQL: postgres=# CREATE SCHEMA lock_schema1; CREATE SCHEMA postgres=# SET search_path = lock_schema1; SET postgres=# CREATE ROLE lock_rol5; CREATE ROLE postgres=# CREATE TABLE lock_tbl5 (a BIGINT); CREATE TABLE postgres=# CREATE TABLE lock_tbl6 (b BIGINT) INHERITS (lock_tbl5); CREATE TABLE postgres=# GRANT USAGE ON SCHEMA lock_schema1 TO lock_rol5; GRANT postgres=# GRANT ALL ON TABLE lock_tbl6 TO lock_rol5; GRANT postgres=# REVOKE ALL ON TABLE lock_tbl5 FROM lock_rol5; REVOKE postgres=# SET ROLE lock_rol5; SET postgres=&gt; SET search_path=lock_schema1; SET postgres=&gt; BEGIN TRANSACTION; BEGIN postgres=&gt; LOCK TABLE ONLY lock_tbl6 IN access EXCLUSIVE MODE; LOCK TABLE postgres=&gt; ROLLBACK; ROLLBACK postgres=&gt; BEGIN TRANSACTION; BEGIN postgres=&gt; LOCK TABLE lock_tbl6 * IN access EXCLUSIVE MODE; LOCK TABLE postgres=&gt; ROLLBACK; ROLLBACK postgres=&gt; BEGIN TRANSACTION; BEGIN postgres=&gt; LOCK TABLE lock_tbl5 IN access EXCLUSIVE MODE; ERROR: permission denied for relation lock_tbl5 STATEMENT: LOCK TABLE lock_tbl5 IN access EXCLUSIVE MODE; ERROR: permission denied for relation lock_tbl5 postgres=&gt; ROLLBACK; ROLLBACK postgres=&gt; RESET ROLE; RESET postgres=# DROP TABLE lock_tbl6; DROP TABLE postgres=# DROP TABLE lock_tbl5; DROP TABLE postgres=# REVOKE ALL ON SCHEMA lock_schema1 FROM lock_rol5; REVOKE postgres=# DROP ROLE lock_rol5 ; DROP ROLE postgres=# Thanks -- Robins Tharakan </pre> Robins Tharakan 2013-04-12T20:59:27 Slow update with ST_Contians() http://comments.gmane.org/gmane.comp.db.postgresql.sql/29797 <pre>UPDATE tbl SET city=s.city_name FROM shp AS s WHERE ST_CONTAINS(s.city_geom,geom); With the code above i can add exact city to a GPS point. It runs about 45-50 min on 50 million rows. There are about 4000 cities in the "city" table that have to be checked. I have another shape file with 19 counties in a given country(only 1 country). It takes it about 1,5 hour to add counties to points. i have a third shape file with 52 EU countries. It runs almost 25 hours with the same sql query. Every table has index by geom, like: CREATE INDEX idx_txt_geom ON txt USING GIST(geom); Q: Why is it so slow when it has to check only a few polygons ? -- View this message in context: http://postgresql.1045698.n5.nabble.com/Slow-update-with-ST-Contians-tp5751814.html Sent from the PostgreSQL - sql mailing list archive at Nabble.com. </pre> androclos 2013-04-11T16:38:39 Restrict FOREIGN KEY to a part of the referenced table http://comments.gmane.org/gmane.comp.db.postgresql.sql/29796 <pre>Hello, is there any best practice method how to create a foreign key that only allows values from those rows in the referenced table that fulfill an additional condition? First I present two pseudo solutions to clarify what I would like to do. They are no real solutions, because they are neither SQL standard nor postgresql compliant. The third solution actually works, but I do not like it for reason I will explain later: CREATE TABLE parent ( id SERIAL, discriminator INT NOT NULL, attribute1 VARCHAR, ... ); Pseudo solution 1 (with a hard-coded value): CREATE TABLE child ( id SERIAL NOT NULL, parent_id INT NOT NULL, attribute2 VARCHAR, ..., FOREIGN KEY ( parent_id, 42 ) REFERENCES parent ( id, discriminator ) ); Pseudo solution 2 (with a nested SELECT statement): CREATE TABLE child ( id SERIAL NOT NULL, parent_id INT NOT NULL, attribute2 VARCHAR, ..., FOREIGN KEY ( parent_id ) REFERENCES ( SELECT * FROM parent WHERE discriminator = 42 ) ( id ) ); Working solution: CREATE TABLE child ( id SERIAL NOT NULL, parent_id INT NOT NULL, parent_discriminator INT NOT NULL DEFAULT 42, attribute2 VARCHAR, ..., FOREIGN KEY ( parent_id, parent_discriminator ) REFERENCES parent ( id, discriminator ), CHECK ( parent_discriminator = 42 ) ); The third solution work, but I do not like it, because it adds an extra column to the table that always contains a constant value for the sole purpose to be able to use this column in the FOREIGN KEY clause. On the one hand this is a waste of memory and on the other hand it is not immediately obvious to an outside person what the purpose of this extra column and CHECK clause is. I am convinced that any administrator who follows me might get into problems to understand what this is supposed to be. I would like to have a more self-explanatory solution like 1 or 2. I wonder if there is something better. Best regards, Matthias ---------------------------------------------------------------------- Matthias Nagel Willy-Andreas-Allee 1, Zimmer 506 76131 Karlsruhe Telefon: +49-721-8695-1506 Mobil: +49-151-15998774 e-Mail: matthias.h.nagel&lt; at &gt;gmail.com ICQ: 499797758 Skype: nagmat84 </pre> Matthias Nagel 2013-04-11T07:55:20 Advice for index design http://comments.gmane.org/gmane.comp.db.postgresql.sql/29794 <pre>I have a table of artists with fields like the ones below: * Name * Birthday * Sex (male/female) Our application offers a catalog of artists where a user can select a range of birthdays and/or sex. For example, a user can get an artists catalog for those male artists who were born between May 1, 1970 and May 1, 1990 ordered by birthday and, within each birthday date, ordered by name. I can think of defining one index for birthday, one index for name, and one index for sex. Also, I can think of defining a compound index for birthday + name. Also there could be a compound index for sex + name. Another option could be a compound index for birthday + sex + name. There are many possible combinations. What is a good index design approach? Maybe, setting simple separate indexes (one for each field) would work fine if I need to retrieve data in different combinatios, but I am not sure. Maybe compound indexes is better. I will very much appreciate your advice. Respectfully, Jorge Maldonado </pre> JORGE MALDONADO 2013-04-10T22:30:47 Search Engine Search the mailing list at Gmane query http://search.gmane.org/?group=$group=gmane.comp.db.postgresql.sql