[GENERAL] News from IBM (DB2)
Hello there! http://news.zdnet.co.uk/software/0,39020381,39249666,00.htm What do you think about this? Nicolay ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] eqpg doesn't like bit fields
Am Mittwoch, 1. Februar 2006 15:46 schrieb Noble, Robert: > I get the following error from ecpg when it encounters a structure > containing a bit field: > ... A quick guess would be that the parser simply doesn't know this syntax. I'll have a look at it. Michael -- Michael Meskes Email: Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org) ICQ: 179140304, AIM/Yahoo: michaelmeskes, Jabber: [EMAIL PROTECTED] Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL! ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] PostgreSQL best practices?
On Friday 03 February 2006 13:02, Bruce Momjian wrote: > Read the FAQ. > Thanks. Somehow it slipped my mind to go to PostgreSQL's FAQ :P Regards, Leonard Soetedjo ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] logging settings
Mott Leroy <[EMAIL PROTECTED]> writes: > I turned on statement logging (and duration), issued a "pg_ctl reload" > and got it working (logging to sys log and standard out). Now however, > when i try to turn it off, it won't turn off, using the same procedure. > I've now commented out all the lines related to logging, but it's still > logging. I think you're getting bit by a standard beginner gotcha: commenting out an entry in postgresql.conf will not change the state of a running postmaster. (A comment is a no-op, eh?) You need to put in a non-comment entry that sets the desired state. There's been various discussions in the past about making this behavior less non-intuitive, but nothing's been settled on ... regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] PostgreSQL best practices?
Read the FAQ. --- Leonard Soetedjo wrote: > On Tuesday 31 January 2006 14:09, Jim C. Nasby wrote: > > At present time, your best bet is to drop in on the IRC channel and ask > > questions there. You'll normally get an immediate reply, even if it's > > just a URL to look at. > > As a beginner, I feel not knowing where to start and what to ask in IRC or > mailing list (e.g. previously I didn't know that PostgreSQL has replication, > until I join the mailing list for some time). And in the mailing list, I saw > that there are repetitive questions asking about replication advice such as > what are the current choices, difference, caveats etc. I suppose that trend > is due to lack of beginner's documentation? (the answer might be in > PostgreSQL documentation, but I do find it rather technical for a beginner > like me to understand). > > Is there a FAQ or wiki that has a list of answer to beginners' questions? It > might not be complete or specific answers, but at least it gives some > direction to the questioner. > > I found Varlena.com's PostgreSQL General Bits to be quite good. However the > archives are sorted by date and therefore more difficult to discover > articles/tips (regarding new PostgreSQL features, for example). > > > Regards, > > Leonard Soetedjo > > ---(end of broadcast)--- > TIP 4: Have you searched our list archives? > >http://archives.postgresql.org > -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] logging settings
On Thu, Feb 02, 2006 at 06:19:53PM -0500, Mott Leroy wrote: > On Postgres 7.4.1 That's pretty old. If you can't upgrade to 8.0 or 8.1 then at least consider staying up to date with bug fixes by using the latest version in the 7.4 branch (currently 7.4.11). > I've been experiencing some strangeness with the logging settings in > postgresql.conf. > > I turned on statement logging (and duration), issued a "pg_ctl reload" > and got it working (logging to sys log and standard out). Now however, > when i try to turn it off, it won't turn off, using the same procedure. > I've now commented out all the lines related to logging, but it's still > logging. Did you change the settings to off and leave them uncommented or did you simply comment them out? Commenting out the lines will leave the settings at their previous values until you restart the server; if you want to change settings with a reload (not a restart) then change the values and leave them uncommented. Once you've actively changed the settings back to their defaults then you could comment them out. > Is it possible that this is related to connection pooling? Does each > connection hold these settings, such that a reload of the configuration > file only affects new connections, not existing ones? I just did some tests in 7.4.11 and settings like log_statement and log_min_duration_statement changed within a session as I reloaded the server, so I don't think connection pooling would matter (at least not for those settings). I think the more likely problem is that you commented out the settings instead of actually changing them. -- Michael Fuhr ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] Automating Backup
I presently use the following to export the database and as per my earleir post hope to have an equivent to do the backup as per: a) check for time since the last backup b) if older than hrs (i) perform a vacuum analyze (ii) force a call to dbbackup (iii) add a new record into fsyslog thanks R CREATE OR REPLACE FUNCTION export_database(text) RETURNS text AS ' declare tblname record; cnt record; dirchar varchar := ''/''; --char(92); --''/''; -- directory separator character, char 92 is backslash or / for windows tname varchar :=; tquery varchar :=; filename varchar :=; begin if $1 <> then tname := dirchar||$1||dirchar; else tname := dirchar; end if; for tblname in select tablename from pg_tables WHERE not(tablename like ''pg_%'') and not(tablename like ''t_%'') and not(tablename like ''%_list'') order by tablename loop filename := tname|| lower(tblname.tablename)||''.dat''; tquery := ''copy '' || tblname.tablename || '' to '' || quote_literal(filename)||'' with binary ''; execute tquery; end loop; return tquery; end; ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Automating backup
I would like each database to encapsulate its own backup procedure.
Each database contains a table (fsyslog) which has a record of each
backup taken:
At the application level I presently
a) check for time since the last backup
b) if older than hrs
(i) perform a vacuum analyze
(ii) force a call to dbbackup
(iii) add a new record into fsyslog
I would like to add a procedure to the database that does the backup and
thus ensure that all applications use the same methodology.
The procedure would take only the backup path as a parameter.
presently I send the command :
pch := pchar('pg_dump -C -h '+host+' -U '+usr+' -p '+pswd+ ' -f
'+bckup_path+' '+dbase);
to postgres.
as the operator is obviously logged in how do I
(1) trap their user id
(2) Send the call to pg_dump without knowing their password?
I expect this is a well worn route and am hoping not to have to reinvent
a wheel.
thanks very much as always
Richard
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [GENERAL] Primary keys for companies and people
Michael Glaesemann wrote: Hello, all! Recently there was quite a bit of discussion regarding surrogate keys and natural keys. I'm not interested in discussing the pros and cons of surrogate keys. What I'd like to find out are the different methods people actually use to uniquely identify companies and people *besides* surrogate keys. I'm currently working on an application that will include contact information, so being able to uniquely identify these two entities is of interest to me. Right now I'm thinking of uniquely identifying companies by telephone number. For example: create table companies ( company_id integer primary key -- telephone number, not serial , company_name text not null ); Of course, the company may have more than one telephone number associated with it, so there will also be a table associating telephone numbers and companies. create table companies__telephone_numbers ( company_id integer not null references companies (company_id) on update cascade on delete cascade , telephone_number integer not null , unique (company_id, telephone_number) ); There should also be a trigger that will check that the company_id matches an existing telephone number associated with the company, something like: create function assert_company_id_telephone_number_exists returns trigger language plpgsql as $$ begin if exists ( select company_id from companies except select company_id from companies join companies__telephone_numbers on (company_id = telephone_number) ) then raise exception 'company_id must match existing company telephone number'; end if; return null; end; $$; For people I'm more or less stumped. I can't think of a combination of things that I know I'll be able to get from people that I'll want to be able to add to the database. Starting off we'll have at least 7,000 individuals in the database, and I don't think that just family and given names are going to be enough. I don't think we'll be able to get telephone numbers for all of them, and definitely aren't going to be getting birthdays for all. I'm very interested to hear what other use in their applications for holding people and companies. Michael Glaesemann grzm myrealbox com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq Telephone numbers make bad primary keys because they get recycled. A phone number that belongs to me this year may belong to somebody else next year. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Solved: Re: [GENERAL] Logging queries
Madison Kelly wrote: Madison Kelly wrote: Hi all, I've got a machine I am setting up (read; low volume atm). I need a way to log all the queries made to Postgres (just for a short time). I've got a problem with a 3rd party program (OSS, but I'm not 'let' enough to trace it) and I am hoping to help fix the problem by being able to see what query it is exactly trying to do. I remember once long ago I did this but I'll be dumb-struck if I can remember how. As an aside; I've got a user named 'madison' who is in postgres and owns another database which is working fine with one of my programs. So I know the user is okay. I've created a new database and I am trying to tell this program to connect to the new database as this user (local machine) but I keep getting the error: 2006-02-02 23:07:36 [21958] LOG: connection received: host=127.0.0.1 port=33387 2006-02-02 23:07:36 [21958] FATAL: IDENT authentication failed for user "madison" 2 I've tried changing the 'pg_hba.conf' file from 'ident sameuser' to 'trust'' with no love, too. I'm hoping that seeing the query this program is using might help me solve this problem. Thanks!! Madison Oops, should have mentioned that I'm running 7.4.7 on Debian Sarge stable. Madison For the love of... >.< I found the answer right after posting... Just had to set 'log_min_duration_statement' in 'postgres.conf' to '0'. Sorry for the line noise! ^.^; Madison -- -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- Madison Kelly (Digimer) TLE-BU; The Linux Experience, Back Up Main Project Page: http://tle-bu.org Community Forum:http://forum.tle-bu.org -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Logging queries
Madison Kelly wrote: Hi all, I've got a machine I am setting up (read; low volume atm). I need a way to log all the queries made to Postgres (just for a short time). I've got a problem with a 3rd party program (OSS, but I'm not 'let' enough to trace it) and I am hoping to help fix the problem by being able to see what query it is exactly trying to do. I remember once long ago I did this but I'll be dumb-struck if I can remember how. As an aside; I've got a user named 'madison' who is in postgres and owns another database which is working fine with one of my programs. So I know the user is okay. I've created a new database and I am trying to tell this program to connect to the new database as this user (local machine) but I keep getting the error: 2006-02-02 23:07:36 [21958] LOG: connection received: host=127.0.0.1 port=33387 2006-02-02 23:07:36 [21958] FATAL: IDENT authentication failed for user "madison" 2 I've tried changing the 'pg_hba.conf' file from 'ident sameuser' to 'trust'' with no love, too. I'm hoping that seeing the query this program is using might help me solve this problem. Thanks!! Madison Oops, should have mentioned that I'm running 7.4.7 on Debian Sarge stable. Madison -- -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- Madison Kelly (Digimer) TLE-BU; The Linux Experience, Back Up Main Project Page: http://tle-bu.org Community Forum:http://forum.tle-bu.org -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Logging queries
Hi all, I've got a machine I am setting up (read; low volume atm). I need a way to log all the queries made to Postgres (just for a short time). I've got a problem with a 3rd party program (OSS, but I'm not 'let' enough to trace it) and I am hoping to help fix the problem by being able to see what query it is exactly trying to do. I remember once long ago I did this but I'll be dumb-struck if I can remember how. As an aside; I've got a user named 'madison' who is in postgres and owns another database which is working fine with one of my programs. So I know the user is okay. I've created a new database and I am trying to tell this program to connect to the new database as this user (local machine) but I keep getting the error: 2006-02-02 23:07:36 [21958] LOG: connection received: host=127.0.0.1 port=33387 2006-02-02 23:07:36 [21958] FATAL: IDENT authentication failed for user "madison" 2 I've tried changing the 'pg_hba.conf' file from 'ident sameuser' to 'trust'' with no love, too. I'm hoping that seeing the query this program is using might help me solve this problem. Thanks!! Madison -- -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- Madison Kelly (Digimer) TLE-BU; The Linux Experience, Back Up Main Project Page: http://tle-bu.org Community Forum:http://forum.tle-bu.org -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] specifying unix domain socket name
No, the name is .s.PGSQL.{port#}, and there is a lock file. Why do you
want to specify a different name?
---
[EMAIL PROTECTED] wrote:
> Is it possible to specify using PQsetdbLogin() or
> PQconnectdb()
> the name of unix domain socket?
>
> In documentation I read that I can specify path using host:
> host
> Name of the host to connect to. If this begins with
> a slash ('/'), it
> specifies Unix domain communication rather than TCP/IP
> communication.
> >>>The value is the name of the directory in which the
> socket file is stored.<<<
> The default is to connect to a Unix-domain socket in /tmp.
>
> And using port I can specify socket name extension:
> port
> The port number to connect to at the server host,
> >>>or the socket filename extension<<< for Unix-domain
> connections.
>
> How is it possible to specify full unix domain socket
> path and name, not only path and extension?
>
> Thanks
> Ervin
>
> ---(end of broadcast)---
> TIP 2: Don't 'kill -9' the postmaster
>
--
Bruce Momjian| http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup.| Newtown Square, Pennsylvania 19073
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [GENERAL] pgsql on win xp user permissions
You'll need to create a separate psql user. See http://www.postgresql.org/docs/8.1/static/sql-createrole.html Then get your php script to connect with that username/password. Trans Porter wrote: I want to setup a database that will be used by my php application. Right now I installed postgresql, I used an admin account to install it on a user account. So I can only access the database (like psql dbname) when i'm logged in as the user, if i try psql from admin it says fatal role "admin name" does not exist. Is this how its supposed to be? I am creating a php application that is powered by apache. I can work from admin account because in php scripts I connect to database using a different user name and so pgsql should not wine about admin accessing it eh? ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] 8.0.3 regexp_replace()...
On 2006-02-01, rlee0001 <[EMAIL PROTECTED]> wrote: > Stephan, > > How do IN and NOT IN treat NULLs? Don't these functions search an array > for a specified value returning true or false? I guess the intuitive > thing for IN and NOT IN to do would be to return NULL if NULL appears > anywhere in the array since those elements values are "unknown". foo IN (x1,x2,x3) is exactly equivalent to (foo = x1) OR (foo = x2) OR (foo = x3) foo NOT IN (x1,x2,x3) is likewise equivalent to (foo <> x1) AND (foo <> x2) AND (foo <> x3) In the first case, if one of the x? is null, then the result of the expression is true if any of the clauses is true, or null otherwise; TRUE OR NULL is true, while FALSE OR NULL is null. In the second case, the result is likewise determined by the logic of three-valued AND. Since TRUE AND NULL is null, and FALSE AND NULL is false, that means that the expression can never return true if any of the x? is null. > Oh yeah, my favorite is this: NULL::VARCHAR? Nope, you can't do it. => select null::varchar; varchar - (1 row) works for me. (Note: that's not an empty string; use \pset null in psql to see the difference.) > Not without creating your own CAST. Casting from what? NULL isn't a type... > Seems to me that an obvious value would be 'NULL'. Or maybe '' > (empty string). If NULL ever got converted to 'NULL' or '', how would you distinguish it from the literal 'NULL' or ''? -- Andrew, Supernews http://www.supernews.com - individual and corporate NNTP services ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Can't get the field = ANY(array) clause to work...
On Thu, 2 Feb 2006 [EMAIL PROTECTED] wrote: > The problem was fixed by initializing the array before giving it a > value. Not surprising Postges isnt as popular as it should be. I was by > luck that I found this out - the manual says nothing about init arrays. Well, I think that's in part because it got changed because it seemed like bad behavior (it looks like the array without the initialization should work in versions past 8.0.2, and it definately does on my reasonably recent checkout). ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] 8.0.3 regexp_replace()...
On Wed, 1 Feb 2006, rlee0001 wrote:
> How do IN and NOT IN treat NULLs? Don't these functions search an array
> for a specified value returning true or false? I guess the intuitive
> thing for IN and NOT IN to do would be to return NULL if NULL appears
> anywhere in the array since those elements values are "unknown".
It's IN and NOT IN (subselect) that people often get confused by, exactly
because it does return NULL which means that a row not selected by IN may
also not be selected by NOT IN.
> Personally I think treating NULL as "unknown" is rediculous. NULL is a
> value and its value is known to be NULL. I know what NULL is: NULL.
The problem is that NULL isn't a known string, numeric, time, etc value.
Until you define semantics for it, you don't really have a value. Those
semantics could be alot simpler than the SQL ones however.
> Oh yeah, my favorite is this: NULL::VARCHAR? Nope, you can't do it. Not
> without creating your own CAST. Seems to me that an obvious value would
> be 'NULL'. Or maybe '' (empty string). I hate having to use COALESCE.
> It just reaks of a bad programming practice.
I don't see how using COALESCE is particularly worse than using CAST,
honestly. CAST(NULL AS VARCHAR(n)) versus COALESCE(NULL, ) seems pretty much a wash, unless you want it to happen implicitly.
> Thats the way I see it. But if NULL has to mean "unknown" then all the
> functions should treat it as such. Also several other values including
> UNSPECIFIED and EMPTY should be provided. EMPTY should return an empty
> array {} and UNSPECIFIED should do what NULL is often used to mean
> (nothing). Then NULL should be renamed to UNKNOWN to clear up any
> confusion. :o)
Well, yes, keeping the separate uses of NULL separate would have been
nice.
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [GENERAL] PostgreSQL best practices?
On Tuesday 31 January 2006 14:09, Jim C. Nasby wrote: > At present time, your best bet is to drop in on the IRC channel and ask > questions there. You'll normally get an immediate reply, even if it's > just a URL to look at. As a beginner, I feel not knowing where to start and what to ask in IRC or mailing list (e.g. previously I didn't know that PostgreSQL has replication, until I join the mailing list for some time). And in the mailing list, I saw that there are repetitive questions asking about replication advice such as what are the current choices, difference, caveats etc. I suppose that trend is due to lack of beginner's documentation? (the answer might be in PostgreSQL documentation, but I do find it rather technical for a beginner like me to understand). Is there a FAQ or wiki that has a list of answer to beginners' questions? It might not be complete or specific answers, but at least it gives some direction to the questioner. I found Varlena.com's PostgreSQL General Bits to be quite good. However the archives are sorted by date and therefore more difficult to discover articles/tips (regarding new PostgreSQL features, for example). Regards, Leonard Soetedjo ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] C Language Stored Procedure Returning No Data
On Wed, Feb 01, 2006 at 12:56:30PM -0500, [EMAIL PROTECTED] wrote:
> From a C stored procedure, how can I tell Postgres to pass on to
> the Java client that there is No Data? A zero length byte array or
> a null value is not the same as No Data.
If you declare the function with "RETURNS bytea" then the function
must return something; if zero-length data and NULL aren't suitable
for indicating no data then you could raise an error and catch that
error in the client. Another possibility would be to make the
function set-returning ("RETURNS SETOF bytea" and some code changes)
and return no rows to indicate no data.
--
Michael Fuhr
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [GENERAL] question about MAKE_EXPIRED_TUPLES_VISIBLE
Stephan Szabo wrote: I think something like CFLAGS="-D MAKE_EXPIRED_TUPLES_VISIBLE" ./configure would be the way to get it. Thanks, I worked like a charm once I knew it was a CFLAG. I did the dump as plain text insert statements, verified the deleted records where there, then restored into a temp database, deleted all records I did not need, then dumped/restored back into the original table. Thanks to Bruce for including this in 8.1, if used properly it's a life saver :-) Tony ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Can't get the field = ANY(array) clause to work...
[EMAIL PROTECTED] wrote: The problem was fixed by initializing the array before giving it a value. Not surprising Postges isnt as popular as it should be. I was by luck that I found this out - the manual says nothing about init arrays. Instead of flippant comments like that, submit docs a patch if you feel it's necessary. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] logging settings
On Postgres 7.4.1 I've been experiencing some strangeness with the logging settings in postgresql.conf. I turned on statement logging (and duration), issued a "pg_ctl reload" and got it working (logging to sys log and standard out). Now however, when i try to turn it off, it won't turn off, using the same procedure. I've now commented out all the lines related to logging, but it's still logging. Is it possible that this is related to connection pooling? Does each connection hold these settings, such that a reload of the configuration file only affects new connections, not existing ones? Any help, much appreciated. Thanks - Mott ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] C Language Stored Procedure Returning No Data
We have a Java client that is using JDBC to communicate with the Postgres database. We have a stored procedure, foo, that is written in C which returns a byte array (bytea) to the Java client. This function takes in a couple of integers and based upon their values can return a byte array or no data. From a C stored procedure, how can I tell Postgres to pass on to the Java client that there is No Data? A zero length byte array or a null value is not the same as No Data.
Thanks for any help that you can give.
The stored procedure is defined as:
CREATE OR REPLACE FUNCTION foo(int8, int8)
RETURNS bytea AS 'foolib.so', 'foo'
LANGUAGE C STRICT;
The C function foo looks like:
PG_FUNCTION_INFO_V1(foo);
Datum foo ( PG_FUNCTION_ARGS)
{
int64 int1 = PG_GETARG_INT64(0);
int64 int2 = PG_GETARG_INT64(1);
bytea *data = "">
if ( int1 > 12345 ) {
/* Somehow indicate that there is no data */
/* Doing the following does not tell Java that there is no data; it gets a null value */
PG_RETURN_NULL();
}
/* Some processing */
PG_RETURN_BYTEA_P( data );
}
Re: [GENERAL] 8.0.3 regexp_replace()...
Stephan,
How do IN and NOT IN treat NULLs? Don't these functions search an array
for a specified value returning true or false? I guess the intuitive
thing for IN and NOT IN to do would be to return NULL if NULL appears
anywhere in the array since those elements values are "unknown".
Personally I think treating NULL as "unknown" is rediculous. NULL is a
value and its value is known to be NULL. I know what NULL is: NULL. How
many NULLs do I have here {NULL, 'hi', NULL, NULL}? NULL NULLs? No,
three NULLs. How many NULLs are in this string: 'hi'? NULL NULLs? No,
zero NULLs. How many occurances of 'yo' are in 'hey'? NULL occurances?
No, zero occurances. How many NULLs are in this paragraph? You better
count them yourself because if you ask SQL you know what it will say.
NULL. Or will it tell you? Who the hell knows!?
Oh yeah, my favorite is this: NULL::VARCHAR? Nope, you can't do it. Not
without creating your own CAST. Seems to me that an obvious value would
be 'NULL'. Or maybe '' (empty string). I hate having to use COALESCE.
It just reaks of a bad programming practice.
Thats the way I see it. But if NULL has to mean "unknown" then all the
functions should treat it as such. Also several other values including
UNSPECIFIED and EMPTY should be provided. EMPTY should return an empty
array {} and UNSPECIFIED should do what NULL is often used to mean
(nothing). Then NULL should be renamed to UNKNOWN to clear up any
confusion. :o)
But I'm not really the ruler of the world. At least not yet. But maybe
someday...
-Robert
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
[GENERAL] specifying unix domain socket name
Is it possible to specify using PQsetdbLogin() or
PQconnectdb()
the name of unix domain socket?
In documentation I read that I can specify path using host:
host
Name of the host to connect to. If this begins with
a slash ('/'), it
specifies Unix domain communication rather than TCP/IP
communication.
>>>The value is the name of the directory in which the
socket file is stored.<<<
The default is to connect to a Unix-domain socket in /tmp.
And using port I can specify socket name extension:
port
The port number to connect to at the server host,
>>>or the socket filename extension<<< for Unix-domain
connections.
How is it possible to specify full unix domain socket
path and name, not only path and extension?
Thanks
Ervin
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Equivalent of a RECORD[] data type used in a function?
Oracle keeps looking more and more appealing. $$$ vs actually being able to get stuff done? ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Can't get the field = ANY(array) clause to work...
The problem was fixed by initializing the array before giving it a
value. Not surprising Postges isnt as popular as it should be. I was by
luck that I found this out - the manual says nothing about init arrays.
DECLARE
id_var INTEGER[];
record_var RECORD;
BEGIN
id_var := '{}';
id_var[0] := 1;
id_var[1] := 2;
id_var[2] := 3;
FOR record_var IN
SELECT id FROM myTable WHERE id = ANY(id_var)
LOOP
RETURN NEXT record_var.id;
END LOOP;
RETURN;
END;
---(end of broadcast)---
TIP 6: explain analyze is your friend
[GENERAL] pgsql on win xp user permissions
I want to setup a database that will be used by my php application. Right now I installed postgresql, I used an admin account to install it on a user account. So I can only access the database (like psql dbname) when i'm logged in as the user, if i try psql from admin it says fatal role "admin name" does not exist. Is this how its supposed to be? I am creating a php application that is powered by apache. I can work from admin account because in php scripts I connect to database using a different user name and so pgsql should not wine about admin accessing it eh? ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] eqpg doesn't like bit fields
I get the following error from ecpg when it encounters a structure
containing a bit field:
../../include/ecn_types.h:317: ERROR: syntax error at or near ":"
The source it is complaining about is:
176 typedef enum{
177 tifIOC = 0,
178 tifDay = 1,
179 tifGTX = 2,
180 tifGTC = 3
181 } TIF_e;
...
316 typedef struct {
=> 317 TIF_e tif :4;
318 boolean_t isCoreOnly :1;
...
Does PostgreSQL support C-language bit fields? I tried searching the
archives, but could not find anything helpful.
Thanks,
Bob Noble
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] Primary keys for companies and people
On Feb 3, 2006, at 7:25 , Merlin Moncure wrote: There is also the problem that a name can change. People change names by deed-poll, and also women can adopt a married name or keep their old one. All in all an ID is about the only answer. I'll take the other side of this issue. The fact that a primary key is mutable does not make it any less primary. As long as we can can count on it to be unique, how often identiying info changes has no bearing on its selection as a p-key from a relational standpoint. The performance issue has zero meaning in a conceptual sense however and I think you are trying to grapple things in conceptual terms. I definitely agree with you here, Merlin. Mutability is not the issue at hand. May I ask what strategies you use for determining uniqueness for people? Michael Glaesemann grzm myrealbox com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] question about MAKE_EXPIRED_TUPLES_VISIBLE
On Thu, 2 Feb 2006, Tony Caduto wrote: > Tom Lane wrote: > > >Tony Caduto <[EMAIL PROTECTED]> writes: > > > > > >>I saw some where that if I recompiled my server with > >>MAKE_EXPIRED_TUPLES_VISIBLE I would be able to see deleted rows? > >> > >> > > > >If you aren't a certified wizard you do NOT want to turn that on, > >because it will very probably help you make things worse. My opinion > >on it is on record: > >http://archives.postgresql.org/pgsql-patches/2005-02/msg00126.php > > > Well, it does not seem to do anything, I enabled it with ./configure > --enable-MAKE_EXPIRED_TUPLES_VISIBLE > I then thought well maybe I need to do dump of the table and the deleted > tuples would be in there, but no. > > ./configure --enable-MAKE_EXPIRED_TUPLES_VISIBLE is the correct way to > enable it right? I think something like CFLAGS="-D MAKE_EXPIRED_TUPLES_VISIBLE" ./configure would be the way to get it. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Any way to extract records from the WAL?
Hi, I accidently deleted 2600 records. They are not super important but I would like to get them back. I copied the WAL file the deletes are in and I can see the text and dates from the records, they are all there. Is there a way to just extract the records without going through the whole PITR thing? It's a test server and I was not backing it up, so there is no dump available. Thanks, Tony ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Primary keys for companies and people
> > I should perhaps be posting this under another subject, but I feel that > > beneath the surface, Michael's problem and my own are strongly related. > There is also the problem that a name can change. People change names > by deed-poll, and also women can adopt a married name or keep their old > one. All in all an ID is about the only answer. I'll take the other side of this issue. The fact that a primary key is mutable does not make it any less primary. As long as we can can count on it to be unique, how often identiying info changes has no bearing on its selection as a p-key from a relational standpoint. SQL gives us RI to help deal with this but in this is not always practical if for example you you have a changing p-key that cascades to a million records. The performance issue has zero meaning in a conceptual sense however and I think you are trying to grapple things in conceptual terms. By assigning a surrogate key to a person, you are simply moving the guess work from one place to another. If you can't logically determine who 'John Smith' is, how can you possibly expect to relate information to him? (which john smith? why, etc)...you are just hiding a guess behind a number. Put into other words, *a record must have unique identifiying criteria or the table containing it cannot be expected to give correct results*. This is, more or less, a mathematical truth. The non key attributes of the tuple are now undefined because they can give two or more different answers to the same question. Surrogates do not change this principle, they just hide it but it still has to be dealt with. Merlin ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Primary keys for companies and people
On Thursday 02 February 2006 21:09, Martijn van Oosterhout wrote: >To the GP, your page is an interesting one and raises several >interesting points. In particular the one about the "person" being the >conclusion of the rest of the database. You essentially have a set of >facts "A married B in C on date D" and you're trying to correlate >these. In the end it's just a certain amount of guess work, especially >since back then they wern't that particular about spelling as they are >today. > >My naive view is that you're basically assigning trust values to each >fact and the chance that two citations refer to the same person. In >principle you'd be able to cross-reference all these citations and >build the structure quasi-automatically. I suppose in practice this is >done by hand. Yes it is. As I stated in the article, I'd like to quantify a 'participant' of an 'event' as a "vector in genealogy space", but I haven't really figured out a sensible entry mode for that evidence yet. For now, I'm trying to enter as much information as possible into the source citations. >As for your question, I think you're stuck with having a person ID. >Basically because you need to identify a person somehow. Given you >still have the original citiations, you can split a person into >multiple if the situation appears to not work out. > >One thing I find odd though, your "person" objects have no birthdate > or deathdate. Or birth place either. I've appropriated the model from my previous program, The Master Genealogist. I like the approach that the "person" entity should contain the least possible number of assertions. I've got views and functions that retrieves a primary birth and death date from the database automatically. > I would have thought these > elements would be fundamental in determining if two people are the > same, given that they can't change and people are unlikely to forget > them. Yes. But in 18th century genealogy, at least in Norway, you're unlikely to find a birth date and place in other records than the christening. As a matter of fact, the birth date wasn't usually recorded either, but as the christening usually took place within a week after birth, you've got a pretty good approximation. >Put another way, two people with the same birthday in the same place >with similar names are very likely to be the same. If you can >demostrate this is not the case that's another fact. In the end you're >dealing with probabilities, you can never know for sure. 18th century genealogy has a lot in common with crime investigation. You've basically got a few clues, and try to figure out a picture from the sparse evidence that may be found. I love that challenge, but it may be quite taxing sometimes. -- Leif Biberg Kristensen | Registered Linux User #338009 http://solumslekt.org/ | Cruising with Gentoo/KDE ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Primary keys for companies and people
- Original Message - From: "Leif B. Kristensen" <[EMAIL PROTECTED]> To: Sent: Thursday, February 02, 2006 4:07 AM Subject: Re: [GENERAL] Primary keys for companies and people [snip] I'm very interested to hear what other use in their applications for holding people and companies. I've been thinking long and hard about the same thing myself, in developing my genealogy database. For identification of people, there seems to be no realistic alternative to an arbitrary ID number. Still, I'm struggling with the basic concept of /identity/, eg. is the William Smith born to John Smith and Jane Doe in 1733, the same William [snip] I have long been interested in this issue, and it is one that transcends the problem of IDs in IT. For my second doctorate, I examined this in the context of historical investigation, applying numerical classification techniques to biographical information that can be extracted from historical documents. It is, I fear, a problem for which only a probabilistic answer can be obtained in most historical cases. For example, there was an eleventh century viking king Harold who as a teenager was part of his cousin's court, and then found it necessary to flee to Kiev when his cousin found hiimself on the losing side of a rebellion. He then made his way into the Byzantine empire and served the emperor as a mercenary through much of the mediterranean, finally returning in fame and glory to Norway where he found another relative (a nephew IIRC) on the throne, which he inherited about a year after his return. Impagine yourself as a historian trying to write his biography. You'd find various documents all over the western world (as known in the viking age) written in a variety of languages, and using different names to refer to him. It isn't an easy task to determine which documents refer specifically to him. And to make things even more interesting, many documents refer to a given person only by his official title, and in other cases, the eldest son of each generation was given the same name as his father. In my own case, in the time I was at the University of Toronto, I know of four other men who had precisely the same name I have. I know this from strange phone calls from faculty I never studied with about assignments and examinations for courses I had never taken. In each case, the professor checked again with the university's records department and found the correct student. The last case was particularly disturbing since in that case, things were a bit different in that I had taken a graduate course with the professor in question, and he stopped me on campus and asked about an assignment for a given advanced undergraduate course that I had not taken, but my namesake had. What made this disturbing is that not only did the other student carry my name, but he also looked enough like me that our professor could mistake me for him on campus! I can only hope that he is a well behaved, law abiding citizen! The total time period in question was 18 years. In general, the problem only gets more challenging as the length, and as the age, of the historical period considered increases. The point is, not only are the combinations of family and given names not reliably unique, even certain biological data, such as photographs of the human face, not adequately unique. Even DNA fingerprints, putatively the best available biometric technology, are not entirely reliable since even that can not distinguish between identical twins, and at the same time, there can be, admittedly extremely rare as far as we know, developmental anomalies resulting in a person being his own twin (this results from twin fetuses merging, with the consequence that the resulting person has some organ systems from one of the original fetuses and some from the other). For historical questions, I don't believe one can get any better than inference based on a balance of probabilities. A geneologist has no option but to become an applied statistician! For purposes of modern investigation or for the purpose of modern business, one may do better through an appropriate use of a combination of technologies. This is a hard problem, even with the use of the best available technologies and especially given the current problems associated with identity theft. For software developers in general, and database developers in particular, there are several distinct questions to consider.: 1) How does one reliably determine identity to begin with, and then use that identity with whatever technology one might use to represent it? 2) How good does this technology, and identification process need to be? In other words, how does the cost of a mistake (esp. in identification) relate to the increased cost of using better technology? In this analysis, one needs to consider both the cost of such a mistake to the person identified or misidentified, and the cost to
Re: [GENERAL] question about MAKE_EXPIRED_TUPLES_VISIBLE
Tom Lane wrote: Tony Caduto <[EMAIL PROTECTED]> writes: I saw some where that if I recompiled my server with MAKE_EXPIRED_TUPLES_VISIBLE I would be able to see deleted rows? If you aren't a certified wizard you do NOT want to turn that on, because it will very probably help you make things worse. My opinion on it is on record: http://archives.postgresql.org/pgsql-patches/2005-02/msg00126.php regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend Well, it does not seem to do anything, I enabled it with ./configure --enable-MAKE_EXPIRED_TUPLES_VISIBLE I then thought well maybe I need to do dump of the table and the deleted tuples would be in there, but no. ./configure --enable-MAKE_EXPIRED_TUPLES_VISIBLE is the correct way to enable it right? I accidentliy deleted 2600 rows that I would like to get back, so I gave MAKE_EXPIRED_TUPLES_VISIBLE a try. Thanks, Tony ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] question about MAKE_EXPIRED_TUPLES_VISIBLE
Tony Caduto <[EMAIL PROTECTED]> writes: > I saw some where that if I recompiled my server with > MAKE_EXPIRED_TUPLES_VISIBLE I would be able to see deleted rows? If you aren't a certified wizard you do NOT want to turn that on, because it will very probably help you make things worse. My opinion on it is on record: http://archives.postgresql.org/pgsql-patches/2005-02/msg00126.php regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] question about MAKE_EXPIRED_TUPLES_VISIBLE
I saw some where that if I recompiled my server with MAKE_EXPIRED_TUPLES_VISIBLE I would be able to see deleted rows? I did a ./configure --enable-MAKE_EXPIRED_TUPLES_VISIBLE It compiled ok but I dont see the deleted tuples ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Primary keys for companies and people
On Thu, Feb 02, 2006 at 10:36:54AM +, David Goodenough wrote: > > Still, I'm struggling with the basic concept of /identity/, eg. is the > > William Smith born to John Smith and Jane Doe in 1733, the same William > > Smith who marries Mary Jones in the same parish in 1758? You may never > > really know. Still, collecting such disparate "facts" under the same ID > > number, thus taking the identity more or less for granted, is the modus > > operandi of computer genealogy. Thus, one of the major objectives of > > genealogy research, the assertion of identity, becomes totally hidden > > the moment that you decide to cluster disparate evidence about what may > > actually have been totally different persons, under a single ID number. > > > > The alternative is of course to collect each cluster of evidence under a > > separate ID, but then the handling of a "person" becomes a programmer's > > nightmare. > There is also the problem that a name can change. People change names > by deed-poll, and also women can adopt a married name or keep their old > one. All in all an ID is about the only answer. True, the issue being ofcourse that changing a name doesn't change their identity. To the GP, your page is an interesting one and raises several interesting points. In particular the one about the "person" being the conclusion of the rest of the database. You essentially have a set of facts "A married B in C on date D" and you're trying to correlate these. In the end it's just a certain amount of guess work, especially since back then they wern't that particular about spelling as they are today. My naive view is that you're basically assigning trust values to each fact and the chance that two citations refer to the same person. In principle you'd be able to cross-reference all these citations and build the structure quasi-automatically. I suppose in practice this is done by hand. As for your question, I think you're stuck with having a person ID. Basically because you need to identify a person somehow. Given you still have the original citiations, you can split a person into multiple if the situation appears to not work out. One thing I find odd though, your "person" objects have no birthdate or deathdate. Or birth place either. I would have thought these elements would be fundamental in determining if two people are the same, given that they can't change and people are unlikely to forget them. Put another way, two people with the same birthday in the same place with similar names are very likely to be the same. If you can demostrate this is not the case that's another fact. In the end you're dealing with probabilities, you can never know for sure. Anyway, hope this helps. It's a subject I've been vaguely interested in but never really had the time to look into. Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them. signature.asc Description: Digital signature
Re: [GENERAL] How to find release notes
Russ Brown wrote: > Yes, and that's fantastic because it's very clear and easy to find. > However, it's for 8.1 only. I was looking for 8.1.2 specifically. > > I suppose what I'm saying is that for someone who isn't familiar with > the layout of the site it's a bit difficult to find the release notes > for any given version of PostgreSQL as things stand. I was going to say that you type in "8.1.2 release notes" in the search box, but that actually gives you the 7.1.2 release notes as first hit. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Alternative to knoda, kexi and rekall?
Michelle Konzack wrote: > Is this like the Adabase Add-on of StarOffice 5.2 ? I don't know what that is. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Alternative to knoda, kexi and rekall?
> > What about PgAdmin III, I know, it is now perfect but I prefer it a > > lot against similar commercial products. > > It is not availlable as a Debian-Package. ;-) Yes, it is. http://www.pgadmin.org/download/debian.php //Magnus ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] dependencies between objects
Toni Casueps wrote: I have got some views (for example view B) that use another views (for example view A) in the FROM clause. If I want to change view A, and it doesn't allow me to (for example adding a new column), another way would be dropping A and recreating it with the new column, but it doesn't allow me to drop view A because B depends on it, nor overwriting A with the new one because it already exists. One thing I can do is rename A to A_ , create the new A and drop A_ but it automatically reassociates B to depend on A_ , not in the new A I just created. Is there a way to disable any of these restrictions? No. If you did PG couldn't guarantee that B would carry on working. What you want to do is put your view definitions into a file with BEGIN...ROLLBACK at the start and end which drops everything necessary. Once your SQL doesn't produce any errors switch the ROLLBACK for COMMIT. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] dependencies between objects
I have got some views (for example view B) that use another views (for example view A) in the FROM clause. If I want to change view A, and it doesn't allow me to (for example adding a new column), another way would be dropping A and recreating it with the new column, but it doesn't allow me to drop view A because B depends on it, nor overwriting A with the new one because it already exists. One thing I can do is rename A to A_ , create the new A and drop A_ but it automatically reassociates B to depend on A_ , not in the new A I just created. Is there a way to disable any of these restrictions? ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Alternative to knoda, kexi and rekall?
PgAdmin III is available on Debian. Its package name is pgadmin3. Try doing: apt-cache show pgadmin3 On Wed February 1 2006 20:06, Michelle Konzack wrote: > Am 2006-01-30 10:30:42, schrieb Cristian Prieto: > > What about PgAdmin III, I know, it is now perfect but I prefer it a lot > > against similar commercial products. > > It is not availlable as a Debian-Package. ;-) > > > Really, I have long time looking for a product with the same specs. > > Me to. It would be time fo a Debian-Package... > > I will check it out, and if there is realy no one who > has packed it for Debian, I will do it. Oh yes, - I am > currently Co-Maintainer for some Packages in Debian. > > Greetings > Michelle Konzack > Systemadministrator > Tamay Dogan Network > Debian GNU/Linux Consultant -- Juan Jose Comellas ([EMAIL PROTECTED]) ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Alternative to knoda, kexi and rekall?
Am 2006-01-30 20:04:43, schrieb Peter Eisentraut: > Michelle Konzack wrote: > > Design a Database with OO2? code Functions? > > I'm not sure about what kind of function coding support you have in > mind, but certainly you can design a database with it. Is this like the Adabase Add-on of StarOffice 5.2 ? Greetings Michelle Konzack Systemadministrator Tamay Dogan Network Debian GNU/Linux Consultant -- Linux-User #280138 with the Linux Counter, http://counter.li.org/ # Debian GNU/Linux Consultant # Michelle Konzack Apt. 917 ICQ #328449886 50, rue de Soultz MSM LinuxMichi 0033/3/8845235667100 Strasbourg/France IRC #Debian (irc.icq.com) ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Alternative to knoda, kexi and rekall?
Am 2006-01-30 10:30:42, schrieb Cristian Prieto: > What about PgAdmin III, I know, it is now perfect but I prefer it a lot > against similar commercial products. It is not availlable as a Debian-Package. ;-) > Really, I have long time looking for a product with the same specs. Me to. It would be time fo a Debian-Package... I will check it out, and if there is realy no one who has packed it for Debian, I will do it. Oh yes, - I am currently Co-Maintainer for some Packages in Debian. Greetings Michelle Konzack Systemadministrator Tamay Dogan Network Debian GNU/Linux Consultant -- Linux-User #280138 with the Linux Counter, http://counter.li.org/ # Debian GNU/Linux Consultant # Michelle Konzack Apt. 917 ICQ #328449886 50, rue de Soultz MSM LinuxMichi 0033/3/8845235667100 Strasbourg/France IRC #Debian (irc.icq.com) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] How to find release notes
On Thu, 02 Feb 2006 16:15:43 + Adam Witney <[EMAIL PROTECTED]> wrote: > On 2/2/06 4:06 pm, "Russ Brown" <[EMAIL PROTECTED]> wrote: > > > Ah, there is it. Thanks! Just me not looking hard enough. > > > > However, I would argue that I shouldn't have to look so hard. I > > instinctively went for the 8.1.2 release link, and I think there > > should be a link to the release notes right there along with it. > > > > Just my opinion though. :) > > Well the "Whats new in 8.1" link is on the front page, although a > link to the Release Notes on that page (whatsnew) could be handy? > > Yes, and that's fantastic because it's very clear and easy to find. However, it's for 8.1 only. I was looking for 8.1.2 specifically. I suppose what I'm saying is that for someone who isn't familiar with the layout of the site it's a bit difficult to find the release notes for any given version of PostgreSQL as things stand. Maybe even just one release notes link somewhere on the front page to the full list would suffice? In fact, the 'Shortcuts' list of links could be the perfect place for it. -- Russ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] How to find release notes
On 2/2/06 4:06 pm, "Russ Brown" <[EMAIL PROTECTED]> wrote: > Ah, there is it. Thanks! Just me not looking hard enough. > > However, I would argue that I shouldn't have to look so hard. I > instinctively went for the 8.1.2 release link, and I think there should > be a link to the release notes right there along with it. > > Just my opinion though. :) Well the "Whats new in 8.1" link is on the front page, although a link to the Release Notes on that page (whatsnew) could be handy? -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] How to find release notes
On 2/2/06 3:56 pm, "Russ Brown" <[EMAIL PROTECTED]> wrote: > Hi, > > I'm looking at www.postgrsql.org and wondering how to find the release > notes for 8.1.2. > > I can see the link for the 8.1.2 release, but that just links to a > directory of the release tarballs. Actually, all that would be needed > here would be a text file in that directory containing the release > notes. > > My reasoning for wanting the release notes if to decide if a) I > actually need to upgrade from 8.1.1 and b) if there are any special > upgrade steps I need to perform. > > Finding release notes is one thing I've always struggled with when it > comes to PostrgreSQL. I'm sure that the information it there, it just > seems to be rather difficult to get to from the site homepage. Any > chance it could be made a little easier? They're under the documentation, in Appendix E http://www.postgresql.org/docs/8.1/static/release.html -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] How to find release notes
Hi, I'm looking at www.postgrsql.org and wondering how to find the release notes for 8.1.2. I can see the link for the 8.1.2 release, but that just links to a directory of the release tarballs. Actually, all that would be needed here would be a text file in that directory containing the release notes. My reasoning for wanting the release notes if to decide if a) I actually need to upgrade from 8.1.1 and b) if there are any special upgrade steps I need to perform. Finding release notes is one thing I've always struggled with when it comes to PostrgreSQL. I'm sure that the information it there, it just seems to be rather difficult to get to from the site homepage. Any chance it could be made a little easier? Thanks. -- Russ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Best way to handle table trigger on update
On Thu, 2006-02-02 at 10:16 -0500, Sven Willenberger wrote: > On Thu, 2006-02-02 at 08:58 -0600, Justin Pasher wrote: > > > -Original Message- > > > From: Sven Willenberger [mailto:[EMAIL PROTECTED] > > > Sent: Wednesday, February 01, 2006 2:13 PM > > > To: Justin Pasher > > > Cc: pgsql-general@postgresql.org > > > Subject: Re: [GENERAL] Best way to handle table trigger on update > > > > > > > > > On Tue, 2006-01-31 at 13:45 -0600, Justin Pasher wrote: > > > > Postgres 7.4.7 (I know, a little old, but we haven't had a chance to > > > > upgrade) > > > > > > > > I have a table that stores menu items for a side navigation > > > menu for a web > > > > site. Each menu item has a "position" column set that > > > determines where to > > > > put the menu item in the display. At any given time, the > > > menu items should > > > > not have any conflicting positions and should be > > > sequential. For example > > > > > > > > id | name| position > > > > -+---+-- > > > >1 | About Us |1 > > > >2 | History |2 > > > >3 | Support |3 > > > >4 | Job Opportunities |4 > > > >5 | Sitemap |5 > > > > > > > > ... > > > > > > > > I have an UPDATE trigger defined on the table to handle keeping the > > > > positions correct. > > > > > > > > CREATE TRIGGER "update_menu_item" BEFORE UPDATE ON > > > "menu_items" FOR EACH ROW > > > > EXECUTE PROCEDURE update_menu_item(); > > > > > > > > When I update an existing row (say ID 3) with a new > > > position (let's say 1), > > > > the trigger will bump the menu items with a lower position up by one > > > > (position 2 becomes 3, 1 becomes 2) and everything is back > > > to normal. The > > > > catch is the trigger performs this position bumping by > > > making an update on > > > > the menu items table, thus firing the trigger again for > > > each updated row > > > > (and leading to chaos). Currently, the only workaround I > > > have found is to > > > > drop the trigger at the start of the stored procedure, make > > > the updates, > > > > then recreate the trigger. > > > > > > Rather than using a trigger why not create a function to do > > > the update? > > > The following will do the trick with the only modification needed to > > > your table is the addition of the boolean column "isupdate" > > > which should > > > default to false. The two arguments taken by the function are the > > > current position of the intended menu item and its new target > > > position: > > > > > > create or replace function update_menu_item(int,int) returns void as ' > > > update menu_items set isupdate = true where position = $1; > > > update menu_items set position = case when $1 > $2 THEN > > > position +1 when > > > $2 > $1 then position - 1 else position end > > > where position <= case when $1 > $2 then $1 else $2 end and > > > position >= > > > case when $1 > $2 then $2 else $1 end and isupdate = false; > > > update menu_items set position = $2 where position = $1 and isupdate; > > > update menu_items set isupdate = false where isupdate = true; > > > ' > > > LANGUAGE sql volatile; > > > > > > Then if you want to move Job Opportunities from position 4 to position > > > 2, just call the function: > > > select update_menu_item(4,2); > > > > > > HTH, > > > > > > Sven > > > > > > This would work, but my goal is to create something that is transparent to > > the user that is inserting the data (i.e. they perform a normal > > INSERT/UPDATE on the table and "It Just Works"). Thanks for the suggestion. > > > > > > Justin Pasher > > > In that case you could create a rule: ON INSERT ... DO INSTEAD ... (and > ON UPDATE ... DO INSTEAD ...) and invoke the function that way. Barring > that, I think that dropping the trigger and re-adding it the way you > have done is about the only way to avoid all that recursion. > > Sven I just realize that this won't work as it suffers the same recursion problem that the trigger does. Interesting puzzle for which I believe you have already found the optimal solution. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Best way to handle table trigger on update
On Thu, 2006-02-02 at 08:58 -0600, Justin Pasher wrote: > > -Original Message- > > From: Sven Willenberger [mailto:[EMAIL PROTECTED] > > Sent: Wednesday, February 01, 2006 2:13 PM > > To: Justin Pasher > > Cc: pgsql-general@postgresql.org > > Subject: Re: [GENERAL] Best way to handle table trigger on update > > > > > > On Tue, 2006-01-31 at 13:45 -0600, Justin Pasher wrote: > > > Postgres 7.4.7 (I know, a little old, but we haven't had a chance to > > > upgrade) > > > > > > I have a table that stores menu items for a side navigation > > menu for a web > > > site. Each menu item has a "position" column set that > > determines where to > > > put the menu item in the display. At any given time, the > > menu items should > > > not have any conflicting positions and should be > > sequential. For example > > > > > > id | name| position > > > -+---+-- > > >1 | About Us |1 > > >2 | History |2 > > >3 | Support |3 > > >4 | Job Opportunities |4 > > >5 | Sitemap |5 > > > > > > ... > > > > > > I have an UPDATE trigger defined on the table to handle keeping the > > > positions correct. > > > > > > CREATE TRIGGER "update_menu_item" BEFORE UPDATE ON > > "menu_items" FOR EACH ROW > > > EXECUTE PROCEDURE update_menu_item(); > > > > > > When I update an existing row (say ID 3) with a new > > position (let's say 1), > > > the trigger will bump the menu items with a lower position up by one > > > (position 2 becomes 3, 1 becomes 2) and everything is back > > to normal. The > > > catch is the trigger performs this position bumping by > > making an update on > > > the menu items table, thus firing the trigger again for > > each updated row > > > (and leading to chaos). Currently, the only workaround I > > have found is to > > > drop the trigger at the start of the stored procedure, make > > the updates, > > > then recreate the trigger. > > > > Rather than using a trigger why not create a function to do > > the update? > > The following will do the trick with the only modification needed to > > your table is the addition of the boolean column "isupdate" > > which should > > default to false. The two arguments taken by the function are the > > current position of the intended menu item and its new target > > position: > > > > create or replace function update_menu_item(int,int) returns void as ' > > update menu_items set isupdate = true where position = $1; > > update menu_items set position = case when $1 > $2 THEN > > position +1 when > > $2 > $1 then position - 1 else position end > > where position <= case when $1 > $2 then $1 else $2 end and > > position >= > > case when $1 > $2 then $2 else $1 end and isupdate = false; > > update menu_items set position = $2 where position = $1 and isupdate; > > update menu_items set isupdate = false where isupdate = true; > > ' > > LANGUAGE sql volatile; > > > > Then if you want to move Job Opportunities from position 4 to position > > 2, just call the function: > > select update_menu_item(4,2); > > > > HTH, > > > > Sven > > > This would work, but my goal is to create something that is transparent to > the user that is inserting the data (i.e. they perform a normal > INSERT/UPDATE on the table and "It Just Works"). Thanks for the suggestion. > > > Justin Pasher > In that case you could create a rule: ON INSERT ... DO INSTEAD ... (and ON UPDATE ... DO INSTEAD ...) and invoke the function that way. Barring that, I think that dropping the trigger and re-adding it the way you have done is about the only way to avoid all that recursion. Sven ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Best way to handle table trigger on update
> -Original Message- > From: Sven Willenberger [mailto:[EMAIL PROTECTED] > Sent: Wednesday, February 01, 2006 2:13 PM > To: Justin Pasher > Cc: pgsql-general@postgresql.org > Subject: Re: [GENERAL] Best way to handle table trigger on update > > > On Tue, 2006-01-31 at 13:45 -0600, Justin Pasher wrote: > > Postgres 7.4.7 (I know, a little old, but we haven't had a chance to > > upgrade) > > > > I have a table that stores menu items for a side navigation > menu for a web > > site. Each menu item has a "position" column set that > determines where to > > put the menu item in the display. At any given time, the > menu items should > > not have any conflicting positions and should be > sequential. For example > > > > id | name| position > > -+---+-- > >1 | About Us |1 > >2 | History |2 > >3 | Support |3 > >4 | Job Opportunities |4 > >5 | Sitemap |5 > > > > ... > > > > I have an UPDATE trigger defined on the table to handle keeping the > > positions correct. > > > > CREATE TRIGGER "update_menu_item" BEFORE UPDATE ON > "menu_items" FOR EACH ROW > > EXECUTE PROCEDURE update_menu_item(); > > > > When I update an existing row (say ID 3) with a new > position (let's say 1), > > the trigger will bump the menu items with a lower position up by one > > (position 2 becomes 3, 1 becomes 2) and everything is back > to normal. The > > catch is the trigger performs this position bumping by > making an update on > > the menu items table, thus firing the trigger again for > each updated row > > (and leading to chaos). Currently, the only workaround I > have found is to > > drop the trigger at the start of the stored procedure, make > the updates, > > then recreate the trigger. > > Rather than using a trigger why not create a function to do > the update? > The following will do the trick with the only modification needed to > your table is the addition of the boolean column "isupdate" > which should > default to false. The two arguments taken by the function are the > current position of the intended menu item and its new target > position: > > create or replace function update_menu_item(int,int) returns void as ' > update menu_items set isupdate = true where position = $1; > update menu_items set position = case when $1 > $2 THEN > position +1 when > $2 > $1 then position - 1 else position end > where position <= case when $1 > $2 then $1 else $2 end and > position >= > case when $1 > $2 then $2 else $1 end and isupdate = false; > update menu_items set position = $2 where position = $1 and isupdate; > update menu_items set isupdate = false where isupdate = true; > ' > LANGUAGE sql volatile; > > Then if you want to move Job Opportunities from position 4 to position > 2, just call the function: > select update_menu_item(4,2); > > HTH, > > Sven This would work, but my goal is to create something that is transparent to the user that is inserting the data (i.e. they perform a normal INSERT/UPDATE on the table and "It Just Works"). Thanks for the suggestion. Justin Pasher ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] NULL values and string
Richard Huxton wrote: Sergey Karin wrote: Are there any abilities to represent NULL values as string? Null isn't a real value. Try not to think of it as a value. That being said, and with due credit elsewhere (http://www.varlena.com/varlena/GeneralBits/84.php), what I do is CREATE OR REPLACE FUNCTION public.textcat_null(text, text) RETURNS text AS 'SELECT textcat(COALESCE($1, ), COALESCE($2, ));' LANGUAGE sql' VOLATILE; CREATE OPERATOR public.||+( PROCEDURE = "public.textcat_null", LEFTARG = text, RIGHTARG = text); This goes against proper form, considering what NULL is designed for, but it sure is convenient. Regards, Berend Tober ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] plpgsql parameters
FERREIRA, William (VALTECH) wrote: Hi, i have a recursive function building a xml file, from differents table. the xml is stored in memory and then flush on disk. the xml text is used in each recursive calls and i would like to know if in plpqsql parameters are passed by reference, I don't believe so. > or if there is a key word for passing parameters by reference. No. Some of the other procedural languages allow you to have a "global" (to the connection) data space. That might be useful in your case. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Indexes again
I know understand. Thank you ;) On 2/2/06, Richard Huxton wrote: > Silas Justiniano wrote: > > Sorry, I'll remake (rewrite? redo? ... bad English :/ ) my question: > > > > I have Books, Authors and Intermediate table. At intermediate I'm > > currently using the following indexes: > > > > CREATE INDEX authorIndex ON Intermediate(author_id); > > CREATE INDEX bookIndex ON Intermediate(book_id); > > > > I need a third index to not allow duplicated data in my table: > > > > CREATE UNIQUE INDEX blablabla ON Intermediate(author_id, book_id); > > > > I'd like to know if, using the third index I can delete one of the > > first. Maybe both? > > You can probably delete authorIndex without impacting performance too much. > > -- >Richard Huxton >Archonet Ltd > -- Silas Justiniano - Brazil ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] plpgsql parameters
Hi, i have a recursive function building a xml file, from differents table. the xml is stored in memory and then flush on disk. the xml text is used in each recursive calls and i would like to know if in plpqsql parameters are passed by reference, or if there is a key word for passing parameters by reference. regards will This e-mail is intended only for the above addressee. It may contain privileged information. If you are not the addressee you must not copy, distribute, disclose or use any of the information in it. If you have received it in error please delete it and immediately notify the sender. Security Notice: all e-mail, sent to or from this address, may be accessed by someone other than the recipient, for system management and security reasons. This access is controlled under Regulation of Investigatory Powers Act 2000, Lawful Business Practises. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Indexes again
Silas Justiniano wrote: Sorry, I'll remake (rewrite? redo? ... bad English :/ ) my question: I have Books, Authors and Intermediate table. At intermediate I'm currently using the following indexes: CREATE INDEX authorIndex ON Intermediate(author_id); CREATE INDEX bookIndex ON Intermediate(book_id); I need a third index to not allow duplicated data in my table: CREATE UNIQUE INDEX blablabla ON Intermediate(author_id, book_id); I'd like to know if, using the third index I can delete one of the first. Maybe both? You can probably delete authorIndex without impacting performance too much. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Indexes again
Sorry, I'll remake (rewrite? redo? ... bad English :/ ) my question: I have Books, Authors and Intermediate table. At intermediate I'm currently using the following indexes: CREATE INDEX authorIndex ON Intermediate(author_id); CREATE INDEX bookIndex ON Intermediate(book_id); I need a third index to not allow duplicated data in my table: CREATE UNIQUE INDEX blablabla ON Intermediate(author_id, book_id); I'd like to know if, using the third index I can delete one of the first. Maybe both? Thank you! Bye! -- Silas Justiniano - Brazil ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Primary keys for companies and people
On Thursday 02 February 2006 09:07, Leif B. Kristensen wrote: > On Thursday 02 February 2006 09:05, Michael Glaesemann wrote: > >For people I'm more or less stumped. I can't think of a combination > >of things that I know I'll be able to get from people that I'll want > >to be able to add to the database. Starting off we'll have at least > >7,000 individuals in the database, and I don't think that just family > >and given names are going to be enough. I don't think we'll be able > >to get telephone numbers for all of them, and definitely aren't going > >to be getting birthdays for all. > > > >I'm very interested to hear what other use in their applications for > >holding people and companies. > > I've been thinking long and hard about the same thing myself, in > developing my genealogy database. For identification of people, there > seems to be no realistic alternative to an arbitrary ID number. > > Still, I'm struggling with the basic concept of /identity/, eg. is the > William Smith born to John Smith and Jane Doe in 1733, the same William > Smith who marries Mary Jones in the same parish in 1758? You may never > really know. Still, collecting such disparate "facts" under the same ID > number, thus taking the identity more or less for granted, is the modus > operandi of computer genealogy. Thus, one of the major objectives of > genealogy research, the assertion of identity, becomes totally hidden > the moment that you decide to cluster disparate evidence about what may > actually have been totally different persons, under a single ID number. > > The alternative is of course to collect each cluster of evidence under a > separate ID, but then the handling of a "person" becomes a programmer's > nightmare. > > I have been writing about my genealogy data model here: > http://solumslekt.org/forays/blue.php> The model has been slightly > modified since I wrote this; due to what I perceive as 'gotchas' in the > PostgreSQL implementation of table inheritance, I have dropped the > 'citations' table. Besides, I've dropped some of the surrogate keys, > and more will follow. I really should update this article soon. > > I should perhaps be posting this under another subject, but I feel that > beneath the surface, Michael's problem and my own are strongly related. There is also the problem that a name can change. People change names by deed-poll, and also women can adopt a married name or keep their old one. All in all an ID is about the only answer. David ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] NULL values and string
Sergey Karin wrote: Hi, List! I'm using PG8.1. Are there any abilities to represent NULL values as string? Null isn't a real value. Try not to think of it as a value. http://archives.postgresql.org/pgsql-sql/2003-01/msg00222.php num_value alias for $1; string_value := \'input value = \' || num_value; If I einvoke my function with NULL argument, it return NULL. But I want 'input value = NULL'. Because NULL means unknown. A string with an unknown string appended to it is itself unknown. Of course, I can check input value like this: if(num_value isnull) then string_value := \'input value = NULL\'; else string_value := \'input_value = \' || num_value; end if; But it is not laconic... Try something like: string_value := ''input_value = '' || COALESCE(num_value, 'a null'); -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] NULL values and string
Sergey Karin schrieb: Hi, List! I'm using PG8.1. Are there any abilities to represent NULL values as string? I'm doing something like this: create function func(int4) returns varchar as' declare num_value alias for $1; string_value varchar; begin string_value := \'input value = \' || num_value; return string_value; end 'language 'plpgsql'; If I einvoke my function with NULL argument, it return NULL. But I want 'input value = NULL'. Of course, I can check input value like this: if(num_value isnull) then string_value := \'input value = NULL\'; else string_value := \'input_value = \' || num_value; end if; You can use COALESCE() create function func(int4) returns text as $$ declare num_value alias for $1; begin return 'input value = ' || COALESCE(num_value,'NULL'); end $$ language 'plpgsql'; (Id rather use more descriptive name for your function) Regards Tino ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] NULL values and string
Hi, List! I'm using PG8.1. Are there any abilities to represent NULL values as string? I'm doing something like this: create function func(int4) returns varchar as' declare num_value alias for $1; string_value varchar; begin string_value := \'input value = \' || num_value; return string_value; end 'language 'plpgsql'; If I einvoke my function with NULL argument, it return NULL. But I want 'input value = NULL'. Of course, I can check input value like this: if(num_value isnull) then string_value := \'input value = NULL\'; else string_value := \'input_value = \' || num_value; end if; But it is not laconic... Sergey Karin
Re: [GENERAL] storing XML and querying
Matthew Terenzio wrote: I'm looking into methods to store XML docs for query and retrieval across all the docs or subsets. I've looked over a couple of options and may end up creating relations with the data. Are there any "Super" solutions that I might be overlooking. Also, this article claims that these functions are part of the distribution, but I don't see it in the contribs: http://www.throwingbeans.org/postgresql_and_xml_updated.html Anyone know the status here. .../contrib/xml2/ certainly seems to be there in 8.1 -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Primary keys for companies and people
On Thursday 02 February 2006 09:05, Michael Glaesemann wrote: >For people I'm more or less stumped. I can't think of a combination >of things that I know I'll be able to get from people that I'll want >to be able to add to the database. Starting off we'll have at least >7,000 individuals in the database, and I don't think that just family >and given names are going to be enough. I don't think we'll be able >to get telephone numbers for all of them, and definitely aren't going >to be getting birthdays for all. > >I'm very interested to hear what other use in their applications for >holding people and companies. I've been thinking long and hard about the same thing myself, in developing my genealogy database. For identification of people, there seems to be no realistic alternative to an arbitrary ID number. Still, I'm struggling with the basic concept of /identity/, eg. is the William Smith born to John Smith and Jane Doe in 1733, the same William Smith who marries Mary Jones in the same parish in 1758? You may never really know. Still, collecting such disparate "facts" under the same ID number, thus taking the identity more or less for granted, is the modus operandi of computer genealogy. Thus, one of the major objectives of genealogy research, the assertion of identity, becomes totally hidden the moment that you decide to cluster disparate evidence about what may actually have been totally different persons, under a single ID number. The alternative is of course to collect each cluster of evidence under a separate ID, but then the handling of a "person" becomes a programmer's nightmare. I have been writing about my genealogy data model here: http://solumslekt.org/forays/blue.php> The model has been slightly modified since I wrote this; due to what I perceive as 'gotchas' in the PostgreSQL implementation of table inheritance, I have dropped the 'citations' table. Besides, I've dropped some of the surrogate keys, and more will follow. I really should update this article soon. I should perhaps be posting this under another subject, but I feel that beneath the surface, Michael's problem and my own are strongly related. -- Leif Biberg Kristensen | Registered Linux User #338009 http://solumslekt.org/ | Cruising with Gentoo/KDE ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] Primary keys for companies and people
Hello, all! Recently there was quite a bit of discussion regarding surrogate keys and natural keys. I'm not interested in discussing the pros and cons of surrogate keys. What I'd like to find out are the different methods people actually use to uniquely identify companies and people *besides* surrogate keys. I'm currently working on an application that will include contact information, so being able to uniquely identify these two entities is of interest to me. Right now I'm thinking of uniquely identifying companies by telephone number. For example: create table companies ( company_id integer primary key -- telephone number, not serial , company_name text not null ); Of course, the company may have more than one telephone number associated with it, so there will also be a table associating telephone numbers and companies. create table companies__telephone_numbers ( company_id integer not null references companies (company_id) on update cascade on delete cascade , telephone_number integer not null , unique (company_id, telephone_number) ); There should also be a trigger that will check that the company_id matches an existing telephone number associated with the company, something like: create function assert_company_id_telephone_number_exists returns trigger language plpgsql as $$ begin if exists ( select company_id from companies except select company_id from companies join companies__telephone_numbers on (company_id = telephone_number) ) then raise exception 'company_id must match existing company telephone number'; end if; return null; end; $$; For people I'm more or less stumped. I can't think of a combination of things that I know I'll be able to get from people that I'll want to be able to add to the database. Starting off we'll have at least 7,000 individuals in the database, and I don't think that just family and given names are going to be enough. I don't think we'll be able to get telephone numbers for all of them, and definitely aren't going to be getting birthdays for all. I'm very interested to hear what other use in their applications for holding people and companies. Michael Glaesemann grzm myrealbox com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
