Re: [GENERAL] How to create unique constraint on NULL columns
Andrus wrote: "if department _id is NULL, user has access to all departments data." This is your problem. You've assigned meaning to the "value" NULL. CREATE TABLE permission ( id serial, user_id CHAR(10) NOT NULL REFERENCES user, permission_id CHAR(10) NOT NULL REFERENCES privilege, UNIQUE (user_id, permission_id)); CREATE TABLE permission_department ( id serial, user_id CHAR(10) NOT NULL REFERENCES user, permission_id CHAR(10) NOT NULL REFERENCES privilege, department_id CHAR(10) REFERENCES department , UNIQUE (user_id, permission_id, department_id)); Any person who is authorized to access documents of a department MUST have a corresponding row in permission_department: If they are authorized to view documents of all departments, then they must have a row corresponding to every department. I don't understand why the permission_department table is required ? I didn't include this because I thought it would be obvious: You have to put a unique constraint on that table so as to eliminate the possibility of redundant departmental permission rows, as you thought was your original problem. If user is authorized to all departments, I can add separate row for each department to former permission table. So the permission_department table is not required at all Except that when abusing the meaning of NULL you can add duplicate rows indicating permission for all departments redundantly, which is what you originally misidentified as being the problem for which you sought a means to put a unique constraint on NULL values. As the first respondent said, the problem IS with the design. Unfortunately, this approach causes loss of information: it loses the fact that user is allowed to see all departments data. If new department is added, this department should be made accessible for all users which have marked as "access all departments". That information is not lost, but it is a little more work to get it: You know how many departments there are. Any user that has a count of departments equal to the number of existing departments is an "all departments" user. You can thus use aggregation to identify the "all departments" users and then add a row for them corresponding to the new department. ---(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] Return SETOF or array from pl/python
Joshua D. Drake wrote:
> Somebody may know of a better way but what I would think would happen is
> this:
>
> Break up list, insert each value of list into a temp table as a row,
> return set of temp table.
Ok. I tried this & ran in to some trouble:
CREATE OR REPLACE FUNCTION setret(text)
RETURNS SETOF record AS
$BODY$plpy.execute("""
CREATE TEMP TABLE my_temp
(
clean_text text NOT NULL
) WITHOUT OIDS
ON COMMIT DROP;
""")
for i in text.split():
plpy.execute("INSERT INTO my_temp VALUES (%s)"%i.lower())
# Do SQL stuff with my_temp - JOIN it to permanent tables, etc..
return plpy.execute("SELECT * FROM my_temp")$BODY$
LANGUAGE 'plpythonu' STABLE STRICT;
ALTER FUNCTION setret(text) OWNER TO postgres;
SELECT setret('foo BAR baz Quux');
ERROR: plpython functions cannot return type record
SELECT * FROM setret('foo BAR baz Quux');
ERROR: a column definition list is required for functions returning
"record"
After much googling, I found
http://archives.postgresql.org/pgsql-general/2005-03/msg01488.php
which indicates it can't be done. Any suggestions?
I suppose I could have my python function return an array of its
processed values and then write a plsql function that loops over it,
returning records (unless there's a builtin to do that?). I need to do
further SQL operations on the output of the python function (JOINs,
etc.). This seems ugly/slow. The whole mess eventually goes in to a
trigger, so doing it client-side isn't an option.
--
Peter Fein [EMAIL PROTECTED] 773-575-0694
Basically, if you're not a utopianist, you're a schmuck. -J. Feldman
---(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] ERROR: could not open relation
One final final question: my suspicion is no, but I just want to ask: this would not affect all inherited tables with bgwriter, would it, in scenarios where a persistent inherited table gets dropped while a parent table is being queried? Could this result in a similar scheduling conflict for bgwriter? I'm under the impression that this is mostly an issue with the implementation of temp tables and the planner, but I'd like confirmation from folks who can read the code more easily... -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC Strategic Open Source: Open Your i™ http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-260-0005 On Jul 14, 2005, at 4:44 PM, Thomas F. O'Connell wrote: From this thread, these two bits about PostgreSQL stand out: "I have an old note to myself that persistent write errors could "clog" the bgwriter, because I was worried that after an error it would stupidly try to write the same buffer again instead of trying to make progress elsewhere. (CVS tip might be better about this, I'm not sure.) A dirty buffer for a file that doesn't exist anymore would certainly qualify as a persistent failure." and "Hmm ... a SELECT from one of the "actual tables" would then scan the temp tables too, no? Thinking about this, I seem to recall that we had agreed to make the planner ignore temp tables of other backends when expanding an inheritance list --- but I don't see anything in the code implementing that, so it evidently didn't get done yet." I don't immediately see TODO items correpsonding to these. Should there be some? Or do these qualify as bugs and should they be submitted to that queue? Thanks again to all developers and community folk who lent insight into this error -- diagnosis and recovery (which was, thankfully, virtually non-existent). -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC Strategic Open Source: Open Your i™ http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-260-0005 ---(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 ---(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] Return SETOF or array from pl/python
Joshua D. Drake wrote: > You would actually have to have a set. What I believe you are trying to > do is transform a list to a result set. I don't think you can do that > without some additional programming within the function. > > Somebody may know of a better way but what I would think would happen is > this: > > Break up list, insert each value of list into a temp table as a row, > return set of temp table. Is there a way to represent a set of constant rows in SQL, aside from creating a temp table & populating it? I've had need of this before - IIRC, something was mentioned about the SQL VALUES construct being unimplemented. -- Peter Fein [EMAIL PROTECTED] 773-575-0694 Basically, if you're not a utopianist, you're a schmuck. -J. Feldman ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] how to insert '\\' in postgres database using java
On Fri, Jul 15, 2005 at 04:20:52PM +0200, Ropel wrote: > If, as the name of the column suggests, the backslash is used for > pathnames, why don't you bypass the problem by using normal slash (I.E: > "path/to/my/file")? It works well > with new windows versions and, of course, unix-style pathnames This has worked since DOS 2.0. Just not on the command line because the slash is the option indicator. But the C library and the OS always accepted forward slashes... -- 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. pgplC8ZemkfV6.pgp Description: PGP signature
Re: [GENERAL] Function returning any (tuple) type
On Fri, Jul 15, 2005 at 04:51:04PM +1000, Ezequiel Tolnay wrote: > Hi, I wonder if anyone can help me find a solution for this problem. I'm > porting a database from MS SQL Server 2000 to PostgreSQL 8.0.1 (Windows). > The main problem I see is that the funcitions in PostgreSQL seem to be > always bound to a particular result datatype. Is there a way to > circumvent this? Two solutions I can think of: - Return SETOF RECORD and specify the type on the actual query from the client - Return a cursor reference. Hope this helps, -- 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. pgp64V1xWynVY.pgp Description: PGP signature
Re: [GENERAL] How to create unique constraint on NULL columns
On Fri, 2005-07-15 at 15:16, [EMAIL PROTECTED] wrote: > [EMAIL PROTECTED] wrote on 07/15/2005 02:49:09 PM: > > > On Fri, Jul 15, 2005 at 20:08:32 +0300, > > Andrus <[EMAIL PROTECTED]> wrote: > > > > > > So I'll think still continuing to use null as unrestricted department > > > access. > > > > > > Is it reasonable to create unique constraint using > > > > > > CREATE UNIQUE INDEX user_id_permission_id_department_id_unique_idx > > > ON permission (user_id, permission_id, COALESCE(department_id,'ALL')) > > > > If you are going to do this a partial index is a better way to go. > > Something like: > > CREATE UNIQUE INDEX user_id_permission_id_null ON permission > > WHERE department_id IS NULL; > > > > However either of these let you insert and entry for "ALL" while also > > having entries for individual departments. > > That's a lot of overhead for doing something very simple, like defining a > department key that means ALL and a row in the foreign table for it to > point to. Maintaining indices is a nontrivial performance trade-off. So, does your system currently support >1 departments for those that would need it? Because if the way you're doing it now doesn't, and you have to change it to support that at some later date, that will be much more work than doing it now. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] How to create unique constraint on NULL columns
[EMAIL PROTECTED] wrote on 07/15/2005 02:49:09 PM: > On Fri, Jul 15, 2005 at 20:08:32 +0300, > Andrus <[EMAIL PROTECTED]> wrote: > > > > So I'll think still continuing to use null as unrestricted department > > access. > > > > Is it reasonable to create unique constraint using > > > > CREATE UNIQUE INDEX user_id_permission_id_department_id_unique_idx > > ON permission (user_id, permission_id, COALESCE(department_id,'ALL')) > > If you are going to do this a partial index is a better way to go. > Something like: > CREATE UNIQUE INDEX user_id_permission_id_null ON permission > WHERE department_id IS NULL; > > However either of these let you insert and entry for "ALL" while also > having entries for individual departments. That's a lot of overhead for doing something very simple, like defining a department key that means ALL and a row in the foreign table for it to point to. Maintaining indices is a nontrivial performance trade-off. > > ---(end of broadcast)--- > TIP 5: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] How to create unique constraint on NULL columns
On Fri, Jul 15, 2005 at 20:08:32 +0300, Andrus <[EMAIL PROTECTED]> wrote: > > So I'll think still continuing to use null as unrestricted department > access. > > Is it reasonable to create unique constraint using > > CREATE UNIQUE INDEX user_id_permission_id_department_id_unique_idx > ON permission (user_id, permission_id, COALESCE(department_id,'ALL')) If you are going to do this a partial index is a better way to go. Something like: CREATE UNIQUE INDEX user_id_permission_id_null ON permission WHERE department_id IS NULL; However either of these let you insert and entry for "ALL" while also having entries for individual departments. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] PostgreSQL 8.0.3
Yes I have, the SO supports IPV6. I can access my local machine using ::1 and also access the apache web server using IPV6, but I am not able to access the Postgre using a command like psql -h ::1 -d dbteste -u postgres psql -h [fe80::1] -d dbteste -u postgres Of course if I use localhost I am able to connect because localhost is mapped by the SO to a IPV4 address - Original Message - From: "Richard Huxton" To: "Solange" <[EMAIL PROTECTED]> Cc: Sent: Thursday, July 14, 2005 11:10 AM Subject: Re: [GENERAL] PostgreSQL 8.0.3 Solange wrote: As to make the connection with the data base PostgreSQL 8.0.3 being used the address localhost in Ipv6([::1 ]) ? It is possible? I think it should be possible to connect via IPv6, so long as your operating-system supports it properly. Have you found problems? PS - did you know the community has a Brazilian group and website too? http://www.postgresql.org/community/lists/ http://www.postgresql.org/community/international -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Trigger on Update
This works. Thanks for response. - Original Message - From: "mark reid" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Cc: Sent: Friday, July 15, 2005 11:16 AM Subject: Re: [GENERAL] Trigger on Update Hi, Change it to a "BEFORE UPDATE" trigger, and set NEW.updated_date := now(); Otherwise each update produces another update produces another update -Mark. [EMAIL PROTECTED] wrote: Can anybody help me creating a trigger on update trigger with update statement as below. This trigger fires after update on a table called note to update the updated_date field. But when the table is updated the trigger is firing recursively. Anybody know what is the syntax I have to use in update statement. CREATE TRIGGER "trg_update_note_updated_date" AFTER UPDATE ON "property"."note" FOR EACH ROW EXECUTE PROCEDURE "property"."update_note_updated_date_trg"(); CREATE OR REPLACE FUNCTION "property"."update_note_updated_date_trg" () RETURNS trigger AS $body$ begin /* New function body */ UPDATE property.note SET updated_date=CURRENT_DATE WHERE note_id = NEW. note_id; return null; end; $body$ LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER; ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] problem after restoring a backup database on a
Not sure, could you post a cut-n-paste of your session so we can see what you're doing? Sometimes just having someone to "look over your shoulder" makes all the difference in the world. On Fri, 2005-07-15 at 12:41, mail TechEvolution wrote: > Hi Scott > > i don't know, i do just the exact thing, with exact users and one the > other pc (where the db is not original created, + tried on 3 different > pc) it is not working. > > i can restore the database, the tables and data is there, but i cannot > use the tables > > i can connect to the database itself, but when i try to run an easy sql > statement as select * from table, i recieve: > > relation "table" does not exist > > if it had to do with the user, i wouldn't be able to restore the > database, right? > > do you think it has to do with the schema? (wich i do not know what you > mean by this, i ame a novice to PostGreSQL) if so, can you help me what > i can do to check this? > > greetZ > > wes > > Scott Ribe schreef: > > >>do you have any idea what i do wrong or what can be going wrong? > >> > >> > > > >Something to do with difference re database user or schema??? > > > > > > > > > ---(end of broadcast)--- > TIP 3: Have you checked our extensive FAQ? > >http://www.postgresql.org/docs/faq ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Trigger on Update
On 7/15/05, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: [snip] > > Anybody know what is the syntax I have to use in update statement. > > > > CREATE TRIGGER "trg_update_note_updated_date" AFTER UPDATE > ON "property"."note" FOR EACH ROW > EXECUTE PROCEDURE > "property"."update_note_updated_date_trg"(); > > > CREATE OR REPLACE FUNCTION > "property"."update_note_updated_date_trg" () RETURNS > trigger AS > $body$ > begin > /* New function body */ NEW.updated_date = NOW(); RETURN NEW; > end; > $body$ > LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER; See http://www.postgresql.org/docs/8.0/interactive/plpgsql-trigger.html -- Mike Rylander [EMAIL PROTECTED] GPLS -- PINES Development Database Developer http://open-ils.org ---(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] Trigger on Update
Anybody know what is the syntax I have to use in update statement. Try using now() instead of CURRENT_DATE. CREATE TRIGGER "trg_update_note_updated_date" AFTER UPDATE ON "property"."note" FOR EACH ROW EXECUTE PROCEDURE "property"."update_note_updated_date_trg"(); CREATE OR REPLACE FUNCTION "property"."update_note_updated_date_trg" () RETURNS trigger AS $body$ begin /* New function body */ UPDATE property.note SET updated_date=CURRENT_DATE WHERE note_id = NEW. note_id; return null; end; $body$ LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER; -- Your PostgreSQL solutions company - Command Prompt, Inc. 1.800.492.2240 PostgreSQL Replication, Consulting, Custom Programming, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Postgres for Fedora Core 2 OS ****************
Hi, On Fri, 15 Jul 2005, Dianne Yumul wrote: Here: http://www.postgresql.org/ftp/binary/v8.0.3/linux/rpms/fedora/fedora-core-2/ Also, 8.0.2+ RPMs need the following RPM: http://developer.postgresql.org/~devrim/compat-postgresql-libs-3-3PGDG.i686.rpm Regards, -- Devrim GUNDUZ devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.tdmsoft.com.tr http://www.gunduz.org ---(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
[GENERAL] Trigger on Update
Can anybody help me creating a trigger on update trigger with update statement as below. This trigger fires after update on a table called note to update the updated_date field. But when the table is updated the trigger is firing recursively. Anybody know what is the syntax I have to use in update statement. CREATE TRIGGER "trg_update_note_updated_date" AFTER UPDATE ON "property"."note" FOR EACH ROW EXECUTE PROCEDURE "property"."update_note_updated_date_trg"(); CREATE OR REPLACE FUNCTION "property"."update_note_updated_date_trg" () RETURNS trigger AS$body$begin /* New function body */ UPDATE property.note SET updated_date=CURRENT_DATE WHERE note_id = NEW. note_id; return null;end;$body$LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
Re: [GENERAL] CVS - psql segfault
On Fri, Jul 15, 2005 at 05:30:52PM +, Matt Miller wrote: > However, my problem was solved doing a "make distclean" and then > rebuilding. Does this mean that there is a dependency missing from a > makefile somewhere? Or, as a matter of policy, should I just always > clean after updating? You should be using --enable-depend in configure if you want GCC to track dependencies; they are not tracked manually in the makefiles. I do that and I very rarely have problems of that nature. -- Alvaro Herrera () "La rebeldía es la virtud original del hombre" (Arthur Schopenhauer) ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] problem after restoring a backup database on a different
Hi Scott i don't know, i do just the exact thing, with exact users and one the other pc (where the db is not original created, + tried on 3 different pc) it is not working. i can restore the database, the tables and data is there, but i cannot use the tables i can connect to the database itself, but when i try to run an easy sql statement as select * from table, i recieve: relation "table" does not exist if it had to do with the user, i wouldn't be able to restore the database, right? do you think it has to do with the schema? (wich i do not know what you mean by this, i ame a novice to PostGreSQL) if so, can you help me what i can do to check this? greetZ wes Scott Ribe schreef: do you have any idea what i do wrong or what can be going wrong? Something to do with difference re database user or schema??? ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] How to create unique constraint on NULL columns
> Then redesign this as a many to many relation. That way someone can > have access to one, two, three, four, or all departments. This means adding separate row for each department into permission table. If new department is added, I must determine in some way users which are allowed access to all departments and add nw rows to permission table automatically. It seems more reasonable to use NULL department value as "do'nt know, all departments allowed" Andrus. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] CVS - psql segfault
On Fri, 2005-07-15 at 13:22 -0400, Tom Lane wrote: > Matt Miller <[EMAIL PROTECTED]> writes: > > The CVS version of psql was segfaulting > > What are your locale settings? [EMAIL PROTECTED] ~]$ locale LANG=en_US.UTF-8 LC_CTYPE="en_US.UTF-8" LC_NUMERIC="en_US.UTF-8" LC_TIME="en_US.UTF-8" LC_COLLATE="en_US.UTF-8" LC_MONETARY="en_US.UTF-8" LC_MESSAGES="en_US.UTF-8" LC_PAPER="en_US.UTF-8" LC_NAME="en_US.UTF-8" LC_ADDRESS="en_US.UTF-8" LC_TELEPHONE="en_US.UTF-8" LC_MEASUREMENT="en_US.UTF-8" LC_IDENTIFICATION="en_US.UTF-8" LC_ALL= However, my problem was solved doing a "make distclean" and then rebuilding. Does this mean that there is a dependency missing from a makefile somewhere? Or, as a matter of policy, should I just always clean after updating? ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] CVS - psql segfault
Matt Miller <[EMAIL PROTECTED]> writes: > The CVS version of psql was segfaulting on exit yesterday, and today's > version segfaults when the program starts. Is this happening to anyone > else? The only work done recently in psql is Bruce's stuff for locale-specific numeric formatting, so I'm betting that's at fault. What are your locale settings? > My understanding is that the latest code is rarely broken. Is is not > unusual for a problem like this to persist for a couple days? Should I > even bother reporting this? If so, should I use "hackers" or a > different list? It's worth mentioning, but hackers is definitely the place; development tip issues are off-topic for -general or -bugs. regards, tom lane ---(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] PLPGSQL how to get transaction isolation level info
Janning Vygen <[EMAIL PROTECTED]> writes:
> How can a function determine in which isolation level it runs?
select current_setting('transaction_isolation');
regards, tom lane
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [GENERAL] How to create unique constraint on NULL columns
> "if department _id is NULL, user has access to all departments data." > > This is your problem. You've assigned meaning to the "value" NULL. > > CREATE TABLE permission ( > id serial, > user_id CHAR(10) NOT NULL REFERENCES user, > permission_id CHAR(10) NOT NULL REFERENCES privilege, > UNIQUE (user_id, permission_id)); > > > CREATE TABLE permission_department ( > id serial, > user_id CHAR(10) NOT NULL REFERENCES user, > permission_id CHAR(10) NOT NULL REFERENCES privilege, > department_id CHAR(10) REFERENCES department , > UNIQUE (user_id, permission_id, department_id)); > > Any person who is authorized to access documents of a department MUST have > a corresponding row in permission_department: If they are authorized to > view documents of all departments, then they must have a row corresponding > to every department. I don't understand why the permission_department table is required ? If user is authorized to all departments, I can add separate row for each department to former permission table. So the permission_department table is not required at all. Unfortunately, this approach causes loss of information: it loses the fact that user is allowed to see all departments data. If new department is added, this department should be made accessible for all users which have marked as "access all departments". So I'll think still continuing to use null as unrestricted department access. Is it reasonable to create unique constraint using CREATE UNIQUE INDEX user_id_permission_id_department_id_unique_idx ON permission (user_id, permission_id, COALESCE(department_id,'ALL')) Andrus. ---(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] Transparent encryption in PostgreSQL?
[EMAIL PROTECTED] ("Matt McNeil") writes:
> Greetings,I need to securely store lots of sensitive contact
> information andnotes in a freely available database (eg PostgreSQL
> or MySQL) that will bestored on a database server which I do not
> have direct access to. This database will be accessed by a PHP
> application that I amdeveloping. However, I also need to be able to
> search/sort these datawith the database functions (SELECT, ORDER BY,
> etc) so encrypting onthe client side (web application) or using
> encryption of specific fields would not work. (For example, I need
> to encryptcontacts' names, but need to be able to search for results
> by name). (Irealize I could load the entire table into memory with
> PHP andprocess/search/sort it there, butthat's obviously not a very
> good solution). Ideally I would like toencrypt entire tables. I
> read something about the pgcrypto contribmodule, but have't been
> able to discern if it can do ecryption in atransparent way (e.g. so
> that I can do regex searches on the data). My sense is that this is
> a difficult problem. However, I made themistake of promising this
> functionality, so I'm scrambling to figure out some kind of
> solution. Anysuggestions? Thanks so much! Matt
It seems to me that you have to step back and actually analyze the
"threat model" that you are trying to deal with.
There is a discussion in recent versions of the documentation as to
different means of encryption that are available, and the sorts of
threats that they protect against, as well as those that they do NOT
protect against.
I would suppose that if the goal is simply to say "Hey! I'm using
encryption!!!", you might accomplish this by using an encrypted
partition.
And that does not require *any* particular support from the database
system.
That seems to me like the easiest way to 'scramble' to provide
something that allows you to say, "Look ma, it's all encrypted!!!"
Of course, the main threat that this protects against is that of
someone walking away with the disk drives. That is probably not the
threat model you honestly need to worry about.
--
(format nil "[EMAIL PROTECTED]" "cbbrowne" "acm.org")
http://www.ntlug.org/~cbbrowne/sap.html
Rules of the Evil Overlord #78. "I will not tell my Legions of Terror
"And he must be taken alive!" The command will be: ``And try to take
him alive if it is reasonably practical.''"
Re: [GENERAL] Postgres for Fedora Core 2 OS ****************
oh, and for installation instructions, please check out the very fine documentation at: http://www.postgresql.org/docs/8.0/interactive/index.html : ) On Jul 15, 2005, at 7:38 AM, Dinesh Pandey wrote: From where can I download? “Postgres 8.x” + required packages and “installation instruction” of Postgres for Fedora Core 2 OS. Thanks Dinesh Pandey
Re: [GENERAL] Return SETOF or array from pl/python
I am not an everyday python programmer but I am pretty sure that you are trying to return a list in arf(). You can't return a list you have to return the array type which is why arf2 works. Ok. How does one convert a python list to a PGSql array then? Is there a better way to do it than what I did in arf2? I don't think you can with plPython at least not without reformatting the list within the function itself. The reasons srf works is because you are just returning text in general regardless that it is formatting to a list. How does one return a set then? I want as my output: You would actually have to have a set. What I believe you are trying to do is transform a list to a result set. I don't think you can do that without some additional programming within the function. Somebody may know of a better way but what I would think would happen is this: Break up list, insert each value of list into a temp table as a row, return set of temp table. Sincerely, Joshua D. Drake SELECT * FROM srf(); srf - 'one' 'two' 'three' (3 rows) -- Your PostgreSQL solutions company - Command Prompt, Inc. 1.800.492.2240 PostgreSQL Replication, Consulting, Custom Programming, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/ ---(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] Postgres for Fedora Core 2 OS ****************
Here: http://www.postgresql.org/ftp/binary/v8.0.3/linux/rpms/fedora/fedora-core-2/ If you go to www.postgresql.org, under Downloads, you'll find directions and a link to the FTP mirrors. On Jul 15, 2005, at 7:38 AM, Dinesh Pandey wrote: From where can I download? “Postgres 8.x” + required packages and “installation instruction” of Postgres for Fedora Core 2 OS. Thanks Dinesh Pandey
Re: [GENERAL] CVS - psql segfault
On Fri, 2005-07-15 at 12:27 -0400, Alvaro Herrera wrote: > On Fri, Jul 15, 2005 at 03:47:20PM +, Matt Miller wrote: > > The CVS version of psql was segfaulting > > Did you try "make distclean" before rebuilding? > I had not done "make distclean." After doing this all is well. ---(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] Return SETOF or array from pl/python
Joshua D. Drake wrote:
> Peter Fein wrote:
>
>> Is it possible to return a SETOF text or a text[] from pl/python?
>>
>> I've got the following test cases:
>>
>> CREATE OR REPLACE FUNCTION arf()
>> RETURNS text[] LANGUAGE plpythonu AS
>> $$return ["one", "two", "three"]$$;
>>
>> SELECT arf();
>>
>> ERROR: missing dimension value
>>
>> CREATE OR REPLACE FUNCTION arf2()
>> RETURNS text[] LANGUAGE plpythonu AS
>> $$return '{"one", "two", "three"}'$$;
>>
>> SELECT arf2();
>>
>>arf2
>> -
>> {one,two,three}
>> (1 row)
>>
>> CREATE OR REPLACE FUNCTION srf()
>> RETURNS SETOF text LANGUAGE plpythonu AS
>> $$return ["one", "two", "three"]$$;
>>
>> SELECT * FROM srf();
>>
>> srf
>> -
>> ['one', 'two', 'three']
>> (1 row)
>>
>> SELECT srf();
>> Never returns.
>
>
> I am not an everyday python programmer but I am pretty sure that you are
> trying to return a list in arf(). You can't return a list you have to
> return the array type which is why arf2 works.
Ok. How does one convert a python list to a PGSql array then? Is there
a better way to do it than what I did in arf2?
> The reasons srf works is because you are just returning text in general
> regardless that it is formatting to a list.
How does one return a set then? I want as my output:
SELECT * FROM srf();
srf
-
'one'
'two'
'three'
(3 rows)
--
Peter Fein [EMAIL PROTECTED] 773-575-0694
Basically, if you're not a utopianist, you're a schmuck. -J. Feldman
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [GENERAL] Return SETOF or array from pl/python
Peter Fein wrote:
Is it possible to return a SETOF text or a text[] from pl/python?
I've got the following test cases:
CREATE OR REPLACE FUNCTION arf()
RETURNS text[] LANGUAGE plpythonu AS
$$return ["one", "two", "three"]$$;
SELECT arf();
ERROR: missing dimension value
CREATE OR REPLACE FUNCTION arf2()
RETURNS text[] LANGUAGE plpythonu AS
$$return '{"one", "two", "three"}'$$;
SELECT arf2();
arf2
-
{one,two,three}
(1 row)
CREATE OR REPLACE FUNCTION srf()
RETURNS SETOF text LANGUAGE plpythonu AS
$$return ["one", "two", "three"]$$;
SELECT * FROM srf();
srf
-
['one', 'two', 'three']
(1 row)
SELECT srf();
Never returns.
I am not an everyday python programmer but I am pretty sure that you are
trying to return a list in arf(). You can't return a list you have to
return the array type which is why arf2 works.
The reasons srf works is because you are just returning text in general
regardless that it is formatting to a list.
Sincerely,
Joshua D. Drake
I can obviously use something like arf2 (manually stringifying w/i
python) but this seems ugly. I'd really prefer to return a set, rather
than an array.
--
Your PostgreSQL solutions company - Command Prompt, Inc. 1.800.492.2240
PostgreSQL Replication, Consulting, Custom Programming, 24x7 support
Managed Services, Shared and Dedicated Hosting
Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] CVS - psql segfault
On Fri, Jul 15, 2005 at 03:47:20PM +, Matt Miller wrote: > The CVS version of psql was segfaulting on exit yesterday, and today's > version segfaults when the program starts. Is this happening to anyone > else? Did you try "make distclean" before rebuilding? -- Alvaro Herrera () Si no sabes adonde vas, es muy probable que acabes en otra parte. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] Return SETOF or array from pl/python
Is it possible to return a SETOF text or a text[] from pl/python?
I've got the following test cases:
CREATE OR REPLACE FUNCTION arf()
RETURNS text[] LANGUAGE plpythonu AS
$$return ["one", "two", "three"]$$;
SELECT arf();
ERROR: missing dimension value
CREATE OR REPLACE FUNCTION arf2()
RETURNS text[] LANGUAGE plpythonu AS
$$return '{"one", "two", "three"}'$$;
SELECT arf2();
arf2
-
{one,two,three}
(1 row)
CREATE OR REPLACE FUNCTION srf()
RETURNS SETOF text LANGUAGE plpythonu AS
$$return ["one", "two", "three"]$$;
SELECT * FROM srf();
srf
-
['one', 'two', 'three']
(1 row)
SELECT srf();
Never returns.
I can obviously use something like arf2 (manually stringifying w/i
python) but this seems ugly. I'd really prefer to return a set, rather
than an array.
--
Peter Fein [EMAIL PROTECTED] 773-575-0694
Basically, if you're not a utopianist, you're a schmuck. -J. Feldman
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [GENERAL] How to create unique constraint on NULL columns
On Fri, 2005-07-15 at 10:51, Andrus wrote: > >> I have a table of users permissions by departments > >> > >> CREATE TABLE permission ( > >> id serial, > >> user_id CHAR(10) NOT NULL REFERENCES user, > >> permission_id CHAR(10) NOT NULL REFERENCES privilege, > >> department_id CHAR(10) REFERENCES department , > >> UNIQUE ( user_id, permission_id, department_id ) ) > > >> if department _id is NULL, user has access to all departments data. > > >> How I should rethink this data design to be implemented in CREATE TABLE > >> statement ? > > > You should use a value like 'ALL' to denote that they have access to all > > departments. Null, in this case, would mean you don't know which > > departments they have access to, and this is not really the case. > > Using department ALL breaks the department_id reference to department table. > Inserting department_id ALL is rejected since there is no such department. > > So I can use department ALL ? Then redesign this as a many to many relation. That way someone can have access to one, two, three, four, or all departments. ---(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 create unique constraint on NULL columns
Andrus wrote: How to create constraint so that NULL values are treated equal and second insert is rejected ? Rethink your data design --- this behavior is required by the SQL standard. I have a table of users permissions by departments CREATE TABLE permission ( id serial, user_id CHAR(10) NOT NULL REFERENCES user, permission_id CHAR(10) NOT NULL REFERENCES privilege, department_id CHAR(10) REFERENCES department , UNIQUE ( user_id, permission_id, department_id ) ) permission_id is a permission name: Invoice, Waybill etc. department _id is a code of department whose documents user is authorized to access. if department _id is NULL, user has access to all departments data. By this design it is meaningless to have two records with same user_id and permission_id both having department_id NULL So I want that Postgres does not allow to insert them. How I should rethink this data design to be implemented in CREATE TABLE statement ? "if department _id is NULL, user has access to all departments data." This is your problem. You've assigned meaning to the "value" NULL. CREATE TABLE permission ( id serial, user_id CHAR(10) NOT NULL REFERENCES user, permission_id CHAR(10) NOT NULL REFERENCES privilege, UNIQUE (user_id, permission_id)); CREATE TABLE permission_department ( id serial, user_id CHAR(10) NOT NULL REFERENCES user, permission_id CHAR(10) NOT NULL REFERENCES privilege, department_id CHAR(10) REFERENCES department , UNIQUE (user_id, permission_id, department_id)); Any person who is authorized to access documents of a department MUST have a corresponding row in permission_department: If they are authorized to view documents of all departments, then they must have a row corresponding to every department. ---(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] how to insert '\\' in postgres database using java
Ketan, Please reply to the list as well so others have the opportunity to help you. And please don't top-post. On Jul 16, 2005, at 12:31 AM, ketan shah wrote: but if i update more then two times then it remove all '\'; and finally it returns 'A' or 'B'... But i want 'A\\d\\d\\d\\d' and 'B\\d\\d\\d\\d' after n number of updates. i.e. no change in inserted value after n numbers of updates for that record. If you don't change change the values of the usr_filename_pattern in the update (in the SET clause), those values should not change. test=# select * from tab1; usr_id | usr_name | usr_filename_pattern +--+-- A | Mr. A| A\d\d\d\d A | Mr. B| A\\d\\d\\d\\d (2 rows) test=# update tab1 set usr_name = 'Mr. C' where usr_id = 'A'; UPDATE 2 test=# select * from tab1; usr_id | usr_name | usr_filename_pattern +--+-- A | Mr. C| A\d\d\d\d A | Mr. C| A\\d\\d\\d\\d I suspect there is some additional SQL code being executed by your Java application. You may want to enable query logging and check the PostgreSQL logs to see exactly what queries are being executed. Michael Glaesemann grzm myrealbox com ---(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] How to create unique constraint on NULL columns
>> I have a table of users permissions by departments >> >> CREATE TABLE permission ( >> id serial, >> user_id CHAR(10) NOT NULL REFERENCES user, >> permission_id CHAR(10) NOT NULL REFERENCES privilege, >> department_id CHAR(10) REFERENCES department , >> UNIQUE ( user_id, permission_id, department_id ) ) >> if department _id is NULL, user has access to all departments data. >> How I should rethink this data design to be implemented in CREATE TABLE >> statement ? > You should use a value like 'ALL' to denote that they have access to all > departments. Null, in this case, would mean you don't know which > departments they have access to, and this is not really the case. Using department ALL breaks the department_id reference to department table. Inserting department_id ALL is rejected since there is no such department. So I can use department ALL ? Andrus. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] CVS - psql segfault
The CVS version of psql was segfaulting on exit yesterday, and today's version segfaults when the program starts. Is this happening to anyone else? I'm on Red Hat ES4. My understanding is that the latest code is rarely broken. Is is not unusual for a problem like this to persist for a couple days? Should I even bother reporting this? If so, should I use "hackers" or a different list? ---(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 create unique constraint on NULL columns
On Fri, 2005-07-15 at 10:26, Andrus wrote: > >> How to create constraint so that NULL values are treated equal and second > >> insert is rejected ? > > > > Rethink your data design --- this behavior is required by the SQL > > standard. > > I have a table of users permissions by departments > > CREATE TABLE permission ( > id serial, > user_id CHAR(10) NOT NULL REFERENCES user, > permission_id CHAR(10) NOT NULL REFERENCES privilege, > department_id CHAR(10) REFERENCES department , > UNIQUE ( user_id, permission_id, department_id ) ) > > permission_id is a permission name: Invoice, Waybill etc. > > department _id is a code of department whose documents user is authorized to > access. > > if department _id is NULL, user has access to all departments data. > > By this design it is meaningless to have two records with same user_id and > permission_id both having department_id NULL > > So I want that Postgres does not allow to insert them. > > How I should rethink this data design to be implemented in CREATE TABLE > statement ? You should use a value like 'ALL' to denote that they have access to all departments. Null, in this case, would mean you don't know which departments they have access to, and this is not really the case. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Looking for a good ERD Tool
On 07.07.2005 06:23, Rob Brenart wrote: Anyway, would like to find a similar tool specifically designed for PostgreSQL... does one exist or is it a futile search? I tested almost anything that is out there over the years.. http://www.casestudio.com Works perfect with PG8, fully script- and extendable, great support and almost bugfree, but it's Win32 only. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] PLPGSQL how to get transaction isolation level info
i have a function which calculates some aggregates (like a materialized view). As my aggregation is made with a temp table and 5 SQL Queries, i need a consistent view of the database. Therefor i need transaction isolation level SERIALIZABLE, right? Otherwise the second query inside of the function could read data which differs from the first query (Nonrepeatable Read or Phantom Read) ok. so far so good. But know i would like my function to abort if it is not running inside ransaction isolation level SERIALIZABLE. How can a function determine in which isolation level it runs? I looked at the SHOW statement but didn't find anything. i dont wnat to know the default_transaction_isolation but the current one used. The reason is mainly for preventing some mistakes inside the caller app. Of course the app should know what it does and wrap the function call inside a serializable transaction, but to be sure that my materialized view is consistent with the rest of the data i would like to enforce it. Is it possible to get info about the current transaction isolation level? kind regards, janning ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] How to create unique constraint on NULL columns
>> How to create constraint so that NULL values are treated equal and second >> insert is rejected ? > > Rethink your data design --- this behavior is required by the SQL > standard. I have a table of users permissions by departments CREATE TABLE permission ( id serial, user_id CHAR(10) NOT NULL REFERENCES user, permission_id CHAR(10) NOT NULL REFERENCES privilege, department_id CHAR(10) REFERENCES department , UNIQUE ( user_id, permission_id, department_id ) ) permission_id is a permission name: Invoice, Waybill etc. department _id is a code of department whose documents user is authorized to access. if department _id is NULL, user has access to all departments data. By this design it is meaningless to have two records with same user_id and permission_id both having department_id NULL So I want that Postgres does not allow to insert them. How I should rethink this data design to be implemented in CREATE TABLE statement ? Andrus. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Transparent encryption in PostgreSQL?
The point of a data base is storing ASCII or unicode not encypting the data... encrypting the data IN the database is a bad idea what happens if you ever lose the key ??? you lose ALL your data... Additionally -- encryption keys are usually machine-dependent so you lose the ability to migrate to new hardware and possibly the ability to upgrade the RDBMS engine itself... It sounds to me like your issues are really about security and access control You'd be better off using an ACL and locking down your server... ""Matt McNeil"" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED]... Greetings, I need to securely store lots of sensitive contact information andnotes in a freely available database (eg PostgreSQL or MySQL) that will bestored on a database server which I do not have direct access to. This database will be accessed by a PHP application that I amdeveloping. However, I also need to be able to search/sort these datawith the database functions (SELECT, ORDER BY, etc) so encrypting on the client side (web application) or using encryption of specific fields would not work. (For example, I need to encryptcontacts' names, but need to be able to search for results by name). (Irealize I could load the entire table into memory with PHP andprocess/search/sort it there, butthat's obviously not a very good solution). Ideally I would like toencrypt entire tables. I read something about the pgcrypto contrib module, but have't been able to discern if it can do ecryption in a transparent way (e.g. so that I can do regex searches on the data). My sense is that this is a difficult problem. However, I made themistake of promising this functionality, so I'm scrambling to figure out some kind of solution. Anysuggestions? Thanks so much! Matt
Re: [GENERAL] How to obtain the list of data table name only
Here is the definition of a view I use to retrieve all of the tables and a list of columns for tables that appear in the "public" schema ... I have others that only retrieve a list of the table names and the views in the public schema as well... -- View: "vcat_pgcolumns" -- DROP VIEW vcat_pgcolumns; CREATE OR REPLACE VIEW vcat_pgcolumns AS SELECT psut.relid AS tbloid, pa.attnum AS colid, psut.relname AS tblname, pa.attname AS colname FROM pg_stat_user_tables psut JOIN pg_attribute pa ON psut.relid = pa.attrelid WHERE psut.schemaname = 'public'::name AND pa.attstattarget = -1 ORDER BY psut.relname, pa.attnum; ALTER TABLE vcat_pgcolumns OWNER TO postgres; "Sunny" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > Hi, > > I am wondering if anyone can tell me how I can obtain only the list of > data table in postgresql without function and other ancillary tables. I > hope that I can add a tag that can filter only data table. > > I am using the following SQL Statement: > > "SELECT TABLE_NAME, TABLE_TYPE FROM INFORMATION_SCHEMA.TABLES" > > I would appreciate if anyone can enligten me on how this can be > achieve. > > Sunny > ---(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 insert '\\' in postgres database using java
Hi Ketan,
On Jul 15, 2005, at 10:49 PM, ketan shah wrote:
My question :
After updation how i get
'A', 'Mr. B', 'A\\d\\d\\d\\d'
i.e. not escapeing '\\'.
I am using postgres 7.4.6 and java 1.4.
pl. help me out...
As you've noticed, the \ character is currently used in PostgreSQL as
an escape, so '\\' is recognized as one \. So if you want to insert \
\, you need to escape both \; i.e., ''
test=# select '\\';
?column?
--
\
(1 row)
test=# select '';
?column?
--
\\
(1 row)
test=# create table tab1 (
usr_id varchar(15)
, usr_name varchar(20)
,usr_filename_pattern varchar(1024)
);
CREATE TABLE
test=# insert into tab1 (usr_id, usr_name, usr_filename_pattern)
values ('A','Mr. A','A\\d\\d\\d\\d');
INSERT 82771 1
test=# insert into tab1 (usr_id, usr_name, usr_filename_pattern)
values ('A','Mr. B','Adddd');
INSERT 82772 1
test=# select * from tab1;
usr_id | usr_name | usr_filename_pattern
+--+--
A | Mr. A| A\d\d\d\d
A | Mr. B| A\\d\\d\\d\\d
(2 rows)
Is this what you're looking for?
As an aside, I find it very helpful to name the columns when I'm
inserting them. (INSERT INTO foo (bar, baz, bat) VALUES ... instead
of INSERT INTO foo VALUES ... )Then I don't have to remember exactly
which column order I used when creating the table.
Hope this helps.
Michael Glaesemann
grzm myrealbox com
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
[GENERAL] Postgres for Fedora Core 2 OS ****************
From where can I download? “Postgres 8.x” + required packages and “installation instruction” of Postgres for Fedora Core 2 OS. Thanks Dinesh Pandey
Re: [GENERAL] Case insensitive unique constraint
On Jul 14, 2005, at 3:14 AM, Rob Brenart wrote: I have a simple table to store account names... I want each name to be unique in a case insensitive manner... but I want the case the user enters to be remembered so I can't do a simple lower() on the data's way in. Is there an easy way to go about this? Am I about to write my first server side function for postgresql? create your unique index using the lower() function. and don't steal threads to start a new one (ie, replying then just changing the subject) Vivek Khera, Ph.D. +1-301-869-4449 x806 ---(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 create unique constraint on NULL columns
"Andrus" <[EMAIL PROTECTED]> writes: > CREATE TABLE test( col1 CHAR NOT NULL, col2 CHAR, > UNIQUE (col1, col2) ); > This table allows to insert duplicate rows if col2 is NULL: > INSERT INTO test VALUES ( '1', NULL ); > INSERT INTO test VALUES ( '1', NULL ); > does NOT cause error! > How to create constraint so that NULL values are treated equal and second > insert is rejected ? Rethink your data design --- this behavior is required by the SQL standard. A unique constraint is defined in terms of a "unique predicate", which is defined as 2) If there are no two rows in T such that the value of each column in one row is non-null and is equal to the value of the cor- responding column in the other row according to Subclause 8.2, "", then the result of the is true; otherwise, the result of the is false. (SQL92 8.9 general rule 2) In general NULL should be used to mean "I don't know the value of this field", not as a special value. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] how to insert '\\' in postgres database using java
If, as the name of the column suggests, the backslash is used for
pathnames, why don't you bypass the problem by using normal slash (I.E:
"path/to/my/file")? It works well
with new windows versions and, of course, unix-style pathnames
Hope this helps, Roberto
ketan shah wrote:
Hi,
All,
My name is ketan, i have problem in postgres db insert..
Here is my problem.
i have created table like..
1) Create table tab1(usr_id varchar(15), usr_name
varchar(20),usr_filename_pattern varchar(1024));
insert table tab1 values('A','Mr. A','A\\d\\d\\d\\d');
Record is successfully inserted But when i retrieve using java
program..
select * from tab1;
The output is 'A', 'Mr. A', 'A\d\d\d\d'
(means it escapeing '\')
Also when i update the record
update tab1 set usr_name='Mr. B' where usr_id='A';
and after succefully updation when i try to retrieve record by
select * from tab1;
output is 'A','Mr. B', 'A'; (means it escapeing '\' again)
My question :
After updation how i get
'A', 'Mr. B', 'A\\d\\d\\d\\d'
i.e. not escapeing '\\'.
I am using postgres 7.4.6 and java 1.4.
pl. help me out...
Thanks in advanced..
ketan
__
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Function returning any (tuple) type
Hi, I wonder if anyone can help me find a solution for this problem. I'm
porting a database from MS SQL Server 2000 to PostgreSQL 8.0.1 (Windows).
We have an web interface that accesses the database, but doesn't have
direct access on any tables, only to execute a number of stored procedures.
Most of the stored procedures that return a large number of records
(mainly for reporting) store the results on transient tables created on
a separate database, naming the table as ResultsN where N is an
identifier that the stored procedure returns. All these created
transient tables include an indexed RowNumber column, with an
autoincremental value.
The returned identifier is later used by the web-front-end calling a
stored procedure which is the one I need help with. The stored procedure
returns a page of data for a requested transient table. Its code is
something like this:
CREATE PROCEDURE GetReportPage(@TableID int, @PageNo int) AS
EXECUTE('SELECT * FROM Results'+CONVERT(varchar, @TableID)+
'WHERE RowNumber >= '+CONVERT(varchar, @PageNo * 50)+
' AND RowNumber < '+CONVERT(varchar, (@PageNo+1) * 50))
The stored procedure is actually quite a lot more complex, because it
includes many other features, but I would like to achieve something
similar to this in PostgreSQL. This is very convenient because it allows
to see paged reports, sort them in different ways quickly, and even
export them later to CSV.
The main problem I see is that the funcitions in PostgreSQL seem to be
always bound to a particular result datatype. Is there a way to
circumvent this?
I've tried to solve this with arrays of text, but this is very
inconvenient and limiting. I've also tried with arrays of ROW and
RECORD, but it didn't work.
Are there any plugins or any way to allow functions to return arbitrary
row types? What about plans to include stored procedures in PGSQL in a
near future?
I hope I was clear enough, and the example in TransactSQL simple to
understand for non-MSSQL witty dbadmins. Please pg-wizards, lend me a
hand with this!
Cheers!
Ezequiel Tolnay
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
[GENERAL] problem after restoring a backup database on a different PC
Hi I think I might be doing something verry wrong, since i ame not being able to transfer a database from one pc to another. so, what I do is, I dump the database as follows: pg_dump -f "c:\file.backup" -F c -b -x -h localhost -U user -W "MyDB" (or i tried it also using the backup button in pgAdmin III) wich works fine, and when i restore the db again on my other PC there is no problem, ALL tables are there, BUT when i try to access a table trough pgsqlxx (c++ library) i get an exception: relation "table" does not exist So, i tried to restore the file.backup on the same pc as it was backuped from, and here i find no problem to access the table do you have any idea what i do wrong or what can be going wrong? greetZ wes ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Case insensitive unique constraint
create unique index tbl_iname_idx on tbl (lower(name_field))
By the way, in case it wasn't obvious, this has a nice side-benefit.
Namely, PG will use that index for caseless lookups, so you can do
this:
select * from tbl where lower(name_field) = lower('John');
very efficiently.
- John D. Burger
MITRE
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
[GENERAL] how to insert '\\' in postgres database using java
Hi,
All,
My name is ketan, i have problem in postgres db insert..
Here is my problem.i have created table like..1) Create table tab1(usr_id varchar(15), usr_name varchar(20),usr_filename_pattern varchar(1024)); insert table tab1 values('A','Mr. A','A\\d\\d\\d\\d'); Record is successfully inserted But when i retrieve using java program.. select * from tab1; The output is 'A', 'Mr. A', 'A\d\d\d\d' (means it escapeing '\') Also when i update the record update tab1 set usr_name='Mr. B' where usr_id='A'; and after succefully updation when i try to retrieve record by select * from tab1; output is 'A','Mr. B', 'A'
; (means
it escapeing '\' again) My question :
After updation how i get 'A', 'Mr. B', 'A\\d\\d\\d\\d' i.e. not escapeing '\\'. I am using postgres 7.4.6 and java 1.4.
pl. help me out... Thanks in advanced..
ketan__Do You Yahoo!?Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
Re: [GENERAL] How to create unique constraint on NULL columns
On 7/15/05, Andrus <[EMAIL PROTECTED]> wrote: > CREATE TABLE test( col1 CHAR NOT NULL, col2 CHAR, > UNIQUE (col1, col2) ); > INSERT INTO test VALUES ( '1', NULL ); > INSERT INTO test VALUES ( '1', NULL ); > does NOT cause error! > > How to create constraint so that NULL values are treated equal and second > insert is rejected ? Please read: http://www.postgresql.org/docs/8.0/interactive/indexes-unique.html ...or this list archives. In short: NULL is not equal to NULL. NULL is a state, not a value. Regards, Dawid ---(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
[GENERAL] uncompressing pgAdmin backup file in windows
I created a backup of Postgres database using pgAdmin II in Windows by default options with (*) COMPRESS radio button checked. How to unpack the created compressed file in windows manually ? ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Nulls in timestamps
On Wed, Jul 13, 2005 at 18:15:12 +, [EMAIL PROTECTED] wrote: > Many thanks Tom. Inconvenient from the point of view of the application but > still useful information. > > The situation is that I've got a query with numerous subselects, each of which > has to return exactly one row so I was doing a union with a nulled record then > selecting the most recent: obviously I need to see bona-fide data if it's > there. you can order by datecol is null, datecol desc to get the most recent non null date. For example: area=> select day from (select 'today'::date as day union select 'tomorrow'::date as day union select null as day) as un order by day is null, day desc; day 2005-07-16 2005-07-15 (3 rows) ---(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] re my previous e-mail client-server example
On Wed, Jul 13, 2005 at 17:56:44 +0100, John Tulodziecki <[EMAIL PROTECTED]> wrote: > Bizzarly its now working after I added the server ip address in addition to > the client ip address in the listen addresses config line !!! That isn't bizzare. The listen address is what address the server should be listening on. Note that computers have multiple IP addresses and this allows you to have the server only listen on one of them. If you want to listen on all of them, you want to use 0.0.0.0 for the address. What addresses are allowed for clients is controlled by the pg_hba.conf file. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] What's Popular for CMS and RAD with PHP/PostgreSQL?
Hi Kevin, On Jul 15, 2005, at 6:57 AM, Kevin Murphy wrote: I too like drupal and use it with postgresql, but some of the Drupal contrib module authors are still very mysql-centric and fond of writing code that breaks when using postgresql (or doesn't support it at all). Luckily, it's usually not too hard to patch yourself or find a patch. A lot of highly desirable functionality does not exist in the Drupal core but instead is provided by these contributed modules. Drupal still recommends mysql and doesn't treat postgresql and mysql equally. It would be nice if Drupal would keep track of which modules had been tested against postgresql, but they don't. If there were a pool of drupal/ postgresql users willing to be testers, and module authors were aware of this, that would help also. I've been too busy to dive in and suggest/coordinate this, though. Yes, I have noticed that MySQL gets better support. Hopefully more PostgreSQL users will join in to improve the situation. But as you say, it is generally very simple to update the table definitions for contributed modules that don't have PostgreSQL support. And all of the core modules have PostgreSQL definitions. John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL ---(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] What's Popular for CMS and RAD with PHP/PostgreSQL?
John DeSoi wrote: CMS and RAD tools out there? Using PHP and PostgreSQL only, what do you feel are the most popular Drupal is a very nice CMS for PHP and PostgreSQL. I'm using it for some consulting projects now and it has saved me a great deal of time. Some of the useful features include: I too like drupal and use it with postgresql, but some of the Drupal contrib module authors are still very mysql-centric and fond of writing code that breaks when using postgresql (or doesn't support it at all). Luckily, it's usually not too hard to patch yourself or find a patch. A lot of highly desirable functionality does not exist in the Drupal core but instead is provided by these contributed modules. Drupal still recommends mysql and doesn't treat postgresql and mysql equally. It would be nice if Drupal would keep track of which modules had been tested against postgresql, but they don't. If there were a pool of drupal/postgresql users willing to be testers, and module authors were aware of this, that would help also. I've been too busy to dive in and suggest/coordinate this, though. -Kevin Murphy ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] How to create unique constraint on NULL columns
I have table CREATE TABLE test( col1 CHAR NOT NULL, col2 CHAR, UNIQUE (col1, col2) ); This table allows to insert duplicate rows if col2 is NULL: INSERT INTO test VALUES ( '1', NULL ); INSERT INTO test VALUES ( '1', NULL ); does NOT cause error! How to create constraint so that NULL values are treated equal and second insert is rejected ? Andrus. ---(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] Function returning any (tuple) type
Hannes Dorbath wrote: On 15.07.2005 08:51, Ezequiel Tolnay wrote: The main problem I see is that the funcitions in PostgreSQL seem to be always bound to a particular result datatype. Is there a way to circumvent this? I tried to find a solution for this as well some time ago. I don't believe there is a practical way. It's only a guess, maybe this "strong typing" helps the planer / optimizer in some way.. If that's not what you want, you can always return text (structure it how you like) or a cursor-reference. -- Richard Huxton Archonet Ltd ---(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] Function returning any (tuple) type
On 15.07.2005 08:51, Ezequiel Tolnay wrote: The main problem I see is that the funcitions in PostgreSQL seem to be always bound to a particular result datatype. Is there a way to circumvent this? I tried to find a solution for this as well some time ago. I don't believe there is a practical way. It's only a guess, maybe this "strong typing" helps the planer / optimizer in some way.. ---(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] Asynchronous connection and command processing.
Hi there, Please can someone point me to example code for the following … Asynchronous connection - PQconnectStart - PQconnectPoll Asynchronous Command Processing - PQsendQuery - PQgetResult - PQconsumeInput Thankyou. John Tulodziecki Senior Software Engineer Squire Technologies Ltd Phone +44(0)1305 757315 Web www.squire-technologies.com Email [EMAIL PROTECTED] NOTICE AND DISCLAIMER: Any views expressed in this message are those of the individual sender, except where the sender specifically states them to be the views of Squire Technologies Ltd. If you have received this email in error please notify the sender immediately and delete this email from your system without copying or disseminating it or placing any reliance upon its contents. -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.323 / Virus Database: 267.8.15/49 - Release Date: 14/07/2005
