Re: [GENERAL] retrieve grouping sets/rollup rows

[Tom Smith]

exactly what I am looking for. very nice.  Thx

On Sun, Mar 13, 2016 at 10:44 PM, Tom Lane  wrote:

> >> On 03/13/2016 10:07 PM, Tom Smith wrote:
> >>> It would help if the resultset has some param to mark which is which
> >>> with the grouping sets index.
>
> I think you're looking for the GROUPING() function.  See
> http://www.postgresql.org/docs/9.5/static/functions-aggregate.html
>
> regards, tom lane
>

Re: [GENERAL] retrieve grouping sets/rollup rows

[James Keener]

/me has learned something new!

Thanks!


On 03/13/2016 10:44 PM, Tom Lane wrote:
>>> On 03/13/2016 10:07 PM, Tom Smith wrote:
 It would help if the resultset has some param to mark which is which
 with the grouping sets index.
> 
> I think you're looking for the GROUPING() function.  See
> http://www.postgresql.org/docs/9.5/static/functions-aggregate.html
> 
>   regards, tom lane
> 


-- 
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] retrieve grouping sets/rollup rows

[Tom Lane]

>> On 03/13/2016 10:07 PM, Tom Smith wrote:
>>> It would help if the resultset has some param to mark which is which
>>> with the grouping sets index.

I think you're looking for the GROUPING() function.  See
http://www.postgresql.org/docs/9.5/static/functions-aggregate.html

regards, tom lane


-- 
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] retrieve grouping sets/rollup rows

[James Keener]

It just dawned on me that you may note have meant having them in a
specific sequence in the result set.  Even still, I think it's much more
clear being explicit with what rows are included and which aren't.

Jim

On 03/13/2016 10:12 PM, James Keener wrote:
> Why? You're already provided with this information: NULL fields are not
> being used in the grouping set for the row. Moreover, it would seem to
> be an implementation- and run-time- dependent value, as there is no
> reason group by grouping set (a,b), (c,d) couldn't be executed in
> written order, or (c,d) first depending on a lot of things.
> 
> Forcing the implementation to conform to a certain way of doing things
> is asking for someone to ask why a certain optimization isn't being
> performed later on.
> 
> My $0.02.
> 
> Jim
> 
> On 03/13/2016 10:07 PM, Tom Smith wrote:
>> It would help if the resultset has some param to mark which is which
>> with the grouping sets index.
>> for example, the results for (a,b,c,d) would be marked as for index =0, 
>> (b,c,d) would be index=1
>>
>> On Sun, Mar 13, 2016 at 9:52 PM, James Keener > > wrote:
>>
>> Do you want to know if a row is from the (a,b) or (c,d) group?  All rows
>> will contain (a,b,c,d) but (a,b) will be NULL for the (c,d) grouping
>> sets, and vice-versa.
>>
>> Jim
>>
>> On 03/13/2016 09:45 PM, Tom Smith wrote:
>> > Hello:
>> >
>> > With JDBC, how can I tell which row is for which grouping sets or
>> rollup
>> > using result sets
>> >
>> > Thanks
>> >
>> >
>>
>>


-- 
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] enum bug

[Alvaro Herrera]

Elein wrote:

Hi Elein,

>* When an insert into an enum column fails give the person a hint as to 
> valid values

> -- Lousy message.  Show enum list.
> insert into badinfo values ('green');
> ERROR:  invalid input value for enum rainbow: "green"
> LINE 1: insert into badinfo values ('green');

True, we could improve that, though it could easily get messy with large
enums.


> >* Make enum_range to not be dependent on values in the target table.
> >  Remove/Obsolete enum_range( enum_column ) and replace with enum_range( 
> > typein regtype )
> > 
> >Workaround: define the enum_range( typein regtyp ) yourself.

Hmm, this is pretty clunky.  I don't think passing the OID of the enum
itself is a lot better, but if you use a regtype cast then perhaps it's
not that bad.  Perhaps we could have both functions.

-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
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] enum bug

[Melvin Davidson]

The post refers to the generic unmanageability of enums in genaral, it just
uses MySQL as a reference basis.
Google evil enum and you will find several articles that all say the same
thing.
To be specific,even in PostgreSQL, there is no easy way to delete enum
values once they exist, other than playing with system catalogs.
The point is, they are an archaic data type and it's a hell of a lot easier
to use Foreign Keys to insure integrity.
I've seen systems designed with an enum that has over 20 values associated
with it. It's insane to do that.

On Sun, Mar 13, 2016 at 10:08 PM, Alvaro Herrera 
wrote:

> Melvin Davidson wrote:
>
> > Enums are evil!
> > http://www.lornajane.net/posts/2010/is-enum-evil
>
> ???
>
> This post is about MySQL's enums, which aren't really related to
> Postgres enums:
>
> "In order to change the allowed values of an enum column, we
> need to issue an alter table statement [...]   Alter table
> actually creates a new table matching the new structure, copies
> all the data across, and then renames the new table to the right
> name."
>
> This is not at all how things happen in Postgres' enums.
>
>
> > enums are from before there were foreign keys
>
> In Postgres, ENUMs are pretty recent actually -- a lot newer than FKs:
>
> commit 57690c6803525f879fe96920a05e979ece073e71
> Author: Tom Lane 
> AuthorDate: Mon Apr 2 03:49:42 2007 +
> CommitDate: Mon Apr 2 03:49:42 2007 +
>
> Support enum data types.  Along the way, use macros for the values of
> pg_type.typtype whereever practical.  Tom Dunstan, with some kibitzing
> from Tom Lane.
>
> --
> Álvaro Herrerahttp://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>



-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: [GENERAL] retrieve grouping sets/rollup rows

[James Keener]

Why? You're already provided with this information: NULL fields are not
being used in the grouping set for the row. Moreover, it would seem to
be an implementation- and run-time- dependent value, as there is no
reason group by grouping set (a,b), (c,d) couldn't be executed in
written order, or (c,d) first depending on a lot of things.

Forcing the implementation to conform to a certain way of doing things
is asking for someone to ask why a certain optimization isn't being
performed later on.

My $0.02.

Jim

On 03/13/2016 10:07 PM, Tom Smith wrote:
> It would help if the resultset has some param to mark which is which
> with the grouping sets index.
> for example, the results for (a,b,c,d) would be marked as for index =0, 
> (b,c,d) would be index=1
> 
> On Sun, Mar 13, 2016 at 9:52 PM, James Keener  > wrote:
> 
> Do you want to know if a row is from the (a,b) or (c,d) group?  All rows
> will contain (a,b,c,d) but (a,b) will be NULL for the (c,d) grouping
> sets, and vice-versa.
> 
> Jim
> 
> On 03/13/2016 09:45 PM, Tom Smith wrote:
> > Hello:
> >
> > With JDBC, how can I tell which row is for which grouping sets or
> rollup
> > using result sets
> >
> > Thanks
> >
> >
> 
> 


-- 
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] enum bug

[Alvaro Herrera]

Melvin Davidson wrote:

> Enums are evil!
> http://www.lornajane.net/posts/2010/is-enum-evil

???

This post is about MySQL's enums, which aren't really related to
Postgres enums:

"In order to change the allowed values of an enum column, we
need to issue an alter table statement [...]   Alter table
actually creates a new table matching the new structure, copies
all the data across, and then renames the new table to the right
name."

This is not at all how things happen in Postgres' enums.


> enums are from before there were foreign keys

In Postgres, ENUMs are pretty recent actually -- a lot newer than FKs:

commit 57690c6803525f879fe96920a05e979ece073e71
Author: Tom Lane 
AuthorDate: Mon Apr 2 03:49:42 2007 +
CommitDate: Mon Apr 2 03:49:42 2007 +

Support enum data types.  Along the way, use macros for the values of
pg_type.typtype whereever practical.  Tom Dunstan, with some kibitzing
from Tom Lane.

-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
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] retrieve grouping sets/rollup rows

[Tom Smith]

It would help if the resultset has some param to mark which is which with
the grouping sets index.
for example, the results for (a,b,c,d) would be marked as for index =0,
(b,c,d) would be index=1

On Sun, Mar 13, 2016 at 9:52 PM, James Keener  wrote:

> Do you want to know if a row is from the (a,b) or (c,d) group?  All rows
> will contain (a,b,c,d) but (a,b) will be NULL for the (c,d) grouping
> sets, and vice-versa.
>
> Jim
>
> On 03/13/2016 09:45 PM, Tom Smith wrote:
> > Hello:
> >
> > With JDBC, how can I tell which row is for which grouping sets or rollup
> > using result sets
> >
> > Thanks
> >
> >
>

Re: [GENERAL] Suppress decimal point like digits in to_char?

[David G. Johnston]

On Sunday, March 13, 2016, Ken Tanzer  wrote:

>
> On Mar 13, 2016 6:29 PM, "David G. Johnston"  > wrote:
> >
> > On Sunday, March 13, 2016, Ken Tanzer  > wrote:
> >>
> >> Hi.  Is there a way with to_char to suppress a decimal point, like a
> leading or trailing 0, so that integers will not have them, but non-ints
> will?  I'm hoping I'm missing something easy.  Thanks.
> >>
> >> Ken
> >>
> >> SELECT val,to_char(val::decimal(6,2),'FM999,999D99') FROM
> >> ( SELECT 1 AS val UNION SELECT 1.05 AS val) foo;
> >>
> >>  val  | to_char
> >> --+-
> >> 1 | 1.
> >>  1.05 | 1.05
> >>
> >>
> >
> > Not seeing a native way to do so - and I'd question doing so as a
> general rule - though you know your domain.  If you must have this you will
> want to utilize regexp_replace to identify the situation and replace it.  A
> simple "\.$" check and a substring would work also.
> >
> > David J.
>
> Thanks David. Just curious what part of this you would question.  The case
> for numbers, currency in particular, coming out with a decimal and pennies
> when present, and as whole dollars when not (and without a decimal place at
> the end) seems pretty common and clear cut.  What am I missing in your
> question?
>

Typically if I'm going to format any currency amount with pennies I would
format all values, even those with zero pennies, to the same precision.
Typically when displaying such amounts I'd right-justify the values and
thus cause the decimals to line up.

David J.

Re: [GENERAL] retrieve grouping sets/rollup rows

[James Keener]

Do you want to know if a row is from the (a,b) or (c,d) group?  All rows
will contain (a,b,c,d) but (a,b) will be NULL for the (c,d) grouping
sets, and vice-versa.

Jim

On 03/13/2016 09:45 PM, Tom Smith wrote:
> Hello:
> 
> With JDBC, how can I tell which row is for which grouping sets or rollup
> using result sets
> 
> Thanks
> 
> 


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

[GENERAL] retrieve grouping sets/rollup rows

[Tom Smith]

Hello:

With JDBC, how can I tell which row is for which grouping sets or rollup
using result sets

Thanks

Re: [GENERAL] Suppress decimal point like digits in to_char?

[Ken Tanzer]

On Mar 13, 2016 6:29 PM, "David G. Johnston" 
wrote:
>
> On Sunday, March 13, 2016, Ken Tanzer  wrote:
>>
>> Hi.  Is there a way with to_char to suppress a decimal point, like a
leading or trailing 0, so that integers will not have them, but non-ints
will?  I'm hoping I'm missing something easy.  Thanks.
>>
>> Ken
>>
>> SELECT val,to_char(val::decimal(6,2),'FM999,999D99') FROM
>> ( SELECT 1 AS val UNION SELECT 1.05 AS val) foo;
>>
>>  val  | to_char
>> --+-
>> 1 | 1.
>>  1.05 | 1.05
>>
>>
>
> Not seeing a native way to do so - and I'd question doing so as a general
rule - though you know your domain.  If you must have this you will want to
utilize regexp_replace to identify the situation and replace it.  A simple
"\.$" check and a substring would work also.
>
> David J.

Thanks David. Just curious what part of this you would question.  The case
for numbers, currency in particular, coming out with a decimal and pennies
when present, and as whole dollars when not (and without a decimal place at
the end) seems pretty common and clear cut.  What am I missing in your
question?

Cheers,
Ken

Re: [GENERAL] Suppress decimal point like digits in to_char?

[David G. Johnston]

On Sunday, March 13, 2016, Ken Tanzer  wrote:

> Hi.  Is there a way with to_char to suppress a decimal point, like a
> leading or trailing 0, so that integers will not have them, but non-ints
> will?  I'm hoping I'm missing something easy.  Thanks.
>
> Ken
>
> SELECT val,to_char(val::decimal(6,2),'FM999,999D99') FROM
> ( SELECT 1 AS val UNION SELECT 1.05 AS val) foo;
>
>  val  | to_char
> --+-
> 1 | 1.
>  1.05 | 1.05
>
>
>
Not seeing a native way to do so - and I'd question doing so as a general
rule - though you know your domain.  If you must have this you will want to
utilize regexp_replace to identify the situation and replace it.  A simple
"\.$" check and a substring would work also.

David J.

[GENERAL] Suppress decimal point like digits in to_char?

[Ken Tanzer]

Hi.  Is there a way with to_char to suppress a decimal point, like a
leading or trailing 0, so that integers will not have them, but non-ints
will?  I'm hoping I'm missing something easy.  Thanks.

Ken

SELECT val,to_char(val::decimal(6,2),'FM999,999D99') FROM
( SELECT 1 AS val UNION SELECT 1.05 AS val) foo;

 val  | to_char
--+-
1 | 1.
 1.05 | 1.05


-- 
AGENCY Software
A Free Software data system
By and for non-profits
*http://agency-software.org/ *
*https://agency-software.org/demo/client
*
ken.tan...@agency-software.org
(253) 245-3801

Subscribe to the mailing list
 to
learn more about AGENCY or
follow the discussion.

Re: [GENERAL] Distributed Table Partitioning

[Melvin Davidson]

I guess that would work if you used a view to combine both tables, but you
would still need a BEFORE trigger to make sure DML goes to the appropriate
table.
Another solution might be to use dblink if you need to access the SATA
table on the 2nd VPS,

On Sun, Mar 13, 2016 at 11:37 AM, Leonardo M. Ramé 
wrote:

> El 13/03/16 a las 10:04, Peter J. Holzer escribió:
>
>> On 2016-03-12 21:19:11 -0500, Melvin Davidson wrote:
>>
>>>  - Original Message -
>>>  From: "Leonardo M. Ramé" 
>>>  To: "PostgreSql-general" 
>>>  Sent: Saturday, 12 March, 2016 8:25:01 PM
>>>  Subject: [GENERAL] Distributed Table Partitioning
>>>
>>>  I have this problem: a Master table containing records with a
>>> timestamp
>>>  column registering creation date-time, and one Detail table
>>> containing
>>>  info related to the Master table.
>>>
>>>  As time went by, those tables grew enormously, and I can't afford
>>>  expanding my SSD VPS. So I'm thinking about storing only NEW data
>>> into
>>>  it, and move OLD data to a cheaper SATA VPS.
>>>
>> [...]
>>
>>> Why don't you just make use of tablespaces and partition the child
>>> tablespaces
>>> so that the newer parttion is on the SSD and the older one is on SATA?
>>>
>>
>> Since he mentioned virtual private servers (VPS) the reason might be
>> that his hoster offers VPS with SSDs (of various sizes) and VPS with
>> rotating hard disks (of various sizes), but not VPS with both. So he
>> can't rent a VPS with a relatively small SSD and a larger hard disk.
>>
>> That might be a reason to look for an alternate hoster, but if he's
>> otherwise happy, switching to an unknown provider might be considered
>> too large a risk.
>>
>>  hp
>>
>>
> Yes, Peter is right, I must store one table in one VPS and the other in a
> 2nd VPS.
>
> I'm thinking of partitioning the table, on local and the remote using a
> Foreign Data Wrapper, what do you think about this approach?.
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>



-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: [GENERAL] index problems (again)

[Geoff Winkless]

On 12 March 2016 at 22:00, Peter J. Holzer  wrote:
> I don't think most people's data is perfectly distributed. But as you
> say most data is probably within some deviation of being perfectly
> distributed and as long as that deviation isn't too big it doesn't
> matter.

Is that how what I wrote came across? I was trying to say exactly the
opposite: that if, instead of assuming perfect distribution, you
assume a certain deviation from that distribution, you will end up
with plans that would win with perfect distribution losing out to
plans that are almost as good on that perfect distribution but behave
better on data that is not perfectly distributed.

Geoff


-- 
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] MongoDB 3.2 beating Postgres 9.5.1?

[Oleg Bartunov]

On Mar 11, 2016 4:40 PM, "Paul Jones"  wrote:
>
> I have been running the EDB benchmark that compares Postgres and MongoDB.
> I believe EDB ran it against PG 9.4 and Mongo 2.6.  I am running it
> against PG 9.5.1 and Mongo 3.2 with WiredTiger storage using 10,000,000
> JSON records generated by the benchmark.  It looks like Mongo is winning,
> and apparently because of its cache management.

Dmitry was working on the same benchmarks. I think edb benchmark is broken
by design. Better,  use ycsb benchmarks. I hope, Dmitry will share his
results.

>
> The first queries on both run in ~30 min.  And, once PG fills its cache,
> it whips Mongo on repeats of the *same* query (vmstat shows no disk
> reads for PG).
>
> However, when different query on the same table is issued to both,
> vmstat shows that PG has to read the *entire* table again, and it takes
> ~30 min.  Mongo does a lot of reads initially but after about 5 minutes,
> it stops reading and completes the query, most likely because it is
> using its cache very effectively.
>
> Host:   Virtual Machine
> 4 CPUs
> 16 Gb RAM
> 200 Gb Disk
> RHEL 6.6
>
> PG: 9.5.1 compiled from source
> shared_buffers = 7GB
> effectve_cache_size = 12GB
>
> Mongo:  3.2 installed with RPM from Mongo
>
> In PG, I created the table by:
>
> CREATE TABLE json_tables
> (
> dataJSONB
> );
>
> After loading, it creates the index:
>
> CREATE INDEX json_tables_idx ON json_tables USING GIN (data
jsonb_path_ops);
>
> After a lot of experimentation, I discovered that the benchmark was not
> using PG's index, so I modified the four queries to be:
>
> SELECT data FROM json_tables WHERE data @> '{"brand": "ACME"}';
> SELECT data FROM json_tables WHERE data @> '{"name": "Phone Service Basic
Plan"}';
> SELECT data FROM json_tables WHERE data @> '{"name": "AC3 Case Red"}';
> SELECT data FROM json_tables WHERE data @> '{"type": "service"}';
>
> Here are two consecutive explain analyze for PG, for the same query.
> No functional difference in the plans that I can tell, but the effect
> of PG's cache on the second is dramatic.
>
> If anyone has ideas on how I can get PG to more effectively use the cache
> for subsequent queries, I would love to hear them.
>
> ---
>
> benchmark=# explain analyze select data from json_tables where data @>
'{"name": "AC3 Case Red"}';
>
>QUERY PLAN
>
>
>
-
>
> Bitmap Heap Scan on json_tables  (cost=113.50..37914.64 rows=1
width=1261)
> (actual time=2157.118..1259550.327 rows=909091 loops=1)
>Recheck Cond: (data @> '{"name": "AC3 Case Red"}'::jsonb)
>Rows Removed by Index Recheck: 4360296
>Heap Blocks: exact=37031 lossy=872059
>->  Bitmap Index Scan on json_tables_idx  (cost=0.00..111.00
rows=1 width =0) (actual time=2141.250..2141.250 rows=909091 loops=1)
>  Index Cond: (data @> '{"name": "AC3 Case Red"}'::jsonb)
> Planning time: 291.932 ms
> Execution time: 1259886.920 ms
> (8 rows)
>
> Time: 1261191.844 ms
>
> benchmark=# explain analyze select data from json_tables where data @>
'{"name": "AC3 Case Red"}';
>   QUERY PLAN
>
>
---
>
> Bitmap Heap Scan on json_tables  (cost=113.50..37914.64 rows=1
width=1261) (actual time=779.261..29815.262 rows=909091 loops=1)
>Recheck Cond: (data @> '{"name": "AC3 Case Red"}'::jsonb)
>Rows Removed by Index Recheck: 4360296
>Heap Blocks: exact=37031 lossy=872059
>->  Bitmap Index Scan on json_tables_idx  (cost=0.00..111.00
rows=1 width =0) (actual time=769.081..769.081 rows=909091 loops=1)
>  Index Cond: (data @> '{"name": "AC3 Case Red"}'::jsonb)
> Planning time: 33.967 ms
> Execution time: 29869.381 ms
>
> (8 rows)
>
> Time: 29987.122 ms
>
>
> --
> 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] pg_restore fails

[Francisco Olarte]

Hi Karsten..

On Sun, Mar 13, 2016 at 12:09 AM, Karsten Hilbert
 wrote:
> I am trying to pg_restore from a directory dump.
> However, despite using
>
> --clean
> --create
> --if-exists
>
> I am getting an error because schema PUBLIC already exists.
snip, snip

Have you tried the classic combo pg_restore -l > toc.dat,
your_favorite_editor toc.dat pg_restore -L toc.dat?

I've had great success with that in the past, even splitting the TOC
in several chunks to be able to make adjustements between them, but
I've never used the directory format for ( serious, I've tried all
when learning ) backups.

Francisco Olarte.


-- 
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] Full text search question: "01.Bez." --> "Erster Bezirk"

[Dane Foster]

Hello,

​
On Sat, Mar 12, 2016 at 11:40 AM, Johann Höchtl 
wrote:

> I fear I have an involved challenge concerning FTS.
>
> Assume I have the following text in a column:
>
> Graz,06.Bez.:Blah
>
> This parses as:
> SELECT alias, description, token FROM ts_debug('german',
> 'Graz,06.Bez.:Blah');
>alias   |   description   | token
> ---+-+
>  asciiword | Word, all ASCII | Graz
>  blank | Space symbols   | ,
>  host  | Host| 06.Bez
>  blank | Space symbols   | .:
>  asciiword | Word, all ASCII | Blah
>
>
> Bez. ist the abbreviation for "Bezirk" (german for ~district). 06.Bez
> means "6th district"
>
> My first problem might be that the parser identifies "06.Bez." as a host
> lexeme, but ...
>
> I already defined a synonym dictionary to enable searching for "Bezirk",
> when there is only "Bez." in the database:
>
> file: bevaddress_host.syn:
> 01.bez bezirk
> 06.bez bezirk
> 
>
>
> CREATE TEXT SEARCH DICTIONARY bevaddress_host_syn (
> TEMPLATE = synonym,
> SYNONYMS = bevaddress_host
> );
> ALTER TEXT SEARCH CONFIGURATION german ALTER MAPPING FOR host WITH
> bevaddress_host_syn, simple;
>
>
> I wonder how I can achieve to be able to search for "Erster Bezirk"
> ("First district") to match eg. "01.Bez."
>
> Thank you for your help, Johann
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

​
As of the time of writing this I haven't seen any replies to your post so
you may not be aware that an answer was provided to your specific question
in a blog. http://obartunov.livejournal.com/185579.html

Regards,​

​
​
Dane​

Re: [GENERAL] "brew services list" shows postgresql as "started", but can not connect to it

[Tom Lane]

Alexander Farber  writes:
> And "brew services list" reports it as "started":
> postgresql started afarber
> /Users/afarber/Library/LaunchAgents/homebrew.mxcl.postgresql.plist
> And I can restart it:
> # brew services restart postgresql
> Stopping `postgresql`... (might take a while)
> ==> Successfully stopped `postgresql` (label: homebrew.mxcl.postgresql)
> ==> Successfully started `postgresql` (label: homebrew.mxcl.postgresql)
> But unfortunately I can not connect with "psql":
> psql: could not connect to server: No such file or directory
> Is the server running locally and accepting
> connections on Unix domain socket "/tmp/.s.PGSQL.5432"?

I'd start by figuring out where that packaging causes the postmaster
to write its log file, and having a look in the log file.

Hopefully there is some documentation included with the homebrew PG
package that explains that.  But if not, perhaps a look into the
launch-agent script mentioned above would tell you.  Look for something
like a -l argument to pg_ctl, or a redirection of stderr into a file.

regards, tom lane


-- 
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] Distributed Table Partitioning

[Leonardo M . Ramé]

El 13/03/16 a las 10:04, Peter J. Holzer escribió:

On 2016-03-12 21:19:11 -0500, Melvin Davidson wrote:

 - Original Message -
 From: "Leonardo M. Ramé" 
 To: "PostgreSql-general" 
 Sent: Saturday, 12 March, 2016 8:25:01 PM
 Subject: [GENERAL] Distributed Table Partitioning

 I have this problem: a Master table containing records with a timestamp
 column registering creation date-time, and one Detail table containing
 info related to the Master table.

 As time went by, those tables grew enormously, and I can't afford
 expanding my SSD VPS. So I'm thinking about storing only NEW data into
 it, and move OLD data to a cheaper SATA VPS.

[...]

Why don't you just make use of tablespaces and partition the child tablespaces
so that the newer parttion is on the SSD and the older one is on SATA?


Since he mentioned virtual private servers (VPS) the reason might be
that his hoster offers VPS with SSDs (of various sizes) and VPS with
rotating hard disks (of various sizes), but not VPS with both. So he
can't rent a VPS with a relatively small SSD and a larger hard disk.

That might be a reason to look for an alternate hoster, but if he's
otherwise happy, switching to an unknown provider might be considered
too large a risk.

 hp



Yes, Peter is right, I must store one table in one VPS and the other in 
a 2nd VPS.


I'm thinking of partitioning the table, on local and the remote using a 
Foreign Data Wrapper, what do you think about this approach?.



--
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] "brew services list" shows postgresql as "started", but can not connect to it

[Chris Ruprecht]

Did you initialize the DB cluster (initdb -D /path/to/data)?
Did you configure the installation (postgres.conf file)?
Does homebrew.mxcl.postgresql where the data lives?

when you run "ps -ef|grep -i postg", do you see the postmaster and other 
postgres executables running?

> On Mar 13, 2016, at 06:07, Alexander Farber  
> wrote:
> 
> Hello!
> 
> I am trying to move from (successfully) using VM Fusion with CentOS Linux 
> image on Mac OSX "El Capitan" for web development to Homebrew:
> 
> brew update
> brew install postgres
> brew tap homebrew/services
> 
> This resulted in some files installed to
> 
>/usr/local/Cellar/postgresql/9.5.1/
> 
> And "brew services list" reports it as "started":
> 
> postgresql started afarber 
> /Users/afarber/Library/LaunchAgents/homebrew.mxcl.postgresql.plist
> 
> And I can restart it:
> 
> # brew services restart postgresql
> Stopping `postgresql`... (might take a while)
> ==> Successfully stopped `postgresql` (label: homebrew.mxcl.postgresql)
> ==> Successfully started `postgresql` (label: homebrew.mxcl.postgresql)
> 
> But unfortunately I can not connect with "psql":
> 
> psql: could not connect to server: No such file or directory
>   Is the server running locally and accepting
>   connections on Unix domain socket "/tmp/.s.PGSQL.5432"?
> 
> And there is no "pg", "post" or "master" in "ps uawwwx" list.
> 
> And I can find neither postgresql.conf nor pg_hba.conf nor any startup logs 
> at my Macbook Air.
> 
> In the troubleshooting process I have tried:
> 
> xcode-select --install
> brew update
> brew update
> sudo chown -R $(whoami) $(brew --prefix)
> 
> and restarted the Macbook and finally filed an issue at
> https://github.com/Homebrew/homebrew/issues/50065 
> 
> 
> If you are using Homebrew PostgreSQL package at the latest Mac OSX, please 
> share some tips on how to get it running or at least proceed in debugging.
> 
> Thank you
> Alex
> 

Re: [GENERAL] Distributed Table Partitioning

[Peter J. Holzer]

On 2016-03-12 21:19:11 -0500, Melvin Davidson wrote:
> - Original Message -
> From: "Leonardo M. Ramé" 
> To: "PostgreSql-general" 
> Sent: Saturday, 12 March, 2016 8:25:01 PM
> Subject: [GENERAL] Distributed Table Partitioning
> 
> I have this problem: a Master table containing records with a timestamp
> column registering creation date-time, and one Detail table containing
> info related to the Master table.
> 
> As time went by, those tables grew enormously, and I can't afford
> expanding my SSD VPS. So I'm thinking about storing only NEW data into
> it, and move OLD data to a cheaper SATA VPS.
[...]
> Why don't you just make use of tablespaces and partition the child tablespaces
> so that the newer parttion is on the SSD and the older one is on SATA?

Since he mentioned virtual private servers (VPS) the reason might be
that his hoster offers VPS with SSDs (of various sizes) and VPS with
rotating hard disks (of various sizes), but not VPS with both. So he
can't rent a VPS with a relatively small SSD and a larger hard disk. 

That might be a reason to look for an alternate hoster, but if he's
otherwise happy, switching to an unknown provider might be considered
too large a risk.

hp

-- 
   _  | Peter J. Holzer| I want to forget all about both belts and
|_|_) || suspenders; instead, I want to buy pants 
| |   | h...@hjp.at | that actually fit.
__/   | http://www.hjp.at/ |   -- http://noncombatant.org/


signature.asc
Description: Digital signature