Re: [GENERAL] In a view, how do I cause fields with possible NULLs to be treated as a blank string in a replace operation?

On Mon, Jun 27, 2011 at 5:58 PM, Thom Brown wrote: > > Try coalesce: > http://www.postgresql.org/docs/9.0/static/functions-conditional.html#AEN15541 > > So if foo is a null value, and you used COALESCE(foo, 'bar'), the > output would be 'bar', otherwise it would be whatever the value of foo > is.

Re: [GENERAL] CRUD plpgsql generator

On Wed, 2011-06-22 at 09:02 -0500, Merlin Moncure wrote: > On Wed, Jun 22, 2011 at 5:40 AM, Wim Bertels wrote: > > Hallo, > > > > does anyone have know of a free CRUD generator > > for generating plpgsql functions for doing CRUD operations on all the > > tables of a database or schema or just one

[GENERAL] rationale behind quotes for camel case?

Hi all, first of all I'm not expressing any critique against the use of quotes for identifier expressed using camel case. However a lot of new postgresql users seems to be unhappy with the use of quotes for camel case identifiers, so I'd like to know what is the rationale behind it. I mean, is a f

[GENERAL] Setup postgres with automatic table and user intitiallisation

Hi I programmed an application which uses postgres 9.0. Currently i am writing an installer for my application and wrote my own GUI to set up postgres settings and i already install it ofer the original setup.exe with parameters. After that i want to create user roles and tables with psql.exe

[GENERAL] Multi-tenancy in Postgres

Hello, I've just read through a paper here: http://www.edbt.org/Proceedings/2011-Uppsala/papers/edbt/a12-schiller.pdfabout multi-tenancy. They used Postgres for their work and while it is academic and would need further work I'm just wondering if anyone in the Postgres team is looking at implemen

Re: [GENERAL] rationale behind quotes for camel case?

On 06/28/11 2:12 AM, fluca1...@infinito.it wrote: Hi all, first of all I'm not expressing any critique against the use of quotes for identifier expressed using camel case. However a lot of new postgresql users seems to be unhappy with the use of quotes for camel case identifiers, so I'd like t

Re: [GENERAL] rationale behind quotes for camel case?

On Tue, Jun 28, 2011 at 4:12 AM, wrote: > Hi all, > first of all I'm not expressing any critique against the use of quotes for > identifier expressed using camel case. However a lot of new postgresql users > seems to be unhappy with the use of quotes for camel case identifiers, so > I'd like to k

Re: [GENERAL] Gist Index: Problem getting data in GiST Support Functions "penalty".

On Monday, June 27, 2011 5:13:04 pm Michael Gould wrote: > I am running 9.0.4 and I'm running it on Windows 7 Ultimate which is my > development machine. > > I can't even create a server. When I try I get a message > > > The server doesn't accept connections: the connection library reports > co

Re: [GENERAL] Gist Index: Problem getting data in GiST Support Functions "penalty".

Basic question in Windows - Did you turned of UAC -Original Message- From: Adrian Klaver Sent: 28 czerwca 2011 16:40 To: pgsql-general@postgresql.org Cc: Michael Gould Subject: Re: [GENERAL] Gist Index: Problem getting data in GiST Support Functions "penalty". On Monday, June 27, 2011 5:

Re: [GENERAL] rationale behind quotes for camel case?

On Tue, Jun 28, 2011 at 8:43 AM, Merlin Moncure wrote: > > The rebuttal to the above points is that the problem with not quoting > is that your identifiers are folded to lower case on the server which > can make them difficult to read in psql, pgadmin, etc. when outputted. > This is true and I c

Re: [GENERAL] rationale behind quotes for camel case?

fluca1...@infinito.it wrote: first of all I'm not expressing any critique against the use of quotes for identifier expressed using camel case. However a lot of new postgresql users seems to be unhappy with the use of quotes for camel case identifiers, so I'd like to know what is the rationale b

Re: [GENERAL] rationale behind quotes for camel case?

On Tue, Jun 28, 2011 at 11:09:52AM -0500, dennis jenkins wrote: > Any suggestions on how to name tables when table names contain both > multi-word nouns and mutli-table "many-many" mappings? > > Example: Suppose that I have a table called "foo" and another table called > "barBiz" (or "bar_biz" i

Re: [GENERAL] rationale behind quotes for camel case?

> On Tue, Jun 28, 2011 at 11:09:52AM -0500, dennis jenkins wrote: > >> Any suggestions on how to name tables when table names contain both >> multi-word nouns and mutli-table "many-many" mappings? >> >> Example: Suppose that I have a table called "foo" and another table called >> "barBiz" (or "

Re: [GENERAL] Multi-tenancy in Postgres

Emrul Islam wrote: I've just read through a paper here: http://www.edbt.org/Proceedings/2011-Uppsala/papers/edbt/a12-schiller.pdf about multi-tenancy. They used Postgres for their work and while it is academic and would need further work I'm just wondering if anyone in the Postgres team is l

[GENERAL] PLPGSQL SETOF functions

I am porting some Oracle code to PLPGSQL and am having a problem with functions that return SETOF datatype. In Oracle, the functions I'm porting return a TABLE of TYPE datatype, this TABLE being itself a named type. I am not aware of how to do this in PLPGSQL. Consider a function with header:

Re: [GENERAL] rationale behind quotes for camel case?

On Tue, Jun 28, 2011 at 04:00:42AM -0700, John R Pierce wrote: > capitals (probably reflecting its 1980s IBM origins), but PG decided > a long time ago to default to lower case, since the final result has > the same effect. Well, it _sort of_ has the same effect. In a traditional SQL environment,

Re: [GENERAL] Multi-tenancy in Postgres

I think Greg might be forgetting that some of us don't always get to choose what we work on. I was in a shop that decided to go with multi-tenancy for reason both technical and um, er envious. One schema to update versus n, for an example of the former. Amazon does it, for the other example. But

[GENERAL] DROP TABLE Appears to Fail

I cannot recall issuing a DROP TABLE command from psql that did not work, but seem to have this as a new experience. When I look at the database table list with '\d' I see public | station_type | table| rshepard public | station_type_statype_seq

Re: [GENERAL] DROP TABLE Appears to Fail

Silly question, but did you try it with a semicolon after the drop table? # drop table station_type; I've noticed that if you are in the middle of a statement and issue a \ command, psql ignores the SQL you've typed in and just does the \ command. On Tue, Jun 28, 2011 at 3:34 PM, Rich Shepard w

Re: [GENERAL] DROP TABLE Appears to Fail

Hi Rich, it might be sufficient to add a semicolon to your drop statement: # drop table station_type; HTH, Andy - Ursprüngliche Mail - > I cannot recall issuing a DROP TABLE command from psql that did not > work, > but seem to have this as a new experience. > > When I look at the datab

Re: [GENERAL] DROP TABLE Appears to Fail

On Wed, 29 Jun 2011, Andy Firel wrote: it might be sufficient to add a semicolon to your drop statement: # drop table station_type; Andy, Actually, that's not true. On a whim I tried that and psql complained about a syntax error at the initial 'd'. Rich -- Sent via pgsql-general mailing l

Re: [GENERAL] Multi-tenancy in Postgres

On 06/28/2011 05:45 PM, Rob Sargent wrote: I think Greg might be forgetting that some of us don't always get to choose what we work on. I was in a shop that decided to go with multi-tenancy for reason both technical and um, er envious. There are certainly successful deployments of multi-tenant

Re: [GENERAL] DROP TABLE Appears to Fail

On Tue, 28 Jun 2011, Rick Genter wrote: Silly question, but did you try it with a semicolon after the drop table? Rick, See my answer to Andy: that's incorrect syntax and psql complains. I've noticed that if you are in the middle of a statement and issue a \ command, psql ignores the SQL

Re: [GENERAL] DROP TABLE Appears to Fail

After issuing the \d you are still in the middle of your command. Witness the following copy/paste of a terminal session: bash-3.2$ ./psql Password: psql (8.4.4) Type "help" for help. postgres=# create table foo (bar int); CREATE TABLE postgres=# drop table foo postgres-# \d List of relat

Re: [GENERAL] rationale behind quotes for camel case?

On 06/28/2011 12:09 PM, dennis jenkins wrote: Example: Suppose that I have a table called "foo" and another table called "barBiz" (or "bar_biz" if you prefer). Further, both of these tables have a "serial" primary key. Now I want to create a third table that represents a many-to-many relatio

Re: [GENERAL] Multi-tenancy in Postgres

On 06/28/2011 04:52 PM, Greg Smith wrote: > On 06/28/2011 05:45 PM, Rob Sargent wrote: >> I think Greg might be forgetting that some of us don't always get to >> choose what we work on. I was in a shop that decided to go with >> multi-tenancy for reason both technical and um, er envious. > > The

[GENERAL] Windows x64 : How do I get OSSP-UUID.sql contrib for postgresql x64

The installer for windows for 64bit versions of postgresql doesn't include ossp-uuid.sql. Does anyone know where or how to get this? Thanks -- *Grace Batumbya* Research Assistant | Seneca CDOT Phone: 416-491-5050 x3548 cdot.senecac.on.ca

[GENERAL] Windows x64 : How do I get OSSP-UUID.sql contrib for postgresql x64

The installer for windows for 64bit versions of postgresql doesn't include ossp-uuid.sql. Does anyone know where or how to get this? Thanks -- *Grace Batumbya* Research Assistant | Seneca CDOT Phone: 416-491-5050 x3548 cdot.senecac.on.ca

Re: [GENERAL] DROP TABLE Appears to Fail

postgres=# drop table foo postgres-# \d specifically note the prompt. the -# means you're in the middle of a command. =# means its ready for a new command. as another example... pierce=# create table foo (id integer); CREATE TABLE pierce=# drop pierce-# table pierce-# foo

Re: [GENERAL] Setup postgres with automatic table and user intitiallisation

On 06/28/2011 05:34 AM, Lodron, Gerald wrote: I have the problem that when i run my batch i always have to let the user type in the password, thats nasty... I also tried PGPASSWORD but it does not work, it always prints that it is the wrong password... I'm not sure if setting PGPASSWORD i

Re: [GENERAL] Windows x64 : How do I get OSSP-UUID.sql contrib for postgresql x64

On 06/28/2011 04:13 PM, Grace Batumbya wrote: > The installer for windows for 64bit versions of postgresql doesn't > include ossp-uuid.sql. > Does anyone know where or how to get this? > > Thanks > -- > *Grace Batumbya* > Research Assistant | Seneca CDOT > Phone: 416-491-5050 x3548 > cdot.seneca

[GENERAL] point types in "DISTINCT" queries

Hi, I am running PostgreSQL 9.0.4 and I am getting an error with a SELECT DISTINCT query that contains a point type in the SELECT clause. To be more specific, a query such as: -- explicit declaration that it's a point type SELECT DISTINCT a.geocode::point FROM a

Re: [GENERAL] DROP TABLE Appears to Fail [SOLVED]

On Tue, 28 Jun 2011, Rick Genter wrote: After issuing the \d you are still in the middle of your command. Witness the following copy/paste of a terminal session: Ah, so! I didn't see this. Thank you very much, Rich -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To m

[GENERAL] PL/PGSQL - dynamic variable names

Hi, I am trying to EXECUTE .. INTO a variable that I want to be dynamically named. stuff := '{a,b,c,d}'; FOR i IN 1..4 LOOP thing := stuff[i]; -- stuff_a, stuff_b etc are functions - substitution works here exec_string := 'SELECT stuff_' || thing || '(''' || arg1

Re: [GENERAL] PL/PGSQL - dynamic variable names

On 06/28/2011 06:29 PM, Ben Carbery wrote: > Hi, > > I am trying to EXECUTE .. INTO a variable that I want to be > dynamically named. > > stuff := '{a,b,c,d}'; > > FOR i IN 1..4 LOOP > thing := stuff[i]; > > -- stuff_a, stuff_b etc are functions - substitution works here >

Re: [GENERAL] PL/PGSQL - dynamic variable names

> Hm, "a" isn't a variable,it's the value of stuff[1]. It's both.. DECLARE a integer; b integer; The point is I want a dynamically named variable. Here I've named them the same as stuff[i] but they can be anything provided it is a different variable name on each loop iteration. The manu

Re: [GENERAL] FOREIGN TABLE with dblink

Hi Jasmin, (2011/06/16 19:40), Jasmin Dizdarevic wrote: > Hi, > > is there any way to use the new foreign table feature with dblink? > That's almost clear to me: > > CREATE FOREIGN DATA WRAPPER pgsql90; > > CREATE SERVER srvlocal90 FOREIGN DATA WRAPPER pgsql90 OPTIONS (hostaddr > '127.0.0.1', d

Re: [GENERAL] PL/PGSQL - dynamic variable names

Actually there is a section in the manual on this problem: http://www.postgresql.org/docs/9.0/static/plpgsql-implementation.html#PLPGSQL-VAR-SUBST On 29 June 2011 11:41, Ben Carbery wrote: > > Hm, "a" isn't a variable,it's the value of stuff[1]. > > > It's both.. > > DECLARE > a integer; >

Re: [GENERAL] PL/PGSQL - dynamic variable names

Ben Carbery wrote: Hm, "a" isn't a variable,it's the value of stuff[1]. It's both.. DECLARE a integer; b integer; The point is I want a dynamically named variable. Here I've named them the same as stuff[i] but they can be anything provided it is a different variable name on

Re: [GENERAL] [PERFORM] change sample size for statistics

On Mon, Jun 13, 2011 at 6:33 PM, Willy-Bas Loos wrote: > On Fri, Jun 10, 2011 at 9:58 PM, Josh Berkus wrote: >> >> It's not 10%.  We use a fixed sample size, which is configurable on the >> system, table, or column basis. > > It seems that you are referring to "alter column set statistics" and >

Re: [GENERAL] point types in "DISTINCT" queries

On Tue, 2011-06-28 at 18:56 -0400, Jonathan S. Katz wrote: > I looked into the mailing list archives and found a potential answer > on this thread: > http://archives.postgresql.org/pgsql-general/2009-10/msg01122.php > However I wanted to see if it was still necessary that I would need > the comple