Re: [SQL] One to many query question
> > On Wed, Jul 30, 2003 at 05:26:23PM -0400, Dmitry Tkach wrote: > > >How do I write a query to find all CDs that are NOT Rock? > > > > > What about > > select * from cd where not exists (select 1 from cd_genres where cd_id > > = cd.id and genre='Rock')? > > Thanks everyone! This did indeed work, and it does seem clearer. I > never knew about EXISTS before. How portable is this? I'm interested > in supporting PostgreSQL and MS SQL Server for now, and possibly > Oracle and MySQL in the future. > It should be portable completely. It's SQL standard. Regards, Christoph ---(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] NEW and a subselect in a rule
> > So, I met such a problem: it's impossible to use NEW in a subselect > used in a (non-select) rule. The error is: exist>. Is this a way to do that newertheless (without using of a > function, of course)? > Could we see the CREATE RULE command causing the error? Regards, Christoph ---(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
[SQL] Unsubscribe
Unsubscribe
Re: [SQL] Fwd: Bad Join moment - how is this happening?
> I have a view: > > create or replace view addenda as > select > documents.id, > documents.oid, > documents.projects_id, > documents.doc_num, > documents.description, > documents.date, > documents.createdate, > documents.moddate, > documents.people_id, > documents.parent, > documents.document_type, > documents.state, > documents.machines_id, > documents.phases_id, > > d_addenda.item_num, > d_addenda.drawing_reference > > from > d_addenda as a, documents as d > where a.documents_id = d.id; > Doing this you should have got NOTICE: Adding missing FROM-clause entry for table "d_addenda" NOTICE: Adding missing FROM-clause entry for table "documents" So it's pretty useful to write psql's output to a file when creating relations and check for NOTICE messages. exec 3>/tmp/psql.out;$PGSQLD/bin/psql 1>&3 2>&3 NOTICE messages appear to be sent to stderr. As the previous posters already made clear create or replace view addenda as select documents.id, documents.oid, documents.projects_id, documents.doc_num, documents.description, documents.date, documents.createdate, documents.moddate, documents.people_id, documents.parent, documents.document_type, documents.state, documents.machines_id, documents.phases_id, d_addenda.item_num, d_addenda.drawing_reference from d_addenda , documents where d_addenda.documents_id = documents.id; resp. create or replace view addenda as select d.id, d.oid, d.projects_id, d.doc_num, d.description, d.date, d.createdate, d.moddate, d.people_id, d.parent, d.document_type, d.state, d.machines_id, d.phases_id, a.item_num, a.drawing_reference from d_addenda as a, documents as d where a.documents_id = d.id; should match your intentions. Regards, Christoph ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[SQL] problem in database backup
Hi All, I'm using Postgresql V7.3.3, on that i created one database with name "mydb". PostgreSQL 7.3.3 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2 20020903 (Red Hat Linux 8.0 3.2-7) While taking a backup of my postgresql database, i'm getting the below error. bash-2.05b$ pg_dump mydb >mydb.outpg_dump: server version: SAGES/pg_dump.mo; pg_dump version: 7.2.2pg_dump: aborting because of version mismatch (Use the -i option to proceed any bash-2.05b$ pg_dump -i mydb > mydb.outpg_dump: server version: SAGES/pg_dump.mo; pg_dump version: 7.2.2pg_dump: proceeding despite version mismatchpg_dump: query to obtain list of data types failed: ERROR: Attribute "typprtlen" not found== After getting these errors, i tried to check the version of pg_dump on my system by using the below command. Here is the result . [EMAIL PROTECTED] root]# find / -name pg_dump -print/root/postgresql-7.3.3/src/bin/pg_dump/root/postgresql-7.3.3/src/bin/pg_dump/pg_dump/usr/bin/pg_dump/usr/local/pgsql/bin/pg_dump Kindly guide me to solve this problem. .. Thanks in advance, With Regards, Vijay It's all happening @ F1. Feel the thrill! Race along right here!
Re: [SQL] problem in database backup
Dnia 2003-07-31 13:13, Użytkownik vijaykumar M napisał: Hi All, I'm using Postgresql V7.3.3, on that i created one database with name "mydb". * PostgreSQL 7.3.3 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2 20020903 (Red Hat Linux 8.0 3.2-7)* While taking a backup of my postgresql database, i'm getting the below error. bash-2.05b$ *pg_dump mydb >mydb.out pg_dump: server version: SAGES/pg_dump.mo; pg_dump version: 7.2.2 pg_dump: aborting because of version mismatch (Use the -i option to proceed any * *bash-2.05b$ pg_dump -i mydb > mydb.out pg_dump: server version: SAGES/pg_dump.mo; pg_dump version: 7.2.2 pg_dump: proceeding despite version mismatch pg_dump: query to obtain list of data types failed: ERROR: Attribute "typprtlen" not found ==* After getting these errors, i tried to check the version of pg_dump on my system by using the below command. Here is the result . [EMAIL PROTECTED] root]# find / -name pg_dump -print /root/postgresql-7.3.3/src/bin/pg_dump /root/postgresql-7.3.3/src/bin/pg_dump/pg_dump /usr/bin/pg_dump /usr/local/pgsql/bin/pg_dump * Probably you have both 7.2.2 and 7.3.3 pg_dump versions and PATH points to 7.2.2 /usr/bin/pg_dump. Try using /usr/local/pgsql/bin/pg_dump Regards, Tomasz Myrta ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] problem in database backup
Hi Tomasz Myrta, Thanks for ur info, u r correct .. after pointing to /usr/bin/pgsql/bin/pg_dump, i'm able to take backup.. Thank u very much.. With Regards Vijay >From: Tomasz Myrta <[EMAIL PROTECTED]> >To: vijaykumar M <[EMAIL PROTECTED]> >CC: [EMAIL PROTECTED] >Subject: Re: [SQL] problem in database backup >Date: Thu, 31 Jul 2003 13:40:45 +0200 > >Dnia 2003-07-31 13:13, U¿ytkownik vijaykumar M napisa³: > >> >>Hi All, >> >>I'm using Postgresql V7.3.3, on that i created one database with >>name "mydb". >> >>* PostgreSQL 7.3.3 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) >>3.2 20020903 (Red Hat Linux 8.0 3.2-7)* >> >>While taking a backup of my postgresql database, i'm getting the >>below error. >> >>bash-2.05b$ *pg_dump mydb >mydb.out >>pg_dump: server version: SAGES/pg_dump.mo; pg_dump version: 7.2.2 >>pg_dump: aborting because of version mismatch (Use the -i option >>to proceed any >>* >> >>*bash-2.05b$ pg_dump -i mydb > mydb.out >>pg_dump: server version: SAGES/pg_dump.mo; pg_dump version: 7.2.2 >>pg_dump: proceeding despite version mismatch >>pg_dump: query to obtain list of data types failed: ERROR: >>Attribute "typprtlen" not found >>==* >> >>After getting these errors, i tried to check the version of pg_dump >>on my system by using the below command. Here is the result . >> >>[EMAIL PROTECTED] root]# find / -name pg_dump -print >>/root/postgresql-7.3.3/src/bin/pg_dump >>/root/postgresql-7.3.3/src/bin/pg_dump/pg_dump >>/usr/bin/pg_dump >>/usr/local/pgsql/bin/pg_dump >>* > >Probably you have both 7.2.2 and 7.3.3 pg_dump versions and PATH >points to 7.2.2 /usr/bin/pg_dump. Try using >/usr/local/pgsql/bin/pg_dump > >Regards, >Tomasz Myrta > > It's all happening @ F1. Feel the thrill! Race along right here!
Re: [SQL] problem in database backup
On Thursday 31 July 2003 12:13, vijaykumar M wrote: *please* don't post HTML only emails to mailing lists OK - you seem to have two pg_dump - one in /usr/bin/pg_dump and one in /usr/local/pgsql/bin/pg_dump. If you type "which pg_dump" as the user who normally does the dump, which one does it show (probably /usr/bin). Now do "pg_dump --version" and see what version it is. My guess is you want the one in /usr/local/... and the one in /usr/bin is from old RPMs -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Problem login to phpPgAdmin 2.x and 3.x
Hi, i have PostgreSQL 7.2.1 . I create new user as postgres. When i login to phpPgAdnin them phpPgAdmin return "Login failed". Where is problem ? My php script runing without problems. Thanks. -- Rado Petrik <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] RI_ConstraintTrigger_11264756
All, I have inherited a table (below) with a RI trigger. I believe this table was created where both clone_id & blastx_id are FKs w/ stipulation 'ON DELETE CASCADE'. My question is, is this specified in the trigger: RI_ConstraintTrigger_11264756? If so how would I learn the content of the trigger knowing just the trigger id (11264756)? I have not seen a 'decoding' of triggers using pgsql. regards, Charles CREATE TABLE clone_blastx( clone_id INTEGER NOT NULL REFERENCES clone(clone_id) ON DELETE CASCADE, blastx_id INTEGER NOT NULL REFERENCES blastx(blastx_id) ON DELETE CASCADE, UNIQUE(clone_id,blastx_id) ); Column | Type | Modifiers ---+-+--- clone_id | integer | contig_id | integer | Indexes: clone_contig_clone_id_key unique btree (clone_id, contig_id), clone_id_clone_contig_key btree (clone_id), contig_id_clone_contig_key btree (contig_id) Triggers: RI_ConstraintTrigger_11264756 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[SQL] Problem with looping on a table function result
I've met the following problem.
I had successfully written a function divide_into_days(timestamp, timestamp)
which returns setof (timestamp, timestamp) pairs - a list of days the
given interval is divided into.
What I want is to use each record from resultset to pass to another
function, something like:
SELECT days.*, summary_stats(days.day_start, days.day_end)
FROMdivide_into_days('2003-06-01', '2003-07-01') days;
The problem is that summary_stats function returns a record, so I have
to use SELECT * FROM summary_stats(...). I can't use the following too:
SELECT *
FROMsummary_stats(days.day_start, days.day_end) stats,
divide_into_days('2003-06-01', '2003-07-01') days;
(there was a discussion a few days ago about using subselects,
but here's a slightly different case).
I wonder if where's a way to do the trick without writing one more
PL/PgSQL table function doing FOR row IN SELECT ... LOOP or using
client-side loop?
Thanks for your help.
--
Fduch M. Pravking
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] converting interval to timestamp
The difference of two dates/timestamps always gives an interval. is there a way to convert this interval into number such as number of milliseconds or number of days? Also does anyone know what field type an interval would map to in jdbc? thanks ---(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
[SQL] join optimization problem
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Hi all,
I'm trying to optimize what I had thought was a simple enough query but I'm
not having much success. It's from a forum based application, and the query
in question is operating on two tables - fbof_msg (8563 rows) and
fbof_thread(1537 rows) and it looks like this:
select t.thread_id,
t.forum_id,
t.thread_title,
t.thread_owner,
t.thread_owner_id,
t.date_created,
t.thread_hits,
t.poem_reference,
t.bArchived,
count(m.msg_id) as msgcount,
max(m.date_created) as lastpost
fromfbof_thread t LEFT OUTER JOIN fbof_msg m ON m.thread_id = t.thread_id
where t.forum_id = 1
and t.bArchived = 0
and t.bSticky = 0
group byt.thread_id,
t.thread_title,
t.thread_owner,
t.thread_owner_id,
t.date_created,
t.thread_hits,
t.forum_id,
t.poem_reference,
t.bArchived,
t.bSticky
order byt.date_created desc
the explain data I'm currently getting is this:
Sort (cost=1660930.18..1660946.63 rows=6581 width=568)
Sort Key: max(m.date_created)
-> Aggregate (cost=0.00..1659452.99 rows=6581 width=568)
-> Group (cost=0.00..1659123.95 rows=65807 width=568)
-> Nested Loop (cost=0.00..1657643.30 rows=65807 width=568)
Join Filter: (("outer".thread_id)::double precision =
"inner".thread_id)
-> Index Scan using fbof_group_idx on fbof_thread t
(cost=0.00..642.03 rows=1537 width=548)
Filter: ((forum_id = 1::double precision) AND
(barchived = 0) AND (bsticky = 0))
-> Seq Scan on fbof_msg m (cost=0.00..949.63 rows=8563
width=20)
(9 rows)
The index it's using is the only one I've been able to get it to use, and
looks like this: (\d fbof_group_idx)
Index "public.fbof_group_idx"
Column | Type
- -+--
thread_id | integer
thread_title| character(255)
thread_owner| character(255)
thread_owner_id | integer
date_created| date
thread_hits | integer
forum_id| double precision
poem_reference | integer
barchived | smallint
btree, for table "public.fbof_thread"
can anyone point out to me where I'm going wrong here? I can't seem to make
it faster for the life of me I've tried adding indices on all the main
fields etc but nada. I'm not subscribed the list currently so please reply
to my address as well as the list.
cheers,
Toby
- --
-
Life is poetry -
write it in your own words
-
Toby Tremayne
Code Poet and Zen Master of the Heavy Sleep
Senior Technical Consultant
Lyricist Software
www.lyricist.com.au
+61 416 048 090
ICQ: 13107913
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.2.2-rc1-SuSE (GNU/Linux)
iD8DBQE/JKKz6KYxVcB16DkRAjl+AKCCrZswP4TL9aAzZUs7CkY9ajjoYwCfZO/v
SGR8GSi++ZZ+DrNXicabzvo=
=kx4x
-END PGP SIGNATURE-
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] length of recordset read through a cursor
After declaring a cursor, one way of obtaining the length of the resultset is to perform a "MOVE 0" and read the PQcmdStatus which returns a "MOVE nn" where nn is the length of the resultset. (A negative MOVE can then be used to allow starting to fetch records from the beginning of the resultset.) Is there another, possibly faster way? Thanks, K.P.Lehre ---(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
[SQL] Does a the block of code within a stored procedure constitute a transaction?
I need to "manually" keep a sequence for each row of the employee table, i.e., I don't want to use postgres's built-in sequences for this application because the sequence numbers are used to generate expense report numbers on a "per-employee" basis. That is, each employee has a separate sequence counter, starting at one, to number their expense reports. Since employees will come and go, I don't want to keep having to create and delete postgres sequence objects as employees come and go. Instead, I have a column of the employee table store the "last value" of the corresponding expense report sequence counter, and in an ON INSERT trigger to the expense report table, I call the following function to get and increment the new sequence value: CREATE OR REPLACE FUNCTION paid.expense_report_next(int4) RETURNS integer AS ' DECLARE l_employee_pk ALIAS FOR $1; l_expense_report_seq INTEGER; BEGIN SELECT INTO l_expense_report_seq expense_report_seq+1 FROM employee WHERE employee_pk = l_employee_pk; UPDATE employee SET expense_report_seq = l_expense_report_seq WHERE employee_pk = l_employee_pk; RETURN l_expense_report_seq; END;' LANGUAGE 'plpgsql' VOLATILE; What I need to know is whether or not this is multi-user safe, i.e., will the block of code in the procedure execute as a transaction so that if more than one clerk creates an expense report for the same employee simultaneously is it possible or impossible that value of the employee.expense_report_seq gets updated by the second clerk between the SELECT and UPDATE statements invoked by the first clerk? And as a follow-up, should I add the FOR UPDATE clause to the SELECT statement? ~Berend Tober ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] One to many query question
On Wed, Jul 30, 2003 at 02:35:20PM -0500, Dave Dribin wrote: > Hi, I'm having trouble with what I think should be an easy query. For > simplicity, I will use a CD database as an example. Each CD may have > multiple genres. Here's some sample data: > > Artist Title Genres > -- -- > Miles Davis Some Kind of Blue Jazz > Metallica Ride the Lightning Rock > Chemical Brothers Surrender Electronic > Radiohead OK Computer Rock, Electronic > > For simplicities sake, let's ignore normalization on artist and genre, > and say the tables look like: > > CREATE TABLE cd ( > id integer unique, > artist varchar(25), > title varchar(25) > ); > > CREATE TABLE cd_genres ( > cd_id integer, > genre varchar(25) > ); Assuming that each CD can have several rows in cd_genres, SELECT id, artist, title FROM cd WHERE id NOT IN ( SELECT cd_id FROM cd_genres WHERE genre = 'Rock' ); will do what you want. Your co-worker is perhaps used to certain lesser databases which don't support subselects... Richard ---(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
[SQL] select query that would join two databases
dear all, How to create a query that would join two databases? In MSSQL, i use select * from dbA.dbo.tableA a dbB.dbo.tableB b where a.id=b.id Is there anyway to write the above query in postgressql ? Please email me back. Thanks, Keith ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Does a the block of code within a stored procedure constitute
> What I need to know is whether or not this is multi-user safe, i.e., will > the block of code in the procedure execute as a transaction so that if > more than one clerk creates an expense report for the same employee > simultaneously is it possible or impossible that value of the > employee.expense_report_seq gets updated by the second clerk between the > SELECT and UPDATE statements invoked by the first clerk? > > And as a follow-up, should I add the FOR UPDATE clause to the SELECT > statement? SELECT .. FOR UPDATE would be appropriate. However, you could also do an update + 1 first and the select second to find what you changed the value to. signature.asc Description: This is a digitally signed message part
Re: [SQL] join optimization problem
Toby Tremayne <[EMAIL PROTECTED]> writes: > the explain data I'm currently getting is this: "explain analyze" would be much more useful. Also, I assume you've vacuum analyzed these tables recently? regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] join optimization problem
How about this? It should spit out the same number, but be quite a bit quicker at it. Untested of course... select t.thread_id, t.forum_id, t.thread_title, t.thread_owner, t.thread_owner_id, t.date_created, t.thread_hits, t.poem_reference, t.bArchived, count, maxdate from fbof_thread t LEFT OUTER JOIN (select thread_id , count(msg_id) as count , max(date_created) as maxdate from msg group by thread_id ) as tab ON m.thread_id = t.thread_id where t.forum_id = 1 and t.bArchived = 0 and t.bSticky = 0 order byt.date_created desc > can anyone point out to me where I'm going wrong here? I can't seem to make > it faster for the life of me I've tried adding indices on all the main > fields etc but nada. I'm not subscribed the list currently so please reply > to my address as well as the list. signature.asc Description: This is a digitally signed message part
Re: [SQL] select query that would join two databases
Take a look at the dblink() contrib module. PostgreSQL does not currently support inter-database joins BUT it does offer Schemas which is often enough. Take each of your source databases and toss them into two different schemas of the same database. On Wed, 2003-07-30 at 07:28, Keith H.K. Lam (U1 Tech.) wrote: > dear all, > > How to create a query that would join two databases? In MSSQL, i use > > select * from dbA.dbo.tableA a dbB.dbo.tableB b where a.id=b.id > > Is there anyway to write the above query in postgressql ? > > Please email me back. > Thanks, > Keith > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster > signature.asc Description: This is a digitally signed message part
[SQL] Using contrib/fulltext on multiple tables
I am running into a problem with using contrib/fulltext on several tables. Here's the details... I have one sql script which drops everything and recreates the database structure, then populates it with some test data. In this script I create tables a,b and c and setup the triggers for fulltext indexing (as well as associated tables for the index data). Later in the script when inserting into these tables via stored procedures, the full text indexes get created for a and b but not c. It's just empty. If I just have a and c setup in the script, they both work. Same for a and b. It seems to be a problem with b and c. Something else that is odd is I can then call the Insert stored procedure for c and it's index doesn't get created. However, if I exit and reenter the psql client app, THEN run the Insert stored procedure, the index is created just fine. I've tried breaking it into two scripts (one for db structure and one for test data) but it still doesn't do the indexing on c. I apologize for the length, Eric ---(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] Does a the block of code within a stored procedure constitute a transaction?
>> What I need to know is whether or not this is multi-user safe, i.e., >> will the block of code in the procedure execute as a transaction so >> that if more than one clerk creates an expense report for the same >> employee simultaneously is it possible or impossible that value of >> the >> employee.expense_report_seq gets updated by the second clerk between >> the SELECT and UPDATE statements invoked by the first clerk? >> >> And as a follow-up, should I add the FOR UPDATE clause to the SELECT >> statement? > > SELECT .. FOR UPDATE would be appropriate. > > However, you could also do an update + 1 first and the select second to > find what you changed the value to. Yes, and I like the fact that the UPDATE first approach eliminates a local variable declaration as well! ~Berend Tober ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] [GENERAL] ALTER TABLE ... DROP CONSTRAINT
--- Elielson Fontanezi <[EMAIL PROTECTED]> wrote: > Hi all! > > Who can tell me what postgres version supports > ALTER TABLE... DROP > CONSTRAINT without > the need of droping the table to remove a simple > coinstraint. (link) > > Elielson Fontanezi > PRODAM - Technical Support > Analyst I performed that task yesterday using PostgreSQL 7.3.3: Alter table mkt drop constraint mkt_pkey; I don't know if it's supported in earlier versions. Best regards, Andrew Gould ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
