Re: [GENERAL] Best practices for aggregate table design

Thomas Kellerer writes: > droberts schrieb am 06.10.2015 um 20:53: >> Okay, so is it safe to say I should use loosely use these guidelines when >> deciding whether to model an attribute as a dimension >> (type=[inbound,outbound]) vs. bundling with a measure (total_inbound) ?

Re: [GENERAL] Optimizing a read-only database

François Battail francois.batt...@sipibox.fr writes: My bad, got it. May be interesting but as I have a lot of indexes it will be hard to test and to choose the best candidate. No idea of how it can affect EWKB data indexed by a GiST (PostGIS) index, but it's something to try just to know.

Re: [GENERAL] Help with PostgreSQL 9.4 to expand jsonb int array into table with row numbers

David G Johnston david.g.johns...@gmail.com writes: Neil Tiffin-3 wrote Trying to wrap my head around postgresql 9.4 jsonb and would like some help figuring out how to do the following. Given the following example jsonb: ‘{“name1” : value1, “name2” : value2, “name3” : [int1,

Re: [GENERAL] Finding date intersections

John McKown john.archie.mck...@gmail.com writes: ​I've been think about this for a bit. But I'm not getting a real solution. I have an approach, shown below, that I think might be the bare beginnings of an approach, but I'm just not getting any more inspiration. Perhaps it will spark an idea

Re: [GENERAL] Converting char to varchar automatically

Andrus kobrule...@hot.ee writes: Hi! Thank you. This revised query should give you what you need: SELECT 'ALTER TABLE ' || quote_ident(n.nspname) || '.' || quote_ident(c.relname) || ' ALTER COLUMN ' || quote_ident(a.attname) || ' TYPE varchar(' ||

Re: [GENERAL] Converting char to varchar automatically

Melvin Davidson melvin6...@gmail.com writes: This query might work for you, but double check all result statements first. SELECT 'ALTER TABLE ' || quote_ident(n.nspname) || '.' || quote_ident(c.relname) || ' ALTER COLUMN ' || quote_ident(a.attname) || ' TYPE varchar;' FROM pg_class

Re: [GENERAL] Aggregating over nodes in hierarchical trees

McGehee, Robert robert.mcge...@geodecapital.com writes: SELECT n.node, sum(students) as students FROM tree_tbl t, node_tbl n WHERE t.course ~ '.*' || n.node || '.*' GROUP BY n.node; I'd write this as SELECT n.node, sum(students) AS students FROM tree_tbl t JOIN node_tbl n ON t.course ~

Re: [GENERAL] Help with exclusion constraint

Moshe Jacobson mo...@neadwerx.com writes: Take the following table: CREATE TABLE exclusion_example AS ( pk_col integer primary key, fk_col integer not null references other_table, bool_col boolean not null ); I want to ensure that for any given value of fk_col that there

Re: [GENERAL] Tree structure

Kaare Rasmussen ka...@jasonic.dk writes: Hi I'm trying to determine the best way to represent a simple tree structure (like a file/dir tree or a uri path). I guess that's done a zillion times before; I just don't seem to be able to find the right solution. I have one special request, that

Re: [GENERAL] Which is faster: char(14) or varchar(14)

Edson Richter edsonrich...@hotmail.com writes: In this specific case, the full length (14) is mandatory... so seems there is no loss or gain. Also, I see all varchar(...) created are by default storage = EXTENDED (from Pg Admin), while other datatypes (like numeric, smallint, integer) are

Re: [GENERAL] Finding first free time from reservations table

Andrus kobrule...@hot.ee writes: How to find first free half hour in table which is not reserved ? E.q if table contains startdate starthour duration 14 9 1 -- ends at 9:59 14 10 1.5-- ends at 11:29, e.q there is 30

Re: [GENERAL] Exclusion constraints with time expressions

Albe Laurenz laurenz.a...@wien.gv.at writes: I think the problem is that this + operator is implemented by the function timestamptz_pl_interval, which is STABLE but not IMMUTABLE. I am not sure why this function cannot be IMMUTABLE, it seems to me that it should be. No: the result of e.g.

Re: [GENERAL] how to group by similarity ?

Andreas maps...@gmx.net writes: How would I group the table so that it shows groups that have similarity () x ? Lets say the table looks like this: id, txt 1, aa1 2, bb1 3, cc1 4, bb2 5, bb3 6, aa2 ... How would a select look like that shows: id, txt, group_id

Re: [GENERAL] how to group by similarity ?

Andreas maps...@gmx.net writes: How would I group the table so that it shows groups that have similarity () x ? Lets say the table looks like this: id, txt 1, aa1 2, bb1 3, cc1 4, bb2 5, bb3 6, aa2 ... How would a select look like that shows: id, txt, group_id

Re: [GENERAL] Anonymized database dumps

Janning Vygen vy...@kicktipp.de writes: pgcrypto does not work for this scenario as far as i know. pgcrypto enables me to encrypt my data and let only a user with the right password (or key or whatever) decrypt it, right? So if i run it in a test environment without this password the

Re: [GENERAL] How to get a signal from the database when a INSERT INTO is done?

Daniele Varrazzo daniele.varra...@gmail.com writes: As mentioned above and as demonstrated in the example, select() also does the job. Using such a fancy framework is usually an overkill. Yeah, the problem is usually if you have to do something else apart from listening from the