[SQL] Comparing arrays
Hello,
I advance in my postgres exploration and found something that
looks quite strange, or at least unexpected regarding the present
7.3.3 documentation.
In two table I store "objects" and their attributes. The
attributes, which are not meant to be searched, are stored as unbound
arrays of varchars. I have a query that needs to use those attributes
on both sides of an EXCEPT statement:
SELECT left.id, left.attribs FROM left
EXCEPT SELECT right.id, right.attribs FROM right;
That query can't be planed because of the following error: "Unable to
identify an ordering operator '<' for type 'character varying[]'".
I thought that I could build such an operator using PL/pgSQL,
unfortunately this language can't receive arguments of type ANYARRAY.
So this led me to the creation of a new ATTRIBUTES data type, the
should be acceptable as an argument to a PL/pgSQL procedure.
When I tried to create such a datatype, using a query modelled
after the documentation examples:
CREATE TYPE attributes (INPUT=array_in, OUTPUT=array_out,
INTERNALLENGTH=VARIABLE, ELEMENT=VARCHAR);
I'm signaled that the array_out procedure is not defined: "ERROR:
TypeCreate: function array_out(attributes) does not exist". That error
sounds strange as the CREATE TYPE manual describes uniform array type
creation as illustrated above and that array_out() seems to exist as
shown bellow.
SELECT proname, oidvectortypes(proargtypes) FROM pg_proc
WHERE proname LIKE 'array_%';
proname | oidvectortypes
-+
array_dims | anyarray
array_eq| anyarray, anyarray
array_in| cstring, oid, integer
array_length_coerce | anyarray, integer, boolean
array_out | anyarray
(5 rows)
All of this leads to the unavoidable questions:
1/ What went wrong with the ATTRIBUTES datatype creation?
How to correctly create it using 7.3.x backends?
2/ There may be better paths than creating a new datatype and
the associated operators that would permit using unbound
uniform arrays on both sides of an EXCEPT statement.
What would be such paths?
Regards.
--
%!PS
297.6 420.9 translate 90 rotate 0 setgray gsave 0 1 1{pop 0 180 moveto 100
180 170 100 170 -10 curveto 180 -9 180 -9 190 -10 curveto 190 100 100 180
0 180 curveto fill 180 rotate}for grestore/Bookman-LightItalic findfont
240 scalefont setfont -151.536392 -63.7998886 moveto (bp)show showpage
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] duplicate dates
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On Friday 01 August 2003 08:56, Jodi Kanter wrote:
> I have one table that has a date/time field in it. I'd like to identify
> the records in the database where the date/time fields are the same. How
> can I do this? Do I need to create a view or temp table? Is there a way
> to run through one table multiple times.
SELECT date_trunc('month', date_column), ...
GROUP BY date_trunc('month', date_column)
HAVING count(primary_key) > 1;
Replace date_trunc('month', date_column) with whatever you want to group it
by. Note that date_part can give you interesting groupings (all the
Decembers, all the 1st of the months, all the Fridays, etc...)
If you want to run through it several times, you can have a sub-select in the
from clause. You could also save the results in a temp table.
- --
Jonathan Gardner <[EMAIL PROTECTED]>
Live Free, Use Linux!
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.2.1 (GNU/Linux)
iD8DBQE/M8HgWgwF3QvpWNwRAsUNAKCLnB6vajJ8fuS7IRgp0pYxp6YaxgCg2qbk
juL5a4tM1la0zmP81PdxS/c=
=N8Q/
-END PGP SIGNATURE-
---(end of broadcast)---
TIP 3: 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
[SQL] PostgreSql under Linux
Hello people. I'm installing Postgresql under linux for better performance and i want to know how is the best configuration. My server is a dual pentium3 1ghz/1gb ram/36gb scsi. running only postgresql. My question is: 1. What is the best linux distribuition for better performance? 2. Does exists any compilation options to better performance on this machine? Thanks Wilson Galafassi
Re: [SQL] [pg-sql] - Problem with duplicate table (PostgreSQL 7.2.3
On Fri, 8 Aug 2003, BenLaKnet wrote: > When I duplicate this code in an other table named spectacle_v without > Foreygn key ... all is running. > > But when I try to delete a spectacle_membre, linked value in spectacle > are correctly deleted, but I have an error for spectacle_v which is not > linked : > > (ERROR: referential integrity violation - key in membre_adherent still > referenced from spectacle_v ) What triggers are defined on membre_adherent? ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] length of recordset read through a cursor
Yes, a move takes less time, but can still a significant amount of time. Do you need to know exactly what to expect? Run ANALYZE recently? A cheat I've used before is to parse the EXPLAIN (not EXPLAIN ANALYZE) output for the expected number of records involved. If that number was less than 2000, I MOVE through them for an exact count -- otherwise display as approx . In most cases it's within 50% of actuality, sometimes better, but very few people care. They just want to know whether the information from their search is within the next screen or two. On Tue, 2003-08-05 at 07:13, Knut P. Lehre wrote: > >> After declaring a cursor, one way of obtaining the length of the > >resultset > >> is to perform a "MOVE 0" and read the PQcmdStatus which returns a > >"MOVE nn" > >> where nn is the length of the resultset. (A negative MOVE can then be > >used > >> to allow starting to fetch records from the beginning of the > >resultset.) > >> > >> Is there another, possibly faster way? > >> > >Looks like you're using libpq (because you mention PQcmdStatus), > >then after declaring a cursor and FETCH ALL, try > > > >1.3.4. Retrieving SELECT Result Information > > > >PQntuples Returns the number of tuples (rows) in the query result. > > > >int PQntuples(const PGresult *res); > > > >I'm not exactly sure what you're trying to achieve or going to do, > >so if I misunderstood you, ask again. > > > >Regards, Christoph > > Thanks for your reply. > What I'm trying to do is the following: I want to browse through a view > containing more than 1 records. To avoid slowing things down too much, > I would like my client program to receive (through the network) only the > records that are to be displayed on the screen. I believe I could do this > by declaring a cursor and then fetching the parts of the resultset I need. > It would be useful to know the size of the resultset immediately after the > cursor has been declared. How do I get this information? I could of course > fetch all of the resultset, but that is what I am trying to avoid. > Shouldn't it be quicker to perform a move through the set than fetching it? > I found that moving zero records results in a move to the end of the > resultset, with a command status returning the number of records moved. > Although I expected this method to take less time than a fetch (does it?), > I was wondering if there might be another way to get the size of the > resultset that can be fetched through the declared cursor. > > KP > > > > ---(end of broadcast)--- > TIP 7: don't forget to increase your free space map settings > signature.asc Description: This is a digitally signed message part
Re: [SQL] How to check: is some key referenced from sometable
On Sun, 10 Aug 2003, eVl One wrote:
> Hello, Bruno.
>
> You wrote 9 08 2003, 18:08:09:
>
> BWI> On Mon, Aug 04, 2003 at 11:17:56 +0300,
> BWI> [EMAIL PROTECTED] wrote:
> >> Please help.
> >>
> >> Need a boolean function which returns true if given key in table is
> >> referensed from another table(s).
>
> BWI> Use "exists" with a subselect. Something like:
> BWI> select exists(select 1 from table where table.key = 'value');
>
> Thanx for advice, but way I know and it can't be used.
> That's why I've got too many and/or too big tables from which
> such key is referenced. I mean (in some simplified way):
>
> table A ( -- main table A
> id SERIAL,
> PRIMARY KEY(id)
> );
>
> tableBxx (-- a lot of tables which got a.id as FOREIGN KEY
> ... -- too much to check 'em all with EXISTS queries
> a_id int4 REFERENCES a(id), -- without significant perfomance loss
> ...
> );
>
> So I need:
>"silent delete" - i.e. when trying to DELETE row from A I'll not fall
> out with "$1 referential integrity violation - key in A still
> referenced from Bxx", but silently doesn't delete row ('cause run
> it from function and need function to executes farther after delete);
>"something to check reference" - system (potgresql) is very quickly
> realizes that key is referenced from another table, maybe this
> information may be accessed through some system relations, or so?
No, postgresql runs a select on each of the referencing tables for
matching rows to figure it out itself. Theoretically it'd be possible to
keep track of it somewhere else with triggers when you insert/update a
value in the various referencing tables and use that data to determine if
a row has references if selecting from the various tables won't work for
you.
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] [PERFORM] EXTERNAL storage and substring on long strings
Oh, and I forgot to mention: it's highly compressed (bzip2 -9) and is 109M. Scott On Tue, 2003-08-05 at 11:01, Scott Cain wrote: > Joe, > > Good idea, since I may not get around to profiling it this week. I > created a dump of the data set I was working with. It is available at > http://www.gmod.org/string_dump.bz2 > > Thanks, > Scott > > > On Mon, 2003-08-04 at 16:29, Joe Conway wrote: > > Is there a sample table schema and dataset available (external-storage > > case) that we can play with? > > > > Joe -- Scott Cain, Ph. D. [EMAIL PROTECTED] GMOD Coordinator (http://www.gmod.org/) 216-392-3087 Cold Spring Harbor Laboratory ---(end of broadcast)--- TIP 3: 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
