Re: [GENERAL] Best practices for aggregate table design

[hari . fuchs]

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) ?
>> 
>> If you know the number of values for a dimension are fixed (e.g. boolean),
>> then creating a measure will have benefits of:
>>   - reduced number of rows/storage
>>   - better performance since less indexing/vacuuming  
>> 
>> the drawbacks are:
>>   -rigid structure, not very extensible over time (e.g. later realize I need
>> to also track 'internal' calls). 
>> 
>> In my case, I'm now needing to add another measure 'encrypted=true/false',
>> so my table is starting to look like 
>
> Have you considered using a hstore column to store the attributes you
> don't know yet?
>
> Which makes this extensible, flexible and fast. 

Is there an advantage of hstore vs. json/jsonb?



-- 
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] Optimizing a read-only database

[hari . fuchs]

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.

You could also raise the statistics target and re-analyze.  This will
take some time, but then your query plans might be better.



-- 
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] Help with PostgreSQL 9.4 to expand jsonb int array into table with row numbers

[hari . fuchs]

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, int2, int3]
 }’::jsonb AS table1.column1
  
 Wanted: Return the “name3” array only, as a table with a return signature
 of 
 
 TABLE( var_name varchar, var_value int, var_row_num int)
 
 So the resulting data would look like this:
  
 (‘name3’, int1, 1)
 (‘name3’, int2, 2)
 (‘name3’, int3, 3)
 
 Assume the array could be any length except zero and ‘name3’ is guaranteed
 to exist.
 
 Also posted on stackoverflow:
 
 http://stackoverflow.com/questions/26691725/postgresql-9-4-expand-jsonb-int-array-into-table-with-row-numbers

 Not syntax checked but...

 SELECT 'name3', int_text::integer AS int, int_ord
 FROM ( VALUES (...) ) src (column1)
 LATERAL ROWS FROM(
 json_array_elements(column1-'name3')
 ) WITH ORDINALITY jae (int_text, int_ord)

 Both WITH ORDINALITY and jsonb are introduced in 9.4; it is possible to
 make this work in all supported versions of PostgreSQL through the liberal
 use of CTE (WITH) as possibly the generate_series() function.

I think this can just be written as

SELECT 'name3' AS var_name,
   json_array_elements(column1-'name3') AS var_value,
   row_number() OVER () AS var_row_num
FROM table1



-- 
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] Finding date intersections

[hari . fuchs]

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 for you or someone else.

 with recursive explode(times) as (
 select * from sales
 union
 select tstzrange(lower(a.times)::timestamptz,upper(b.times)::timestamptz)
 as times
 from explode as a
 join sales as b
 on upper(a.times) = lower(b.times)
 where lower(a.times) is not null and upper(b.times) is not null
 )
 select * from explode
 order by times
 ;

 If you run it with your example, you will see that it does get rows which
 contain the answer. But it gets all the intermediate rows as well. It is
 removing those intermediate result rows that I just can't get a handle
 onl​

For that, you could use a LEFT JOIN with itself:

WITH RECURSIVE explode(times) AS (
  SELECT times
  FROM sales
UNION
  SELECT a.times + b.times
  FROM explode a
  JOIN sales b ON b.times  a.times OR b.times -|- a.times
)
SELECT a.times
FROM explode a
LEFT JOIN explode b ON b.times @ a.times AND b.times != a.times
WHERE b.times IS NULL
ORDER BY a.times



-- 
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] Converting char to varchar automatically

[hari . fuchs]

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(' 
 || i.character_maximum_length || ');'
  FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
JOIN pg_attribute a ON a.attrelid = c.oid
JOIN pg_type t ON t.oid = a.atttypid
JOIN information_schema.columns i ON (i.table_name = c.relname AND 
 i.column_name = a.attname)
WHERE t.typname = 'bpchar'
   AND c.relkind = 'r'
   AND n.nspname  'pg_catalog' and not attisdropped;

 How to create single alter table command for every table ?
 Can we use string concat aggregate function or window functions or plpgsql or 
 something other ?

string_agg should do it:

SELECT 'ALTER TABLE ' || quote_ident(n.nspname) ||
   '.' || quote_ident(c.relname) || ' ' ||
   string_agg('ALTER COLUMN ' || quote_ident(a.attname) ||
   ' TYPE varchar(' || i.character_maximum_length || ')', ', ') || ';'
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
JOIN pg_attribute a ON a.attrelid = c.oid
JOIN pg_type t ON t.oid = a.atttypid
JOIN information_schema.columns i ON
  i.table_name = c.relname AND i.column_name = a.attname
WHERE t.typname = 'bpchar'
  AND c.relkind = 'r'
  AND n.nspname  'pg_catalog' and not attisdropped
GROUP BY n.nspname, c.relname;



-- 
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] Converting char to varchar automatically

[hari . fuchs]

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 c
   JOIN pg_namespace n ON n.oid = c.relnamespace
   JOIN pg_attribute a ON a.attrelid = c.oid
   JOIN pg_type t ON t.oid = a.atttypid
  WHERE t.typname = 'char'
AND n.nspname  'pg_catalog';

Make that t.typname = 'bpchar'.



-- 
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] Aggregating over nodes in hierarchical trees

[hari . fuchs]

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 ~ ('*.' || n.node || '.*')::lquery
GROUP BY n.node;



-- 
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] Help with exclusion constraint

[hari . fuchs]

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 is a maximum
 of one row with bool_col = true.

This should be what you want:

ALTER TABLE exclusion_example
ADD CONSTRAINT ex
EXCLUDE (fk_col WITH =) WHERE (bool_col);



-- 
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] Tree structure

[hari . fuchs]

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 I'd like to find all
 shorter' paths, i.e. given 'a/b/c/d' it'll find

 a
 a/b
 a/b/c
 - but not
 b
 a/c
 b/a

If I understand you correctly, you want a prefix match, and sure there's
a PostgreSQL extension for that:

CREATE EXTENSION prefix;

CREATE TABLE t1 (
  id serial NOT NULL,
  p prefix_range NOT NULL,
  PRIMARY KEY (id)
);

CREATE INDEX pp ON t1 USING gist(p);

INSERT INTO t1 (p) VALUES
('a'),
('b'),
('a/c'),
('a/b'),
('b/a'),
('a/b/c');

EXPLAIN ANALYZE
SELECT id, p
FROM t1
WHERE p @ 'a/b/c/d'
;



-- 
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] Which is faster: char(14) or varchar(14)

[hari . fuchs]

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 storage = MAIN.
 Can I have a gain using fixed length datatype in place of current
 varchar (like numeric (14,0))?
 Or changing to char(14) check length(doc)=14 and storage=MAIN?

Sounds like premature optimization to me.  I'd first express what I want
as clear as possible, e.g. CREATE DOMAIN BrazilianCompanyId AS char(14),
and try to spot and fix performance problems when I'm done with all that.



-- 
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] Finding first free time from reservations table

[hari . fuchs]

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 minute gap before next
  14  12  2
  14  16  2
  
 result should be:
  
  starthour  duration
  11.5   0.5
  
   
 Probably PostgreSql 9.2 window function should used to find
 first row whose starthour is greater than previous row starthour +
 duration 

Yes, you could use something like this:

SELECT min(c1)
FROM (
SELECT starthour + duration AS c1,
   lead(starthour) OVER (ORDER BY starthour) AS c2
FROM tst
  ) dummy
WHERE c2 = c1 + 0.5



-- 
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] Exclusion constraints with time expressions

[hari . fuchs]

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.

  SELECT TIMESTAMPTZ '2012-10-28 01:30:00' + INTERVAL '24 hours';

depends on the client's timezone and its DST rules.



-- 
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 group by similarity ?

[hari . fuchs]

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
 1,   aa1,   1,
 6,   aa2,   1,
 2,   bb1,   2,
 4,   bb2,   2,
 5,   bb3,   2,
 3,   cc1,   3

I could only come up with this convoluted query:

WITH grp (t1, id, t2) AS (
  SELECT t1.txt, t1.id, t2.txt
  FROM tbl t1
  LEFT JOIN tbl t2 ON t2.txt  t1.txt
  WHERE t2.txt IS NULL OR similarity(t1.txt, t2.txt)  0
)
SELECT t1, min(id)
FROM (
SELECT t1, id
FROM grp
  UNION ALL
SELECT t2, id
FROM grp
WHERE t2 IS NOT NULL
  ) dummy
GROUP BY t1
ORDER BY t1


-- 
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 group by similarity ?

[hari . fuchs]

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
 1,   aa1,   1,
 6,   aa2,   1,
 2,   bb1,   2,
 4,   bb2,   2,
 5,   bb3,   2,
 3,   cc1,   3

The following query will do that, but it's convoluted:

WITH grp (t1, id, t2) AS (
  SELECT t1.txt, t1.id, t2.txt
  FROM tbt t1
  LEFT JOIN tbt t2 ON t2.txt  t1.txt
  WHERE t2.txt IS NULL OR similarity(t1.txt, t2.txt)  0
)
SELECT t1, min(id)
FROM (
SELECT t1, id
FROM grp
  UNION ALL
SELECT t2, id
FROM grp
WHERE t2 IS NOT NULL
  ) dummy
GROUP BY t1
ORDER BY t1


-- 
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] Anonymized database dumps

[hari . fuchs]

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 application is broken.

 I still want to use these table columns in my test environment but
 instead of real email addresses i want addresses like
 random_num...@example.org.

 You might be right that it is a good idea to additional encrypt this data.

Maybe you could change your application so that it doesn't access the
critical tables directly and instead define views for them which, based
on current_user, either do decryption or return randim strings.


-- 
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 get a signal from the database when a INSERT INTO is done?

[hari . fuchs]

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 notification. select() will block the entire
 application, so you would put it into a separate thread to have the
 app running on.

I don't know Python, but the C library function of select() has a
timeout parameter that can be set to 0.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general