Re: [GENERAL] OT hardware recommend
I would start with something like this http://www.ebay.com/itm/2U-24-bay-2-5-Supermicro-Server-X8DTH-iF-2x-Xeon-Quad-Core-32GB-RAM-SAS2-216EL1-/222132081393?hash=item33b81a92f1:g:UzYAAOSwR5dXSQVw With it being 2U you can then pop out the motherboard and go with anything more modern you wanted in terms of the motherboard/cpu/ram. $1200 all in would certainly get you a nice system with that as the base. John On Fri, Jun 17, 2016 at 1:36 PM, Andy Colson wrote: > Hi all. > > I have access to quite a few laptop HD's (10 to 15 of them at least), and > thought that might make a neat test box that might have some good IO speed. > > Needs to be cheap though, so linux with software raid, rack mount > preferred but not required. > > Anyone have any experience with anything like that? $2K might be > possible, painful, but possible. > > Suggestions? > > Thanks for your time, > > -Andy > > > -- > 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] Add relcreated (timestamp) column to pg_class catalog to record the time an object was created
On Thu, Apr 21, 2016 at 9:55 AM, Melvin Davidson wrote: > Please, just ONE LOGICAL VALID argument, not speculation. Otherwise, stop > with the nay saying. I think you should look seriously at the suggestion offered of using an event trigger to get what you desire here. I think the most logical argument is that there is no need to do anything if a solution is available on the table right now. Whether or not you agree with someone's fear of the side effects of a larger solution - if you can use a tool that is already available you should. You are also more than happy to produce any patch that you would like to offer up for inclusion. I completely appreciate that for any reason you may not be able to offer up such a patch - but on the other hand - you cannot demand that someone create the patch for you if they do not desire. John
Re: [GENERAL] Group by range in hour of day
Assuming 3 things
Table name - test
Column names - start_time, end_time
Added an id column (int) to distinguish each record in the table
You can go with this. (my apologies for formatting issues)
with
slots as (
select *
fromgenerate_series(0,1439) as s(slot)
),
slots_hours as (
select slot,
slot / 60 as hour
from slots
),
minutes as (
select id,
date_part('hour', start_time) * 60 + date_part('minute',
start_time) as start_minute,
date_part('hour', end_time) * 60 + date_part('minute',
end_time) as end_minute
fromtest
),
minute_slots as (
select id,
slot,
hour
fromminutes
joinslots_hours
on minutes.start_minute <= slots_hours.slot
and minutes.end_minute > slots_hours.slot
)
select hour,
count(*) / 60.0 as sum,
count(distinct id) as count
fromminute_slots
group byhour
I'm certain there are more elegant solutions possible - but you can grasp
each step this way.
John
On Mon, Mar 16, 2015 at 2:57 PM, Israel Brewster
wrote:
> I have a table with two timestamp columns for the start time and end time
> of each record (call them start and end).I'm trying to figure out if there
> is a way to group these records by "hour of day", that is the record should
> be included in the group if the hour of the day for the group falls
> anywhere in the range [start,end]. Obviously each record may well fall into
> multiple groups under this scenario.
>
> The goal here is to figure out, for each hour of the day, a) what is the
> total number of "active" records for that hour, and b) what is the total
> "active" time for those records during the hour, with an ultimate goal of
> figuring out the average active time per record per hour.
>
> So, for simplified example, if the table contained three records:
>
> start | end
> -
> 2015-03-15 08:15 | 2015-03-15 10:45
> 2015-03-15 09:30 | 2015-03-15 10:15
> 2015-03-15 10:30 | 2015-03-15 11:30
>
>
> Then the results should break out something like this:
>
> hour | count | sum
> -
> 8 |1 | 0.75
> 9 |2 | 1.5
> 10 |3 | 1.5
> 11 |1 | 0.5
>
> I can then easily manipulate these values to get my ultimate goal of the
> average, which would of course always be less than or equal to 1. Is this
> doable in postgress? Or would it be a better idea to simply pull the raw
> data and post-process in code? Thanks.
>
> ---
> Israel Brewster
> Systems Analyst II
> Ravn Alaska
> 5245 Airport Industrial Rd
> Fairbanks, AK 99709
> (907) 450-7293
> ---
>
>
>
>
>
>
Re: [GENERAL] In need of some JSONB examples ?
create table json_data(row_id int, json_text jsonb);
insert into json_data(1,
'[{"ID":"1","location_name":"Test"},{"ID":"2","location_name":"Examples"}]');
To search for an ID
select row_id, parsed.* from json_data, lateral
jsonb_to_recordset(json_data.json_text) as parsed("ID" text, location_name
text) where parsed."ID" = '1';
To get all records just drop the where clause.
Obviously you could use the result to insert the data into a table as well
if you wished.
As to results to json
select row_to_json(row_data) from (select id, parsed.* from json_data,
lateral jsonb_to_recordset(json_data.json_text) as parsed("ID" text,
location_name text)) row_data;
While the number of examples are weak - the docs are not weak in terms of
getting you in the ballpark.
John
On Fri, Jan 23, 2015 at 12:20 PM, Tim Smith
wrote:
> So basically we're saying JSON in 9.4 is still a little way from where
> it needs to be in terms of real-world functionality ? Or am I being
> too harsh ? ;-)
>
> On 23 January 2015 at 18:49, Adrian Klaver
> wrote:
> > On 01/23/2015 10:15 AM, Tim Smith wrote:
> >>>
> >>> How does it not work?
> >>> In other words what was the query you tried and what was the output?
> >>
> >>
> >> As in, it doesn't work. Full stop
> >>
> >> \d+ json_test
> >>Table "public.json_test"
> >> Column | Type | Modifiers | Storage | Stats target | Description
> >> -+---+---+--+--+-
> >> content | jsonb | not null | extended | |
> >> Indexes:
> >> "idxgin" gin (content)
> >>
> >>
> >> truncate json_test;
> >> TRUNCATE TABLE
> >> insert into json_test(content) values('[{"ID":
> "3119","Desc":"bob"}]');
> >> INSERT 0 1
> >>
> >> select content->'Desc' from json_test where content @> '{"ID":"3119"}';
> >> ?column?
> >> --
> >> (0 rows)
> >>
> >
> > WITH c AS
> > (SELECT
> > jsonb_array_elements(content) AS content
> > FROM
> > json_test)
> > SELECT
> > content->'Desc'
> > FROM
> > c
> > WHERE
> > content @> '{"ID":"3119"}'
> >
> > ?column?
> > --
> > "bob"
> > (1 row)
> >
> >
> > With the caveats that Christophe Pettus mentioned.
> >
> >>> --
> >>> Adrian Klaver
> >>> adrian.kla...@aklaver.com
> >>
> >>
> >>
> >
> >
> > --
> > Adrian Klaver
> > adrian.kla...@aklaver.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] Complex Recursive Query
https://gist.github.com/wishdev/635f7a839877d79a6781
Sorry for the 3rd party site - just easier to get the layout correct.
A CTE and dense_rank is all it takes. I am always amazed at what one can
now pack into such small amounts of code.
On Wed, Jul 23, 2014 at 4:00 PM, Jim Garrison wrote:
> I have a collection of relationship rows of the form
>
> Table: graph
> key1 varchar
> key2 varchar
>
> A row of the form ('a','b') indicates that 'a' and 'b' are related.
> The table contains many relationships between keys, forming several
> disjoint sets. All relationships are bi-directional, and both
> directions are present. I.e. the table contains a set of disjoint
> graphs specified as node pairs.
>
> For example the set of values
>
> key1key2
> - -
> a x
> a y
> b w
> c t
> x a
> y a
> y z
> z y
> t c
> w b
> w d
> d w
>
> defines three disjoint groups of connected keys:
>
> a x y z
> c t
> b w d
>
> What I would like to achieve is a single SQL query that returns
>
> group key
> - ---
> 1a
> 1x
> 1y
> 1z
> 2c
> 2t
> 3b
> 3w
> 3d
>
> I don't care about preserving the node-to-node relationships, only
> the group membership for each node.
>
> I've been playing with "WITH RECURSIVE" CTEs but haven't had any
> success. I'm not really sure how to express what I want in SQL, and
> it's not completely clear to me that recursive CTEs will help here.
> Also I'm not sure how to generate the sequence numbers for the groups
>
>
> --
> 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 create multiple databases running in different dirs
Afternoon Frank, I believe what you might wish to look at is a single database with a set of schemas[1] which would separate your data in a logical way. You could have a single connection url and then each individual connection could create a schema (or reuse if you wish), set the search path (first example here [2]) and populate the schema (if needed) within your single database That would give you your desired isolation but within a simple framework that is PostgreSQL friendly. John [1] - http://www.postgresql.org/docs/9.3/static/ddl-schemas.html [2] - http://www.postgresql.org/docs/9.3/static/sql-set.html On Wed, Jul 2, 2014 at 4:24 PM, frank ernest wrote: > > how would an instance of your program know what to connect to, or which > > previous instance its 'predecessor' was ? > > normally, you have ONE database for a given set of applications, and all > > the applications share the same database tables and such. > > That's the problem, is there some way to tell pgsql "Go to dir X, open > your data base Y and prepare for connections at adress Z and port P"? And > could pgsql accept multiple connections on the same address and port? I was > thinking of using my pID but that would change and if I used a user created > string then if I started only on a single instace of pgsql and pointed it > to it's databases the user might get the strings duplicated and that would > be no good. I also thought of naming each database with a name generated by > using the uuid library but I'm not sure which would be best. > > If I choose to have multiple servers running on the same machine how would > my program start them? > > > Is each runtime instance of your application to have a new, empty > > database? No data from any other run of the application? > > No, the dynamically generated content is to be dropped (drop table > dynamic_content;) but the rest is to be preserved. The idea is to create a > database of a file system but the files contain no data, I only want their > metadata and I will add a few additional metadta values to each file. > > Thanks, David > >
