[SQL] [Fwd: [Gborg-bugs] BUG: reference error when using inherited tables (ID: 269) (new)]
I received this bug on a project I administer that Isn't related to my project. I forwarded it here to see if any of you could help this person. [EMAIL PROTECTED] wrote: > > Title: reference error when using inherited tables > Bug Type: Software bug > Severity: Serious > Software Version: Other > Environment: k6III-500/394 > SuSE 7.1 > Postgres 7.1 > > Created By: gorefest > Description: Hi > > I have a problem with inherited refences. > For example : > CREATE TABLE A(LNR integer Primary key blabla); > CREATE TABLE B () INHERITS(A); > CREATE TABLE C(LNR integer primary key blabla, RNR Intger not null, unique(RNR), >FOREIGN KEY(RNR) REFERENCES A.LNr ON DELETE CASCADE); > > will throw an error, if i try to insert an object into B with a counterpart in C. A >with a counterpart in C works. Due to the fact, that the inheritance is an acyclic >graph, the machine should look in B to. But i get a reference error instead. Are >references on inherited tables not implemented yet ? > > greetings gorefest > Status: Submitted > > http://www.greatbridge.org/project/gborg/bugs/bugupdate.php?269 > > ___ > Gborg-bugs mailing list > [EMAIL PROTECTED] > http://www.greatbridge.org/mailman/listinfo/gborg-bugs ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Select most recent record?
Hi, I was hoping I could get some help with a select statement. I have a log table with three columns: id int4, timestamp datetime, value int4. For any given ID, there will be a large number of rows, with different timestamps and values. I'd like to select the newest (max(timestamp)) row for each id, before a given cutoff date; is this possible? The best I've been able to come up with is the rather ugly (and very slow): select * from log as l1 where timestamp in (select max(timestamp) from log where id=l1.id and timestamp<'2001-01-01' group by id); There must be a better way to do this; any tips? Thanks, - Marc ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Restricting the number of decimal digits
I have a query that calculates the average of difference between timestamps. I want the output to be upto 1 decimal point. How do I do it? __ Do You Yahoo!? Yahoo! Auctions - buy the things you want at great prices http://auctions.yahoo.com/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] Select most recent record?
"Marc Sherman" <[EMAIL PROTECTED]> writes: > I'd like to select the newest (max(timestamp)) row for each id, > before a given cutoff date; is this possible? select * from log order by timestamp desc limit 1; regards, tom lane ---(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] Select most recent record?
Except that he wants max(timestamp) by id; perhaps a GROUP BY would help, something like (untested): select max(timestamp) from log group by id; Tom Lane wrote: > > "Marc Sherman" <[EMAIL PROTECTED]> writes: > > I'd like to select the newest (max(timestamp)) row for each id, > > before a given cutoff date; is this possible? > > select * from log order by timestamp desc limit 1; > > regards, tom lane > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) -- -- Andrew J. Perrin - Programmer/Analyst, Desktop Support Children's Primary Care Research Group, UNC - Chapel Hill (919)966-9394 * [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] oid and triggers
Hi everybody! Is there any possibility to access oid's in trigger routines? I suppose that it makes no sense while 'before insert' routines but it would be very usable while treating oid's as rows identifiers. Thanks, Adam ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] To Run 2 database servers at the same time
HI all, I want to install postgresql v71.1.1 while keeping my old version of it . I think it is v7.0.3. What your advise would be for me ? I have down loaded all the file form the following link: http://www.ca.postgresql.org/ftpsite/v7.1.1/ What are the necessary step should i take to do it correctly? Any doc links would also greatly appreciateld. Thank you all for your help. Regards -- Najm Hashmi Tel:514-271-9791 www.mondo-live.com www.flipr.com
RE: [SQL] Select most recent record?
From: Tom Lane [mailto:[EMAIL PROTECTED]] > > "Marc Sherman" <[EMAIL PROTECTED]> writes: > > I'd like to select the newest (max(timestamp)) row for each id, > > before a given cutoff date; is this possible? > > select * from log order by timestamp desc limit 1; Heh. I obviously simplified my situation too much. This is closer to what I've really got: create table user (userid int4 primary key, groupid int4); create table log (userid int4, timestamp datetime, value int4); I need to select sum(value) for each group, where the values chosen are the newest log entry for each group member that is before a cutoff date (April 1, 2001). Here's what I'm currently using: select user.groupid, sum(l1.value) from log as l1, user where user.userid=log.userid and log.timestamp in ( select max(timestamp) from log where log.timestamp<'2001-04-01' and log.userid=l1.userid) group by user.groupid; When I first posted, this was _very_ slow. I've since improved it by adding an index on log(userid,timestamp) - now it's just slow. If anyone knows how to make it faster, I'd appreciate it. - Marc ---(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] Select most recent record?
Andrew Perrin <[EMAIL PROTECTED]> writes: > Except that he wants max(timestamp) by id; perhaps a GROUP BY would > help, something like (untested): Oh, I'm sorry, I missed the "for each id" bit. > select max(timestamp) from log group by id; That gives you the max timestamp for each id, all right, but not the rest of the row in which the max timestamp occurs. I know of no good way to get that in standard SQL. If you don't mind being nonstandard, this is exactly the problem SELECT DISTINCT ON is meant for: select distinct on (id) * from log order by id, timestamp desc; See the SELECT reference page for more about this. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] [Fwd: [Gborg-bugs] BUG: reference error when using inheritedtables (ID: 269) (new)]
> I have a problem with inherited refences. > For example : > CREATE TABLE A(LNR integer Primary key blabla); > CREATE TABLE B () INHERITS(A); > CREATE TABLE C(LNR integer primary key blabla, RNR Intger not null, > unique(RNR), FOREIGN KEY(RNR) REFERENCES A.LNr ON DELETE CASCADE); > will throw an error, if i try to insert an object into B with a > counterpart in C. A with a counterpart in C works. Due to the fact, > that the inheritance is an acyclic graph, the machine should look in B > to. But i get a reference error instead. Are references on inherited > tables not implemented yet ? Exactly. Currently a foreign key reference is a reference to only the table explictly mentioned. There's a bunch of work that needs to get done to fix this, but people have been thinking about it. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Give me a suggestion 'START WITH .. CONNECT BY'.
Hello! I try to translate a database to PostgreSQL from ORACLE, but have a regret over a function '... START WITH .. CONNECT BY ...'. This function is descrived in a sql sentence for ORACLE, and I could not find in PostgreSQL. For example SELECT LEVEL, COMP_ITM, COMP_NAME, COMP_AMT FROM PSFILE START WITH PRNT_ITM = 'A0010' CONNECT BY PRIOR COMP_ITM = PRNT_ITEM; I want to know an similar SQL description in PostgreSQL. Will you give me a suggestion? Thanks! $B#H#i#r#o#s#i(B $B#Y#a#m#a#o#k#a(B Co.ltd $B#I#n#t#e#g#r#a#t#e#d(B $B#S#y#s#t#e#m#s(B $B#I#n#s#t#i#t#u#t#e(B701-0211$B!!(B688-55 Higasiune, Okayama-city, Okayama-pref. Japan
[SQL] "avg" function for arrays?
I know that there's an "average" function (avg) for some datatypes.
Is there something comparable for float or int arrays?
e.g.
select avg(time_instants[1:5]) from ellipse_proc where rep = 1;
time_instants
-
{"148","167.8","187.6","207.4","227.2"}
(1 row)
select avg(time_instants[1:5]) from ellipse_proc where rep = 1;
ERROR: Unable to select an aggregate function avg(_float4)
Thanks.
-Tony
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Select most recent record?
Marc Sherman wrote: > .. > > Heh. I obviously simplified my situation too much. > > This is closer to what I've really got: > > create table user (userid int4 primary key, groupid int4); > create table log (userid int4, timestamp datetime, value int4); > > I need to select sum(value) for each group, where the values chosen > are the newest log entry for each group member that is before a cutoff > date (April 1, 2001). > > Here's what I'm currently using: > > select user.groupid, sum(l1.value) > from log as l1, user > where user.userid=log.userid > and log.timestamp in ( > select max(timestamp) from log > where log.timestamp<'2001-04-01' > and log.userid=l1.userid) > group by user.groupid; > > When I first posted, this was _very_ slow. I've since improved > it by adding an index on log(userid,timestamp) - now it's just > slow. If anyone knows how to make it faster, I'd appreciate it. > > - Marc Try something like this: SELECT user.groupid, sum(l1.value) FROM log as l1, user WHERE user.userid = l1.userid and l1.timestamp = ( SELECT max(timestamp) from log WHERE log.timestamp < '2001-04-01' and log.userid = l1.userid ) GROUP by user.groupid; 1. you use in the same query both "log" and "l1" for the same table: "log as l1"; 2. you use log.timestamp in () ... but in this case you have ony one value ... use "=" instead "in". == George Moga, Data Systems Srl Slobozia, ROMANIA P.S. Sorry for my english ... ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] case insensitive sort for output from SELECT
Is there any way to alphabetically sort the output of a select statement correctly? what I'm using at the moment is SELECT data FROM table ORDER BY DATA ASC which gives (for example) A B C a b Is there any way to rig this so that it sorts 'properly', i.e. both the a's come together? Given what I'm working on, I could use either a pure SQL solution or some perl to put around the perl DBI. -- It's a great big universe and we're all really puny, just tiny little specks 'bout the size of Mickey Rooney -- Animaniacs -- -- -- -- -- -- -- -- -- -- Stephen Patterson [EMAIL PROTECTED] (Remove SPAMOFF to reply) http://home.freeuk.net/s.patterson/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Re: Auto incrementing an integer
Thanks MartínI found this http://dis.lib.muohio.edu/code/entry.html?ID=3
Creating an Autoincrement field in Postgresql
postgres does this a little differently; the "serial" type creates an
integer column, and a sequence used to increment this column.
try this:
create table table_name(
control serial,
another_column text,
primary key(control)
);
results in this:
Table= table_name
+--+--+-
--+
| Field | Type|
Length|
+--+--+-
--+
| control | int4 not null default nextval('" |
4 |
| another_column | text |
var |
+--+--+-
--+
Index:table_name_pkey
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
[SQL] Exp/Imp Problems...
We are trying to export a tablespace 'XYZ' from an Oracle8i Database server to a Oracle 9i Application Server, both residing on different machines. As far the Export is concerned,we are succesfully able to create the required 'abc.dmp' file on the 8i server. We then copy the 'abc.dmp' file and the 'XYZ.DBF'(approx. 150 MB) to the machine where the 9i server is located. And finally when we try to utilise the import utility from the 9i server,it gives us the following messages. - Export file created by EXPORT:V08.01.07 via conventional path About to import transportable tablespace(s) metadata... import done in WE8ISO8859P1 character set and WE8ISO8859P1 NCHAR character set . importing SYS's objects into SYS IMP-00017: following statement failed with ORACLE error 19722: "BEGIN sys.dbms_plugts.checkDatafile(NULL,1722002184,8,64000,7,8,0,0,21160" "6,3279934,1,NULL,NULL,NULL,NULL); END;" IMP-3: ORACLE error 19722 encountered ORA-19722: datafile c:\a\b\XYZ.dbf is an incorrect version ORA-06512: at "SYS.DBMS_PLUGTS", line 1577 ORA-06512: at line 1 IMP-0: Import terminated unsuccessfully Your help will be appreciated.Kindly acknowledge. Ameet Setlur. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Setting session global variables
Is there a way to set a session global variable in PostgreSQL? The only thing I see are examples of setting config variables. What I would like to do is this: Have a table "insertedby" or something that has created_object oid, created_by_user oid, creation_date datetime Then have a procedure, so that any table that wanted to record who inserted which record could just use that procedure as an insert trigger. The trigger would simply read the "current_user" session variable (which the client had presumably set upon connection) and use it to populate the "created_by_user" field. However, the only way I can see to implement this is to have session global variables. Is there a way to fake session global variables with a table? Any comments would be appreciated. Jon [EMAIL PROTECTED] SDF Public Access UNIX System - http://sdf.lonestar.org ---(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] Setting session global variables
Jonathan Bartlett <[EMAIL PROTECTED]> writes: > However, the only way I can see to implement this is to have session > global variables. Is there a way to fake session global variables with a > table? Any comments would be appreciated. You could do it with temp tables: the same temp table name would refer to a different table in each session. (You realize, of course, that CURRENT_USER already exists per SQL spec. I assume you just meant that you'd like to have things *like* CURRENT_USER, but defined by yourself...) regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Exp/Imp Problems...
On Tue, May 15, 2001 at 08:22:36PM +0530, ameet wrote: > > We are trying to export a tablespace 'XYZ' from an Oracle8i Database server to a >Oracle 9i Application Server, both residing on different machines. > > As far the Export is concerned,we are succesfully able to create the required >'abc.dmp' file on the 8i server. > > We then copy the 'abc.dmp' file and the 'XYZ.DBF'(approx. 150 MB) to the machine >where the 9i server is located. Heh? I think you asked this on the wrong list. This list is for help with the PostgreSQL database. For Oracle support you should look for an Oracle forum/list or call their support. http://www.arsdigita.com/bboard (web/db forum) has lots of Oracle users. -Roberto -- +| http://fslc.usu.edu USU Free Software & GNU/Linux Club |--+ Roberto Mello - Computer Science, USU - http://www.brasileiro.net http://www.sdl.usu.edu - Space Dynamics Lab, Developer "Call it a hunch." -- Quasimodo ---(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] Give me a suggestion 'START WITH .. CONNECT BY'.
On Tue, May 15, 2001 at 05:40:32PM +0900, ?$B;3$5$s wrote: > Hello! > I try to translate a database to PostgreSQL from ORACLE, but have a regret > over a function '... START WITH .. CONNECT BY ...'. > This function is descrived in a sql sentence for ORACLE, and I could not > find in PostgreSQL. > For example >SELECT LEVEL, COMP_ITM, COMP_NAME, COMP_AMT FROM PSFILE > START WITH PRNT_ITM = 'A0010' > CONNECT BY PRIOR COMP_ITM = PRNT_ITEM; > There's no equivalent for the Oracle tree extensions in PostgreSQL. Neither LEVEL, START WITH or CONNECT BY PRIOR. There's no easy way out of this AFAIK. You'll have to re-write your code in a way that allows you to do this query in a SQL92 way. In Joe Celko's "SQL For Smarties" he talks about a nifty algorithm that you can use to do this. The OpenACS folks used that approach to port Oracle's CONNECT BY to PostgreSQL, so you could probably ask them over at openacs.org/bboard. -Roberto P.S: I plan to add this to my expanded "Porting From Oracle" chapter of the documentation. -- +| http://fslc.usu.edu USU Free Software & GNU/Linux Club |--+ Roberto Mello - Computer Science, USU - http://www.brasileiro.net http://www.sdl.usu.edu - Space Dynamics Lab, Developer Air conditioned environment - Do not open Windows. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] Setting session global variables
Yes, that's perfect! Actually, I was thinking of a real user, but more in the fashion of "pretend" users. If you've ever used Oracle apps, it would be like an apps user, not the user "apps". Anyway, that's an excellent idea. Thank you much! Jon [EMAIL PROTECTED] SDF Public Access UNIX System - http://sdf.lonestar.org On Wed, 16 May 2001, Tom Lane wrote: > Jonathan Bartlett <[EMAIL PROTECTED]> writes: > > However, the only way I can see to implement this is to have session > > global variables. Is there a way to fake session global variables with a > > table? Any comments would be appreciated. > > You could do it with temp tables: the same temp table name would refer > to a different table in each session. > > (You realize, of course, that CURRENT_USER already exists per SQL spec. > I assume you just meant that you'd like to have things *like* > CURRENT_USER, but defined by yourself...) > > regards, tom lane > > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html > ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
