Re: [SQL] Constraint on 2 column possible?
On Jan 27, 2005, at 6:44 AM, Andrei Bintintan wrote: Hi, I have a table: CREATE TABLE werke1( id SERIAL, id_hr int4 NOT NULL, id_wk int4 NOT NULL ); CREATE TABLE contact( id SERIAL, type varchar(20), ); It looks like you want a two-column primary key for table contact and then you can reference contact(id,type). Sean Now id_hr and id_wk are all referencing the same table contact(id). In the contact table I have another column called type. How can I write a constraint that checks that id_hr references contact(id) and the contact(type='t1') and that id_wk references contact(id) and the contact(type='t2'). More explicit: the id_hr shows to the id from contact, and this line from contact must have the line type='t1'. The same for id_wk just the type is another. I can write: CREATE TABLE werke1( id SERIAL, id_hr int4 NOT NULL references contact(id), id_wk int4 NOT NULL references contact(id) ); but how do I check also the type column? Best regards, Andy. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] What's the equivalent in PL/pgSQL
See this section of the manual: http://www.postgresql.org/docs/8.0/interactive/plpgsql-control- structures.html In particular, look at 35.7.1.2 and 35.7.4. I think you need to loop through the results of the query in order to return them. If you just want to return the result set as a whole to another function that needs to work with the data, you can use a cursor. Sean On Jan 27, 2005, at 7:46 AM, KÖPFERL Robert wrote: Hi, I'm trying to find an equivalent plpgsql function as this: func x returns SETOF "Tablename" AS ' Select * from "Tablename"; ' language sql How is this accomplished with plpgsql while not using a loop or a second and third temporal table? ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] URL activation through trigger
Sandeep, Using pl/perl at least, you could probably do this. My guess is that pl/python (and perhaps pl/R) would offer similar functionality. At least for pl/perl, I think you would need to use the untrusted version and use a module like LWP. I haven't tried this, but I think it should be possible (and fairly easy to do). Perhaps others can add some more detail. Sean On Jan 27, 2005, at 9:38 AM, Sandeep Gaikwad wrote: Hi, I am using Postgres 7.3.4. I want to write a trigger for insert, update, delete operation on say, user table. This trigger will activate an URL. URL will be as: http://sandeep/Trigger?userid=4&name=sandeep where, userid and password will be parameters to send. They may taken from database say, user table. Is this possible ? How can I do that ? Thanks & Regards, Sandeep - Disclaimer: The contents of this message are confidential and intended to the addressee at the specified e-mail address only. Its contents may not be copied or disclosed to anyone other than the intended recipient. If this e-mail is received in error, please contact Vertex Software Pvt. Ltd immediately on +91 20 4041500 with details of the sender and addressee and delete the e-mail. Vertex Software Pvt. Ltd accepts no responsibility in the event that the onward transmission, opening or use of this message and/or any attachments adversely affects the recipient's systems or data. It is the recipient's responsibility to carry out such virus and other checks as the recipient considers appropriate. - ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] What's the equivalent in PL/pgSQL
On Jan 27, 2005, at 11:10 AM, KÖPFERL Robert wrote: That's bad. Is there really no ohter way? So it takes TWO termoral tables. Or even more? I'm not sure what is bad. In what sense is it bad? How does more than one table come into things? And it can't be just sql because theres more around that statement. I'm not sure what you mean. Which statement? -Original Message----- From: Sean Davis [mailto:[EMAIL PROTECTED] Sent: Donnerstag, 27. Jänner 2005 14:57 To: KÖPFERL Robert Cc: [email protected] Subject: Re: [SQL] What's the equivalent in PL/pgSQL See this section of the manual: http://www.postgresql.org/docs/8.0/interactive/plpgsql-control- structures.html In particular, look at 35.7.1.2 and 35.7.4. I think you need to loop through the results of the query in order to return them. If you just want to return the result set as a whole to another function that needs to work with the data, you can use a cursor. Sean On Jan 27, 2005, at 7:46 AM, KÖPFERL Robert wrote: Hi, I'm trying to find an equivalent plpgsql function as this: func x returns SETOF "Tablename" AS ' Select * from "Tablename"; ' language sql How is this accomplished with plpgsql while not using a loop or a second and third temporal table? ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 8: explain analyze is your friend ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] getting back autonumber just inserted
On Feb 3, 2005, at 5:16 PM, lorid wrote: I could have sworn I kept a copy of prior emails that discussed how to get back a value that was just inserted into a autonumber (or in postgresql case a sequence number) See here: http://www.postgresql.org/docs/8.0/interactive/functions- sequence.html#FUNCTIONS-SEQUENCE-TABLE Sean ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [PERFORM] [SQL] OFFSET impact on Performance???
On Jan 26, 2005, at 5:36 AM, Leeuw van der, Tim wrote: Hi, What you could do is create a table containing all the fields from your SELECT, plus a per-session unique ID. Then you can store the query results in there, and use SELECT with OFFSET / LIMIT on that table. The WHERE clause for this temp-results table only needs to contain the per-session unique id. This is what I do, but I use two columns for indexing the original query, a user_id (not session-id) and an index to the "query_id" that is unique within user. This "query_id" is a foreign key to another table that describes the query (often just a name). I allow the user only a fixed number of "stored" queries and recycle after hitting the maximum. You can timestamp your queries so that when you recycle you drop the oldest one first. If you don't need multiple stored query results, then using the user_id is probably adequate (assuming the user is not logged on in several locations simultaneously). This of course gives you a new problem: cleaning stale data out of the temp-results table. And another new problem is that users will not see new data appear on their screen until somehow the query is re-run (... but that might even be desirable, actually, depending on how your users do their work and what their work is). See above. The query refresh issue remains. And of course better performance cannot be guaranteed until you try it. For the standard operating procedure of perform query===>view results, I have found this to be a nice system. The user is accustomed to queries taking a bit of time to perform, but then wants to be able to manipulate and view data rather quickly; this paradigm is pretty well served by making a separate table of results, particularly if the original query is costly. Would such a scheme give you any hope of improved performance, or would it be too much of a nightmare? This question still applies Sean -Original Message- From: [EMAIL PROTECTED] on behalf of Andrei Bintintan Sent: Wed 1/26/2005 11:11 AM To: [EMAIL PROTECTED]; Greg Stark Cc: Richard Huxton; [email protected]; [email protected] Subject: Re: [PERFORM] [SQL] OFFSET impact on Performance??? The problems still stays open. The thing is that I have about 20 - 30 clients that are using that SQL query where the offset and limit are involved. So, I cannot create a temp table, because that means that I'll have to make a temp table for each session... which is a very bad ideea. Cursors somehow the same. In my application the Where conditions can be very different for each user(session) apart. The only solution that I see in the moment is to work at the query, or to write a more complex where function to limit the results output. So no replace for Offset/Limit. Best regards, Andy. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] More efficient OR
Could 'in' or 'between' do what you want? I know that using 'in' is equivalent to what you have below. Could 'between' be more efficient--you could do explain analyze on various options to see what the actual plan would be. Sean On Feb 16, 2005, at 11:02 AM, Keith Worthington wrote: Hi All, In several of my SQL statements I have to use a WHERE clause that contains mutiple ORs. i.e. WHERE column1 = 'A' OR column1 = 'B' OR column1 = 'C' Is there a more efficient SQL statement that accomplishes the same limiting functionality? Kind Regards, Keith ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Software for database-visualisation
If you mean literally visualizing the ERD, you can look at SQL::Translator (on cpan) which can draw fairly complex ERDs and output as graphics (I forget the supported formats) Sean On Feb 24, 2005, at 3:17 AM, Richard Huxton wrote: Kai Hessing wrote: Another question: Which software are you using to visualize your database-structur. We're doing it with Quark, but are not very happy with this. Well, AutoDoc can generate HTML/Dia/other outputs http://www.rbt.ca/autodoc/index.html Might be worth checking the (freely available) Red-Hat db tools. Can't remember if there's a schema visualiser in there, but there might well be. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Junk queries with variables?
- Original Message - From: "Steve Valaitis" <[EMAIL PROTECTED]> To: "KÖPFERL Robert" <[EMAIL PROTECTED]>; Sent: Thursday, February 24, 2005 12:15 PM Subject: Re: [SQL] Junk queries with variables? > In pgadmins SQL-window SQL is the 'language' of choice. Or it is rather the only language. Thus if you intend to program plTk or PL/pgSQL, there's no way around defining a function. (At first you have to define a new language in your schema) I'm a little confused, is there no way around this, or are you saying I need to use CREATE LANGUAGE to define a new language to use? Currently the only language I have for the DB is plpgsql. Yes. You need to install the language into the database using CREATE LANGUAGE and then you can use the language to create functions. Sean ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] how to speed up these queries ?
On Mar 3, 2005, at 6:05 AM, Dracula 007 wrote:
Hello,
I have two "large" tables - "sessions" (about 1.500.000 rows) and
"actions" (about 4.000.000 rows), and the "actions" table is connected
to the "sessions" (it contains a key from it). The simplified structure
of these tables is
sessions (
session_id int4,
visitor_id int4,
session_ip inet,
session_date timestamp
)
actions (
action_id int4,
session_id int4, -- foreign key, references sessions(session_id)
action_date timestamp,
action_year int2,
action_month int2,
action_day int2
)
I run SQL queries like
SELECT
COUNT(actions.session_id) AS sessions_count,
COUNT(DISTINCT visitor_id) AS visitors_count,
COUNT(DISTINCT session_ip) AS ips_count
FROM actions LEFT JOIN sessions USING (session_id)
GROUP BY action_year, action_month, action_day
but it's really really slow. I've tried to use different indexes on
different columns, but no matter what I've tried I can't get it
faster. The explain analyze of the query is
--
Aggregate (cost=347276.05..347276.05 rows=1 width=23) (actual
time=210060.349..210060.350 rows=1 loops=1)
-> Hash Left Join (cost=59337.55..305075.27 rows=4220077
width=23) (actualtime=24202.338..119924.254 rows=4220077 loops=1)
Hash Cond: ("outer".session_id = "inner".session_id)
-> Seq Scan on actions (cost=0.00..114714.77 rows=4220077
width=8) (actual time=7539.653..44585.023 rows=4220077 loops=1)
-> Hash (cost=47650.64..47650.64 rows=1484764 width=19)
(actual time=16628.790..16628.790 rows=0 loops=1)
-> Seq Scan on sessions (cost=0.00..47650.64
rows=1484764 width=19) (actual time=0.041..13378.667 rows=1484764
loops=1)
It looks like you are going to always do a sequential scan on the
tables, as you always look a the entire table(s). How often do you do
the query as compared to the load on the database? If you do the query
often relative to the load, could you keep a table of counts something
like:
create table summarize_use (
action_date date,
sessions_count int,
visitors_count int,
isp_count int)
and then use triggers from the sessions and actions to increment the
various counts in the summarize_use table based on the action_date and
session_date date parts? The summarize_use table would then look like:
action_date sessions_count visitors_count ips_count
3-2-200515 12
12
Just a thought, and of course you would pay the price of triggers with
each insert to the sessions or actions table.
If you don't like that and you run this say every night at midnight,
you could set up a cron job that ran this query and selected it into a
table for direct querying--call this table summarize_use again. Then,
to get the full result, you would need to simply do something like:
select * from summarize_use
union
SELECT
COUNT(actions.session_id) AS sessions_count,
COUNT(DISTINCT visitor_id) AS visitors_count,
COUNT(DISTINCT session_ip) AS ips_count
FROM actions LEFT JOIN sessions USING (session_id)
WHERE
action_year=2005 AND
action_month=3 AND
action_day=3;
This would then be fully up-to-date and would use indices on
action_year, action_month, action_day. DISCLAIMER--All of this is
untested
Sean.
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Building a database from a flat file
On Mar 2, 2005, at 7:15 PM, Casey T. Deccio wrote: A database I am currently using is built and updated periodically from a flat csv file (The situation is rather unfortunate, but that's all I have right now). The schema I use is more complex than the flat file, so I follow a process to populate the tables with the data from the file. First I slurp the whole file into one temporary table, whose columns correspond to the columns in the file. Then I DELETE all the existing rows from the tables in the schema and perform a series of queries on that table to INSERT and UPDATE rows in the tables that are in the schema. Then I DELETE the data from the temporary table. I do it this way, rather than trying to synchronize it, because of the inconsistencies and redundancies in the flat file. There is more than one problem with this, but the largest is that I would like to perform this whole database rebuild within one transaction, so other processes that need to access the database can do so without noticing the disturbance. However, performing this set of events (besides populating the temporary table) within a single transaction takes a long time--over an hour in some cases. What are some suggestions to help improve performance with replacing one set of data in a schema with another? Why not rebuild the entire thing in a separate "build" schema then do only the stuff like copying tables inside the transaction block: BEGIN; truncate table1; truncate table2; --reset any sequences you feel you need to have reset; select * into table1 from build.table1; select * into table2 from build.table2; vacuum analyze table1; vacuum analyze table2; COMMIT; I haven't tried this method exactly, but building in a separate schema (expensive) and then doing cheap operations like copying the table into the working schema should minimize the amount of time you spend inside the transaction block. **I don't know what effect this will have on performance of the whole process, though**. Sean ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] how to speed up these queries ?
On Mar 3, 2005, at 8:34 AM, Dracula 007 wrote: It looks like you are going to always do a sequential scan on the tables, as you always look a the entire table(s). How often do you do the query as compared to the load on the database? If you do the query often relative to the load, could you keep a table of counts something like: create table summarize_use ( action_date date, sessions_count int, visitors_count int, isp_count int) and then use triggers from the sessions and actions to increment the various counts in the summarize_use table based on the action_date and session_date date parts? The summarize_use table would then look like: I can't exactly predict how often these queries will be executed - this is an example of one of many different queries that are executed over these two tables (in these two tables the main part of statistics of our web application is stored). And for every query there can be different parameter values etc. It definitely will be less than 1% of all queries run on the server, but the problem is that running of it consumes most of the cpu, so all the other queries have to wait and timeouts. And we don't want the visitors on the web to wait ... As a temporary solution we build all the possible results once a week (every Monday on 00:00) and store it in a separate tables, so it's pretty fast to view, but it takes about 3 hours to rebuild all the possible stats (total, by month, by day, by week) for different view (pages, articles, visitors, etc). I still hope I'll be able to speed it up somehow. The solution using a triggers looks nice, I'll try that and it probably will work, but I can't predict how complicated it will be to log all the interesting stats. t.v. If your group by is at its finest grain only daily, then the most you will run these queries is daily, correct? Could you try some of your queries doing: select BLAH,BLAH,BLAH from BLAH where action_year=2005 and action_day=3 and action_month=3 after building indices on the columns? If this is fast (which it very well may be), then you can simply run that set of queries daily and insert the result into your "big stats" table, as presumably all other data in the "big stats" table is static if the date has past. That would save you writing the triggers, which could be complicated from what you are saying Sean ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Building a database from a flat file
On Mar 3, 2005, at 1:37 PM, Casey T. Deccio wrote:
On Thu, 2005-03-03 at 08:28 -0700, Markus Schaber wrote:
- Create the new date in another schema, and then simply rename those
two schemas for "switch over"
This worked very well. I created another schema ("build") and
populated
the tables within build. Then:
BEGIN;
ALTER SCHEMA public RENAME TO public_old;
ALTER SCHEMA build RENAME TO public;
COMMIT;
/* remove data from tables in build schema... */
It was very quick and seamless.
Question: is there an "easy" way to duplicate an existing schema
(tables, functions, sequences, etc.)--not the data; only the schema?
This way, I would only need to modify one schema (public) to make
changes, and the build schema could be created each time as a duplicate
of the public schema. Maintenance would be much simpler.
Why not just create a dump of your schema (without data), drop the
schema, rebuild it from the dump and then populate as normal. Then do
the name switch.
Sean
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
Re: [SQL] count array in postgresql
How about array_dims? It looks like you will still have to do a string
split, but
Sean
- Original Message -
From: "bandeng" <[EMAIL PROTECTED]>
To:
Sent: Sunday, March 06, 2005 9:09 PM
Subject: Re: [SQL] count array in postgresql
my Postgresql version is 7.4
I still cannot find count function in that reference. but I try to use
like this just checking the array value is null or not, it's work but
dunno it is the good way or not.
for example,
vararray := {ab,cd,ef,gh}
i := 1;
while vararray[i] is not null loop
i := i + 1;
end loop;
raise info i;
On Sun, 6 Mar 2005 12:10:55 -0700, Michael Fuhr <[EMAIL PROTECTED]> wrote:
On Sun, Mar 06, 2005 at 11:54:15PM +0700, bandeng wrote:
> I need some help, I have created function with argument in array.
> I already try to find array function in manual but failed.
Maybe you're looking for "Array Functions and Operators" in the
"Functions and Operators" chapter. You don't say what version of
PostgreSQL you're using; here's a link to the 8.0 documentation:
http://www.postgresql.org/docs/8.0/interactive/functions-array.html
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
--
Gutten Aben Sugeng Sonten, Jangane Kurang Santen
bandeng
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Newbie wonder...
Bernard, If you are simply doing a one-time convert of an old database schema to a new one, simply load the old tables into postgres and then use SQL commands to insert the data into the new tables. For a sequence on the "existing table," you can do as above and load the old table or just use copy with the column names of all columns except the column with the serial values--these will be auto-incremented. Sean - Original Message - From: "Bernard Grosperrin" <[EMAIL PROTECTED]> To: Sent: Tuesday, March 08, 2005 6:25 PM Subject: [SQL] Newbie wonder... Please, bear with me, as this is my first post here. (1) I have a 2 table database, result of a conversion from Access. This has been made by an amateur, as one of the tables should be at least 3 related tables, bunch of redundant data,and the other one 2. I know I could create a table as the result of a request, so that I could isolate these redundant data, but what I don't know is how I would in the same time update the original table to put the ID of the matching ROW number in the newly created table, instead of the redundant data ? Should I create a stored procedure for that, and if yes, how ? Or should I do that in 2 passes, sequentially ? (2) How should I go to create a sequence for an existing table? For all futures data entry, after this conversion, I want the unique ID for each row to come from a sequence, but if I know how to create a table using serial, I am not sure how to modify one for this. Thanks, Bernard ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] "Flattening" query result into columns
Thomas, You probably want a crosstab. There is a contributed module in contrib/crosstab. If you do a search of the postgres mailing lists, there will be several posts relating to the same issue. Sean - Original Message - From: Thomas Borg Salling To: [email protected] Sent: Monday, March 21, 2005 4:57 PM Subject: [SQL] "Flattening" query result into columns I am looking for a way to flatten a query result, so that rows are transposed into columns, just as asked here for oracle: http://groups.google.dk/groups?hl=da&lr=&client=firefox-a&rls=org.mozilla:en-US:official&selm=aad10be0.0401292322.7b6c320b%40posting.google.com Is there any way to do this with pgsql ? Thanks, /Thomas.
[SQL] Self-referencing table question
I have a table that looks like: Column | Type | Modifiers | Description -+--+---+- from_id | integer | not null | to_id | integer | not null | val | numeric(4,3) | | Indexes: "correlation_pkey" PRIMARY KEY, btree (from_id, to_id) "correlation_from_id_idx" btree (from_id) "correlation_to_id_idx" btree (to_id) "correlation_val_idx" btree (val) Has OIDs: yes The table describes a pairwise correlation matrix between about 7700 vectors (so the table has n^2= 60652944 rows, to be exact). I am trying to choose the top 100 correlated vectors with a seed vector; this is easily: select to_id from correlation where from_id=623 order by val desc limit 100; Then, I want to take those 100 values and find all from_id,to_id tuples where val>0.5 (to construct a graph where all "ids" are nodes and are connected to each other when their correlation is >0.5). I can do this like: explain analyze select from_id,to_id,val from exprsdb.correlation where from_id in (1,2,3,4,5,6,7,8,10,9,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27 ,28,29,30) and to_id in (1,2,3,4,5,6,7,8,10,9,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27 ,28,29,30) and from_id>to_id and val>0.5; However, this does not scale well AT ALL. The actual (very messy) explain analyze output is below. The thing I notice is that the index on to_id is not used. Also, the primary key index on (from_id, to_id is not used, it seems. Finally, with only 30 values, this already takes 2.6 seconds and I am proposing to do this on 100-200 values. Any hints on how better to accomplish this set of tasks? Index Scan using correlation_from_id_idx, correlation_from_id_idx, correlation_from_id_idx, correlation_from_id_idx, correlation_from_id_idx, correlation_from_id_idx, correlation_from_id_idx, correlation_from_id_idx, correlation_from_id_idx, correlation_from_id_idx, correlation_from_id_idx, correlation_from_id_idx, correlation_from_id_idx, correlation_from_id_idx, correlation_from_id_idx, correlation_from_id_idx, correlation_from_id_idx, correlation_from_id_idx, correlation_from_id_idx, correlation_from_id_idx, correlation_from_id_idx, correlation_from_id_idx, correlation_from_id_idx, correlation_from_id_idx, correlation_from_id_idx, correlation_from_id_idx, correlation_from_id_idx, correlation_from_id_idx, correlation_from_id_idx, correlation_from_id_idx on correlation (cost=0.00..129377.49 rows=62 width=17) (actual time=340.563..2603.967 rows=19 loops=1) Index Cond: ((from_id = 1) OR (from_id = 2) OR (from_id = 3) OR (from_id = 4) OR (from_id = 5) OR (from_id = 6) OR (from_id = 7) OR (from_id = 8) OR (from_id = 10) OR (from_id = 9) OR (from_id = 11) OR (from_id = 12) OR (from_id = 13) OR (from_id = 14) OR (from_id = 15) OR (from_id = 16) OR (from_id = 17) OR (from_id = 18) OR (from_id = 19) OR (from_id = 20) OR (from_id = 21) OR (from_id = 22) OR (from_id = 23) OR (from_id = 24) OR (from_id = 25) OR (from_id = 26) OR (from_id = 27) OR (from_id = 28) OR (from_id = 29) OR (from_id = 30)) Filter: (((to_id = 1) OR (to_id = 2) OR (to_id = 3) OR (to_id = 4) OR (to_id = 5) OR (to_id = 6) OR (to_id = 7) OR (to_id = 8) OR (to_id = 10) OR (to_id = 9) OR (to_id = 11) OR (to_id = 12) OR (to_id = 13) OR (to_id = 14) OR (to_id = 15) OR (to_id = 16) OR (to_id = 17) OR (to_id = 18) OR (to_id = 19) OR (to_id = 20) OR (to_id = 21) OR (to_id = 22) OR (to_id = 23) OR (to_id = 24) OR (to_id = 25) OR (to_id = 26) OR (to_id = 27) OR (to_id = 28) OR (to_id = 29) OR (to_id = 30)) AND (from_id > to_id) AND (val > 0.5)) Total runtime: 2604.383 ms Thanks, Sean ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Self-referencing table question
I answer my own question, if only for my own records. The following
query is about 5-6 times faster than the original. Of course, if
anyone else has other ideas, I'd be happy to hear them.
Sean
explain analyze select from_id,to_id,val from exprsdb.correlation where
from_id in (select to_id from exprsdb.correlation where from_id=2424
order by val desc limit 100) and to_id in (select to_id from
exprsdb.correlation where from_id=2424 order by val desc limit 100) and
val>0.6 and to_id
QUERY PLAN
Hash IN Join (cost=4709.94..74758.01 rows=555 width=17) (actual
time=110.291..1671.767 rows=973 loops=1)
Hash Cond: ("outer".to_id = "inner".to_id)
-> Nested Loop (cost=2354.97..72181.72 rows=43154 width=17)
(actual time=54.036..1612.746 rows=1482 loops=1)
-> HashAggregate (cost=2354.97..2354.97 rows=100 width=4)
(actual time=53.656..54.062 rows=100 loops=1)
-> Subquery Scan "IN_subquery" (cost=2353.47..2354.72
rows=100 width=4) (actual time=53.473..53.595 rows=100 loops=1)
-> Limit (cost=2353.47..2353.72 rows=100
width=13) (actual time=53.469..53.507 rows=100 loops=1)
-> Sort (cost=2353.47..2415.03 rows=24624
width=13) (actual time=53.467..53.481 rows=100 loops=1)
Sort Key: val
-> Index Scan using
correlation_from_id_idx on correlation (cost=0.00..557.42 rows=24624
width=13) (actual time=0.199..17.717 rows=7788 loops=1)
Index Cond: (from_id = 2424)
-> Index Scan using correlation_from_id_idx on correlation
(cost=0.00..692.87 rows=432 width=17) (actual time=2.765..15.560
rows=15 loops=100)
Index Cond: (correlation.from_id = "outer".to_id)
Filter: ((val > 0.6) AND (to_id < from_id))
-> Hash (cost=2354.72..2354.72 rows=100 width=4) (actual
time=56.239..56.239 rows=0 loops=1)
-> Subquery Scan "IN_subquery" (cost=2353.47..2354.72
rows=100 width=4) (actual time=56.004..56.121 rows=100 loops=1)
-> Limit (cost=2353.47..2353.72 rows=100 width=13)
(actual time=56.001..56.038 rows=100 loops=1)
-> Sort (cost=2353.47..2415.03 rows=24624
width=13) (actual time=55.999..56.012 rows=100 loops=1)
Sort Key: val
-> Index Scan using correlation_from_id_idx
on correlation (cost=0.00..557.42 rows=24624 width=13) (actual
time=0.517..20.307 rows=7788 loops=1)
Index Cond: (from_id = 2424)
Total runtime: 1676.966 ms
On Mar 22, 2005, at 2:33 PM, Sean Davis wrote:
I have a table that looks like:
Column | Type | Modifiers | Description
-+--+---+-
from_id | integer | not null |
to_id | integer | not null |
val | numeric(4,3) | |
Indexes:
"correlation_pkey" PRIMARY KEY, btree (from_id, to_id)
"correlation_from_id_idx" btree (from_id)
"correlation_to_id_idx" btree (to_id)
"correlation_val_idx" btree (val)
Has OIDs: yes
The table describes a pairwise correlation matrix between about 7700
vectors (so the table has n^2= 60652944 rows, to be exact). I am
trying to choose the top 100 correlated vectors with a seed vector;
this is easily:
select to_id from correlation where from_id=623 order by val desc
limit 100;
Then, I want to take those 100 values and find all from_id,to_id
tuples where val>0.5 (to construct a graph where all "ids" are nodes
and are connected to each other when their correlation is >0.5). I
can do this like:
explain analyze select
from_id,to_id,val
from exprsdb.correlation
where from_id in
(1,2,3,4,5,6,7,8,10,9,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,2
7,28,29,30)
and to_id in
(1,2,3,4,5,6,7,8,10,9,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,2
7,28,29,30)
and from_id>to_id
and val>0.5;
However, this does not scale well AT ALL. The actual (very messy)
explain analyze output is below. The thing I notice is that the index
on to_id is not used. Also, the primary key index on (from_id, to_id
is not used, it seems. Finally, with only 30 values, this already
takes 2.6 seconds and I am proposing to do this on 100-200 values.
Any hints on how better to accomplish this set of tasks?
Index Scan using correlation_from_id_idx, correlation_from_id_idx,
correlation_from_id_idx, correlation_from_id_idx,
correlation_from_
Re: [SQL] Self-referencing table question
- Original Message - From: "Richard Huxton" To: "Sean Davis" <[EMAIL PROTECTED]> Cc: "PostgreSQL SQL" Sent: Tuesday, March 22, 2005 3:59 PM Subject: Re: [SQL] Self-referencing table question Sean Davis wrote: I answer my own question, if only for my own records. The following query is about 5-6 times faster than the original. Of course, if anyone else has other ideas, I'd be happy to hear them. Sean explain analyze select from_id,to_id,val from exprsdb.correlation where from_id in (select to_id from exprsdb.correlation where from_id=2424 order by val desc limit 100) and to_id in (select to_id from exprsdb.correlation where from_id=2424 order by val desc limit 100) and val>0.6 and to_idMight not be any faster, but you can do this as a self-join with subquery: SELECT c1.from_id, c1.to_id, c1.val FROM correlation c1, ( SELECT to_id FROM correlation WHERE from_id=2424 ORDER BY val DESC LIMIT 100 ) AS c2 ( SELECT to_id FROM correlation WHERE from_id=2424 ORDER BY val DESC LIMIT 100 ) AS c3 WHERE c1.from_id = c2.to_id AND c1.to_id = c3.to_id AND c1.val > 0.5 AND c1.to_id < from_id ; I think PG should be smart enough nowadays to figure out these two queries are basically the same. Richard, In another email, I posted what I did (which was what you suggest), along with explain analyze output. It looks like the subquery is 4-6 times faster, which is getting into the acceptible for my little web application. Thanks for the help. Sean ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Self-referencing table question
On Mar 22, 2005, at 7:07 PM, Sean Davis wrote: - Original Message - From: "Richard Huxton" To: "Sean Davis" <[EMAIL PROTECTED]> Cc: "PostgreSQL SQL" Sent: Tuesday, March 22, 2005 3:59 PM Subject: Re: [SQL] Self-referencing table question Sean Davis wrote: I answer my own question, if only for my own records. The following query is about 5-6 times faster than the original. Of course, if anyone else has other ideas, I'd be happy to hear them. Sean explain analyze select from_id,to_id,val from exprsdb.correlation where from_id in (select to_id from exprsdb.correlation where from_id=2424 order by val desc limit 100) and to_id in (select to_id from exprsdb.correlation where from_id=2424 order by val desc limit 100) and val>0.6 and to_idMight not be any faster, but you can do this as a self-join with subquery: SELECT c1.from_id, c1.to_id, c1.val FROM correlation c1, ( SELECT to_id FROM correlation WHERE from_id=2424 ORDER BY val DESC LIMIT 100 ) AS c2 ( SELECT to_id FROM correlation WHERE from_id=2424 ORDER BY val DESC LIMIT 100 ) AS c3 WHERE c1.from_id = c2.to_id AND c1.to_id = c3.to_id AND c1.val > 0.5 AND c1.to_id < from_id ; I think PG should be smart enough nowadays to figure out these two queries are basically the same. Oops, I DID do a different query in my previous email than what you suggest in the your email. Testing both against each other, the two queries--using subselects in 'in' and doing a self-join via subquery--have basically the same performance. Thanks again for the help. Sean ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Self-referencing table question
Thanks. I thought about that a bit and it seems like it is highly
likely to be expensive for a single query (though I should probably try
it at some point). If I do find myself reformatting results after
response to user input (i.e., reusing the query), though, then your
solution is likely to be very useful.
Sean
On Mar 24, 2005, at 11:13 AM, Edmund Bacon wrote:
Sometimes using a temp table is a better idea:
e.g.
-- start by creating a temp table 'tids' that hold the to_ids that
-- we are interested in.
SELECT to_id
INTO TEMP TABLE tids
FROM correlation
WHERE from_id = 1234
ORDER BY val DESC limit 100;
-- The following temp table makes use of the primary key on
-- the correlation table, and the stated goal from the original
-- question that:
-- from_id > to_id
-- and from_id in (tids.to_id)
--and to_id in (tids.to_id)
SELECT t1.to_id AS from_id, t2.to_id
INTO TEMP TABLE from_to
FROM tids t1, tids t2
WHERE t1.to_id > t2.to_id;
-- Now we can use the from_to table as an index into the correlation
-- table.
SELECT c.from_id, c.to_id, c.val
FROM from_to
JOIN correlation c USING(from_id, to_id)
WHERE val > 0.5;
The explain analyze for the final select works out to:
Nested Loop (cost=0.00..50692.00 rows=8488 width=16) (actual
time=0.171..150.095 rows=2427 loops=1)
-> Seq Scan on from_to (cost=0.00..79.38 rows=5238 width=8)
(actual time=0.006..7.660 rows=4950 loops=1)
-> Index Scan using correlation_pkey on correlation c
(cost=0.00..9.63 rows=2 width=16) (actual time=0.024..0.025 rows=0
loops=4950)
Index Cond: (("outer".from_id = c.from_id) AND ("outer".to_id
= c.to_id))
Filter: (val > 0.5::double precision)
Total runtime: 152.261 ms
Richard Huxton wrote:
Sean Davis wrote:
I answer my own question, if only for my own records. The following
query is about 5-6 times faster than the original. Of course, if
anyone else has other ideas, I'd be happy to hear them.
Sean
explain analyze select from_id,to_id,val from exprsdb.correlation
where from_id in (select to_id from exprsdb.correlation where
from_id=2424 order by val desc limit 100) and to_id in (select
to_id from exprsdb.correlation where from_id=2424 order by val desc
limit 100) and val>0.6 and to_id
Might not be any faster, but you can do this as a self-join with
subquery:
SELECT c1.from_id, c1.to_id, c1.val
FROM
correlation c1,
(
SELECT to_id FROM correlation WHERE from_id=2424
ORDER BY val DESC LIMIT 100
) AS c2
(
SELECT to_id FROM correlation WHERE from_id=2424
ORDER BY val DESC LIMIT 100
) AS c3
WHERE
c1.from_id = c2.to_id
AND c1.to_id = c3.to_id
AND c1.val > 0.5
AND c1.to_id < from_id
;
I think PG should be smart enough nowadays to figure out these two
queries are basically the same.
--
Edmund Bacon <[EMAIL PROTECTED]>
---(end of
broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Self-referencing table question
On Mar 24, 2005, at 1:11 PM, Edmund Bacon wrote: Sean Davis wrote: Thanks. I thought about that a bit and it seems like it is highly likely to be expensive for a single query (though I should probably try it at some point). If I do find myself reformatting results after response to user input (i.e., reusing the query), though, then your solution is likely to be very useful. Note that the subselect version takes about 10 times as long as the temptable version, and does not seem to be dependent on what data might be cached. Nice. Thanks for doing my work for me! I guess I will have to think about it more seriously. It could be a slight bit complicated because my code is running under mod_perl, so connections are cached. As I understand it, the temp table will stick around, so I will have to be careful to explicitly drop it if I don't want it to persist? Also each table will need a unique name (I have a session_id I can use), as it is possible that multiple temp tables will exist and be visible to each other? Thanks again, Sean ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Self-referencing table question
On Mar 24, 2005, at 2:37 PM, Edmund Bacon wrote: Sean Davis wrote: Nice. Thanks for doing my work for me! Yeah, well put it down to a certain amount of curiosity and a slack period at work ... I guess I will have to think about it more seriously. It could be a slight bit complicated because my code is running under mod_perl, so connections are cached. As I understand it, the temp table will stick around, so I will have to be careful to explicitly drop it if I don't want it to persist? I'm guessing so.However you could put everything in a transaction and use CREATE TEMP TABLE ... ON COMMIT DROP, and use INSERT INTO rather than SELECT INTO. The speed should be about equivalent - but you'd have to test to make sure. Also each table will need a unique name (I have a session_id I can use), as it is possible that multiple temp tables will exist and be visible to each other? Each session (connection in your case?) has it's own temporary table space, so you shouldn't have to worry about that. Sessions don't map 1-to-1 with connections in the web environment. It is possible that a connection to the database would be simultaneously serving multiple users (sessions), if I understand Apache::DBI correctly. In any case, this is probably a viable solution. Sean ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] cost of CREATE VIEW ... AS SELECT DISTINCT
On Mar 29, 2005, at 5:07 AM, T E Schmitz wrote: Hello Scott, Scott Marlowe wrote: On Mon, 2005-03-28 at 15:43, T E Schmitz wrote: How expensive would it be to maintain the following VIEW: CREATE VIEW origin AS SELECT DISTINCT origin FROM transaktion if there is in index on transaktion.origin; the table transaktion has thousands of records and there are only a few distinct origin? The cost will only be encurred when running the view. if you want materialized views (which WOULD have maintenance costs) you'll have to Thank you for the pointer - that might come in handy for another scenario. The cost of executing that view should be the same as the cost of running the query by hand. I did an EXPLAIN ANALYZE and a sequential scan was carried out despite the index I had on the column. Maybe this is because I only have very few records in my test DB. Would the "SELECT DISTINCT origin" always cause a sequential table scan regardless whether there is an index on the origin column or not? I think you are right. If this is expensive and run often, you could always normalize further and create a table of "unique_origin" that would have only unique origins and set transaktion to have a foreign key referring to the unique_origin table and then just query the unique_origin table when you need to do the query above. Sean ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] a very big table
- Original Message - From: "_moray" <[EMAIL PROTECTED]> To: Sent: Tuesday, March 29, 2005 12:25 PM Subject: [SQL] a very big table hullo all, I have a problem with a table containing a lot of data. referred tables "inserzionista" and "pubblicazioni" (referenced 2 times) have resp. 1909 tuples and 8300 tuples, while this one 54942. now the problem is that it is slow, also a simple "select * from pubblicita". (it takes 5-6 seconds on my [EMAIL PROTECTED],6Ghz laptop...) I tried using some indexes, but the main problem is that I am using a php script to access the data that builds the query according to user input. Generally, you need to have an index for any column that will appear in a 'where' clause or be referenced as a foreign key. The referencing columns should be declared "unique" or "primary key" and will also then be indexed. So, any column that is on the left or right of an '=' sign in a join or a 'where' clause should probably be indexed. There are exceptions, but that is the general rule. Also, after you make your indices, you need to remember to vacuum analyze. As you can see it is a quite heavy query...but also with simple queries: === cioe2=# explain SELECT * from pubblicita; QUERY PLAN --- Seq Scan on pubblicita (cost=0.00..2863.42 rows=54942 width=325) (1 row) cioe2=# explain SELECT * from pubblicita where soggetto ilike 'a%'; QUERY PLAN --- Seq Scan on pubblicita (cost=0.00..3000.78 rows=54942 width=325) Filter: (soggetto ~~* 'a%'::text) (2 rows) === suggestions on how to make things smoother? (the table is below) thnx Ciro. === create table pubblicita ( codice_pubblicita bigserial, codice_inserzionista int NOT NULL, codice_pagina varchar(2), codice_materiale varchar(2), codice_pubblicazione bigint NOT NULL, data_registrazione timestamp, ripete_da bigint, soggetto text, inserto text, prezzo numeric, ns_fattura int, ns_fattura_data date, vs_fattura int, vs_fattura_data date, colore bool, data_prenotazione date, data_arrivo date, data_consegna date, note_prenotazione text, note_consegna text, note text, annullata bool DEFAULT 'f', PRIMARY KEY (codice_pubblicita), FOREIGN KEY (codice_pubblicazione) REFERENCES pubblicazioni ON UPDATE CASCADE, FOREIGN KEY (ripete_da) REFERENCES pubblicazioni (codice_pubblicazione) ON UPDATE CASCADE, FOREIGN KEY (codice_inserzionista) REFERENCES inserzionisti ON UPDATE CASCADE, FOREIGN KEY (codice_pagina) REFERENCES pagine ON UPDATE CASCADE, FOREIGN KEY (codice_materiale) REFERENCES materiali ON UPDATE CASCADE ); === ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Table PARTITION
This is a totally selfish question, but IF someone has a few minutes,
could he/she explain why table partitioning is such an important tool?
Thanks,
Sean
On Apr 7, 2005, at 8:06 AM, Richard Huxton wrote:
Dinesh Pandey wrote:
How can we create oracle's table with partition in Postgres. (How to
create
Table partion in postgres)
CREATE TABLE invoices
(invoice_noNUMBER NOT NULL, invoice_date DATE NOT NULL,
comments VARCHAR2(500))
PARTITION BY RANGE (invoice_date)
(PARTITION invoices_q1 VALUES LESS THAN (TO_DATE('01/04/2001',
'DD/MM/')) TABLESPACE users
By hand, I'm afraid (although I think people are looking into this
feature for version 8.1)
There's lots of discussion in the mail archives about how people are
handling this. You're basically looking at some combination of
- partial/conditional indexes
- unions
- inheritance
--
Richard Huxton
Archonet Ltd
---(end of
broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Question on triggers and plpgsql
On Apr 8, 2005, at 8:28 AM, John DeSoi wrote: On Apr 7, 2005, at 5:45 PM, Carlos Moreno wrote: The thing seems to work -- I had to go in a shell as user postgres and execute the command: $ createlang -d dbname plpgsql (I'm not sure I understand why that is necessary, or what implications -- positive or negative -- it may have) As a security measure, no pl language is available by default. What you did is correct. There is not much (any?) risk with pl/pgsql, so you can install it in template1 so it will be available in any new database you create. Am I doing the right thing? Have I introduced some sort of catastrophe waiting to happen? I did not notice any problems. Just one detail, but in the form of a question. In the original posting, I think the trigger was doing the logging for something happening on a table as a before insert or update--I may be wrong on that detail. I would think of doing such actions AFTER the update/insert. In the world of transaction-safe operations, is there ANY danger in doing the logging as a BEFORE trigger rather than an AFTER trigger? Thanks, Sean ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Problems with Set Returning Functions (SRFs)
On Apr 6, 2005, at 2:53 PM, Otto Blomqvist wrote: secom=# select f1, f2, f3 from testpassbyval(1, (Select number1 from test)); ERROR: more than one row returned by a subquery used as an expression This is where I fail. Am I even on the right path here ? Writing the actual parsing function will be easy once I have a working concept. How about (untested): select f1, f2, f3 from ( select testpassbyval(1,( select number1 from test) ) ) a; Sean ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] dynamic 'INSERT' query?
On Apr 14, 2005, at 7:37 AM, Dinesh Pandey wrote: How can we set A1, A2 values in dynamic 'INSERT’ query? DECLARE _record RECORD; _sql VARCHAR(2000); FOR _record IN SELECT A1, A2 FROM A LOOP _sql := 'INSERT INTO B VALUES (:A1, :A2)’; EXECUTE (_sql); END LOOP; = I can do this as (but I need another way instead of using || operator). _sql := 'INSERT INTO B VALUES (‘ || _record.A1 || ’,’ || _record.A2 || ’)’; Dinesh, I think what you are showing here IS the way to build up a dynamic sql statement. I'm not sure that you can write a prepared statement within the body of a function, which would then look more like what you are suggesting you want to do--perhaps others on the list can enlighten us about that. In any case, why won't using the || operator work for you? Sean ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Combining values in a column
See below for creating aggregates: http://www.postgresql.org/docs/current/static/xaggr.html But, there is a useful function built-in, bit_and, that does what you want: http://www.postgresql.org/docs/current/static/functions- aggregate.html#FUNCTIONS-AGGREGATE-TABLE create table testint ( myint int ); CREATE TABLE insert into testint values (31); INSERT 428988938 1 insert into testint values (511); INSERT 428988939 1 select bit_and(myint) from testint; bit_and - 31 (1 row) On Apr 15, 2005, at 5:22 AM, Stephen Quinney wrote: I have a query which returns a single column of integers which I want to combine together with &, "bitwise AND". Basically it's a simple list of access levels that a user might have and I want to merge the list into one value. There can be zero, one or more values in the list so I have to be able to cope with the no-results . For example access 31 511 Would lead to 31 & 511 which gives 31. I guess really this can lead to the more generic question. How do I apply a general function to combine the values, if I, for example, wanted to write my own aggregate function like sum? Thanks in advance, Stephen Quinney ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] user connection over tcp
Did you start the postmaster with -i to allow tcp connections? What is the error that you get when you try to connect? Sean - Original Message - From: "Frank Habermann" <[EMAIL PROTECTED]> To: Sent: Monday, April 18, 2005 9:31 AM Subject: [SQL] user connection over tcp hello! i have some problems to understand the manual with pg_hba.conf. i use a tcp connection with phppgadmin to connect to the db. in my pg_hba.conf i say for this connection trust! in phppgadmin i need the for my user the right password to connect. but the manual says that every password will accepted. why isnt that work at me? i also try password in pg_hba.conf for my tcpconnections. but after this i cant connect to the db. but the manual says that users with right password can connect. but this doesnt work here. can somebody explain me whats wrong with me or my postgre?! thx frank habermann ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] How to add 1 hour in a date or time stamp?
Dinesh, The documentation is very helpful for this topic. Typing 'add 1 hour timestamp' into the search box at: http://www.postgresql.org/docs/8.0/interactive/index.html yields the following page as the first hit: http://www.postgresql.org/docs/8.0/static/functions-datetime.html Sean - Original Message - From: Dinesh Pandey To: 'PostgreSQL' ; [email protected] Sent: Monday, April 18, 2005 9:38 AM Subject: [SQL] How to add 1 hour in a date or time stamp? How to add 1 hour in a date or time stamp? RegardsDinesh Pandey--
Re: [SQL] Function declaration
Alex, What happens if you declare your function as: function(text,text) or whatever your datatypes for each argument are supposed to be and then, within your function, test whether the arguments are NULL? I haven't tried it, so I can't say for sure, but it seems like you should be able to pass a SQL NULL as an argument. Sean - Original Message - From: "A. Kulikov" <[EMAIL PROTECTED]> To: "Sean Davis" <[EMAIL PROTECTED]> Sent: Monday, April 18, 2005 8:44 PM Subject: Re: [SQL] Function declaration Unfortunately that is not what I am looking for =( Although the task I have in mind can be accomplished using overloading. Is there no equivalent in plpgsql for function(foo,bar=null)... ?? regards, alex 2005/4/19, Sean Davis <[EMAIL PROTECTED]>: Alex, I think what you are looking for is called function overloading. See the documenation here: http://www.postgresql.org/docs/8.0/interactive/xfunc-overload.html Sean - Original Message - From: "A. Kulikov" <[EMAIL PROTECTED]> To: Sent: Monday, April 18, 2005 8:28 PM Subject: [SQL] Function declaration Is there a possiblity to declare functions with optional parameters i.e. in case some parameters are not passed to the function, then some kind of default value is assigned to the function? -- The mind is essential -- http://essentialmind.com/ ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Query about SQL in PostgreSQL
On Apr 19, 2005, at 5:48 AM, Muhammad Nadeem Ashraf wrote: Hi, I am new user of PostGreSQL 8.0.1. While using it i faced following issue. As SQL is Case insensetive Language So the Uper or Lower cases are not significant. But while using the database there is problem. If i Create new Table with name (tblstudent) then upon SQL queries it is fine to use Select * from tblstudent. However i face probel if i give the name of table in Capital letters i.e. if the name is (tblStudent) then upon using Select * from tblStudent, following error is appeard: ERROR: relation "tblst" does not exist And if i use the query Select * from "tblStudent" then it works fine. SQL is case insensitive. However, identifiers (table names, column names, index names, etc.) are case sensitive in Postgresql. So, your operational understanding of how things work seems to be correct. I tend to use all lower-case, just because it saves me some thought, but I know some folks like to use a mix of upper and lower case so that they can be used in a web application, for example. If you do use mixed or upper case, you do have to quote them. If you still have a problem, could you be more specific about what it is? Sean ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Looking for a way to sum integer arrays....
You would definitely want to look into using pl/R for this. Also, other
procedure languages (perl, for example) work well with arrays so may be
easier to use for this situation. As for the aggregate, I don't know how to
make that more dynamic in terms of return value.
Sean
- Original Message -
From: "Tony Wasson" <[EMAIL PROTECTED]>
To:
Sent: Thursday, April 21, 2005 9:21 PM
Subject: [SQL] Looking for a way to sum integer arrays
I'd like to be able to sum up an integer array. Like so:
{3,2,1}
+ {0,2,2}
---
{3,4,3}
The following solution I've been hacking on works, although I think it
is far from "ideal". Is there a built in way to sum up arrays? If not,
is there a better way than my crude method? I have tested this on 7.4
and 8.0. I'd also be appreciate if any insight on why my aggregate
fails to work when I have an empty initcondition. P.S. I have never
written an aggregate and I was lost trying to follow the complex_sum
example in the docs.
-
CREATE OR REPLACE FUNCTION sum_intarray(INTEGER[],INTEGER[]) RETURNS
INTEGER[] LANGUAGE 'plpgsql' AS '
/*
|| Author: Tony Wasson
||
|| Overview: Experiment with arrays and aggregates
|| 3,2,1
||+ 0,2,2
|| ---
|| 3,4,3
||
|| Revisions: (when, who, what)
|| 2005/04/21 -- TW - Create function
*/
DECLARE
inta1 ALIAS FOR $1;
inta2 ALIAS FOR $2;
out_arr INTEGER[];
out_arr_textTEXT := ;
i INTEGER;
nextnum INTEGER;
BEGIN
FOR i IN array_lower(inta1, 1)..array_upper(inta1, 1)
LOOP
RAISE NOTICE ''looking at element %'',i;
nextnum := COALESCE(inta1[i],0) + COALESCE(inta2[i],0);
RAISE NOTICE ''nextnum %'',nextnum;
out_arr_text := out_arr_text || nextnum::TEXT || '','';
RAISE NOTICE ''text %'',out_arr_text;
END LOOP;
RAISE NOTICE ''text %'',out_arr_text;
--drop the last comma
IF SUBSTRING(out_arr_text,length(out_arr_text),1) = '','' THEN
out_arr_text := substring(out_arr_text,1,length(out_arr_text)-1);
END IF;
out_arr_text := ''{'' || out_arr_text || ''}'';
RAISE NOTICE ''text %'',out_arr_text;
out_arr := out_arr_text;
RAISE NOTICE ''out_arr %'',out_arr;
RETURN out_arr;
END
';
SELECT sum_intarray('{1,2}','{2,3}');
SELECT sum_intarray('{3,2,1}','{0,2,2}');
--- Now I make a table to demonstrate an aggregate on
CREATE TABLE arraytest (
id character varying(10) NOT NULL,
somearr integer[]
);
INSERT INTO arraytest (id, somearr) VALUES ('a', '{1,2,3}');
INSERT INTO arraytest (id, somearr) VALUES ('b', '{0,1,2}');
CREATE AGGREGATE sum_integer_array (
sfunc = sum_intarray,
basetype = INTEGER[],
stype = INTEGER[],
initcond =
'{0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0}'
);
--
# SELECT sum_integer_array(somearr) FROM arraytest;
sum_integer_array
-
{1,3,5,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0}Thanks
in advance to anyone who reads this far.Tony [EMAIL PROTECTED](end of
broadcast)---TIP 3: if posting/reading through Usenet,
please send an appropriate subscribe-nomail command to [EMAIL PROTECTED]
so that your message can get through to the mailing list cleanly
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Does Postgresql have a similar pseudo-column "ROWNUM" as
On May 18, 2005, at 3:52 PM, Chris Browne wrote: [EMAIL PROTECTED] (Alain) writes: Andrew Sullivan escreveu: On Thu, May 12, 2005 at 01:07:00PM -0600, [EMAIL PROTECTED] wrote: Does Postgresql have a similar pseudo-column "ROWNUM" as Oracle? If so, we can write the following query: No. What is the purpose of your query? You could use ORDER BY and LIMIT..OFFSET to do what you want. I think. The problem is probably speed. I have done a lot of tests, and when OFFSET gets to a few thousands on a multimega-recs database, it gets very very slow... Is there any other to work around that? The other way to do this would involve creating a cursor against the table, and using suitable FETCHes to grab the portions that you needed. In practice, this has seemed to be the relevant answer to what the application developer actually wanted. The common "use case" where I see it is in a web application where they discover that there are 800K records, and the user only wants a screenful at a time. Establishing a cursor, and having the web app jump around on it, seems to be the right answer. (Whether it's reasonably implementable by the developers may be another question, but that's allowed to be a separate question ;-).) In a web app, I doubt that cursors can be useful because of the stateless nature of web interaction. I'd love to hear otherwise, but Sean ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Alias to a type
On Jun 22, 2005, at 7:42 AM, Achilleus Mantzios wrote: O Veikko Mδkinen έγραψε στις Jun 22, 2005 : Hey, Is it possible to create a new type as an alias to a pre-defined type? I use "USERID varchar(20)" in almost every table I have I'd like to make an alias for that type eg. create type myschema.useridtype as varchar(20); Try something like CREATE DOMAIN my_integer AS INTEGER; Just for my own edification, does creating a "simple" domain like this then require a whole set of functions for indexing, etc., like other more complex user-defined types, or will postgres "do the right thing"? Thanks, Sean ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] How can I simply substatue a value in a query?
- Original Message - From: Roy Souther To: [email protected] Sent: Monday, June 27, 2005 1:16 PM Subject: [SQL] How can I simply substatue a value in a query? I want to do a simple substatution of a value in a query. For example I have a boolean field that wil return t or f for True and False. I would like it to return Yes or No. I don't want to have a 2x2 table to look up the output. I don't want to use stored procedue.I think there is a better way, somthing very simple but I cannot remember what it is. See CASE: http://www.postgresql.org/docs/8.0/interactive/functions-conditional.html#AEN12006 Sean
Re: [SQL] How to connect ORACLE database from Postgres function
Title: Re: [SQL] How to connect ORACLE database from Postgres function using plpgsql/pltclu? On 8/1/05 6:35 AM, "Dawid Kuroczko" <[EMAIL PROTECTED]> wrote: On 8/1/05, Dinesh Pandey <[EMAIL PROTECTED]> wrote: > Is there any way to connect ORACLE database from Postgres function using > plpgsql/pltclu? With PLpgSQL I don't think its possible. I don't know how about PLtclU (should be possible), but I'm sure its doable from PLperlU (using DBI). Don't expect it to be easy to set up and fast performing though. ;) Have a look at DBI-Link. http://pgfoundry.org/projects/dbi-link There is a PostgreSQL tidbits column on DBI-Link here: http://www.pervasive-postgres.com/postgresql/tidbits.asp Sean
Re: [SQL] SQL Newbie
On 8/12/05 11:09 AM, "Lane Van Ingen" <[EMAIL PROTECTED]> wrote: > It seems to me that I should be able to do this, but after 5 hrs of trying, > I > can't figure this one out. > > I could do this in two queries, but seems like I should be able to do this > in > one. What I am trying to do: > Find the highest speed at which each interface of a router has run over > time. > > I have three tables, two of which (interface, speed_history) are being used > in > this query (primary / foreign key fields noted as PK / FK): > > router-> 1:M -> interface -> 1:M -> speed_history > --- --- -- > - > router_no (int2) PK interface_id (int4) PK interface_id (int4) PK > name (varchar) router_no (int2) FK updated_time (timestamp) > PK > link_description (varchar) speed(int4) > > Data in speed history looks like this: > interface_id updated_time speed > 1 2005-08-11 08:10:23 450112 > 1 2005-08-11 10:53:34 501120 <--- > 1 2005-08-11 10:58:11 450112 > 2 2005-08-11 08:10:23 450112 <--- > 2 2005-08-11 11:00:44 350234 > 3 2005-08-11 08:10:23 450112 <--- > The rows of speed_history I want back are marked above with ' <--- '. > > Query results should look like: >interface.interface_id >interface.link_description >speed_history.updated_time >speed_history.speed What about (untested): SELECT a.interface_id, a.link_description, c.updated_time, c.speed FROM interface a, (select interface_id,max(speed) as speed from speed_history,interface group by interface_id) as b, speed_history c WHERE b.interface_id=a.interface_id AND c.speed=b.speed; Sean ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] How to join several selects
On 8/24/05 9:46 AM, "Josep Sanmartí" <[EMAIL PROTECTED]> wrote: > Hello, > I have a 'big' problem: > I have the following table users(name, start_time, end_time), a new row > is set whenever a user logs into a server. I want to know how many > users have logged in EVERYDAY between 2 different dates. The only idea > that I have is making several select (one for each day): > SELECT COUNT(name) FROM users WHERE start_time between "startDate" > and "startDate+1" > SELECT COUNT(name) FROM users WHERE start_time between "startDate+1" > and "startDate+2" > ... > I would like to know if its possible to make it in 1 sql statement or > just which is the best efficient way to solve it. > By the way, I use Postgres 7.4. See: http://www.postgresql.org/docs/8.0/interactive/sql-select.html#SQL-UNION like: SELECT COUNT(name) FROM users WHERE start_time between "startDate" and "startDate+1" union SELECT COUNT(name) FROM users WHERE start_time between "startDate+1" and "startDate+2" Sean ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] table listing queries
On 8/25/05 7:13 AM, "Daniel Silverstone" <[EMAIL PROTECTED]> wrote: > Hi, > > I know that questions like this have been asked in the past, but I can > find no definitive answer to one particular part of my problem... > > Namely, in MySQL I can say: "SHOW TABLES FROM 'dbname'" to list tables > in a database I'm not currently connected to. > > I can find no way of doing this in PgSQL. > > Is there a way, or is postgres not letting me list the tables until I > have connected to the database for security reasons? There are system catalogs which contain the information about tables. http://www.postgresql.org/docs/8.0/interactive/catalogs.html However, they are specific to each database. In other words, the storage of database-specific information is all WITHIN the given database, so you need to be physically accessing the given database to even see those tables (or do the query). Sean ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[SQL] R-tree and start/end queries
I have a table like: Create table gf ( pkserial, start int, end int, gfvarchar ); I want to do queries along the lines of: "find all gf that overlap with (1,2)" or "find all gf that overlap with each other" And others. I have read over the documentation, but I still remain unclear about how to implement R-tree indexing in this situation. Any suggestions? Thanks, Sean ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] RULES on SELECT with JDBC/perlDBI from other RDBMS
On 10/6/05 9:07 PM, "Bath, David" <[EMAIL PROTECTED]> wrote: > Folks, > > I'm looking at using pg to be the main platform for integrating info > from other RDBMS products (particularly Oracle) as pg seems to be > the most flexible RDBMS around. > > Disregarding writing to foreign products, query-plan efficiencies, > or differences of SQL dialect, I'd like to have a way of setting > up a fairly-transparent SELECT within pg that pulls rows from the > other product. I wonder if anyone has attempted something like > this, and can recommend (or even deprecate) an approach, and perhaps > point to a code template. > > Possible approaches that occur to me include > 1. For the low-level integration > a) Use of "foreign" JDBC thin client within PL/Java > b) Use of untrusted perl DBI/DBD > c) Use of low-level C code (e.g. declaring Oracle OCI calls > to pg) - very labor intensive > 2. For "transparent" use by other routines > Create pg table/view, then write rules that use functions > returning rows (including barf exceptions if someone tries > writing to a table). > > If I can embed a java thin client binary/jar for the foreign > database in pg and use it using pg pl/java, then I'd like to > go that path as it would decrease setup/admin effort when > porting to other platforms, as there would be few dependencies > on things like external perl modules. > > If any pg developer gurus are reading this, perhaps such templates > might be worthwhile including in the contrib bundle? > The DBI-link project is quite useful for doing just what you describe, if I understand you correctly. http://pgfoundry.org/projects/dbi-link/ http://www.pervasivepostgres.com/postgresql/tidbits_June05.asp Sean ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] question re. count, group by, and having
On 10/11/05 8:50 AM, "Rick Schumeyer" <[EMAIL PROTECTED]> wrote:
> The following query returns an error ("column c does not exist") in pg 8.0.3:
>
>
>
> (The column 'state' is the two letter abbreviation for a US state)
>
>
>
> -- get the number of rows for each state; list in descending order; include
> only states with at least 6 rows
>
> select state, count(state) as c from t group by state having c > 5 order by c
> desc; -- gives error
>
>
>
> If I leave the having clause out, I get the expected results:
>
>
>
> select state, count(state) as c from t group by state order by c desc; -- this
> works
>
>
>
> Is this a bug or a feature? I'm not sure why I can use 'c' in the order by
> clause but not the having clause. pg is much happier with the full "having
> count(state) > 5". Will this cause count to be evaluated twice?
I think that postgres is smart enough to do the evaluation only once, but
this might be version-dependent, but one of the gurus will have to comment
on which version (if there is a version dependence) first made this
improvement.
Sean
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [SQL] SETOF RECORD RETURN VALUE
On 10/26/05 6:34 AM, "Christian Paul B. Cosinas" <[EMAIL PROTECTED]> wrote:
> Hi I am having some problem with function that returns SETOF RECORD
>
> Here is my function:
>
> CREATE OR REPLACE FUNCTION test_record(text)
> RETURNS SETOF RECORD AS
> $BODY$
>
>
> DECLARE
> p_table_name ALIAS FOR $1;
> temp_rec RECORD;
> v_query text;
>
> BEGIN
>
> v_query = 'SELECT * FROM ' || p_table_name; FOR temp_rec IN EXECUTE v_query
> LOOP
> RETURN NEXT temp_rec;
> END LOOP;
>
> RETURN ;
>
> END;
>
> $BODY$
> LANGUAGE 'plpgsql' VOLATILE;
>
>
> And here is how I execute the function:
> select * from test_record('field_list')
>
> I have this error:
>
> ERROR: a column definition list is required for functions returning
> "record"
Since Postgres doesn't know what to expect from your function, you have to
tell it by giving the list of columns that are actually returned:
select * from test_record('field_list') as s(a,b,c,d)
where a,b,c,d are the columns in your returned set. (ie., in your example,
if p_table_name has 5 columns, you would use "as s(a,b,c,d,e)", etc.).
See here for more detail:
http://techdocs.postgresql.org/guides/SetReturningFunctions
Sean
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [SQL] RETURNS SETOF primitive returns results in parentheses
On 10/26/05 8:38 AM, "Mario Splivalo" <[EMAIL PROTECTED]> wrote: > Consider this function: > > CREATE OR REPLACE FUNCTION php_get_subfield_data_repeating(int4, > "varchar") > RETURNS SETOF "varchar" AS > $BODY$ > DECLARE > aRecordID ALIAS FOR $1; > aSubFieldId ALIAS FOR $2; > > returnValue record; > subFieldNumber char(3); > subFieldLetter char(1); > > BEGIN > subFieldNumber = substr(aSubFieldId, 1, 3); > subFieldLetter = substr(aSubFieldId, 4); > > FOR returnValue IN SELECT "subfieldValue"::varchar > FROM "records_sub" > WHERE "fieldTag" = subFieldNumber AND "subfieldTag" = subFieldLetter > AND "recordId" = aRecordId > LOOP > RETURN NEXT returnValue; > END LOOP; > > RETURN; > END > $BODY$ > LANGUAGE 'plpgsql' VOLATILE; > > > > Now, when I do this: > > biblio3=# select * from php_get_subfield_data_repeating(1,'606a'); > php_get_subfield_data_repeating1 > -- > (Anđeli) > (ofsajd) > (2 rows) Does: select * from php_get_subfield_data_repeating(1,'606a') as s(a) do what you want (single column)? ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] [NOVICE] JOIN
Loredana Curugiu wrote:
> Hi everybody,
>
> I have the following table:
>
> count | theme | receiver| date
> | dates
>|
> ---+---+--++-+---
>
> 2 | LIA | +40741775621 | 2007-06-02 00:00:00+00 |
> {2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07}
> |
> 1 | LIA | +40741775621 | 2007-06-04 00:00:00+00 |
> {2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09}
>
> |
> 3 | CRIS | +40741775622 | 2007-06-01 00:00:00+00 |
> {2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07}
> |
> 1 | CRIS | +40741775622 | 2007-06-04 00:00:00+00 |
> {2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09,2007-06-10}
> |
> 2 | LIA | +40741775621 | 2007-06-03 00:00:00+00 |
> {2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08}
>|
> 1 | CRIS | +40741775622 | 2007-06-04 00:00:00+00 |
> {2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09,2007-06-10}
> |
> 1 | CRIS | +40741775622 | 2007-06-03 00:00:00+00 |
> {2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09}
> |
> 1 | CRIS | +40741775622 | 2007-06-04 00:00:00+00 |
> {2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09,2007-06-10}
> |
> 4 | LIA | +40741775621 | 2007-06-01 00:00:00+00 |
> {2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06}
>
> |
> 1 | LIA | +40741775621 | 2007-06-04 00:00:00+00 |
> {2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09}
>
> |
> 1 | CRIS | +40741775622 | 2007-06-02 00:00:00+00 |
> {2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08}
> |
>
> I want to add up the count column grouped by theme and receiver for the
> dates included in the dates column.
> So I have the following query:
>
>SELECT SUM(A.count),
> A.theme,
> A.receiver,
> A.dates
> FROM my_table A
> INNER JOIN my_table B
> ON A.theme=B.theme
> AND A.receiver=B.receiver
> AND A.date=ANY(B.dates)
> GROUP BY A.theme,A.receiver, A.dates;
>
> The result of the query is:
>
> sum | theme |receiver|
> dates
> ---+---+--+
> 3 | CRIS | +40741775622 |
> {2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07}
> 2 | CRIS | +40741775622 |
> {2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08}
> 3 | CRIS | +40741775622 |
> {2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09}
>18 | CRIS | +40741775622 |
> {2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09,2007-06-10}
> 4 | LIA | +40741775621 |
> {2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06}
> 4 | LIA | +40741775621 |
> {2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07}
> 6 | LIA | +40741775621 |
> {2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08}
>10 | LIA | +40741775621 |
> {2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09}
>
> The result is wrong. I don't know what it is wrong at my query.
> Please help.
Loredana,
It is great to see your determination to get the answer, but we still do
not know what is "wrong" with the query result. You will need to
explain what you think is wrong before anyone can help. The output
looks like it matches the query perfectly.
Sean
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
[SQL] Re: [NOVICE] Install two different versions of postgres which should run in parallel
Loredana Curugiu wrote: > Hi all, > > I need to have two different vesions of postgres running in parallel on > different ports. Does anyone knows how to install two different versions > of postgres (7.4.5 and 8.2.4) on the same computer? I am using Linux > operating system. You can install from source and provide the --prefix argument to the configure command. Sean ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
[SQL] Reporting functions (OLAP)
I am looking for reporting extensions such as windowing, ranking, leads/lags, etc. for postgresql. A quick google search turned up some "working on it" type results, but I was wondering if anything actually existed up to this point? Thanks, Sean ---(end of broadcast)--- TIP 6: explain analyze is your friend
[SQL] Aggregates with NaN values
I am happy to see NaN and infinity handled in input. I would now like to compute aggregates (avg, min, max, etc) on columns with NaN values in them. The standard behavior (it appears) is to have the aggregate return NaN if the data contain one-or-more NaN values. I am used to using coalesce with NULL values, but that doesn't work with NaN. I can deal with these using CASE statuement to assign a value, but is there a standard way of dealing with the NaN (or Infinity, for that matter) cases to get a behvavior where they are "ignored" by an aggregate? Thanks, Sean -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Aggregates with NaN values
On Fri, Dec 5, 2008 at 1:51 PM, Mark Roberts <[EMAIL PROTECTED]> wrote: > > On Thu, 2008-12-04 at 13:01 -0500, Sean Davis wrote: >> I am happy to see NaN and infinity handled in input. I would now like >> to compute aggregates (avg, min, max, etc) on columns with NaN values >> in them. The standard behavior (it appears) is to have the aggregate >> return NaN if the data contain one-or-more NaN values. I am used to >> using coalesce with NULL values, but that doesn't work with NaN. I >> can deal with these using CASE statuement to assign a value, but is >> there a standard way of dealing with the NaN (or Infinity, for that >> matter) cases to get a behvavior where they are "ignored" by an >> aggregate? >> >> Thanks, >> Sean >> > > Have you considered using a where clause? Thanks, Mark. Yes. I have about 20 columns over which I want to simultaneously compute aggregates. Each has NaN's in different rows, so a where clause won't do what I need. The CASE statement approach works fine, though. Sean -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
