Re: [GENERAL] postgres.app OS X psql character encoding (utf-8) problem

On Jan 9, 2014, at 8:56 PM, Tom Lane wrote: > Uh-huh. You need to complain to whoever packages Postgres93.app, then. This is done: https://github.com/PostgresApp/PostgresApp/issues/165 Thanks Tom, I appreciate your time. -Chris -- Sent via pgsql-general mailing list (pgsql-general@postgre

Re: [GENERAL] postgres.app OS X psql character encoding (utf-8) problem

Chris Hiestand writes: > postgres93.app is supplied with its own version of libedit. > user@hostname:~ $ otool -L `which psql` > /Applications/Postgres93.app/Contents/MacOS/bin/psql: > /Applications/Postgres93.app/Contents/MacOS/lib/libpq.5.dylib > (compatibility version 5.0.0, current ver

Re: [GENERAL] SQL State XX000 : XML namespace issue

Panneerselvam Posangu wrote > Hi, > When we run a SQL statement in Postgres 9.2 we get an error. > Error : Could not register XML namespace with name "" and URI "" SQL State > XX000 > In the SQL state we use xpath function. Any reason why this is happening.. > Thanks,Panneer No. Try providing mor

Re: [GENERAL] Looking for settings/configuration for FASTEST reindex on idle system.

On Thu, Jan 9, 2014 at 2:03 PM, Jeff Amiel wrote: > I have a maintenance window coming up and using pg_upgrade to upgrade from > 9.2.X to 9.3.X. > As part of the window, I’d like to ‘cluster’ each table by its primary key. > After doing so, I see amazing performance improvements (probably mostl

Re: [GENERAL] wal archive peak during pg_dump

On Thu, Jan 9, 2014 at 7:42 PM, Willy-Bas Loos wrote: > Hi, > > I've set up hot standby slaves for a couple of clusters. > The wal is cleaned up after use, i don't use it as a backup (yet). > It seems that the amount of wal peaks shortly after midnight, when pg_dump > is running. > > It doesn't se

Re: [GENERAL] Looking for settings/configuration for FASTEST reindex on idle system.

On Thursday, January 9, 2014 4:03 PM, Jeff Amiel wrote: I am familiar with pg_reorg and it’s sibling pg_repack - but they call the base postgresql reindex functions underneath - and I have learned by using ‘verbose’ that the actual clustering of the table is quick - it’s the reindexing that

Re: [GENERAL] pg_restore - selective restore use cases. HINT use DROP CASCADE

On 01/09/2014 01:51 PM, Day, David wrote: Adrian, Thank you for your response. I would note that the original dump archive created by pg_dump included all schemas and that I only intend to restore a schema from it that is self contained, or a group of related tables from it. I just tried th

Re: [GENERAL] returning json object with subset of keys

On Thu, Jan 9, 2014 at 1:42 AM, Raphael Bauduin wrote: > On Wed, Jan 8, 2014 at 4:05 PM, Merlin Moncure wrote: >> >> On Wed, Jan 8, 2014 at 6:37 AM, Raphael Bauduin wrote: >> > Hi >> > >> > I'm using the json functionalities of postgresql 9.3. >> > I have a query calling json_populate_recordset

[GENERAL] Looking for settings/configuration for FASTEST reindex on idle system.

I have a maintenance window coming up and using pg_upgrade to upgrade from 9.2.X to 9.3.X. As part of the window, I’d like to ‘cluster’ each table by its primary key.  After doing so, I see amazing performance improvements (probably mostly because of index bloat - but possibly due to table fragm

Re: [GENERAL] pg_restore - selective restore use cases. HINT use DROP CASCADE

Adrian, Thank you for your response. I would note that the original dump archive created by pg_dump included all schemas and that I only intend to restore a schema from it that is self contained, or a group of related tables from it. I acknowledge the dangers inherent in selective restoration,

Re: [GENERAL] postgres.app OS X psql character encoding (utf-8) problem

On Jan 9, 2014, at 1:09 PM, Tom Lane wrote: > What this sounds like is that the readline or libedit library doesn't > understand multibyte characters properly. psql itself doesn't have > anything to do with the display of un-entered lines, but relies on > one of those libraries to manage input

Re: [GENERAL] Add custom properties to a column's definition (pg_attribute)

Brooke Beacham wrote > (without having to replicate/maintain a table of columns separately from > the system catalog) Just create the friggin' table and wrap whatever logic you want in a view (or functions) so that you at least get usable results/defaults for any columns you haven't added. Any

Re: [GENERAL] postgres.app OS X psql character encoding (utf-8) problem

Chris Hiestand writes: > If I enter a unicode character in the psql cli, such as: > user=# select 'ö'; > But before hitting enter, use the keyboard "left" button to move the cursor > across all the way to the left edge, and then back all the way to the right, > the output get distorted and look

[GENERAL] postgres.app OS X psql character encoding (utf-8) problem

Hi everyone, I am running Postgres93.app (v 9.3.1). I'm new to Postgres, but experienced with MySQL and *nix. I'm having a problem with the command line client "psql" while connecting to postgress.app running on the localhost on OS X. It's behaving as if somewhere a character encoding is set in

Re: [GENERAL] pg_restore - selective restore use cases. HINT use DROP CASCADE

On 01/09/2014 08:51 AM, Day, David wrote: I have needs to do selective schema or table restorations and the pg_restore utility seems to have hooks for this, yet seems deficient for this type of problem. It appears that I have to develop a custom script to do what I think would be a standard ki

Re: [GENERAL] Add custom properties to a column's definition (pg_attribute)

On 09/01/2014 20:16, Raymond O'Donnell wrote: > On 09/01/2014 20:08, Brooke Beacham wrote: >> Can anyone suggest a way to associate a set of custom properties >> (key/value pairs) with a column's definition (ie pg_attribute)? >> >> (without having to replicate/maintain a table of columns separately

Re: [GENERAL] Add custom properties to a column's definition (pg_attribute)

On 09/01/2014 20:08, Brooke Beacham wrote: > Can anyone suggest a way to associate a set of custom properties > (key/value pairs) with a column's definition (ie pg_attribute)? > > (without having to replicate/maintain a table of columns separately from > the system catalog) > > For example: > * I

[GENERAL] Add custom properties to a column's definition (pg_attribute)

Can anyone suggest a way to associate a set of custom properties (key/value pairs) with a column's definition (ie pg_attribute)? (without having to replicate/maintain a table of columns separately from the system catalog) For example: * I have a table with 50 columns * I'd like to 'flag' 30 of th

Re: [GENERAL] Index space growing even after cleanup via autovacuum in Postgres 9.2

Tirthankar Barari writes: > However, we are noticing that after autovacuum, our disk space > consumption is still increasing and the increase is in the index size > (by querying pg_total_relation_size("mytable") and > pg_indexes_size("mytable")). > In Postgres 9.2.2, doesn't autovacuum cleanup

[GENERAL] Index space growing even after cleanup via autovacuum in Postgres 9.2

Hi, We have a table where we insert about 10 million rows everyday. We keep 14 day's worth of entries (i.e. 140 mil). A scheduled task wakes up every day and deletes all entries past the 14 day window (i.e. deletes entries from the 15th day in the past). We have autovacuum set to trigger whe

Re: [GENERAL] How to know server status variable in postgresql?

oh thanks @Sameer Kumar Thanks & Regards, A.Mohamed Bilal On Thu, Jan 9, 2014 at 12:50 PM, Sameer Kumar [via PostgreSQL] < ml-node+s1045698n5786022...@n5.nabble.com> wrote: > > > > On Wed, Jan 8, 2014 at 7:22 PM, ambilalmca <[hidden > email]

Re: [GENERAL] Last inserted row id with complex PK

On Wed, Jan 8, 2014 at 5:39 PM, Alban Hertroys wrote: > On 08 Jan 2014, at 16:54, Nelson Green wrote: > > > I have a projects log table with a three column PK, project_num, > person_num, and sequence, where each new entry for a project/person > combination increments the sequence, which is not a

[GENERAL] SQL State XX000 : XML namespace issue

Hi, When we run a SQL statement in Postgres 9.2 we get an error. Error : Could not register XML namespace with name "" and URI "" SQL State XX000 In the SQL state we use xpath function. Any reason why this is happening.. Thanks,Panneer

[GENERAL] pg_restore - selective restore use cases. HINT use DROP CASCADE

I have needs to do selective schema or table restorations and the pg_restore utility seems to have hooks for this, yet seems deficient for this type of problem. It appears that I have to develop a custom script to do what I think would be a standard kind of restorations. --- Given: Postgres 9.3

Re: [GENERAL] getting domain information from query results

Marco Baringer writes: > it seems, if my understanding of the protocol is correct, that the oid > of the underlying type, text in this case, is returned and not the oid > of the domain. That's correct. This was an intentional decision long ago, and we're unlikely to reconsider now for fear of br

[GENERAL] getting domain information from query results

hi, i have this schema: create domain almost_a_string AS text; create table object ( name_like_thing almost_a_string ); and i'm trying to go from the results, using postgresql's frontend/backend protocol, of this query: select name_like_thing from object; to the domain of the column nam

Re: [GENERAL] argument of CASE/WHEN must not return a set

- Original Message - From: David Johnston >The condition (WHEN) in a case cannot be a set. You have to make the >expression always resolve to a single row/value. >I'd suggest creating a regexp_matches_single(...) function that calls >regexp_matches(...) in a sub-select so that no matc

Re: [GENERAL] argument of CASE/WHEN must not return a set

Sameer Kumar wrote > On Thu, Jan 9, 2014 at 1:26 AM, George Weaver < > gweaver@ > > wrote: > >> ARRAY_TO_STRING( REGEXP_MATCHES('12-70510','^[0-9,0-9.0-9]+') >> development(# , ',') > > > I guess this part of your statement will return 1,2, which is a set > >

Re: [GENERAL] argument of CASE/WHEN must not return a set

On Thu, Jan 9, 2014 at 6:05 PM, Sameer Kumar wrote: > > On Thu, Jan 9, 2014 at 1:26 AM, George Weaver wrote: > >> ARRAY_TO_STRING( REGEXP_MATCHES('12-70510','^[0-9,0-9.0-9]+') >> development(# , ',') > > > I guess this part of your statement will return 1,2, whic

Re: [GENERAL] getting domain information from query results

On Thu, Jan 09, 2014 at 12:10:34PM +0100, Marco Baringer wrote: > assuming i have this schema: > > create domain almost_a_string AS text; > create table object ( name_like_thing almost_a_string ); > > and i'm trying to go from the results, using postgresql's > frontend/backend protocol, of t

[GENERAL] getting domain information from query results

i am trying to access a value's (a particular column of a row of query results) domain information; but all i seem to be able to get at, using the frontend/backend protocol, is the underlying type. assuming i have this schema: create domain almost_a_string AS text; create table object ( name

[GENERAL] wal archive peak during pg_dump

please excuse my forgetting [GENERAL] in the subject of the other mail. On Thu, Jan 9, 2014 at 11:42 AM, Willy-Bas Loos wrote: > Hi, > > I've set up hot standby slaves for a couple of clusters. > The wal is cleaned up after use, i don't use it as a backup (yet). > It seems that the amount of wal

[GENERAL] wal archive peak during pg_dump

Hi, I've set up hot standby slaves for a couple of clusters. The wal is cleaned up after use, i don't use it as a backup (yet). It seems that the amount of wal peaks shortly after midnight, when pg_dump is running. It doesn't seem logical to me that pg_dump should generate wal, but i haven't been

Re: [GENERAL] Last inserted row id with complex PK

Hi Nelson: On Wed, Jan 8, 2014 at 7:14 PM, Nelson Green wrote: > My apologies, I was not completely clear. I will not know any of the columns > in advance. The most recent insert is the result of user input from a web > form, so I won't know what project or what user generated the last insert. >

Re: [GENERAL] argument of CASE/WHEN must not return a set

On Thu, Jan 9, 2014 at 1:26 AM, George Weaver wrote: > ARRAY_TO_STRING( REGEXP_MATCHES('12-70510','^[0-9,0-9.0-9]+') > development(# , ',') I guess this part of your statement will return 1,2, which is a set Can you try below: SELECT CASE WHEN LEN