[SQL] Seeding
Is there any way to automaticly "seed" a number into a list. For example create table "temp"( select distinct(full_phone) from lists where client_id =8) This gives me 100,000 unique records What i would like to do is, every 2500, insert a specific number like '5552552555' can this be done through sql? or what would be the best approach. TIA Chad
Re: [SQL] Please Help me
I am running RedHat, with Apache and Cold Fusion. I chose PostgreSQL for all of the aforementioned reasons. It works very well with Cold Fusion. I have done some optimizing and am able to run rather complex queries much faster than I ever was able to on any Windows platform database. I had to bail on MySQL because it wouldnt run the sub-queries that i needed. Thanks Chad - Original Message - From: Waheed Rahuman To: [EMAIL PROTECTED] Sent: Thursday, August 01, 2002 11:10 AM Subject: [SQL] Please Help me Dear all Please suggest me which database i can choose for my server setup like 1. Manrake Linux 2. ColdFusion 3. Apache Webserver Now i dont know which database to choose Whether MySQL or PostgreSQL Please suggest me a. Thank you Expecting your reply Regards Waheed Rahuman
Re: [SQL] Please Help me
Title: Re: [SQL] Please Help me Unfortunatly i know of no such problem. I have large text fields being submited to my database, but i restrict the submit page to 255 chars. I will have to test larger numbers and see what errors i get. Thanks Chad - Original Message - From: Michelle Murrain To: Chad Thompson ; Waheed Rahuman ; [EMAIL PROTECTED] Sent: Thursday, August 01, 2002 8:48 AM Subject: Re: [SQL] Please Help me At 8:32 AM -0600 8/1/02, Chad Thompson wrote: I am running RedHat, with Apache and Cold Fusion. I chose PostgreSQL for all of the aforementioned reasons. It works very well with Cold Fusion. I have done some optimizing and am able to run rather complex queries much faster than I ever was able to on any Windows platform database. I had to bail on MySQL because it wouldnt run the sub-queries that i needed. How did you solve the problem of large text fields? We ran into this problem, and was unable to solve it. We'd get an error if we wanted to add more than, I think 200 or so characters. It had to do with the connection between postgres and Cold Fusion.-- .Michelle--Michelle Murrain, Technology Consulting[EMAIL PROTECTED] http://www.murrain.net413-253-2874 ph413-222-6350 cell413-825-0288 faxAIM:pearlbear0 Y!:pearlbear9 ICQ:129250575
Re: [SQL] grouping and first()
select first(a) from ta order by a in access, is equivilent to select a from ta order by a limit 1 Thanks Chad P.S. Note that access will always return the same value if you exclude the order by, this is not necessarily true with postgresql or any real RDBS. - Original Message - From: "peter" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Thursday, October 24, 2002 2:12 AM Subject: [SQL] grouping and first() > Hi, just wondering if anyone can tell me what replaces the first function > in access. > > > Thanks > > PEter > > > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org > ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] subscrip out of range
"subscript out of range" is an error that means you are trying to access part of an array that doesnt exist. It probably means that your data doesnt have all the fields for all the records. Check to see if your data is truncated or if there is an unusual (usually shortened) number of fields in a given record. HTH Chad - Original Message - From: "peter" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Saturday, October 26, 2002 1:25 AM Subject: [SQL] subscrip out of range > i am getting this error when iimport data using the pgaccess client from a > txt file. I will import some records then crash out. Any IDeas? > > has it got anything to do with the primary key on the destination table and > if so how do you temporaryily disable it > > Thanks for any hellp > > PEter > > > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [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
Re: [SQL] making queries more effecient
This should be all you need.
insert into VisitorPointer839 ("VisitorID")
select VisitorID
from ProgramEvent
Where ProgramID = 10
and Type = 0
group by VisitorID
You dont need order by because its not important the order it goes in the
database, just the order that it comes out.
I have found that group by works faster than distinct in some cases. You
may have to test it for your senario.
Thanks
Chad
- Original Message -
From: "Peter T. Brown" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Friday, November 01, 2002 3:24 PM
Subject: [SQL] making queries more effecient
> Hi. I have this query that I have been trying to reduce to a single
> statement, but haven't figured out how. Am I missing something?
>
> CREATE TEMP TABLE temp20561149207391 AS SELECT DISTINCT ON ("VisitorID")
> "VisitorID","Type" FROM "ProgramEvent" WHERE "ProgramID" = 10 ORDER BY
> "VisitorID","Created" DESC;INSERT INTO "VisitorPointer839" ("VisitorID")
> SELECT temp20561149207391."VisitorID" FROM temp20561149207391 WHERE
> temp20561149207391."Type" = 0
>
>
> Thanks
>
>
> ---(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] CSV import
> > Unix EOL is LF not CR. > > Is this the only difference between a dos and unix text file? Thanks Chad ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] 7.2 functions that return multiple result sets?
Found this using google from http://archives.postgresql.org/pgsql-sql/2002-01/msg00312.php Depending on what you're doing (and if you're willing to work with the7.2rcs or wait for it), 7.2 allows you to define functions that returncursors that you can then fetch from within the transaction you called thefunction in, so you should be able to do a sequence like:begin;select * from func(param);-- get back name of cursor, say "" --fetch 10 from "";fetch 10 from "";close "";commit;Seems 7.2 is limited to cursors, where 7.3 will do recordsets (and upgrading is SO easy) HTH Chad - Original Message - From: mail.luckydigital.com To: [EMAIL PROTECTED] Sent: Sunday, February 02, 2003 2:45 PM Subject: [SQL] 7.2 functions that return multiple result sets? Can some one please confirm( with a plpgsql function example please ) a postgres "7.2" function that can return multiple rows to the client. I've gone through the docs and can't find anything to support this -it seems you can only have one return value or null. Yes i'm aware this it is possible in 7.3 - can someone please confirm its not possible in 7.2 or provide me with an example of how to go about it. Thank you.
Re: [SQL] 7.3 "group by" issue
> On 21 Feb 2003 at 19:18, Gaetano Mendola wrote: > > > > Hi folks, > > > > > > This query: > > > > > > SELECT element_id as wle_element_id, COUNT(watch_list_id) > > > FROM watch_list JOIN watch_list_element > > > ON watch_list.id = watch_list_element.watch_list_id > > >AND watch_list.user_id = 1 > > > GROUP BY watch_list_element.element_id > > > > Try: > > > > SELECT element_id as wle_element_id, COUNT(watch_list_id) > > FROM watch_list JOIN watch_list_element > > ON watch_list.id = watch_list_element.watch_list_id > > WHERE > > watch_list.user_id = 1 > >GROUP BY watch_list_element.element_id > > ERROR: Attribute unnamed_join.element_id must be GROUPed or used in > an aggregate function > I think that the wrong problem was solved here. Items in the order by clause must be in the target list. heres what it says in the docs *The ORDER BY clause specifies the sort order: *SELECT select_list * FROM table_expression * ORDER BY column1 [ASC | DESC] [, column2 [ASC | DESC] ...] *column1, etc., refer to select list columns. These can be either the output name of a column (see Section 4.3.2) or the number of a column. Some examples: Note that "column1, etc., refer to select list" HTH Chad ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] 7.3 "group by" issue
> On 21 Feb 2003 at 13:00, Chad Thompson wrote: > > > > > On 21 Feb 2003 at 19:18, Gaetano Mendola wrote: > > > > > > > > Hi folks, > > > > > > > > > > This query: > > > > > > > > > > SELECT element_id as wle_element_id, COUNT(watch_list_id) > > > > > FROM watch_list JOIN watch_list_element > > > > > ON watch_list.id = watch_list_element.watch_list_id > > > > >AND watch_list.user_id = 1 > > > > > GROUP BY watch_list_element.element_id > > > > > > > > Try: > > > > > > > > SELECT element_id as wle_element_id, COUNT(watch_list_id) > > > > FROM watch_list JOIN watch_list_element > > > > ON watch_list.id = watch_list_element.watch_list_id > > > > WHERE > > > > watch_list.user_id = 1 > > > >GROUP BY watch_list_element.element_id > > > > > > ERROR: Attribute unnamed_join.element_id must be GROUPed or used in > > > an aggregate function > > > > > > > I think that the wrong problem was solved here. Items in the order by > > clause must be in the target list. > > > > heres what it says in the docs > > *The ORDER BY clause specifies the sort order: > > > > *SELECT select_list > > * FROM table_expression > > * ORDER BY column1 [ASC | DESC] [, column2 [ASC | DESC] ...] > > *column1, etc., refer to select list columns. These can be either the output > > name of a column (see Section 4.3.2) or the number of a column. Some > > examples: > > > > Note that "column1, etc., refer to select list" > > I don't see how ORDER BY enters into this situation. It's not used. > What are you saying? > -- The same applies to group by... Sorry for the confusion. If the column is not in the select section of the statement, it cant group by it. Try this. SELECT element_id as wle_element_id, COUNT(watch_list_id) FROM watch_list JOIN watch_list_element ON watch_list.id = watch_list_element.watch_list_id WHERE watch_list.user_id = 1 GROUP BY wle_element_id ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Concatenation Snafu
The assumtion that char and varchar can be compared is gone. Any comparison or in this case concatination between the two types needs to be explicitly cast. try SELECT code::varchar || ' ' || diag::varchar, code FROM dsm4 WHERE axis = 1 ORDER BY code; Thanks Chad - Original Message - From: "Thomas Good" <[EMAIL PROTECTED]> To: "Postgres SQL List" <[EMAIL PROTECTED]> Sent: Tuesday, March 25, 2003 3:28 PM Subject: [SQL] Concatenation Snafu SELECT code || ' ' || diag, code FROM dsm4 WHERE axis = 1 ORDER BY code; This worked on 6.3-7.3.1 now it dies with: 'unable to identify an operator || for types 'character' and 'character varying' What happened? --- Thomas Good e-mail: [EMAIL PROTECTED] Programmer/Analyst phone: (+1) 718.818.5528 Residential Services fax: (+1) 718.818.5056 Behavioral Health Services, SVCMC-NY mobile: (+1) 917.282.7359 // Krieg ist selbst Terror - Für Frieden und globale Gerechtigkeit! ---(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
[SQL] Index scan never executed?
I have never been very good at reading these query plans, but I have a bit
of a problem w/ my query. So any help is appreciated.
The query is fairly self explanitory i think. 2 tables, call_results ( 6.5
Million records ) and lists ( 11 Million records )
weblink=# explain analyze
weblink-# select count(*) as count
weblink-# from call_results cr join lists l on cr.list_id = l.id
weblink-# where cr.project_id = '55'
weblink-# and cr.start_time between '4/4/2003 0:0' and now()
weblink-# and l.list_of_lists_id = '691';
QUERY PLAN
-
Aggregate (cost=2519.58..2519.58 rows=1 width=16) (actual
time=110715.45..110715.46 rows=1 loops=1)
-> Nested Loop (cost=0.00..2519.58 rows=1 width=16) (actual
time=110715.43..110715.43 rows=0 loops=1)
-> Index Scan using start_time_idx on call_results cr
(cost=0.00..2021.00 rows=164 width=8) (actual time=110715.42..110715.42
rows=0 loops=1)
Index Cond: ((start_time >= '2003-04-04
00:00:00-07'::timestamp with time zone) AND (start_time <= now()))
Filter: (project_id = 55::bigint)
-> Index Scan using lists_pkey on lists l (cost=0.00..3.03 rows=1
width=8) (never executed)
Index Cond: ("outer".list_id = l.id)
Filter: (list_of_lists_id = 691::bigint)
Total runtime: 110747.58 msec
(9 rows)
The big thing I dont understand is why it tells me (never executed) on
lists_pkey.
I also dont see where all the time is being taken up. I thought that
(actual time=110715.42..110715.42) meant from millisecond this... TO
millisecond that, but that would mean that this index scan took no time. So
as you can see I am very confused. :-)
TIA for any suggestions on how to make this query faster.
Chad
PS I have run vacuum full and analyze as reciently as last night :-)
---(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] Index scan never executed?
> > I guess it's a little unclear what to print for the first number when no > rows are output at all. The code evidently is using the total time spent > in the plan node, but I think it would be at least as justifiable to > print a zero instead. Would you have found that less confusing? Anyone > else have an opinion about whether to change that detail? > No, that makes perfect sense now that it has been explained. > Perhaps an index on project_id would be more helpful, or a two-column > index on (project_id, start_time). As usual, you are in fine form. A two-column index brought the query from 115 sec to 6. Thanks! Chad ---(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] To ListAdms: Is pgsql-sql operating?
I see your post. But no others since Monday. Thanks Chad - Original Message - From: "Achilleus Mantzios" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Thursday, June 05, 2003 5:39 AM Subject: [SQL] To ListAdms: Is pgsql-sql operating? > > Is there any problem with [EMAIL PROTECTED] list? > > -- > == > Achilleus Mantzios > S/W Engineer > IT dept > Dynacom Tankers Mngmt > Nikis 4, Glyfada > Athens 16610 > Greece > tel:+30-210-8981112 > fax:+30-210-8981877 > email: achill at matrix dot gatewaynet dot com > mantzios at softlab dot ece dot ntua dot gr > > > ---(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 4: Don't 'kill -9' the postmaster
Re: [SQL] Urgent Help : Use of return from function/procedure.
Title: Urgent Help : Use of return from function/procedure. - Original Message - From: Anagha Joshi To: [EMAIL PROTECTED] Sent: Sunday, June 22, 2003 11:42 PM Subject: [SQL] Urgent Help : Use of return from function/procedure. Hi, I'm new to postgres and using version 7.2.4 I've created a trigger and function which does the following: trigger 'T' fires after insert on a spcific table takes place and it executes function 'F' Function 'F' returns the new record inserted by 'return new' statement. Now my question is: How can I use this 'new' value in my client program? In this prgm., I want to know which all values are inserted into the table. Help is appreciated. Thx, Anagha Im not sure if this is what you are looking for. But I use postgres this way to know which record I have sent. Its a very simple function and should be self explanitory. I think the GET DIAGNOSTICS is the key for you in this case. -- Function: public.return_mortgage_id(varchar, varchar) CREATE FUNCTION public.return_mortgage_id(varchar, varchar) RETURNS int8 AS ' DECLARE oid1 INTEGER; retval integer; BEGIN insert into mortgage(contact_firstname, contact_lastname, date_submitted) values($1,$2, now()); GET DIAGNOSTICS oid1 = RESULT_OID; select id into retval from mortgage where oid = oid1; return retval; end;' LANGUAGE 'plpgsql' IMMUTABLE; Hope that helps Chad
Re: [SQL] One to many query question
> On Wed, Jul 30, 2003 at 01:11:35PM -0700, Eric Clark wrote: > > On Wed, 2003-07-30 at 12:35, Dave Dribin wrote: > > > CREATE TABLE cd ( > > > id integer unique, > > > artist varchar(25), > > > title varchar(25) > > > ); > > > > > > CREATE TABLE cd_genres ( > > > cd_id integer, > > > genre varchar(25) > > > ); > > > > I think you've got this backwards. There is no advantage in the above > > table's over simply having a genre varchar(25) in the cd table. > > > > You really want: > > > > CREATE TABLE genre ( > > genre_id serial, > > genre varchar(25) > > ); > > > > CREATE TABLE cd ( > > cd_id integer unique, > > artist varchar(25), > > title varchar(25), > > genre_id varchar(25) references genre (genre_id) > > ); > > This doesn't allow multiple genre's per CD, though, does it? A CD > can only have 1 genre_id. I would like the ability to have multiple > genres, in which case a third table is necessary: > > CREATE TABLE cd_genres ( > cd_id integer, > genre_id integer > ); > > cd_id references cd.id and genre_id references genre.genre_id. > > This still requires the complex LEFT JOIN query from my first post, > too, I think, *plus* an extra join between cd_genres and genre. > > -Dave What you may be looking for is a not exists subselect. Im not sure if this quite fits your example.. but maybe it will give you some ideas... SELECT cd.*, rock.genre AS rock, jazz.genre AS jazz, electronic.genre AS electronic FROM cd LEFT JOIN cd_genres jazz ON (cd.id = jazz.cd_id AND jazz.genre = 'Jazz') LEFT JOIN cd_genres electronic ON (cd.id = electronic.cd_id AND electronic.genre = 'Electronic'); WHERE NOT EXISTS (SELECT cd.id FROM cd join cd_genres rock ON (cd.id = rock.cd_id AND rock.genre = 'Rock')) This is quite fast in postgres unless configured wrong.. be sure to join your subselect to your outer query. Hope that helps Chad ---(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] ALTER TABLE ... DROP CONSTRAINT
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) >\\\!/< 55 11 5080 9283 !_"""_! Elielson Fontanezi (O) (o) PRODAM - Technical Support Analyst---oOOO--(_)--OOOo--- Success usually comes to those who are too busy to be looking for it. 0 0---( )--( ) \ ( ) / \_/ \_/ 7.3 supports the drop constraint. The only exception begin if you upgraded your database. It keeps the existing trigger like constraints if youve moved from 7.1 or 7.2. But these triggers can be dropped as well. HTHChad P.S. Great signature! :-)
