Re: [GENERAL] Where do I enter commands?

Hello David, This cookbook has worked for me for the last five years on Mac OS X, always and totally reliable up to incl. El Capitan and every intermittent release before. And most of all, it worked even as a repair measure. So if my server would stop working after a small Mac Os X update, I to

[GENERAL] Cannot query views with WHERE clause on renamed columns

Hi, I try to do what seems to be totally simple, but it fails. If I query a view that contains renamed columns without any qualifier like SELECT * FROM myview it displays everything. If however I do SELECT reanmedviewcolumn FROM myview It tells me column does not exist which in fact does. I

[GENERAL] DISTINCT ON changes sort order on its own it seems

Hi, following a query: SELECT DISTINCT ON (msgid) msgid FROM (SELECT refid_messages as msgid FROM messagehistorywithcontent WHERE 1=1 AND (lower(ARRAY_TO_STRING(ARRAY[login,firstname,lastname,content,msgtitle], ' ')) LIKE '%gg%') ORDER BY messagekind DESC) as foo This query rearranges the sor

Re: [GENERAL] DISTINCT ON changes sort order

R BY messagekind ASC Thanks Alex On 24.04.2013, at 16:03, Alexander Reichstadt wrote: > Hi, > > following a query: > SELECT DISTINCT ON (msgid) msgid FROM (SELECT refid_messages as msgid FROM > messagehistorywithcontent WHERE 1=1 AND > (lower(ARRAY_TO_STRING(ARRAY[log

[GENERAL] DISTINCT ON changes sort order

Hi, following a query: SELECT DISTINCT ON (msgid) msgid FROM (SELECT refid_messages as msgid FROM messagehistorywithcontent WHERE 1=1 AND (lower(ARRAY_TO_STRING(ARRAY[login,firstname,lastname,content,msgtitle], ' ')) LIKE '%gg%') ORDER BY messagekind DESC) as foo This query rearranges the sor

Re: [GENERAL] Cannot install postgres 9.2, less than 32 MB of memory

Thanks, impatient me, did not reboot, did now, works! Thank you Am 19.01.2013 um 17:52 schrieb Tom Lane: > Alexander Reichstadt writes: >> I never had any problems to install postgres 9.1, now I do with 9.2. This is >> the enterprise installer for Mac OS X. I tried to install

[GENERAL] Cannot install postgres 9.2, less than 32 MB of memory

Hi, I never had any problems to install postgres 9.1, now I do with 9.2. This is the enterprise installer for Mac OS X. I tried to install and am getting the dreaded error message regarding shared memory, which is not very helpful because it does not seem to actually look at the file it complai

[GENERAL] Re: [GENERAL] SELECT …. WHERE id is in pool of ids of subquery……

Heureka, it works. Thanks! Am 26.09.2012 um 22:37 schrieb David Johnston: > On Sep 26, 2012, at 16:28, Alexander Reichstadt wrote: > >> Hi, >> >> I am reading the docu and am looking for an example our explanation as to >> the difference of setof and arrays. >

[GENERAL] SELECT …. WHERE id is in pool of ids of subquery……

Hi, I am reading the docu and am looking for an example our explanation as to the difference of setof and arrays. But maybe this is not even relevant. The reason I am looking for this is because I need to build a function that looks for all records whose ids are in a pool of other records. Ma

[GENERAL] Displaying image from php script displays string

Sorry if this has been asked a thousand time. I read through the web and think to have done everything I should. I have a db with a bytea field. In it I stored data from a png file. When I stored it I used pg_escape_bytea() and it seems it ended up ok in the database. I made a test setup, che

Re: [GENERAL] Where do I get pgAdmin 1.16 for openSuSE?

Also, you could try the enterprise package, it does have the pgAdmin and probably every other bit you could have a need for now or later: http://enterprisedb.com/downloads/postgres-postgresql-downloads The installer walks you through all the subpackages and you can confirm for each if you want

[GENERAL] Displaying image from php script displays string

Sorry if this has been asked a thousand time. I read through the web and think to have done everything I should. I have a db with a bytea field. In it I stored data from a png file. When I stored it I used pg_escape_bytea() and it seems it ended up ok in the database. I made a test setup, che

Re: [GENERAL] [postgres no lnger launches

Thanks, Tom, I messed it up myself, see my other post. Am 04.06.2012 um 01:16 schrieb Tom Lane: > Alexander Reichstadt writes: >>> So sure enough that process claimed to might be starting does not end up >>> being a process. Where do I look for what's actually going

[Solved] Re: [GENERAL] [postgres no lnger launches

I found the issue, I changed the connection amount. I changed it back now. Alex Am 04.06.2012 um 00:39 schrieb Alexander Reichstadt: > Hi, > > how do I troubleshoot postgres? It simply won't start any more. I cannot > login, not locally nor remotely. I tried a couple of thing

Re: [GENERAL] [postgres no lnger launches

Btw, this is OSX 10.7. Am 04.06.2012 um 00:39 schrieb Alexander Reichstadt: > Hi, > > how do I troubleshoot postgres? It simply won't start any more. I cannot > login, not locally nor remotely. I tried a couple of things without success. > > bash-3.2$ ps -Ac | eg

[GENERAL] [postgres no lnger launches

Hi, how do I troubleshoot postgres? It simply won't start any more. I cannot login, not locally nor remotely. I tried a couple of things without success. bash-3.2$ ps -Ac | egrep postgres bash-3.2$ /Library/PostgreSQL/9.1/bin/pg_ctl start -D /Library/PostgreSQL/9.1/data -l postgres.log pg_ctl:

Re: [GENERAL] SELECT issue with references to different tables

ain.ref_petid) > > Kind Regards, > > Misa > > > 2012/6/2, Alexander Reichstadt : >> Hi, >> >> I have a query I cannot figure out in postgres or actually in any other way >> than using the client front end, which I would prefer not to do. >> >

[GENERAL] SELECT issue with references to different tables

Hi, I have a query I cannot figure out in postgres or actually in any other way than using the client front end, which I would prefer not to do. So, I have 4 tables pets persons companies pets_reference pets have owners, the owner at any point in time is either a persons or a company, never b

[GENERAL] Export and import from one postgres server to another

Hi, I am trying to "simply" export my postgres database from one server and then import it into another. I thought I could use PhpPgAdmin, but the hints on the web don't seem to work. One said to simply get a dump from one phpPgAdmin, the go to the other server, select the database, click the S

Re: [GENERAL] Memory Management in pqlib, Garbage Collection support

(Sorry, I meant libpq). Actually it's finalize in Objective-C as well. PGSQLKit is the ObjC wrapper framework for libpq. I was confused by what I had learnt about GC, being it can't mix with non-GC. What the docu didn't mention in the places I read --or at least not so that it stuck-- was that i

Re: [GENERAL] Memory Management in pqlib, Garbage Collection support

ng PQclear'ed. Alex Am 03.05.2012 um 09:16 schrieb Peter Bex: > On Thu, May 03, 2012 at 09:08:53AM +0200, Alexander Reichstadt wrote: >> >> Hi, >> >> since I got no answer so far I searched through the docu again. I searched >> for GC as well as Garbage, a

Re: [GENERAL] pqlib garbage collection

against pqlib, it seems I need to have garbage collection enabled in order for it to link. But the documentation mentions it nowhere. Please, can someone confirm this or is this the wrong list? Thanks Alex Am 02.05.2012 um 23:32 schrieb Alexander Reichstadt: > Hi, > > regarding pq

[GENERAL] Memory Management in pqlib, Garbage Collection support

Hi, since I got no answer so far I searched through the docu again. I searched for GC as well as Garbage, and all garbage refers to is with regard to vacuuming a database. But my question refers to wether or not memory management is with garbage collection supported or not. When I try to link

[GENERAL] pqlib garbage collection

Hi, regarding pqlib, from what I gather it is with garbage collection supported, yes? If so, is PQclear a left-over or still mandatory for avoiding leaks? Thanks Alex -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.pos

Re: [GENERAL] Listen and Notify

consideration so for listener and notifier to match. Am 01.05.2012 um 14:51 schrieb Mark Morgan Lloyd: > Merlin Moncure wrote: >> On Mon, Apr 30, 2012 at 1:47 AM, Alexander Reichstadt wrote: >>> Hi, >>> >>> From the documentation I was able to build a tr

[GENERAL] Listen and Notify

Hi, >From the documentation I was able to build a trigger firing upon deletion of a >record a function that delivers tablename_operation as a notification one >needs to subscribe to. So in terminal I can say LISTEN persons_delete and >instantly will receive Asynchronous notification "p

Re: [GENERAL] Question on notifications

wn upon insertion. Doesn't the record exist given I trigger AFTER and not BEFORE the operation took palce? Am 30.04.2012 um 09:53 schrieb Albe Laurenz: > Alexander Reichstadt wrote: >> From the documentation I was able to build a trigger firing upon > deletion of a record a functio

Re: [GENERAL] QUestion on notifications

wn upon insertion. Doesn't the record exist given I trigger AFTER and not BEFORE the operation took palce? Am 30.04.2012 um 09:53 schrieb Albe Laurenz: > Alexander Reichstadt wrote: >> From the documentation I was able to build a trigger firing upon > deletion of a record a functio

Re: [GENERAL] Question on notifications

Thank you. You don't get the error, because you called FOR EACH ROW EXECUTE, which I didn't. I fixed it, it all works now! Am 30.04.2012 um 14:58 schrieb Albe Laurenz: > CREATE TRIGGER t1_trig AFTER INSERT OR UPDATE OR DELETE ON t1 > FOR EACH ROW EXECUTE PROCEDURE notify_trigger(); signatur

[GENERAL] Question on notifications

Hi, From the documentation I was able to build a trigger firing upon deletion of a record a function that delivers tablename_operation as a notification one needs to subscribe to. So in terminal I can say LISTEN persons_delete and instantly will receive Asynchronous notification "perso

Re: [GENERAL] Cannot store special chars using c lib

Thanks, Albe, I had checked this, too, and it was ok. I already posted the solution to the board. It was an error due to an incorrect conversion between an object-instance and a char. It works now. Am 22.03.2012 um 12:50 schrieb Albe Laurenz: > Alexander Reichstadt wrote: >> I need t

Re: [GENERAL] Cannot store special chars using c lib

to the Cocoa list at Apple. Thanks and regards Alex Am 22.03.2012 um 09:06 schrieb Alexander Reichstadt: > Sorry, there is a copy-paste error, actually the code really is: >> const char *cString = [sql cStringUsingEncoding:defaultEncoding]; >> if (cString == NULL) { > blabl

Re: [GENERAL] Cannot store special chars using c lib

um 09:02 schrieb Alexander Reichstadt: > Hi, > > I need to be able to store special chars, German Umlaute, in my tables. This > works when using pgPHPAdmin to store the same value to the same field. But > when using the c-library it doesn't, fields stored are garbled. >

[GENERAL] Cannot store special chars using c lib

Hi, I need to be able to store special chars, German Umlaute, in my tables. This works when using pgPHPAdmin to store the same value to the same field. But when using the c-library it doesn't, fields stored are garbled. I checked using \l to see what encoding the database is which is UTF8, UTF8

Re: [GENERAL] Altering column type from text to bytea

I just found a thread that outlines this issue and how to solve it here: <http://archives.postgresql.org/pgsql-general/2005-12/msg01322.php> Thanks Alex Am 21.03.2012 um 20:56 schrieb Alexander Reichstadt: > Hi, > > when trying to change a text column to bytea I am gettin

[GENERAL] Altering column type from text to bytea

Hi, when trying to change a text column to bytea I am getting the following error: SQL error: ERROR: column "comment" cannot be cast to type bytea In statement: ALTER TABLE "public"."persons" ALTER COLUMN "comment" TYPE bytea I found that others had the same issue but I found no solution for

Re: [GENERAL] PASSWORD vs. md5('somepass')

Thanks, I was here <http://www.postgresql.org/docs/9.1/static/sql-createrole.html>. Am 20.03.2012 um 16:55 schrieb Josh Kupershmidt: > On Tue, Mar 20, 2012 at 8:28 AM, Alexander Reichstadt wrote: >> Hi, >> >> I look for a way to reproduce the encrypted string stored

[GENERAL] PASSWORD vs. md5('somepass')

Hi, I look for a way to reproduce the encrypted string stored as a password by means other than using the CREATE ROLE command. When using CREATE ROLEPASSWORD 'somepass' the resulting string for rolpassword in pg_authid always starts with md5, suggesting it would create some md5 string.

Re: [GENERAL] pg_role vs. pg_shadow or pg_user

Am 14.03.2012 um 22:52 schrieb Tom Lane: > Alexander Reichstadt writes: >> in the documentation of 8.1 the concept of roles is outlined compared >> to users and groups at >> <http://www.postgresql.org/docs/8.1/static/user-manag.html>. > > Um ... why are you read

[GENERAL] pg_role vs. pg_shadow or pg_user

Hi, in the documentation of 8.1 the concept of roles is outlined compared to users and groups at . I am running 9.1 and due to currently learning about the ins and outs of users and permissions in postgres as opposed to mysql, and becau

Re: [GENERAL] Ways to edit users and permissions for database

Excellent, Thank you. Exactly what I was looking for. Am 14.03.2012 um 14:26 schrieb Adrian Klaver: > On 03/14/2012 12:59 AM, Alexander Reichstadt wrote: >> Thanks, creation works fine, but how do I read existing permissions through >> SQL, is there some SELECT-stat

Re: [GENERAL] Ways to edit users and permissions for database

Thanks, creation works fine, but how do I read existing permissions through SQL, is there some SELECT-statement I can use? Am 14.03.2012 um 08:43 schrieb John R Pierce: > On 03/14/12 12:38 AM, Alexander Reichstadt wrote: >> this was probably asked dozens of times before, but I coul

[GENERAL] Ways to edit users and permissions for database

Hi, this was probably asked dozens of times before, but I couldn't find where, and neither in the docs and what I found on the web didn't make sense. I found how to create users and check their permissions using terminal. But I need to alter and create users and permissions through libpq or SQL

Re: [GENERAL] GROUP BY or alternative means to group

d with the company without the where-clause. But where would I insert the max(address) piece? Am 12.03.2012 um 22:09 schrieb Scott Marlowe: > On Mon, Mar 12, 2012 at 1:35 PM, Alexander Reichstadt wrote: >> Hi, >> >> the following statement worked on mysql but gives me an e

Re: [GENERAL] GROUP BY or alternative means to group

there is a solution with subquery which finds min id in table > addresses of each refid_companies in table addresses_reference and this > subquery is joined with companies table, but I am afraid this is not the best > one. > > Regards, > Bartek > > > 2012/3/12 Alexander

Solved [Re: [GENERAL] GROUP BY or alternative means to group]

So the mysql way for group by seems to be non-standard. What works for postgres is the DISTINCT ON (fieldname) approach. Thanks Am 12.03.2012 um 20:35 schrieb Alexander Reichstadt: > Hi, > > the following statement worked on mysql but gives me an error on postgres: &g

[GENERAL] GROUP BY or alternative means to group

Hi, the following statement worked on mysql but gives me an error on postgres: column "addresses.address1" must appear in the GROUP BY clause or be used in an aggregate function I guess I am doing something wrong. I read the web answers, but none of them seem to meet my needs: SELECT compani

Re: [GENERAL] Return keys for values with dot-separation in joined statements

PGSQLKit for Cocoa. Am 12.03.2012 um 18:09 schrieb Tom Lane: > Alexander Reichstadt writes: >> Is there a way or some setting on postgres server to tell postgres to use >> the fieldnames exactly as provided in the select? > > You didn't say exactly which "API&

[GENERAL] Return keys for values with dot-separation in joined statements

Hi, writing a client frontend I started with mysql and migrated to postgres. Now I found out that there is a difference in the way joined queries are returned: The query SELECT persons.id,persons.lastname,persons.firstname,persons.salutation,persons.title,addresses.address1,addresses.address2,

[GENERAL] Question on datatypes returned for "select oid, typname from pg_type"

Hi all, for an API I want to make some changes to, I need to know the datatype being used for a record's column. In fact the entire record is to be transferred into a dictionary. From my web research I came to use select oid, typname from pg_type to find out what datatypes exist. When checkin

Re: [GENERAL] Quoted strings on CLI

> LINE 1: select 'Peter\'s toy'; > ^ > HINT: Use '' to write quotes in strings, or use the escape string > syntax (E'...'). > ?column? > ─ > Peter's toy > (1 row) > > or you can use PostgreSQL enhanced

[GENERAL] Quoted strings on CLI

Hi, I just migrated from mysql and am running into an issue I found no solution for when researching. Using the web interface I can insert values that contain single-quotes. But using the CLI I found no way to select or insert single quotes: PetWork=# select * from persons where firstname='\'P