[SQL] Greetings folks, dumb question maybe?
Hello, I'm a little new at this so please bear with me.
I am trying to create a function that loads 100M test records into a
database, however I am having a hard time building the function that
does so.
I'm trying to do this in PGAdmin III for Ubuntu. Is there something
that I have wrong with this? I know that this works in MySQL (and yes I
know that MySQL bends the SQL Standards), but I am not sure what I am
doing wrong exactly. I am coming up with the error that says there's an
error in my syntax near the v INTEGER := 0 line. I get the same error
in psql as I do in the PGAdmin III.
I have the following so far:
DECLARE
v INTEGER := 0;
BEGIN
while v < 1
DO
INSERT INTO unpart_tbl_test VALUES
(v, 'test string data', adddate('1995-01-01', (rand(v)*36520) mod 3652));
v := v + 1;
END WHILE;
END;
Any insight would be greatly appreciated.
- J
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Greetings folks, dumb question maybe?
On 05/12/2010 01:32 PM, Josh wrote:
Hello, I'm a little new at this so please bear with me.
I am trying to create a function that loads 100M test records into a
database, however I am having a hard time building the function that
does so.
I'm trying to do this in PGAdmin III for Ubuntu. Is there something
that I have wrong with this? I know that this works in MySQL (and yes
I know that MySQL bends the SQL Standards), but I am not sure what I
am doing wrong exactly. I am coming up with the error that says
there's an error in my syntax near the v INTEGER := 0 line. I get the
same error in psql as I do in the PGAdmin III.
I have the following so far:
DECLARE
v INTEGER := 0;
BEGIN
while v < 1
DO
INSERT INTO unpart_tbl_test VALUES
(v, 'test string data', adddate('1995-01-01', (rand(v)*36520) mod 3652));
v := v + 1;
END WHILE;
END;
Any insight would be greatly appreciated.
- J
after some digging I had to first create a language plpgsql, then I
changed the function to be as follows:
CREATE FUNCTION no_part_tbl() RETURNS void AS '
DECLARE
v INTEGER := 0;
BEGIN
WHILE v < 1 LOOP
INSERT INTO no_part_tbl VALUES
(v, "testing no parts", adddate("1995-01-01",
(rand(v)*36520 % 3652));
v := v + 1;
END LOOP;
END;
' LANGUAGE 'plpgsql';
And it seems to accepted the function finally. SOrry for the waste of
bandwidth and anyones time. I'm not used to this syntax, so it will
take me a bit to get on boad with it.
- J
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
[SQL] DELETE FROM takes forever
Hi I'm trying to do a DELETE FROM on my large table (about 800 million rows) based on the contents of another, moderately large table (about 110 million rows). The command I'm using is: DELETE FROM records WHERE id NOT IN (SELECT id FROM unique_records); This process ran for about two weeks before I decided to stop it -- it was dragging down the DB server. I can understand long-running processes, but two weeks seems a bit much even for a big table. Is this the best way to approach the problem? Is there a better way? Some background: The server is version 8.3, running nothing but Pg. The 'records' table has 'id' as its primary key, and one other index on another column. The table is referenced by just about every other table in my DB (about 15 other tables) via foreign key constraints, which I don't want to break (which is why I'm not just recreating the table rather than deleting rows). Most of the dependent tables have ON DELETE CASCADE. The 'unique_records' table is a temp table I got via something like: SELECT DISTINCT (other_column) id INTO unique_records FROM records Thanks very much! Josh Leder -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] DELETE FROM takes forever
Many of the tables do not have indexes on the FK, though a couple of the biggest ones do. It does seem worth the time to put an index on each of these tables, considering the few hundred hours I'm already spending on the DELETE. I've started the EXPLAIN ANALYZE but it will take a while, no doubt. In the meantime I'm going to play with the NOT EXISTS angle, its something I hadn't considered. On Thu, Feb 10, 2011 at 12:44 PM, Tom Lane wrote: > Hmm ... do all of those referencing tables have indexes on the > referencing columns? It seems plausible that the time is going into > seqscan searches for referencing rows. > > You might try doing EXPLAIN ANALYZE of this same delete for a limited > number of rows (maybe 1000 or so) so that you could see what plan you're > getting and where the time really goes. I think 8.3 had the ability to > break out time spent in triggers, so if the problem is the FK > propagation, EXPLAIN ANALYZE would show it. > > Also, the NOT IN is probably going to suck performance-wise no matter > what, for such large numbers of rows. Converting to NOT EXISTS might > help some, though I don't remember right now how smart 8.3 is about > either. > > regards, tom lane > -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Limit # of recs on inner join?
I want to limit the number of records returned by an inner join. Suppose I have a table of Books: book_id title And, a table of authors: book_id author_name Now, suppose I want to get book + author, but I only want one author for books with multiple authors. Traditionally, I'd do something like: select books.book_id, books.title, authors.author_name from books inner join authors on authors.book_id = books.book_id where books.book_id = ? This might return: 1 A Cat In The HatDr. Seuss 1 A Cat In The HatDr. Seuss' Partner Instead, I just want: 1 A Cat In The HatDr. Seuss How can I limit the inner join? Cheers, -J ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Weighted Searching
Mr. Vincent, > I emailed the list a while back about doing some weighted searching, asking > if anyone had implemented any kind of weighted search in PostgreSQL.. I'm > still wondering the same thing and if anyone has, I would greatly appreciate > a private email, I'd like to discuss it in detail.. I have several ideas but > most of them are pretty dirty and slow.. You really need to do this in PLSQL, Perl or C because there isn't any good way to implement weighting in pure SQL -- weighting is a procedureal thing. I'm currently in the process of designling an HR app that will use quite elaborate weighting scheme. Candidates are compared against job openings and the matches are weighted according to the degree of similarity (e.g. Job A requires 5 years of experience and the candidate has 4 or -20% for a weight of -1 but he is in the right location for a weight of +2 etc.) with the summary of weights to be fudged by the user according to what s/he considers most important for the job (location, skills, etc. I'm not done but I expect this function to be 4-5 pages of PL-SQL. Fuzzy logic eats processing power. I'll be open-sourcing the program in December, but you probably can't wait that long. I'd be interested to hear from anyone who's written a web search engine. It seems to me that the algorithm for, say, google should be fairly similar to what I'm doing for HR. -Josh Berkus -- __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 436-9166 for law firms, small businesses fax 436-0137 and non-profit organizations. pager 338-4078 San Francisco
Re: [SQL] PLSQL
Sr. Siquiera,
> Where can I find a tutorial on PL/SQL for postgres? Is there any
> documentation focused on it on postgres' site?
Try: http://www.postgresql.org/users-lounge/docs/v7.0/postgres/c4091.htm
-Josh
--
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
Complete information technology [EMAIL PROTECTED]
and data management solutions (415) 436-9166
for law firms, small businesses fax 436-0137
and non-profit organizations. pager 338-4078
San Francisco
Re: [SQL] Group by within table joins
Mr. Huang,
Seems to me that your GROUP BY line should read:
GROUP BY ltb.v_id, vtb.equip_attr[1], vtb.equip_attr[3],
vtb.equip_attr[4]
Or am I missing the point?
-Josh
> SELECT ltb.v_id,
>count(ltb.v_id) AS num_of_times_borrowed,
>vtb.equip_attr[1] AS year,
>vtb.equip_attr[3] AS model,
>vtb.equip_attr[4] AS type
> FROM log_tb ltb, vehicle_tb vtb
> WHERE ltb.v_id=vtb.equip_id
> GROUP BY ltb.v_id
> ORDER BY year;
--
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
Complete information technology [EMAIL PROTECTED]
and data management solutions (415) 436-9166
for law firms, small businesses fax 436-0137
and non-profit organizations. pager 338-4078
San Francisco
Re: [SQL] sum of agreggates in one SELECT?
Tom, > You can't have aggregates of aggregates in a single SELECT structure; > that's just not in the SQL execution model. The way around this is > to write multiple levels of SELECT, using either selection from a > grouped/aggregated view or subselect-in-FROM. Unfortunately Postgres > doesn't have either of those features --- yet. They might be in 7.1 > if I spend less time answering email and more time coding... Well, stop answering your e-mail, then, dammit! (impatiently waiting for 7.1) Seriously, you could actually slack (as in wait 8-12 hours) on answering the questions. There's been several basic SQL questions I could have fielded and you answered them first. Unfortunately, Louis-David, I don't see any way around subselects in the FROM clause as Tom mentions, which are not currently supported. I'd suggest using a Function to create a temporary table or view and summarizing from that. -Josh Berkus -- __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 436-9166 for law firms, small businesses fax 436-0137 and non-profit organizations. pager 338-4078 San Francisco
Re: [SQL] [GENERAL] Foreign Keys Help Delete!
Mr. Covell,
> 2. When I try to update "routes" table, it updates.
Actually, what I'm curious about is this part. Most databases that
support foriegn keys will not allow you to modify them as long as a
relation exists referencing the key, on either the master or child side,
unless you are updating the child to NULL (if the column is nullable) or
a valid alternative forign key value.
If you have updated the child record so that no records reference the
master key value, that key value should be then updatable without
violating the Forign Key constraint. However, I have not had reason to
test this on 7.0.2.
This provides you with two approaches for updating BOTH hosts and routes
table:
1. a. Create new record with new key value in hosts table with the
desired value
b. Update the routes record to reference the new value
c. Delete the old record in the hosts table
2. a. Drop the Foriegn Key constraint
b. Update both the routes and hosts tables
c. Re-establish the foriegn key constraint
If either of these approaches doesn't work, you have a valid bug
report. COngratulations!
-Josh Berkus
--
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
Complete information technology [EMAIL PROTECTED]
and data management solutions (415) 436-9166
for law firms, small businesses fax 436-0137
and non-profit organizations. pager 338-4078
San Francisco
Re: [SQL] [GENERAL] Foreign Keys Help Delete!
Timothy, Tom: > >1. a. Create new record with new key value in hosts table with the > >desired value > > b. Update the routes record to reference the new value > > c. Delete the old record in the hosts table > > > > Yes, that's what I tried. > > 1. foo.old.com exists in "hosts" table and "routes" table > 2. create foo.new.com in "hosts" table > 3. delete foo.old.com in "routes" table > 4. add foo.new.com into "routes" table > 5. try to delete foo.old.com and it complains! Tom - not to interrupt your coding :-) this sounds like a bug. Any thoughts? > >2. a. Drop the Foriegn Key constraint > > b. Update both the routes and hosts tables > > c. Re-establish the foriegn key constraint > > This is the part that I'm fuzzy on. I've tried this before > with complete DB corruption resulting. I had to dump each table > one by one, edit my schema with vi, create new DB, import tables > one by onevery painful! This also sounds like a problem. One should be able to drop a constraint, the re-create the restraint and check existing records against it. You can do this in MSSQL and Oracle. > PPS. As I replied to Stephan, I'm contracting at a company and I > don't have access to e-mail. Taking a schema home is NOT OK. > I already asked the manager if I could GPL my DNS-DB implementation. > As you might expect, the non-technical manager, didn't know what > GPL was, and he was NOT going to allow my work to be released to > publicAnd of course, higher ups in company may decide that > my solution breaks the "don't build if you can buy" policy, in which > case all of my work is for naught! ARGH!! Well, if they don't use it, you can easily re-create your work at home and GPL it. It also depends on the contract you signed ... -Josh -- __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 436-9166 for law firms, small businesses fax 436-0137 and non-profit organizations. pager 338-4078 San Francisco
[Fwd: Re: [SQL] no ORDER BY in subselects?]
Ooops, posted this to Phillip rather than the list, sorry Phillip ... Folks, Philip Warner wrote: > > At 15:23 20/09/00 +0200, Louis-David Mitterrand wrote: > > > >ERROR: parser: parse error at or near "order" > > > >Aren't ORDER BY clauses allowed in subselects? > > > > It is a very very sad fact, but, no, they're not. H ... can't say as I've ever seen an ORDER BY in a subselect before. Why would you want one? And if you do want one, Louis-David, you can always use a temporary table as previously described. -Josh -- __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 436-9166 for law firms, small businesses fax 436-0137 and non-profit organizations. pager 338-4078 San Francisco
Re: [Fwd: Re: [SQL] no ORDER BY in subselects?]
Phillip, > The main reason I use them is to find the 'next' or > 'previous' record in a > list (eg. next date, next ID). eg. > The fact that Dec RDB, Oracle and SQL/Server all allow it > probably means > that there is a reasonable user base out there who think > it's a good idea. Makes sense. Fortunately, in PGSQL there's another function to grab the next ID. As for dates ... hmmm... there we come up against the "no subselect in FROM" bug, don't we? I get the problem. (Personally, I've always SELECTed into a variable in MSSQL rather than using the syntac you're suggesting. I've found that the order that the MSSQL chooses to execute query segments in can cause some unpredicatble results ... ) -Josh Berkus
[SQL] Three Unrelated Questions
Folks, Here's three unrelated questions which have been bothering me: 1. What TYPE does one use in a Create Function statement to return a full dataset? (Create Function XXX AS VIEW? Create Function XXX AS TABLE?) 2. The Curval(), Setval() and Nextval() functions allow one to easily manage SERIAL values for multi-table updates. Can someone give me an easy way, within a function, to grab the OID returned by the INSERT statement? 3. I seem to remember reading somewhere some bitwise operators for PGSQL. But I can't find them in the documentation now. Was I mistaken? Do we just use modulo instead? Thanks for any advice! -Josh Berkus
Re: [SQL] memory usage
Ms. Wong, > This program seems to use a lot of the memory on the > linux server, and > the memory doesn't seem to be released at the end of > execution. The same > thing occurs when I try to connect to the database from > MS Access via > ODBC. It's been my experience that Win32 ODBC does not drop connections until the database client is closed (e.g. MS Access is shut down). For example, I have a program that connects MS Access through ODBC to a remote MySQL database via an SSH tunnel; the tunnel does not close until I shut down MS Access even if I kill the parent shell. ODBC keeps it alive. If, howoever, the connections (and their memory usage) are persisting after termination of the client application, I would look to your network environment for answers. It could be that you are routing through a switch that is, for some reason, keeping the client-server connection open after data has stopped transmitting. The last place I'd look would be the linux server; my experience is that Linux is worlds better at managing connections than Win32 or cheap Ethernet switches. Good Luck! -Josh
Re: [SQL] Date problem
Elipo,
> Ok. Let's work. I posted a mail before explaining a strange
> cituation if my Postgresql: when I use date_part() function to split
> day, month and year of a date type column, it returns one day before.
> In other words, '2000-01-01' returns day: 31, month:12, year: 1999.
No, I was hoping an expert would take this on. Lemme test it on Linux:
create table test_date AS (
haveadate DATE );
insert into test_date ( haveadate )
values ( '2000-04-30' );
select haveadate, date_part('month',haveadate),
date_part('day',haveadate),
date_part('year',haveadate) from test_date
haveadate
2000-04-30 4 30 2000
No problem here. Or on PG-ACCESS.
The problem must be in the OS/2 compile, probably some problem in
accessing the internal clock?
-Josh
--
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
Complete information technology [EMAIL PROTECTED]
and data management solutions (415) 436-9166
for law firms, small businesses fax 436-0137
and non-profit organizations. pager 338-4078
San Francisco
[SQL] Concerns about the OID
[SQL] OID Perfomance - Object-Relational databases
Folks,
Because it's a very elegant solution to my database structure issues,
I'm using OID's extensively as referents and foriegn keys. However, I
wanted to see if others had previous experience in this (answer as many
as you like):
1. Is there a performance loss on searches and joins when I use the OID
as a liniking field as opposed to a SERIAL column?
2. Can I define my own index on the OIDs of a table?
3. What is the difference between these two DDL statements in terms of
data access and PG-SQL performance (assuming that table clients has
already been defined):
CREATE TABLE client_addresses AS (
client_OID OID REFERENCES clients,
address1VARCHAR (30),
address2VARCHAR (30),
address3VARCHAR (30)
)
and:
CREATE TABLE client_addresses AS (
client clients,
address1VARCHAR (30),
address2VARCHAR (30),
address3VARCHAR (30)
)
(This is Michael's questions rephrased)
4. Int4 seems kinda small to me for a value that needs to enumerate
every single database object. Within a couple of years of heavy use, a
customer-transaction database could easily exceed 2 billion objects
created (and destroyed). Are there plans to expand this to Int8?
-Josh Berkus
P.S. My aplolgies if I've already posted these questions; I never
received them back from the list mailer.
--
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
Complete information technology [EMAIL PROTECTED]
and data management solutions (415) 436-9166
for law firms, small businesses fax 436-0137
and non-profit organizations. pager 338-4078
San Francisco
Re: [SQL] Something I'd like to try...
Tom,
> >> I just noticed that postgres doesn't totally support
> >> column aliases on UPDATE statements, for example
>
> The SQL92 spec very clearly does not allow an alias on the target table:
I have to agree here. The only improvement from a deviation (alllowing
aliasing) would be cosmetic.
-Josh
--
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
Complete information technology [EMAIL PROTECTED]
and data management solutions (415) 436-9166
for law firms, small businesses fax 436-0137
and non-profit organizations. pager 338-4078
San Francisco
Re: [SQL] OID Perfomance - Object-Relational databases
Tom, > By and large I'd recommend using a serial column in preference to OIDs, > though, for two reasons: > > 1. dump/restore is more practical that way (don't have to worry about >saving/reloading OIDs). > > 2. counter overflow problems hit you only per-table, not >per-installation. Hmmm ... for some tables, switching to Serial would work. However, one of the things I've done is add universal mod_data (modification stats) and notes tables, which have to relate via OID because they relate to 5-7 different tables. To wit: CREATE TABLE notes AS ( ref_OID OID, staff_OID OID REFERENCES staff, note_date DATE, note_text TEXT ) And the ref_oid relates to any of 5 different tables, thus allowing a single table to hold notes on clients, candidates, bills, etc. Very elegant, and using serials instead of the OID not possible. SO I'm concerned about the problems you mentioned above. pg_dump has a -o option; are there problems with this? And how liekly are counter overflow problems? Josh Berkus -- __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 436-9166 for law firms, small businesses fax 436-0137 and non-profit organizations. pager 338-4078 San Francisco
Re: [SQL] OID Perfomance - Object-Relational databases
Tom, > The trouble with pg_dump -o is that after reload, the OID > generator > will be set to max(any OID in the dumped data). So a > dump & reload > doesn't do anything to postpone OID-wraparound Ragnarok. > > As for the likelihood of overflow, figure 4G / tuple > creation rate > for your installation (not database, but whole > installation controlled > by one postmaster). Unless your installation has just > one active > table, per-table sequence values look like a better bet. Somebody (urgently) needs to tell all of the above to Bruce Momjian (I've cc'd him); his book-in-the-making points up OID's as a convenient and universal way to identify and link tuples (chapter 7) and doen't mention these problems. Who can I bug about how useless the above makes OID's? Thanks for the warning, and thanks Michael for the suggestion; I'll use it and send you all back notes on how it affects performance. -Josh
Re: [SQL] JDBC and BLOB in Postgres
Folks, These JDBC issues belong on the pgsql-interfaces list, where you'll find a community of JDBC bug-finders. Have fun! -Josh Berkus
Re: [SQL] Object syntax
Tom, > I'd recommend the traditional SQL solution: add a primary > key to the > address table and reference key values in the client > table. What you seem to be telling us is that, other than inheritance, PGSQL doesn't really support OODB functionality at this time. Is that an accurate summary assessment? -Josh berkus
[SQL] The TEXT data type
Folks, Is the TEXT data type automatically a BLOB (or TLOB?), or does it only become so if huge amounts of text are saved to the TEXT field? -Josh Berkus
Re: [SQL] Re: [HACKERS] Counting bool flags in a complex query
Tom, > Just FYI, I have committed code for 7.1 that allows ORDER > BY to work > correctly for a UNION'd query. A limitation is that you > can only do > ordering on columns that are outputs of the UNION: As far as I know, that limitation is standard to all SQL that supports UNION; the relational calculus (I'm told) is impossible otherwise. So ... we keep hearing about all the fantastic fixes in 7.1. When will a stable build show up? :-) -Josh
Re: [SQL] OID Perfomance - Object-Relational databases
ruce, > The oid counter is preserved with -o on reload. It is > not reset. I'll let you and Tom duke this one out. :-) It's all beyond me. > > 2. When OID's "wrap around" does the whole database go > > kablooie? If so, why hasn't it happened to anyone yet? > If > > not, can you describe the system PGSQL uses to allocate > OIDs > > once it gets to 2,147,xxx,xxx? > > oid's start getting re-used on wraparound. > This is what I mean. Does the DB engine only recycle *unused* OIDs (that is, does it check for teh continued existance of a tuple with OID 198401)? If that's the method, then there isn't really a problem even if I do use OIDs as a primary index. None of my OIDs still in use will be touched. If OIDs start getting re-used regardless if they are already present, then, like Tom says, it's Ragnarok. But it seems like somebody would have increased the OID to INT8 if that were a prospect. -Josh Berkus P.S. Bruce, I'm sorry about not sending my comments on your book. Do you have any use for copy-editing comments from the June 28th version, or are you already in pre-press?
Re: [SQL] OID Perfomance - Object-Relational databases
Bruce, Tom, etc.: > > This is what I mean. Does the DB engine only recycle > > *unused* OIDs (that is, does it check for teh continued > > existance of a tuple with OID 198401)? If that's the > > method, then there isn't really a problem even if I do > use > > OIDs as a primary index. None of my OIDs still in use > will > > be touched. > > > No, it uses all oids, and can create duplicates. Does this mean that Tom's "Wraparound Ragnarok" is the accurate scenario? -Josh Berkus
Re: [SQL] OID Perfomance - Object-Relational databases
Tom, Bruce,
Thanks. I think that gives me a pretty clear picture. How can we
submit this whole OID thing to the PGSQL FAQ? Want me to write it up?
-Josh Berkus
P.S. BTW, my conclusion based on this discussion is that I will not use
the OIDs for indexing/linking. Instead, I will embrace Michael's
earlier suggestion and have already created universal_sq and started
migrating primary keys to that sequence.
P.P.S. Thank you both for taking the time to hash out this issue.
--
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
Complete information technology [EMAIL PROTECTED]
and data management solutions (415) 436-9166
for law firms, small businesses fax 436-0137
and non-profit organizations. pager 338-4078
San Francisco
Re: [SQL] "Delete before" in ms sql?
Mr. Vadsholt, This is a Postgre-SQL list. If you are using Microsoft SQL Server you should proceed to http://msdn.microsoft.com/ -Josh Berkus -- __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 436-9166 for law firms, small businesses fax 436-0137 and non-profit organizations. pager 338-4078 San Francisco
Re: [SQL] if else query help
Brian, Jean-Christophe, >Someone corrects me if I'm wrong, I come from the Oracle world... > > Dates (or I should say TimeStamps) are stored as floating point values > : the integer part is the number of days since a certain date > (epoch or 01/01/1970 on unix-based databases) and the fractionnal part is the > portion of the day (although I don't know --yet-- how to convert > date2-date1 to an integer, trunc does not work). You're doing this the hard way. One of Postgres' best features is its rich collection of date-manipulation functions. Please see: ... H. The online docs appear to be down. When they're back up, please check the sections on: Date/Time data types, and Date/Time manipulation functions. -Josh Berkus P.S. Brian, a general tutorial on writing SQL, such as O'Reilly's soon-to-be released SQL book, might help you a great deal. -- __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 436-9166 for law firms, small businesses fax 436-0137 and non-profit organizations. pager 338-4078 San Francisco
Re: [SQL] Standard syntax?
Franz, You'd better stay away from that syntax if you want to make your applications portable. I can tell you that it won't work on MS SQL Server or MySQL. I can't speak for Oracle. -Josh Berkus P.S. Thanks for the nifty construction ... I wouldn't have thought of it! -- __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 436-9166 for law firms, small businesses fax 436-0137 and non-profit organizations. pager 338-4078 San Francisco
Re: [SQL] Variable-length Types
Folks,
I'm a bit confused on custom type declarations, actually. I'd like to
create a custom type for American phone numbers, such that:
It takes a string and returns something that looks like Varchar(22);
If someone types in 10 digits, it returns output like (###) ###-
If a user enters 7 digits, it picks a default area code and returns
(415) ###-,
and if they type more than 10 digits or enter any digits after an "x",
they get (###) ###- x###
My questions are as follows:
1. Can I reference a custom function (phoneformat) in a type definition?
2. If 1. is "no", is there a way to do the above without programming the
type in C?
3. What sort of trouble am I going to get into trying to pull data from
a custom type into an external interface (i.e. PHP4)?
Thanks for your thoughts!
-Josh Berkus
--
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
Complete information technology [EMAIL PROTECTED]
and data management solutions (415) 436-9166
for law firms, small businesses fax 436-0137
and non-profit organizations. pager 338-4078
San Francisco
Re: [SQL] Variable-length Types
Tom,
> However, building a new type for this seems like overkill, because you'd
> also have to supply a set of functions and operators for the type. It
> would be a lot less work just to provide a normalization function
> interpret_phone_no(text) returns text
> which could be invoked explicitly, eg
> insert into tab values (..., interpret_phone_no('5551212'), ...)
> or implicitly in ON INSERT and ON UPDATE triggers for the table.
Thanks. You're probably right ... reformatting the phone numbers is
going to be a lot less work than a custom type. Still, I can see the
usefulness of a custom type if one had the time to build the new library
of operators etc. For example, a special set of comparison operators for
phone numbers. Maybe I'll hire somebody to do it :-)
I do think I'll use triggers for ON INSERT and ON UPDATE, because it
will space me having to remember to use the function every time I handle
a phone number field. I'll post the PLSQL function after I've written
it.
-Josh Berkus
--
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
Complete information technology [EMAIL PROTECTED]
and data management solutions (415) 436-9166
for law firms, small businesses fax 436-0137
and non-profit organizations. pager 338-4078
San Francisco
Re: [SQL] Variable-length Types
KuroiNeko, > I remember from the old days of Delphi/InterBase, and even older days of > Paradox, there were so called input masks, US phone code mask would be like Input masks still exist for some languages (VB, Paradox) but I've found that even in those platforms that support them I tend to replace them with custom functions, because actually using them is too buggy. > I'm just not sure what to do if 8 or 9 digits are supplied? Maybe, reject, > assuming that such things should be caught by UI, and if we get such a > weird thing, there's something really really wrong? Or, if you're using an interface that doesn't readily support entry validation (e.g. PHP) then you can nest functions and have the "Save" button test for a reply indicating that something's wrong. To wit: Create Function save_candidate (Lots of candidate data variables) RETURNS VARCHAR (100) AS Declare output_string VARCHAR(100) Do a whole bunch of stuff SELECT first_phone = phoneformat(first_phone) IF first_phone = 'fail' ( SELECT output_string = 'Bad phone number for Primary Phone' RETURN output_string ) More code -Josh -- __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 436-9166 for law firms, small businesses fax 436-0137 and non-profit organizations. pager 338-4078 San Francisco
Re: [SQL] Variable-length Types
Mr. Popkov,
> http://www.primechoice.com/hum/uspn.c
Thanks! Since I don't read C, I'll just have to compile it as a
function and try it out. To repay you, I'll write the function (in
PL/PGSQL) to strip out any extraneous characters that the user might
have added in data entry.
Soon.
-Josh
P.S. this makes you the first outside contributor to my open-source
project ... which isn't up on the web yet!
--
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
Complete information technology [EMAIL PROTECTED]
and data management solutions (415) 436-9166
for law firms, small businesses fax 436-0137
and non-profit organizations. pager 338-4078
San Francisco
Re: Antw: [SQL] many-many mapping between unique tables
Mr. Majumdar,
>
> Sorry for splitting mails. Is there some way of using the oid of each
> row to do the mapping instead of creating two more columns of integers?
> The third table can then be like this:
>
> create table T3(
> T1 oid,
> T2 oid
> );
Please reference two things:
1. Look in the list archives for the last 4 weeks and you will read a
long discussion on the risks/limitations of using an OID as the primary
key on very large tables. (Subject: Object-Relational Database Design).
2. For how to use OIDs, please look in Bruce Momjian's excellent PGSQL
book-in-progress, available on the Postgresql.org website. (Chapter 7, I
believe).
-Josh Berkus
--
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
Complete information technology [EMAIL PROTECTED]
and data management solutions (415) 436-9166
for law firms, small businesses fax 436-0137
and non-profit organizations. pager 338-4078
San Francisco
Re: [SQL] JDBC Performance
Mr. May, For discussions of JDBC, please subscribe to the pgsql-interfaces list. You will find many JDBC users on that list. -Josh Berkus P.S. PGSQL folks, is there any way we can clarify this on the web page? The JDBC users seem to keep ending up on this list. -- __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 436-9166 for law firms, small businesses fax 436-0137 and non-profit organizations. pager 338-4078 San Francisco
Re: [SQL] Last serial number inserted
Eduardo, Use the curval(serial) function. For more information, look in the online docs under the name of that function. nextval(), curval() are wonderful things! -Josh Berkus -- __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 436-9166 for law firms, small businesses fax 436-0137 and non-profit organizations. pager 338-4078 San Francisco
Re: [SQL] Function that returns a tuple.
Claudio,
> How can I write a function in sql or pl- pgsql that returns a set of
> tuples ?
I've had the same question myself for the past month, which has gone
unanswered (hint, hint, Jeff!). Currently I'm using custom functions to
build views and then referenceing the views:
CREATE FUNCTION fun_generate_statistics (
do a bunch of stuff, ending with:
CREATE VIEW vw_generate_statistics (
bunch of stuff);
);
Then the front-end code references vw_generate_statistics.
Not elegant, but sufficient until I hear from Jeff or Jan Wieck.
-Josh Berkus
--
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
Complete information technology [EMAIL PROTECTED]
and data management solutions (415) 436-9166
for law firms, small businesses fax 436-0137
and non-profit organizations. pager 338-4078
San Francisco
[SQL] Re: SQL
(Aplolgies to Mr. McCoy, to whom I mailed this awnser in error) Mr. Bajerski, > > I've got answer from Postgres > > "Illegal attributes or non-group column" > > > > Is it error in query or in parser ? It's your query. In a GROUP BY query, all named columns must either contain and aggregate function (e.g. SUM) or be named in the GROUP BY clause. O'Reilly has just come out with a SQL in a Nutshell book. I'd suggest picking one up. -Josh Berkus -- __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 436-9166 for law firms, small businesses fax 436-0137 and non-profit organizations. pager 338-4078 San Francisco
Re: [SQL] help on createing table
Sherwin,
I've done this before for one project. Here's what you do:
CREATE TABLE sample_heirarchy (
unique_id SERIAL CONSTRAINT PRIMARY KEY,
node_linkup INT4,
node_level INT2,
label VARCHAR(30)
datawhatever
);
Then you use the unique_id and node_linkup fields to create a heirarchy
of data nodes, with an indefinite number of levels, where the
node_linkup of each lower level equals the id of its parent record. For
example:
id linkup level label data
3 0 1 Node1 Node1
4 3 2 Node1.1 Node1.1
6 3 2 Node1.2 Node1.2
7 6 3 Node1.2.1 Node1.2.1
5 0 1 Node2 Node2
etc.
You can then access the whole heirarchy through moderately complex, but
very fast-executing UNION queries. The one drawback is that you need to
know in advance the maximum number of levels (3 in this example), but
I'm sure someone on this list can find a way around that:
SELECT n1.unique_id, n1.label, n1.data, n1.node_level, n1.unique_id AS
level1,
0 AS level2, 0 AS level3
FROM sample_heirarchy n1
WHERE n1.node_level = 1
UNION ALL
SELECT n2.unique_id, n2.label, n2.data, n2.node_level, n1.unique_id,
n2.unique_id, 0
FROM sample_heirarchy n2, sample_heirarchy n1
WHERE n1.unique_id = n2.node_linkup
AND n2.node_level = 2
UNION ALL
SELECT n3.unique_id, n3.label, n3.data, n3.node_level, n1.unique_id,
n2.unique_id, n3.unique_id
FROM sample_heirarchy n1, sample_heirarchy n2, sample_heirarchy n3
WHERE n1.unique_id = n2.node_linkup AND
n2.unique_id = n3.node_linkup
AND n3.node_level = 3
ORDER BY level1, level2, level3
Should produce this output (pardon any parsing errors; I'm not at a
PGSQL terminal right now):
unique_id label datalevel level1 level2 level3
3 Node1 Node1 1 3 0 0
4 Node1.1 Node1.1 2 3 4 0
6 Node1.2 Node1.2 2 3 6 0
7 Node1.2.1 Node1.2.1 3 3 6 7
5 Node2 Node2 1 7 0 0
etc.
This sorts them in numerical (id) order, but one could just as easily
substitute the labels or data for the various levels and sort them
alphabetically (although you do need to allow for NULL sort order on
your database, and any label duplicates).
The advantages of this structure are:
1. It allows you to create, assign, and re-assign nodes freely all over
the heirarchy ... just change the level and/or linkup.
2. Aside from the Union query above, the table structure allows for any
number of levels, unlike a set or relationally linked tables.
3. Because the display query is entirely once table linking to itself on
(hopefully) indexed fields, in my expreience it runs very, very fast.
4. My PHP developer has reprogrammed the easily available PHP Tree
Control to uses this table structure (I don't know if he's giving it
out, but he said it wasn't very difficult).
CHALLENGE FOR THE LIST:
Re-write the above UNION query, possibly using a PL/PGSQL or C function,
so that it works for any number of node levels.
-Josh Berkus
--
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
Complete information technology [EMAIL PROTECTED]
and data management solutions (415) 436-9166
for law firms, small businesses fax 436-0137
and non-profit organizations. pager 338-4078
San Francisco
--
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
Complete information technology [EMAIL PROTECTED]
and data management solutions (415) 436-9166
for law firms, small businesses fax 436-0137
and non-profit organizations. pager 338-4078
San Francisco
Re: [SQL] Help on Union Query
Mr. May,
The reason you're having trouble is that the problem you've described
does not call for a union query at all. What you want is a simple GROUP
BY query:
SELECT Node_ID, Word, Count(*)
FROM NodeIndex
GROUP BY Node_ID, Word
-Josh Berkus
--
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
Complete information technology [EMAIL PROTECTED]
and data management solutions (415) 436-9166
for law firms, small businesses fax 436-0137
and non-profit organizations. pager 338-4078
San Francisco
[SQL] Query Problem
Folks:
Here's the problem, in abstract: I need to select every record in table
A that does not have a link in table B Join Table C where Table
C.account = 11
The relevant fields:
Table_A
CaseID
Data
Table_B
GroupID
CaseID
Amount
Table_C
GroupID
AccountID
Thus, I need to select:
SELECT Data FROM Table A
WHERE CaseID NOT IN (
SELECT CaseID FROM Table_B, Table_C
WHERE Table_B.GroupID = TableC.GroupID
AND TableC.AccountID = 11)
The problem is, since Table_B and Table_C are large (10,000 records +)
this exclusion query takes several *minutes* to run.
I've fooled around with drectional joins, views, and temporary tables,
but I can seem to find anything that works faster. Suggestions?
-Josh Berkus
--
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
Complete information technology [EMAIL PROTECTED]
and data management solutions (415) 436-9166
for law firms, small businesses fax 436-0137
and non-profit organizations. pager 338-4078
San Francisco
Re: [SQL] Query Problem
Michael,
> SELECT Data
> FROM Table A
> WHERE NOT EXISTS (
> SELECT * FROM Table_B, Table_C
> WHERE Table_B.GroupID = TableC.GroupID
> AND TableC.AccountID = 11
> )
>
> I think that the not exists is a bit quicker than the NOT IN. Give it
> a whirl.
A *lot* faster. Like, 7x as fast. I'd forgotten about EXISTS, since I
so seldom have a use for it ... but this is shy it was created, I guess.
Thanks so much for your help!
-Josh Berkus
--
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
Complete information technology [EMAIL PROTECTED]
and data management solutions (415) 436-9166
for law firms, small businesses fax 436-0137
and non-profit organizations. pager 338-4078
San Francisco
Re: [SQL] benchmarks
Frankyl, Clayton:
Comparing PostgreSQL to MySQL is like comparing an 18-wheel Kenworth
to a Porsche. The two are not equivalent ... if you want a simple, very
very fast READ-ONLY database, use MySQL. If you want a full-featured
transaction-environment database for a huge, complex set of data, use
PostgreSQL.
Any benchmarks you find are likely comapring the two for hosting web
sites, which is unfair to PostgreSQL. If everything your database needs
to do is serve up page content, go for MySQL. If you've got to build a
60-user inventory management system, MySQL won't even bring you close -
heck, it's not even close to SQL92-compliant.
Oracle is another game altogether, though I can imagine Postgres
catching up in a few years. Still, the price tag for Oracle weeds out
all but the very serious and deep-pocketed. And stay away from MS SQL
Server ... I run two of the damn machines, and they're nothing but
grief.
-Josh
--
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
Complete information technology [EMAIL PROTECTED]
and data management solutions (415) 436-9166
for law firms, small businesses fax 436-0137
and non-profit organizations. pager 338-4078
San Francisco
Re: [SQL] Outer Joins
Marc,
> select a.col1, b.col2 from a,b
> where a.col1 = b.col2
>or b.col2 is null
The above would select each record in a that matched a record in b, plus
each record in b for every possible a where b.col2 was null - not where
there was no match.
> select a.col1, b.col2 from a,b
> where a.col1 = b.col2
>or a.col1 not in (select b.col2 from b)
This would work, but it would be *much* slower than a UNION query. "Not
In" queries are perhaps the slowest you can run; see the earlier thread
"Query Problem" for a discussion. UNION queries are, in fact, very fast
... just awkward to code and manipulate.
-Josh
--
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
Complete information technology [EMAIL PROTECTED]
and data management solutions (415) 436-9166
for law firms, small businesses fax 436-0137
and non-profit organizations. pager 338-4078
San Francisco
Re: [SQL] Outer Joins
Marc, > >This would work, but it would be *much* slower than a UNION query. "Not > >In" queries are perhaps the slowest you can run; see the earlier thread > >"Query Problem" for a discussion. UNION queries are, in fact, very fast > >... just awkward to code and manipulate. > > Why should this be slower since the UNION Query still has an identical not in clause? > This is far easier (for me) to read. Good point. Frankly, if you have a relevant large population of data (>10,000 rows) to test, I'd love to see comparative execution tests between the two query structures. Fortunately, this will all soon become moot; Tom says that outer joins have been stable in the 7.1 build for a while. Speaking of which, when's the 7.1 "release"? Huh, huh? -Josh Berkus -- __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 436-9166 for law firms, small businesses fax 436-0137 and non-profit organizations. pager 338-4078 San Francisco
Re: [SQL] Outer Joins
Marc, Tom, > Good point. Frankly, if you have a relevant large population of data > (>10,000 rows) to test, I'd love to see comparative execution tests > between the two query structures. > > Fortunately, this will all soon become moot; Tom says that outer joins > have been stable in the 7.1 build for a while. Speaking of which, > when's the 7.1 "release"? Huh, huh? On second thought, couldn't we use some kind of EXCLUDES clause to expedite this? Tom? Further, it occurs to me that as in my query, you don't want to use "NOT IN" on *either* version. Instead, use "NOT EXISTS", which is much, much faster. -Josh Berkus -- __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 436-9166 for law firms, small businesses fax 436-0137 and non-profit organizations. pager 338-4078 San Francisco
Re: [SQL] Oracle, ODBC, MS IIS -> problem!
Mr. Steinbach,
> I have to create a web interface for an Oracle database. I use MS Internet
> Information Server, ODBC driver (tried one from MS and one from Oracle) and an
> Oracle database (I have no permission to change anything in that database).
You seem to have joined/posted to our mailing list in error. This is a
PostgreSQL SQL-developers mailing list. If you need help with Oracle, I
suggest proceeding to www.oracle.com and looking for appropriate forums;
I'm sure there are many.
-Josh Berkus
--
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
Complete information technology [EMAIL PROTECTED]
and data management solutions (415) 436-9166
for law firms, small businesses fax 436-0137
and non-profit organizations. pager 338-4078
San Francisco
[SQL] Requests for Development
Tom, Bruce, Jan, etc.:
As a PGSQL developer and business customer, I wanted to make some
public requests as to the development path of PGSQL. While, obviously,
you will develop the functionality *you* are interested in, I thought it
might be valuable to you to know what things would be most appreciated
(and please, list folks, speak up).
1. Please finish 7.1, stabilize it, and release it. I am probably not
the only developer with an application that is waiting for the many
wonderful improvements Tom has added to 7.1, but I can't build a
commercial app off the CVS source tree.
The rest of these requests apply to 7.2:
2. Stored Procedure functionality, i.e. outputting a full recordset from
a function (or new structure, if functions are hard to adapt) based on
the last SELECT statement passed to the function. An alternative would
be to develop parameterized views, which might be the easiest path.
3. Slightly more informative syntax error messages - frankly, just
grabbing a little more text around the word or punctuation that
triggered the error would be enormously helpful (I can't tell you the
number of times I've gotten "Error at or near ')'" in a huge DDL
statement.
4. Use of named in addition to ordinal variables in PL/PGSQL functions
(e.g. $account_type, $period instead of $1, $2).
Thanks so much for your ongoing hard work!
-Josh Berkus
--
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
Complete information technology [EMAIL PROTECTED]
and data management solutions (415) 436-9166
for law firms, small businesses fax 436-0137
and non-profit organizations. pager 338-4078
San Francisco
Re: [SQL] FTI, paged, ranked searching and efficiency.
Paul,
I'm afraid that much of that was over my head. In fact, I'm keeping it
as an example in case I ever need to do something similar. Forward your
info and I'll include credit in the source :-)
In general terms, I've always depended on the PHP to select a "page" of
results, using the logic that the number of results on a page is a
matter for the web application to handle (a display issue) rather than
something to be handled on the back-end (a data issue).
However, you point about not pulling out tons of data the user will
never examine (i.e. 2nd and succeeding pages) is well-taken. Although,
without pulling the entire data set, you can't display to the user how
many results there are, total. If it's a strong possibility that the
users are really only ever going to want the top 20-40 rated results,
then splitting it as you suggest ... first, counting all the matches and
then dragging in the rest of the data for the top X records ... makes a
lot of sense.
Unfortunately, your only real option I can see for DB server-side row
grabbing is: Create the query(ies) as a temporary table or view using a
function. Then use Limit and Offset to grab one chunk of data at a
time. This is, of course, a serious problem for mutli-user performance
since eash user would need their own temp table or view.
From what I can tell, search engines (google, for example) grab the
whole recordset and use the web script to parse it out 25 records at a
time.
Hopefully, someone on this list will have done that before and can
provide less theoretical advice.
-Josh Berkus
P.S. I've also posted this to the pgsql-php list. I;ve quoted the full
text of your question below my .sig for that reason.
--
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
Complete information technology [EMAIL PROTECTED]
and data management solutions (415) 436-9166
for law firms, small businesses fax 436-0137
and non-profit organizations. pager 338-4078
San Francisco
Paul wrote:
>
> Hello,
>
> This is going to be a bit long, I hope some of you will take the
> trouble to read it :)
>
> I am building a search engine for a section of a (PHP based) website.
> I wish the user to be able to a number of words in the search, and the
> search results to be ranked by the number of times words occur (both
> different words and the same word occuring multiple times are good).
>
> My (simplified) table structure is this:
> ==
> Table "entry_fti"
> Attribute |Type | Modifier
> ---+-+--
> string| varchar(25) |
> id| oid |
> Index: entry_fti_string_idx
>
>Table "entry"
>Attribute | Type |Modifier
> ---+---+--
> -
> entry_id |integer| not null default
> nextval('entry_id_seq'::text)
> entry_name|text |
> entry_description_html|text |
> entry_image_id|integer| not null default 0
> entry_tn_image_id |integer| not null default 0
> entry_live|boolean| not null default 't'
> Index: entry_pkey
>
>Table "image"
> Attribute |Type |Modifier
> +-+
> image_id | integer | not null default nextval('image_id_seq'::text)
> image_name | varchar(32) |
> height | integer | not null
> width | integer | not null
> Indices: image_pkey
> ==
>
> And my (simplified) query looks like this:
> ==
> SELECT COUNT(entry_fti.id) AS rating,
> entry.entry_name AS name,
> entry.entry_id AS id,
> entry.entry_description_html AS description_html,
> image.image_name AS thumb1_name,
> image.height AS thumb1_height,
> image.width AS thumb1_width
> FROM entry, entry_fti, image
> WHEREentry_fti.id=entry.oid
> ANDentry.entrytn_image_id=image.image_id
> ANDentry.entry_live = 't'::bool
> AND(
> entry_fti.string ~'^word1'
> OR
> entry_fti.string ~'^word2'
> OR
>.
>.
> OR
> entry_fti.string ~'^wordn'
> )
> GROUP BY entry.entry_id,
> entry.entry_name,
> entry.entry_descripti
Re: [PHP] Re: [SQL] FTI, paged, ranked searching and efficiency.
Stephen, > How come nobody's ever thought of cursors? > > DECLARE foo CURSOR FOR SELECT stuff FROM stuff WHERE foo ORDER BY > something; > > Hop forward N rows? > MOVE FORWARD $n IN foo > > Want M rows? > FETCH FORWARD $m IN foo I'm intrigued by this. How would I retrieve cursor rows into a web application? If we could output a cursor to a functon result (we can't), it would be easy, but I'm not sure otherwise. -Josh -- __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 436-9166 for law firms, small businesses fax 436-0137 and non-profit organizations. pager 338-4078 San Francisco
Re: [SQL] Requests for Development
Jan, > To put the ball back into your yard, I'd like to > make a > request too. There seem to be alot people using > PL/pgSQL > and/or PL/Tcl extensively. OTOH there are newbies > again and > again asking for a good tutorial, programming > examples and so > on. Writing a good tutorial doesn't require a good > backend > developer, IMHO an experienced SQL-programmer > would be the > better guy anyway. During the past 4 years I've > heard over > and over that people would like to contribute their > $0.05 if > they only could code in C. That's an area where > nobody needs > any C experience. Point taken. Hmmm... when we finish the current project, I ought to have more than a few dozen PL/PGSQL functions as examples. I can definitely talk to my help writer about dressing those up into an educational "chapter". It'll cost me a little more than $0.05, but is only my fair contribution. Look for something in february-march. -Josh Berkus
Re: [SQL] Requests for Development
Roberto - > > I have this on the way. I started creating such document a > > couple months ago when I was porting stuff from Oracle to PostgreSQL and > > stumbled on the few examples on the documentation. I'd be glad to finish > > it up, add more things to it and then put it somewhere for review, > > comments, suggestions, additions, etc. > > Don't worry too much about final polish: "release early, release often!" To further that ... let me put my ex-professional copy-editor skills at your disposal. Post the text, I'll help clean it up! -Josh -- __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 436-9166 for law firms, small businesses fax 436-0137 and non-profit organizations. pager 338-4078 San Francisco
Re: [SQL] How to represent a tree-structure in a relational database
Frank, Please look in the list archives. About 2 months ago this topic came up and was discussed extensively (including a creative solution by yours truly). -Josh Berkus -- __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 436-9166 for law firms, small businesses fax 436-0137 and non-profit organizations. pager 338-4078 San Francisco
Re: [SQL] How to represent a tree-structure in a relational database
Frank, etc: > > create table Category ( > > CategoryID int4 not null primary key, > > ParentCategoryID int4 not null REFERENCES Category (CategoryID), > > CategoryName varchar(100) > > ); That was it. I also gave an example of a UNION query that would display the whole category tree in ASCII format: I've done this before for one project. Here's what you do: CREATE TABLE sample_heirarchy ( unique_id SERIAL CONSTRAINT PRIMARY KEY, node_linkup INT4, node_level INT2, label VARCHAR(30) datawhatever ); Then you use the unique_id and node_linkup fields to create a heirarchy of data nodes, with an indefinite number of levels, where the node_linkup of each lower level equals the id of its parent record. For example: id linkup level label data 3 0 1 Node1 Node1 4 3 2 Node1.1 Node1.1 6 3 2 Node1.2 Node1.2 7 6 3 Node1.2.1 Node1.2.1 5 0 1 Node2 Node2 etc. You can then access the whole heirarchy through moderately complex, but very fast-executing UNION queries. The one drawback is that you need to know in advance the maximum number of levels (3 in this example), but I'm sure someone on this list can find a way around that: SELECT n1.unique_id, n1.label, n1.data, n1.node_level, n1.unique_id AS level1, 0 AS level2, 0 AS level3 FROM sample_heirarchy n1 WHERE n1.node_level = 1 UNION ALL SELECT n2.unique_id, n2.label, n2.data, n2.node_level, n1.unique_id, n2.unique_id, 0 FROM sample_heirarchy n2, sample_heirarchy n1 WHERE n1.unique_id = n2.node_linkup AND n2.node_level = 2 UNION ALL SELECT n3.unique_id, n3.label, n3.data, n3.node_level, n1.unique_id, n2.unique_id, n3.unique_id FROM sample_heirarchy n1, sample_heirarchy n2, sample_heirarchy n3 WHERE n1.unique_id = n2.node_linkup AND n2.unique_id = n3.node_linkup AND n3.node_level = 3 ORDER BY level1, level2, level3 Should produce this output (pardon any parsing errors; I'm not at a PGSQL terminal right now): unique_id label datalevel level1 level2 level3 3 Node1 Node1 1 3 0 0 4 Node1.1 Node1.1 2 3 4 0 6 Node1.2 Node1.2 2 3 6 0 7 Node1.2.1 Node1.2.1 3 3 6 7 5 Node2 Node2 1 7 0 0 etc. This sorts them in numerical (id) order, but one could just as easily substitute the labels or data for the various levels and sort them alphabetically (although you do need to allow for NULL sort order on your database, and any label duplicates). The advantages of this structure are: 1. It allows you to create, assign, and re-assign nodes freely all over the heirarchy ... just change the level and/or linkup. 2. Aside from the Union query above, the table structure allows for any number of levels, unlike a set or relationally linked tables. 3. Because the display query is entirely once table linking to itself on (hopefully) indexed fields, in my expreience it runs very, very fast. 4. My PHP developer has reprogrammed the easily available PHP Tree Control to uses this table structure (I don't know if he's giving it out, but he said it wasn't very difficult). -Josh Berkus -- __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 436-9166 for law firms, small businesses fax 436-0137 and non-profit organizations. pager 338-4078 San Francisco
Re: [SQL] postgres
Mr. Daoust, You have reached the PostgreSQL SQL developers mailing list. We are not PostgreSQL sales people, and we have no marketing information to sell you. Please have a clue. I suggest that you try http://www.postgresql.org/ and http://www.pgsql.com/ for more information. -Josh Berkus -- __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 436-9166 for law firms, small businesses fax 436-0137 and non-profit organizations. pager 338-4078 San Francisco
Re: [SQL] How to represent a tree-structure in a relational database
Stuart, > I don't think I'd be comfortable with having the node_level column in the > table structure. First, because you can derive that value using a function, > it's duplicate data. Second, if you decide to take an entire segment of your > hierarchy and move it under another node (by changing the value of > node_linkup/ParentCategoryID), you'll need to recalculate all of those > node_level values. And all the node_level values underneath it. I can see that. I suppose it depends on the data you're storing. The project I was working on tracked grocery inventory for a delivery service, and thus each item had a fixed "level" in the heirarcy (Food Class, Food Type, Manufacturer, and Item) and thus while items might get reassigned *across* the heirarcy, they did not get re-assigned *up and down* the heirarcy. Also, I can't think of a way to represent the tree in pure SQL without having the level identifiers (and a fixed number of levels). > We've done a similar thing for Java. It was ridiculously easy to create a > TreeModel wrapped around this data. Almost too easy; it made me feel dirty. Great. Maybe I'll buy it from you if I ever need to use Java :-) -Josh -- __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 436-9166 for law firms, small businesses fax 436-0137 and non-profit organizations. pager 338-4078 San Francisco
Re: [SQL] Tree structure table normalization problem (do I need a trigger?)
Frank, > However, I have > a problem now > which seems non-trivial: I am at some point in the tree, > say 3 nodes > down from the root, but I don't know where I am exactly > (across which > nodes would I travel along the shortest path to the top?) > and would like > to find out. This is, again, not really difficult if I > know how deep > into the tree I am, in which case I can simply do (I know > that I am 3 > nodes from the root and that my current node number is > x): This is exactly why my model includes a "Level" column. It was more important to me to have the easy queriability of the "redundant" level info than to have the fluid flexibility of a tree without it. The choice sorta depends on what you're storing in the tree. > (This is probably very expensive if the tree gets really > deep, but I > don't expect that to happen in my database anytime soon.) Not really. You're querying (hopefully) two indexed fields within the same table, refrenced to itself. Once you've run it a few times, even the elaborate UNION query I posted will run very quickly - on my table (~300 items) it runs <2 seconds. > This means > you need a loop control structure which means you have to > write a > PL/pgSQL procedure (or some other procedure) that is run > by a trigger to > update the level column on insert or update, as in > This seems to feasible but not really as straightforward > as one might > hope. Is there an easier way? Hmmm. I don't know, Frank. That strikes me as a really good, straightforward workaround to your problem. I'm not sure what you could do that would be simpler. This is practically a textbook example of why triggers are necessary to retain relational integrity. -Josh Berkus
Re: [SQL] substring ..
Jeff,
> i want to do this to a datetime field..
>
> select foo from table where substr(datefoo,1,11) = '2000-12-14';
>
> it returns no results yet..
>
> select substr(datefoo,1,11) does return some values that say
> 2000-12-14
Well, for one it's not a string, it's a datetime field.
WHy are you trying to substring a datetime field, anyway?
-Josh
--
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
Complete information technology [EMAIL PROTECTED]
and data management solutions (415) 436-9166
for law firms, small businesses fax 436-0137
and non-profit organizations. pager 338-4078
San Francisco
Re: [SQL] Invoice number
Kaare, > How many times have I tried this > mailing list with no > success :-( Hey! It's a peer-to-peer mailing list. You want guarenteed answers, pay for support (I do) > I'm wondering how people creates guaranteed sequential > numbers - in my case > for invoice numbers. > > - Sequences are not rollback'able. > - It seems overkill to have a table just for this. > - What else? Given a full set of business rules for invoice numbers, I could probably throw you a solution. Gods know that I've devised enough invoicing systems in the past. Please post more detail. -Josh Berkus
Re: [SQL] sql/database admin courses
Mr. Vanags,
Please be aware that you e-mailed a PostgreSQL developer mailing list.
If you were looking for courses on Microsoft SQL Server, this is an
inappropriate forum for such requests. You might, however, try
www.infotech.com for Microsoft training.
IF you need formal instruction in PostgreSQL, you are out of luck at
this time. However, any number of institutions may offer it soon, and
O'Reilly has a new book pending that covers many SQL databases ("SQL in
a Nutshell").
-Josh
--
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
Complete information technology [EMAIL PROTECTED]
and data management solutions (415) 436-9166
for law firms, small businesses fax 436-0137
and non-profit organizations. pager 338-4078
San Francisco
[SQL] Re: [PHP] GUI interface
Julio,
> The best I've seen is a web interface -- phpPgAdmin. I think it's from
> phpwizards or it's a project on sourceforge -- search google for it and
> you'll have yourself a link. It allows gui control for most common tasks,
> and you can submit raw sql from it too for more exotic tasks.
Also, pgAccess, a tcl/tk GUI, is excellent although incomplete. I end
up using a combination of pgAccess, KpgSQL, and command line access.
-Josh Berkus
--
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
Complete information technology [EMAIL PROTECTED]
and data management solutions (415) 436-9166
for law firms, small businesses fax 436-0137
and non-profit organizations. pager 338-4078
San Francisco
Re: [SQL] Serials
Mr. Naik, > What's the purpose of Serials in Data types of postgres ? > TIA > Sharmad This topic is more than adequately covered in both the online documentation and Bruce Momjian's book. If language or other problems are preventing you from finding the correct reference, please reply and I'll give you a pointer. Othervwise, RTFM. -Josh Berkus
[SQL] Re: [PHP] Automatic increment
GH, svangemond,
> > Many people do the first SELECT on a form, stuff the
> result in a hidden
> > variable, then do the INSERT in the form's handler.
> This prevents the
> > double- (or triple-) clicking of "submit" resulting in
> extra database
> > inserts.
Actually, that raises a very good question:
If I have a defined sequence 'universal_sq', and I want to
insert rows into a table using 'universal_sq', is there any
reason *not* to use the DEFAULT NEXTVAL('universal_sq')
declaration in the table definition?
For example, if I want to locate the NEXTVAL. do a bunch of
stuff, and then insert the record, does this obligate me to
take the DEFAULT NEXTVAL out of the table and do it the
NEXTVAL insertion in my functions, or is it irrelevant?
-Josh Berkus
[SQL] Three questions regarding PL/PGSQL
Folks, 1. While I am able to use the %TYPE declaration within PL/PGSQL functions, I am unable to use this declaration in the parameters for the function -- I get 'Parse Error at or near "."' 2. When I have a PL/PGSQL function return a custom message using a VARCHAR return value, I get backslashes in front of all of the spaces in the message. 3. Given the odd/weak exception handling within the current Postgres database engine, has anyone developed strategies to make certain that their PL/PGSQL functions do not perform inconsistent updates? If so, can you give some examples? Anybody (Jan?) who can shed some light on the above will receive my enthusiastic gratitude in ASCII text. -Josh Berkus P.S. I'm using Postgres 7.0.2 on SuSE 7.0 and use pgaccess extensively for function editing. P.P.S. My most heartfelt gratitude to Jan Wieck for writing some decent compile error text into the PL/PGSQL compiler, and to Constantin Teodorescu for putting a terrific function editor into pgaccess!
[SQL] Question #4 about PL/PGSQL
Folks, Oh, yes, one more: 4. If I pass a NULL to any of the parameters of a PL/PGSQL function, any (other) VARCHAR parameters are set to NULL as well. Thanks! -Josh
Re: [SQL] Question #4 about PL/PGSQL
Tom, > Not only varchar --- any other parameters, period. And > not only that, > but the result is taken as NULL no matter what you try to > return. Not quite. I tried the following: Parameters: $1=integer, $2=NULL, $3=varchar And I had the function test for nulls. It read the first parameter, but not $3. It did return the string telling me it had found nulls, however. > This is a longstanding deficiency that is fixed by the > new function > manager in 7.1. Damn! When is the "holy grail" of PostgreSQL going to be stable enough to use? Beta3 still has a "not advisable for production" warning, and I'm being tied up in knots by the number of things I need in 7.1. Would it help if I sent more money? :-) -Josh
[SQL] Yet one more question
Folks, ALTER TABLE won't work until 7.1. CUrrently, I have a table that needs one small change, but it's refrenced as a foriegn key by 7 other tables. Any suggestions on how I can make the table change without having to drop and re-create 8 tables? -Josh Berkus
Re: [SQL] Question #4 about PL/PGSQL
Tom, > Damn! When is the "holy grail" of PostgreSQL going to be > stable enough to use? Beta3 still has a "not advisable > for > production" warning, and I'm being tied up in knots by > the > number of things I need in 7.1. Ooops! That may have sounded a little harsh. I am a bit desperate, but that's hardly your fault. Thank you so much for all of your hard work as our "database engine" guru, and for staying up late to answer our questions! -Josh
Re: [SQL] Three questions regarding PL/PGSQL
Jan, > I assume you're trying to do something like > > CREATE FUNCTION myfunc(mytab.x%TYPE, mytab.y%TYPE) ... > > because that's the only way I've found to get this error That's correct. > Indeed, a good idea (for 7.2). Bruce, put it onto TODO > please. Thanks! I'm a little surprised that this hasn't come up before -- after all, why did you include PLSQL-style %TYPE and %ROWTYPE declarations if not for parameters? > Can't reproduce that in 7.1(BETA). Could you send a little > sql snippet reproducing the behaviour? Sure, when I get home. I've a feeling that it's related to the Function handler in 7.0.x storing functions as TEXT. What I'm trying to make sure of is that it's not related to using PGAccess. > Dunno what's exactly meant by that. Up to now we don't have > savepoints and thus, anything done eventually in a PL/pgSQL > trigger or function will allways roll back if a transaction > get's aborted. Single statements (outside transaction block) > have their own transaction, so nothing to worry about. What I'm talking about is how, if an error occurs, the entire function rolls back, not just a selected portion. I can't even include a BEGIN TRANSACTION statment in a function; it errors out on compile. Nor can I return a custom error message in place of a database error. ALso, in other database engines, I've been able to use transactions to prevent the interleaving of conflicting updates on the database server. For example, I have some functions that insert a row into a table and then report back the ID of the new row: INSERT INTO clients ( ... ) VALUES ( ... ); SELECT CURRVAL(client_id) INTO new_client; It's vitally important that another operation on the clients table does not execute between the INSERT and the SELECT CURRVAL. It may be that by creating transactions by default PGSQL functions are alredy doing this; some reassurance on that count would be nice. > Some sql examples would allways help. More later when I get back to my PGSQL server. > Getting better compile error messages (anything else than > "parse error at or near ...") isn't easy in yacc/bison. Of > course, the PL/pgSQL function handler does write some more as > DEBUG messages to the Postmaster log. Unfortunately, these > don't show up at the frontend side and cannot easily get > turned into NOTICE ones because at that time the original > ERROR has already been sent to the client and emitting > NOTICE's then could confuse the fe/be protocol. Hey, just the fact that you spit back "Error on Line 38" cuts my debugging time in half over the SQL handler's "Error at or near ';'" Of course, running a tail on the postmaster log helps, too ... -Josh Berkus -- __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 436-9166 for law firms, small businesses fax 436-0137 and non-profit organizations. pager 338-4078 San Francisco
Re: [SQL] Boolean and Bit
Keith, > This is my first post (so be gentle with me)... Well, yea-hah! Fresh meat, boys! Bring out the branding iron and the Wayne Newton B-sides! > I would like suggestions and examples of adding SQL-92 > data type BIT compatibility to a PostgreSQL schema. First let me ask you: Why do you want a Bit type? What purpose does it serve that INT2 and BOOLEAN do not? I'm not being sarcastic -- this is an important question to ask before creating *any* custom type on any RDBMS. -Josh Berkus
[SQL] Bruce's Book and Built-in Functions
Folks, Well, I now have a copy of Bruce's book on order. Bruce, if your sales have been slow, don't let A-W blame it on the online draft. According to Stacy's, it takes them an average of 7 working days to get Ingram to cough up a new copy, which is twice the normal period for tech books. As a result, they're out of stock a lot. Plus www.postgresql.org could do a little more to promote the book. There's *still* nothing on the web site to tell me the book's in print. HINT, HINT. So, while I wait for my copy ... I can't find in the online docs anywhere a comprehensive list of built-in functions. You know, stuff like CURRVAL() and NOW(). Can anyone point me to such a list? Guessing parameters is getting frustrating! -Josh
[SQL] Re: Boolean and Bit
Keith, > This is a compatibility issue. While I prefer to use > BOOLEAN, this is SQL3 > and not available on the (unfortunately must use) > MS-SQL/MSDE platform. > > My options are to use a CHAR field and re-write my code > for "T" and "F" or > an int field and re-write my code to use "field=0" and > "field<>0" Given that all the MS-SQL BIT field is, is INT1, using INT2 should not be much of a problem. Go ahead an create a custom type based on INT2 and add constraints to prevent any values outside of the range of 0 and 1. This is where the SQL92 DOMAIN (not, as far as I know, available in PGSQL) construction would be useful in PostgreSQL instead of TYPE (Tom?). The problem with TYPE is that you theoretically need to define a whole set of operators for your TYPE, while DOMAIN is a bit simpler. > I would like to distribute a script (SQL) file to our > users to update > databases to new versions... obstacles include > BOOLEAN/BIT and the > inconsistent use of BLOB/MEMO/[long]varchar(4096). Well, yes. This is beacause BLOBs are NOT part of the SQL standard and IMHO a bad idea relationally; thus their implementation is entirely proprietary to the RDBMS. The solution is not to use BLOBs. > Distributing schema patches is proving troublesome across > multiple > platforms. Yup. Yer in for a world of pain, sonny. Hope you get paid hourly. -Josh
Re: [SQL] Re: Boolean and Bit
Josh Berkus wrote:
> Well, yes. This is beacause BLOBs are NOT part of the SQL
> standard and IMHO a bad idea relationally; thus their
> implementation is entirely proprietary to the RDBMS. The
> solution is not to use BLOBs.
Ooops. Let me re-state: This is because the *implementation* of BLOBS
is not defined in the SQL standard, and BLOBs are IMHO a bad idea for
relational database design, as they violate Codd's Rules. Thus the
implementation of, anf functions and operators for BLOBs are entirely
proprietary to the RDBMS platform.
The solution to this is not to use BLOBs, but rather to use file system
handles for the location of the binary data on the server. This way,
all you need is DOS-to-UNIX and UNIX-to-DOS translation for the
filesystem handles, something easily accomplished through
string-manipulation functions or stored procedures.
-Josh Berkus
--
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
Complete information technology [EMAIL PROTECTED]
and data management solutions (415) 436-9166
for law firms, small businesses fax 436-0137
and non-profit organizations. pager 338-4078
San Francisco
[SQL] Re: Yet one more question
Justin, > How do you do the "drop and create" of tables? 1. Save table definition as text. 2. Create a duplicate of the table definition as "temp_table" 3. INSERT all of the table records into the temp_table 4. DROP the existing table 5. Re-CREATE the table with the altered definition. 6. INSERT the rows from the temp_table back into the table. Of course, this process fails to preserve SERIAL keys, FORIEGN KEYS, etc. and is somewhat labor intensive. ANybody create a script to do this dynamically? > I use pg_dump -d > something.sql > > Then I use vi/sed/something-else to modify the schema in the dumped > file, > then reload it into postgreSQL with psql -e < something.sql > > /dev/null Thanks. SOunds like a good alternate strategy, although it still blows away our test data. I'd also need to see if our functions survive the dump ... -Josh Berkus -- __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 436-9166 for law firms, small businesses fax 436-0137 and non-profit organizations. pager 338-4078 San Francisco
Re: [SQL] RE: Help with query. (*)
Mike, In that case, you want to use this construction: DELETE FROM a WHERE EXISTS ( SELECT 1 FROM b WHERE b.1 = a.1 AND b.2 = a.2 AND b.3 = a.3 ); Of course, a good primary keying system would make this somewhat less complex ... -Josh Berkus -- __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 436-9166 for law firms, small businesses fax 436-0137 and non-profit organizations. pager 338-4078 San Francisco
Re: [SQL] update help
Tom, > UPDATE t1 SET amount = (select sum(b.amount) from t2 b > WHERE t1.id = b.id); Interesting. I'm used to (not necessarily in PGSQL): UPDATE t1 SET amount = t2ttl.totalamount FROM (SELECT sum(amount) as totalamount, id FROM t2 GROUP BY id) t2ttl WHERE t1.id = t2.id Although this is a subselect in the FROM clause, something we've had trouble with as I recall. -Josh Berkus
[SQL] One Question Answered
Folks- Answered my own question about the backslashes before spaces, in text returned as results from functions: it's a bug in kpsql, one of the interface tools I was using. Somebody might want to forward this to the Interfaces list. -Josh Berkus
Re: [SQL] Selecting Current value from a sequence
Najm
CURRVAL('sequence_name')
For this and other sequence and serial functions, please see the online
version of Bruce's book.
BTW, O'Reilly's "SQL in at Nutshell" also catalogs all PGSQL functions
from ver. 6.5.
-Josh Berkus
--
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
Complete information technology [EMAIL PROTECTED]
and data management solutions (415) 436-9166
for law firms, small businesses fax 436-0137
and non-profit organizations. pager 338-4078
San Francisco
Re: [SQL] Selecting Current value from a sequence
Clayton,
> > Hi all,
> > It is a very simple but I am not able to recall how to do it I
> > just need to find out the current value of a seq. It is very simple
> > select statement but I can't recall it... Help me please before I get
> > myself get fired -:).
> > Regards, Najm
>
> select nextval('nameofseq');
Sorry, no. You want SELECT CURRVAL('sequence_name'). NEXTVAL select
the NEXT value, and increments the sequence in the process.
-Josh Berkus
--
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
Complete information technology [EMAIL PROTECTED]
and data management solutions (415) 436-9166
for law firms, small businesses fax 436-0137
and non-profit organizations. pager 338-4078
San Francisco
Re: [SQL] Selecting Current value from a sequence
Bruce, > > BTW, O'Reilly's "SQL in at Nutshell" also catalogs all PGSQL functions > > from ver. 6.5. > > It does, or only the standard SQL functions? Do they have > PostgreSQL-specific stuff. Both. The book is set up to cover 1) the SQL99 standard, and 2) Specific variants for SQL Server, MySQL, Oracle and PostgreSQL. Unfortunately, the book was somewhat delayed in publication ( + 4 months past list) so that it is a little out of date. Pgsql 6.5 is covered without any notation on what will change/be added in 7.x. Still, since I don't even *have* your book yet, it's better than nothing! :-) -Josh Berkus -- __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 436-9166 for law firms, small businesses fax 436-0137 and non-profit organizations. pager 338-4078 San Francisco
Re: [SQL] PostgreSQL HOWTO
Folks,
Fascinating as this thread is, is the SQL Developers list really the
appropriate place for it? Don't we have a Policy list or something?
-Josh Berkus
--
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
Complete information technology [EMAIL PROTECTED]
and data management solutions (415) 436-9166
for law firms, small businesses fax 436-0137
and non-profit organizations. pager 338-4078
San Francisco
Re: [SQL] Correct Syntax for alter table ..add constraint
Najm, > references age_list(id); > And I get the following error: > flipr=# alter table users > flipr-# add constraint age_fk foreign key(age) references > age_list(id); > NOTICE: ALTER TABLE ... ADD CONSTRAINT will create > implicit trigger(s) > for FOREIGN KEY check(s) > ERROR: referential integrity violation - key > referenced from > users not found in age_list Simple ... you have values in the AGE column that are not in the age_list table. Thus you're in violation of the foriegn key you're trying to establish. -Josh Berkus
Re: [SQL] abstract data types?
Jim, > > I'm trying to figure out what support PostgreSQL > offers for SQL99 > > abstract data types. I'm a little curious why what you're attempting couldn't be done with two columns rather than inventing your own data type. As somebody who often rescues databases gone bad, composite data types have not earned a warm place in my heart ... -Josh Berkus
Re: [SQL] pl/pgsql Limits
Ian,
> That works, but when do you delete the records?
I delete the records:
a) When the user runs the report a second time, with different
parameters.
b) After the user exits, as part of a DB-wide clean-up procedure
(Function) that dumps everything with the user's session key.
-Josh Berkus
--
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
Complete information technology [EMAIL PROTECTED]
and data management solutions (415) 436-9166
for law firms, small businesses fax 436-0137
and non-profit organizations. pager 338-4078
San Francisco
[SQL] Re: abstract data types?
Mr. Reid,
> To answer your question, it is a bit hard to say at the moment as the
> design schema for our project has only just been started. The draft
> versions of the ISO standard that I have seen use an object oriented
> data model, so to me it makes sense to try and keep the database schema
> as close as possible to this (minimise data impedance).
>
> Briefly, at its' simplest the schema will probably use a two tier approach.
Let me preface this by saying that I know squat-all about building
geometric databases. My background is in db's for accounting, billing,
scheduling, and fundraising.
Given that .., over the last 3 months, I have become a believer in C.J.
Date and Fabian Pascal, who point out quite a few ways that
object-oriented and relational approaches to data problems *cannot* be
made to reconcile. See http://www.firstsql.com/dbdebunk for some
examples of their objections.
Of course, Date and Pascal reject Object Oriented approaches entirely,
something I'm not ready to do ... but I do see that trying to build a
database accessable to both a range of OODB tools and relationally
compliant is not achievable.
-Josh Berkus
--
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
Complete information technology [EMAIL PROTECTED]
and data management solutions (415) 436-9166
for law firms, small businesses fax 436-0137
and non-profit organizations. pager 338-4078
San Francisco
[SQL] Request for change in PL/PGSQL function handler
Jan, Tom, etc:
Currently (7.0.3) the PLPGSQL function compiler permits only one RETURN
statment, at the end of the function. RETURN statements placed
elsewhere cause a compile error.
This, combined with the lack of an ELSEIF statement, has forced me into
sometimes 7 levels of nested IF..THEN statements. WHile they work fine,
they're a bit hard to read and edit. For example, say I want to test
for a, b, c, or d sequentially, under the 7.0.3 compiler, I must:
BEGIN
IF a THEN
return_text := 'One';
ELSE
IF b THEN
return_text := 'Two';
ELSE
IF c THEN
return_text := 'Three';
ELSE
IF d THEN
return_text := 'Four';
ELSE
return_text := 'Not Found';
END IF;
END IF;
END IF;
END IF;
RETURN return_text;
END;
As you can see, this kind of structure gets kind of had to read and
maintain for more complex statments. I have two suggested revisions to
the compiler that would make this much easier:
SUGGESTION A: Support of an ELSEIF statement, as:
IF a THEN
return_text := 'One';
ELSEIF b THEN
return_text := 'Two';
ELSIF c THEN
return_text := 'Three';
...etc.
SUGGESTION B: Allow more than one RETURN statment in the function text,
with funciton processing to terminate as soon as a RETURN is reached in
the program logic, but otherwise be ignored:
IF a THEN
RETURN 'One';
END IF;
IF b THEN
RETURN 'Two';
END IF;
...etc.
Both approaches would, from my perspective, make my code easier to read
and maintain. And, of course, you may have already implemented one or
the other in 7.1 (which I have not yet got to run on an alternate port).
Thanks for your hard work and consideration towards us users.
-Josh Berkus
--
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
Complete information technology [EMAIL PROTECTED]
and data management solutions (415) 436-9166
for law firms, small businesses fax 436-0137
and non-profit organizations. pager 338-4078
San Francisco
Re: [SQL] Request for change in PL/PGSQL function handler
Tom, > Say what? > > regression=# create function foo(int) returns int as ' > regression'# begin > regression'# if $1 > 10 then return $1; > regression'# end if; > regression'# return $1 - 1; > regression'# end;' language 'plpgsql'; > CREATE Hmmm? When I've tried creating similar functions, I got from the compiler: Error at or near 'END' I'll try your code above as a test, then try re-modifying some of my own functions. -Josh Berkus -- ______AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 436-9166 for law firms, small businesses fax 436-0137 and non-profit organizations. pager 338-4078 San Francisco
Re: [SQL] Re: abstract data types?
John, > Thanks for your comments. My 2c worth: That was at least $1.50 worth. Teach me to speak 'off the cuff' on this list ... > As > far as the > relationship between the schemas for financial and > spatial information > systems goes, a book I have (on OO database management) > goes so far as > to say "that relational database systems do not > adequately support these > so-called non-standard applications." I'd agree with you, I'm afraid. Most of the "spatial database projects" I've been familiar with involved either: a) completely custom software, or b) *lots* of RAM and processing power, or c) both. > Unfortunately I can't speak from personal > experience - I > don't have any access to it, as at uni we are a Oracle/MS > SQL > Server/mySQL shop, and from my preliminary investigations > none of these > seem to cut it for this task as far as I am concerned :-( A definite No for 2 of the above. MySQL was built to be fast and light, with a minimal feature set. As a semi-certified MS SQL Admin, I can tell you that MS SQL Server isn't up to anything better than a *simple* accounting database. Oracle, on the other hand, claims to do anything. They really have no geometic support? > Interesting. This is a really cool site. Thanks. However > I don't see how > you draw the conclusion from what I have read on this > site "that > object-oriented and relational approaches to data > problems *cannot* be > made to reconcile." C.J. Date here seems to be arguing > more about the > semantics employed in UML modelling, Pascal more about > the quality of > database design. This site does give me the urge to read > up on set > theory - I've forgotten what little I once knew. You're right, that's what's currently on the site. I'm basing my opinion more on the earlier writings of Pascal ... and porbably on my own expereinces. Of course, we could ask him. > In [DAT00] (Section 25.1 pg 863) Date states "we need do > nothing to the > relational model in order to achieve object functionality > in relational > systems - nothing, that is, except implement it, fully > and properly, > which most of today's systems have so signally failed to > do." Yeah. Few systems bother even to fully implement the SQL standard fully ... and SQL 99 was as much a product of politics in the computer industry as logic. For example, I agree with Pascal & Date that BLOBs are a bad idea, and a violation of relational priniciples (being data that cannot be stores as a value in a column in a relation). One need only look at the terrible and persistent implementation problems for BLOB support in various platforms for proof of this. > He then states that "the support is already there [in the > relational > model -jgr], in the shape of domains (which we prefer to > call types > anyway)." > Yeah. Real DOMAIN and TYPE support (which are really two diffetent things, a Domain being a specification for a more general Type) in Postgres would be teriffic. How about it, Tom, Stephen? > Chapter 1, pg 6). Interesting, I just noticed the > statement "is truly > relational (unlike SQL)."! Yes -- see my comments above. Market pressues and politics have caused the ISO to abandon relational standards in formulating the SQL standard in many areas. > Sorry, disagree strongly here. Ok. I'm probably just biased, anyway, from being burned by DB tools claiming both OO and SQL-relational support. > As far as I can tell, PostgreSQL has most, if not all, of > the building > blocks to supply support for abstract data types already > in place. > Whoever thought up the system catalogs (as well) was one > very smart > individual. Salutations, whoever you are! I'd definitely stand back and applaud any effort to support this. When I first started with PostgreSQL, I thought it was a really nifty idea, until I tried to build a database on it. Puls I soon discovered that nifty ideas do not a payment-processing database make :-( > Any help people can give me would be much appreciated. > I'm already > feeling a little lost. I hope people don't mind if I ask > a lot of dumb > questions over the next few weeks :-) Is this the > appropriate list, or > should I move over to hackers? You should probably cross-post. This list is the place to see if a number of other developers are interested in the functionality you propose (yes), hackers is probably the place to ask how to make the actual changes. I can't help. Heck, I can't even get 7.1 beta to run on an alternate port. -Josh Berkus P.S. BTW, John, I'm thrilled to get a discussion of issues, going here in addition to the how-tos!
[SQL] Four Odd Questions
Folks,
1. Has anyone had experience with trying to link Informix's 4GL as a
procedural language extension for PostgreSQL? ANyone care to
speculate? I happen to have access to a couple of former Informix
employees ...
2. Is there any documentation on the SQL changes being incorporated into
7.1? We've talked about some of them on this list, but I'm still not
sure what the syntax for "ALTER TABLE" will be, for example.
3. pg_dump and Restore is currently a bit awkward, and requires the
intervention of a developer to get the database running properly again.
Are there plans to improve this, or has somebody written a script that
handles the steps involved?
4. I'm not trying 7.1 beta 3. I noticed that for this version,
Theodescu's PGAccess lists all builtin functions along with the
user-defined functions in the functions window. Anybody else notice
this?
-Josh Berkus
--
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
Complete information technology [EMAIL PROTECTED]
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco
Re: [SQL] Hrm...why is this wrong?
Tom, Ken, > There should be a more direct way of doing this, but for > now, the > postmaster logfile is the best recourse ... > > regards, tom lane I've also found that if, when debugging, you launch postmaster from a kconsole and leave the process running in the foreground, you see all sorts of useful debugging info as execution takes place. Not quite as complete, but a *lot* faster than checking the log manually. -Josh __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 565-7293 for law firms, small businessesfax 621-2533 and non-profit organizations. San Francisco
[SQL] Directional join syntax in 7.1?
Tom, What's the syntax for directional joins in 7.1 beta? Thanks! -Josh Berkus __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 565-7293 for law firms, small businessesfax 621-2533 and non-profit organizations. San Francisco
[SQL] Never mind (Directional Joins)
Tom, Sorry! Never mind, I found it in the Development Docs. Grazie! -Josh __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 565-7293 for law firms, small businessesfax 621-2533 and non-profit organizations. San Francisco
[SQL] JOIN chaining not working in 7.1 beta 3
Tom, Stephen, I'm trying to parse a query like the following and keep getting various errors (I'd give you the real query but &%^$# Netscape won't do cut-and-paste): SELECT a.1, b.2, c.14, a.2, c.5 FROM a INNER JOIN b ON a.1=b.3 LEFT OUTER JOIN c on a.1=c.2; And I get: ERROR: JOIN/ON CLAUSE REFERS TO 'c' WHICH IS NOT PART OF JOIN. What's wrong here? -Josh Berkus __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 565-7293 for law firms, small businessesfax 621-2533 and non-profit organizations. San Francisco
Re: [SQL] PL/PGSQL function with parameters
Mr. Richter, > I wrote that function, wich doesn't work. I want to hand > over the name > of the tables(relation_table, update_table) and a > column(column_to_fill). The intention is, to use the > function also with > other tables(not hard coded). 1. Try using type VARCHAR instead of TEXT for the parameters. PL/pgSQL may be objecting to the "undefined" size of TEXT. 2. I don't believe that you can supply variables in the place of object names in PL/pgSQL, only in place of values. Thus, "SELECT * FROM table_name" would be invalid. (Jan, please tall me if I'm wrong in this; I could really use the functionality if it *is* possible.) You could use some creative manipulation of the system tables to achieve the same result, however. -Josh Berkus __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 565-7293 for law firms, small businessesfax 621-2533 and non-profit organizations. San Francisco
Re: [SQL] PL/PGSQL function with parameters
Mike, Jan, > Michael Ansley wrote: > > With the latest release, I think you can do: > > EXEC ''SELECT * FROM '' || $1; > > or > > DECLARE SQL VARCHAR; > ... > SQL = ''SELECT * FROM '' || $1; > EXEC SQL; > > or something similar (it may be EXECUTE), which uses the dynamic sql > elements of plpgsql. I think. Is this true, Jan? Does anyone have more specific documentation? -Josh Berkus -- __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco
Re: [SQL] PL/PGSQL function with parameters
Tom, Jan, Michael, > While I have not looked closely, I seem to recall that plpgsql handles > INTO by stripping that clause out of the statement before it's passed to > the SQL engine. Evidently that's not happening in the EXECUTE case. > > Jan, do you agree this is a bug? Is it reasonable to try to repair it > for 7.1? If we do not change the behavior of EXECUTE now, I fear it > will be too late --- some people will come to depend on the existing > behavior. If you think that's the best way. What we're really all wanting is a wy in PL/pgSQL to pass a parameter as an object name. Doing it *without* using EXECUTE would be even better than modifying EXECUTE to accomdate SELECT ... INTO variable. If we can write queries that address tables by OID, that would give us a quick workaround ... get the OID from pg_class, then pass it to the query as variables of type OID: SELECT column1_oid, column2_oid FROM table_oid WHERE column2_oid = variable1 ORDER BY column1_oid; OF course, having PL/pgSQL do this automatically would be even better, but I suspect would require a *lot* of extra programming by Jan. And all of this should be influenced by whatever you guys are planning to do about Stored Procedures. -Josh Berkus -- __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco
Re: [SQL] how to do plpgsql?
Joseph, First you need to install plpgsql on a per database basis, or you can just install it on template1 and it will get added to all new databases. CREATE FUNCTION "plpgsql_call_handler" ( ) RETURNS opaque AS '/usr/lib/pgsql/plpgsql.so' LANGUAGE 'C'; CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql' HANDLER "plpgsql_call_handler" LANCOMPILER 'PL/pgSQL'; -Josh Berkus (Instructions courtesy of Jeff at PGSQL Inc.) -- __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco
[SQL] Is this a bug, or is it just me?
Tom et al. Discovered this quirk in foriegn keys: In the preliminary version of a database, I added foriegn key constraints to a number of tables, linking them to a column in a shared reference table (status.status) that was only one-half of a composite primary key (and thus the values were not unique). When I tried to delete a row containing a "2" in the status column from the status relation, I received a Foreign Key violation error event though there were other "2"'s in the table still present. So ... is this a bug in forign key implementation, or just my fault for keying off a non-unique value? And, if the latter, is there a way I can construct a foreign key constraint that keys onto a view or query? Grazie! -Josh Berkus __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 565-7293 for law firms, small businessesfax 621-2533 and non-profit organizations. San Francisco
