[SQL] Query-ing arrays
Stupid FAQ probably:
Is it possible to query an array for an item WITHOUT KNOWING where in the
array the item might be held?
For example, I have a tbale with the following character array:
> category char(3)[],
I would like to find all records in which 'category' contains an array
element of a certain type, like maybe 'xxx' for instance. So, for a
particular record, 'category' may be set to '{'gfe','qwe','xcs','xxx'}'
I cannot do:
SELECT * FROM blah WHERE category[4]='xxx';
because I do not know that the 'xxx' will always be in the 4th position.
Suggestions? TIA, Jon
--
-**-*-*---*-*---*-*---*-*-*-*---*-*---*-*-*-*-*---
Jon Lapham
Centro Nacional de Ressonancia Magnetica Nuclear de Macromoleculas
Universidade Federal do Rio de Janeiro (UFRJ) - Brasil
email: [EMAIL PROTECTED]
***-*--**---***---
Re: [SQL] Query-ing arrays
On Fri, Sep 01, 2000 at 09:07:35AM -0700, Stephan Szabo wrote: > > I'd suggest checking the array utilities in contrib. I believe > it has functions/operators for element in set. Thanks, that looks like exactly what I need. I wonder if someone could put a reference to this array utility package in the manual, chapter 8, on arrays. Maybe something like this: For more complex work with arrays, see Massimo Dal Zotto's contrib package 'array_iterator'. This loadable module "...defines a new class of functions which take an array and a scalar value, iterate a scalar operator over the elements of the array and the value, and compute a result as the logical OR or AND of the iteration results." -- -**-*-*---*-*---*-*---*-*-*-----*---*-*---*-*-*-*-*--- Jon Lapham Centro Nacional de Ressonancia Magnetica Nuclear de Macromoleculas Universidade Federal do Rio de Janeiro (UFRJ) - Brasil email: [EMAIL PROTECTED] ***-*--**---***---
[SQL] Why do I need to set UPDATE permissions for fkey check?
Hello all-
Running: Pg v7.0.2, home rolled, RedHat 6.2 linux.
I am trying to set up a read-only static lookup table, to which other
tables will reference. However, it seems I need to GRANT SELECT, UPDATE
permissions (at least) on the lookup table in order to perform foreign key
integrity checking. This seems strange to me, any ideas as to
why? After filling with data, nothing ever gets updated in this
table! Any suggestions on how I could set up a read-only lookup table
that is involved in foreign key integrity checking?
Thanks! -Jon
Here is the simplest example I could create:
###
-- Create a read-only static lookup table
CREATE TABLE lookup ( id int, value text );
INSERT INTO lookup (id, value) VALUES (1,'hello');
INSERT INTO lookup (id, value) VALUES (2,'world');
REVOKE ALL ON lookup FROM PUBLIC;
GRANT SELECT ON lookup TO PUBLIC;
-- Create the read/write dynamic work table
CREATE TABLE work ( info int references lookup (id) );
REVOKE ALL ON work FROM PUBLIC;
GRANT ALL ON work TO PUBLIC;
###
Now, if I attempt to insert something into the 'work' table:
template1=> \z
Access permissions for database "template1"
Relation | Access permissions
--+
lookup | {"=r"}
work | {"=arwR"}
template1=> INSERT INTO work (info) VALUES (1);
ERROR: lookup: Permission denied.
###
But:
template1=> GRANT UPDATE ON lookup TO PUBLIC;
CHANGE
template1=> \z
Access permissions for database "template1"
Relation | Access permissions
--+
lookup | {"=rw"}
work | {"=arwR"}
template1=> INSERT INTO work (info) VALUES (1);
INSERT 331226 1
--
-**-*-*---*-*---*-*---*-*-*-*---*-*---*-*-*-*-*---
Jon Lapham
Centro Nacional de Ressonancia Magnetica Nuclear de Macromoleculas
Universidade Federal do Rio de Janeiro (UFRJ) - Brasil
email: [EMAIL PROTECTED]
***-*--**---***---
Re: [SQL] Why do I need to set UPDATE permissions for fkey check?
On Fri, Jul 21, 2000 at 02:00:00PM -0700, Stephan Szabo wrote: > > It's a known problem in the foreign key code. The reason is that > the fk triggers use SELECT FOR UPDATE to select the matching > rows that it is checking and the reason for using FOR UPDATE is > to lock those rows so that someone cannot delete/change them out > from under your nose while you're looking at them. However, > SELECT FOR UPDATE is asking for update permissions because it > grabs that row lock. Oh, okay, I understand your explanation, and it fits with what I am seeing. But... ...this is a READ ONLY table! Maybe it would be possible to have the fkey triggers look to see if the table is read-only, and then simply use SELECT instead of SELECT FOR UPDATE and then not perform the row locking? Since this is a read-only table, there would be no risk of deleting/changing any of the data. Yeah, I realize that with this solution, you cannot guarantee that the table doesn't become 'writable' sometime during the fkey lookup. It would seem to me that this is a serious problem. I absolutely cannot have my data table be writable, and I need to maintain fkey integrity. Urg this is very bad, the fkey integrity check is the reason I installed Pg v7. I would think that keeping read-only static data table would be a common database occurance, any suggestions on how to get around this issue? Possibly with a (gulp) permissions switching trigger (gulp)? -- -**-*-*---*-*---*-*---*-----*-*-----*---*-*---*-*-*-*-*--- Jon Lapham Centro Nacional de Ressonancia Magnetica Nuclear de Macromoleculas Universidade Federal do Rio de Janeiro (UFRJ) - Brasil email: [EMAIL PROTECTED] ***-*--**---***---
[SQL] on line numbers, drop table errors, and log files
Hello- A few quick questions, I'm interested to know how people solve these issues. Thanks! (BTW: Pg 7.0.2) 1) Is there a way to find discover the line number of a script that causes an error? I mean, if I "\i script.sql" a 2000 line script, it is often difficult to track down that it was line #1211 that has a problem. What I have been doing recently (don't laugh) is to iteratively chop the script up into smaller pieces until I find the offensive command. 2) Is there a way to write a script such that it only drops a table IF it exists? Currently, I just place a "drop table blah" in front of a "create table blah" and live with the error message. I've noticed that some people make a "drop table" script and a "create table" script, is this the generally best way to do things? The problems arise in that sometimes I do not know (because things are automated) if the table exists yet or not. 3) Is it possible to have psql make a log file of a session? This would solve problem #1 above, because one could look in the log file to find the first error message from the large script. 4) I run pg_dump this way: "pg_dump -D -u -a -f backup.dump main_v0_6" in order to create a backup of my "main_v0_6" database. In order to regenerate the database, I first run a "\i create_tables.sql" script, then I run "\i backup.dump". However, I end up having to hand edit the "backup.dump" file to place "drop sequence" statements before the "create sequence" statements because they are created by my "create_tables.sql" script. I'm sure you Postgres gurus out there are not doing it this way, what is your method of database backup/recovery? TIA, Jon -- -**-*-*---*-*---*-*---*-*-*-*---*-*---*-*-*-*-*--- Jon Lapham Centro Nacional de Ressonancia Magnetica Nuclear de Macromoleculas Universidade Federal do Rio de Janeiro (UFRJ) - Brasil email: [EMAIL PROTECTED] ***-*--**---***---
Re: [SQL] on line numbers, drop table errors, and log files
On Tue, Aug 01, 2000 at 02:32:27PM -0400, Michael Richards wrote: > I would consider running: > pg_dump -D -u -c -f backup.dump > This will dump the schema and drop the objects before creating them. > -Michael > > > 4) I run pg_dump this way: "pg_dump -D -u -a -f backup.dump > > main_v0_6" in order to create a backup of my "main_v0_6" database. > > In order to regenerate the database, I first run a "\i > > create_tables.sql" script, then I run "\i backup.dump". However, > > I end up having to hand edit the "backup.dump" file to place "drop > > sequence" statements before the "create sequence" statements > > because they are created by my "create_tables.sql" script. I'm > > sure you Postgres gurus out there are not doing it this way, what > > is your method of database backup/recovery? Michael- As I mentioned earlier, your solution worked great. I am a bit puzzled about the syntax that is created by my old method, using "pg_dump -D -u -a". I wonder why it creates "create sequence ..." commands instead of "update sequence ..."? The reason I was originally using "-a" to begin with was that I wanted to seperate the data entry from the table creation step. So, the idea was that I would have a nice hand editted script to make all my tables, then the "-a" output of pg_dump would fill the database with data. As I said, your solution works, thanks, I'm just puzzled why my way didn't. Later, Jon -- -**-*-*---*-*---*-*---*-*-*-*---*-*---*-*-*-*-*--- Jon Lapham Centro Nacional de Ressonancia Magnetica Nuclear de Macromoleculas Universidade Federal do Rio de Janeiro (UFRJ) - Brasil email: [EMAIL PROTECTED] ***-*--**---***---
