Re: [SQL] [HACKERS] Index of a table is not used (in any case)
> Hello PostgreSQl Users! > > PostSQL V 7.1.1: You should upgrade to 7.1.3 at some point... > I have defined a table and the necessary indices. > But the index is not used in every SELECT. (Therefore, the selects are > *very* slow, due to seq scan on > 20 million entries, which is a test setup up to now) > > The definitions can be seen in the annex. > > Does some body know the reason and how to circumvent the seq scan? Yes. You probably have not run 'VACUUM ANALYZE' on your large table. > Is the order of index creation relevant? I.e., should I create the > indices before inserting > entries or the other way around? If you are inserting a great many entries, insert the data first and then create the indices - it will be much faster this way. > Should a hashing index be used? (I tried this, but I got the known error > "Out of overflow pages") Just do the default CREATE INDEX - btree should be fine... (probably) > The table entry 'epoche' is used in two different indices. Should that > be avoided? It's not a problem, but just check your EXPLAIN output after the VACUUM to check that you have them right. Chris ---(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] make a unique index for foreign keys?
> Chris/ Josh- > > OK-response to Chris below. Synopsis heresimply by creating a foreign > key will not create an index. On large tables I SHOULD put a non-unique > index on the foreign key (right?) For large tables, I guess you may as well. You can be more scientific about it if you you unhash this in your postgresql.conf: stats_command_string = true stats_row_level = true stats_block_level = true Then you can just use the pg_stat views to see how many sequential scans are being run over your tables and how expensive they are, etc. Chris ---(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] Function not running after upgrade 7.03 to 7.2
> Query :
>
> SELECT *,
> (SELECT rtrim(buildUmfang(zylinder.z_typ), ','::text) AS rtrim) AS umfang
> FROM zylinder;
>
> Thank's in advance for any help jr
Try this:
SELECT *,
(SELECT rtrim("buildUmfang"(zylinder.z_typ), ','::text) AS rtrim) AS umfang
FROM zylinder;
I think it's because you have an upper case letter in the name - you might
need to quote it.
Chris
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Aggregates not allowed in WHERE clause?
> Next I tried this one: > > SELECT kvvnr, max(lf_sem.semester) AS akt_semester > FROM lv, lf_sem > WHERE lv.semester = akt_semester; > > Now I got: 'Attribute 'akt_semester' not found' > > Is there another way to get what I want? What about: SELECT kvvnr FROM lv WHERE semester = (SELECT MAX(semester) FROM lf_sem); Chris ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Select + min question
> Now, what I want is to select the b_date which has the minimum "id" and > active=t. > > A normal query would be : > SELECT b_date,min(id) FROM test WHERE active='t' GROUP BY id LIMIT 1 > > However, I only want to select b_date. So, if I perform the following > query, I get the following error: > > devrim=# SELECT b_date FROM test WHERE active='t' AND id=min(id) LIMIT 1; > ERROR: Aggregates not allowed in WHERE clause Use a subselect (and don't compare to 't' if it's a boolean field...) SELECT b_date FROM test WHERE active AND id=(SELECT min(id) FROM test) LIMIT 1; Chris ---(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] what is the difference between default 0 vs default '0'
Hi Beth, I wouldn't have thought there'd be any difference to you, the user. If you used 0, then that is an integer and that will be the default. If you used '0' then that is a character or string and it will automatically be cast into an integer when used as a default. Chris - Original Message - From: "Beth Gatewood" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Wednesday, June 26, 2002 8:52 AM Subject: [SQL] what is the difference between default 0 vs default '0' > all- > Could somebody tell me why I would use default 0 vs default '0' in the > following > CREATE TABLE foo (col1 INTEGER default 0) <-- or default '0' > > Thanks- > Beth > > > > > ---(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 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Possibility of Index-Only access in PostgreSQL?
> - Oracle is content with data read from an index if that is all that's >needed. Indeed, I only need bar and baz and those are in the index. >The benefits of not having to seek the scattered rows from the data >table saves 35000 back and forth head movements / seeks. Postgres can't use the data from its indices, because of the MVCC mechanism. Maybe you should try CLUSTERing your table to improve performance??? Chris ---(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] selecting from integer[]
Well, you can always just UNION them - I don't know if there's a better way tho. select * from ... union [all] select * from ... union [all] select * from ...; Chris On Sat, 29 Jun 2002, Kelly wrote: > > select * from pg_user where usesysid=(select grolist[1] from pg_group > where groname='mygroup'); > select * from pg_user where usesysid=(select grolist[2] from pg_group > where groname='mygroup'); > select * from pg_user where usesysid=(select grolist[3] from pg_group > where groname='mygroup'); > > Can those three queries be merged to one query? (and still gives me > those three rows) > Or do I have to explicitly say grolist[1], grolist[2], etc > > Thanks in advance :) > > > > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org > > ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] CASE Select, referring to selected value
> select case when (current_date > available) then ((end_date - > current_date) / 30) > when (current_date < available) then ((end_date - available) / > 30) >end >from listing >where case > 4 > > which yields: > ERROR: parser: parse error at or near ">" > > Does anyone know how I could accomplish this? Try an alias: select case when (current_date > available) then ((end_date - current_date) / 30) when (current_date < available) then ((end_date - available) / 30) end as asdf from listing where asdf > 4 ---(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] constraint
Hi Ricardo, I assume you're talking about foreign key constraints? Dropping a constraint is a real pain in all versions of Postgres up to and including 7.2.1. You will need to manually drop the RI trigger on the child table and the two triggers on the parent table. Techdocs has some information on doing this here: http://techdocs.postgresql.org/techdocs/hackingreferentialintegrity.php Chris > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED]]On Behalf Of Ricardo Javier > Aranibar León > Sent: Wednesday, 3 July 2002 4:08 AM > To: [EMAIL PROTECTED] > Subject: [SQL] constraint > > > > > Hi list, > I need your help. > How I can delete or DROP a constraint? > I use POSTGRESQL 7.0.3 > > _ > MSN. Más Útil cada Día. http://www.msn.es/intmap/ > > > > > ---(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] Why doesn't it use indexes?
1. ANALYZE both tables. Go 'VACUUM ANALYZE;' to vacuum and analyze your tables. Analyzing means to update the planner statistics for the tables, which might make Postgres use your indices. 2. If you tables are very small (eg. only a few hundred rows) then using an index is usually slower than just scanning the table, so Postgres won't use the index until the table grows. (So long as you regularly update the planner statistics) Chris > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED]]On Behalf Of Ahti Legonkov > Sent: Tuesday, 2 July 2002 11:47 PM > To: [EMAIL PROTECTED] > Subject: [SQL] Why doesn't it use indexes? > > > Hi, > > I have this query: > select * from reo inner join usr on reo.owner_id=usr.user_id > > I also have created these indexes: > CREATE INDEX "owner_id_reo_key" ON reo (owner_id); > CREATE INDEX "user_id_user_key" ON usr (user_id); > > Explain tells me this: > Merge Join (cost=1341.74..1481.12 rows=299697 width=461) >-> Sort (cost=775.05..775.05 rows=6629 width=328) > -> Seq Scan on apartment_reo reo (cost=0.00..354.29 rows=6629 > width=328) >-> Sort (cost=566.69..566.69 rows=4521 width=133) > -> Seq Scan on USER usr (cost=0.00..292.21 rows=4521 width=133) > > Why it does not use indexes I have created? > > -- > Ahti Legonkov > > > > > ---(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 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Possible Bug regarding temp tables (sql or psql?)
> Forgot to mention that adding > DROP TABLE v_idx ; > before the END WORK will fix things. However, I was under the > impression that > temporary tables would go away after a transaction in which they > were created > was committed. No - they go away at the end of a _connection_. However, there is now a patch floating around on -hackers that would add an ' ON COMMIT DROP;' option to CREATE TEMP TABLE. Chris ---(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] postgres7.2.1 upgrading
> http://www.ca.postgresql.org/sitess.html > says that: > The current version of PostgreSQL is 7.2.1. > NOTE: An initdb will only be required if upgrading from pre 7.2 > > So, if my current version is 7.2.0 and I want upgrade it to > 7.2.1, what file should I download in order to get 'intidb'? > if only the 'initdb' is required, > then what is the upgrade procedure? > I don't need to install whole 7.2.1 at all, do I. > my guess is that: > 1.shutdown the db; > 2.copy initdb of 7.2.1 to the location of initdb of 7.2.0; > 3.re_intialize db by running initdb; > 4.re_start postmaster. You do not need to do anything special. Just install the new version of postgres over your existing version (making a backup dump of your data first, of course). 7.2.0 is the same as 7.2, it's not 'pre 7.2'. Chris ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Is Dropping a column "CHECK" constraint possible?
> can anyone please help? > i have a to drop a check contstraint from a column. eg > > tradein_clients=# \d t_a >Table "t_a" >Column | Type | Modifiers > +-+--- > company_id | integer | > exp| text| > imp| text| > Check constraints: "$1" (length(imp) > 1) >"aq" (length(imp) > 1) > > Can i remove to contraints thru ALTER TABLE or similar commands. > I have read the docs for ALTER TABLE but the command described is > not working for me. This should work, so long as you're using postgres 7.2+ ALTER TABLE "t_a" DROP CONSTRAINT "aq" RESTRICT; Chris ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] newbie question
> Transactions (well, recently mysql allows them but using propietary > extensions), foreign key relationships, subqueries, stored > procedures/triggers. MySQL lacks all of these. MySQL's InnoDB tables have foreign keys, but they are RESTRICT only - ie. they're kinda useless. > On the other side, postgres is only ported on *nix platforms, but you > can put cygwin/cygipc on Windows, if that is the case. And mysql is > "friendlier", that means wrong column value types (INT_VALUE = '5') and > double quotes working as simple quotes, so it's easier to start with. It's a trap that 'easy to use' factor. Problem with MySQL is that they just make up whole swathes of their SQL syntax out of whole cloth. They just make it up. Then, when you go to use any other SQL-standard database on Earth you have a rather painful learning and code conversion process. Just talk to anyone who uses MySQL's date and time functions, ISNULL(field), or 'KEY's and stuff... Poor newbies get to thinking that MySQL's way is the standard way, but it's not. That's what happened to me, and my life has been a wonderful thing ever since we ditched MySQL in favour of Postgres in our company! Chris ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] is there a way to get hh:mm:ss given seconds
> > I tried all these. This is what I get > > SQL> SELECT "interval"(cast(422 as varchar) || ' seconds'); > > SELECT "interval"(cast(422 as varchar) || ' seconds') > > * > > ERROR at line 1: > > ORA-00906: missing left parenthesis > > > SQL> select '422 seconds'::interval; > > select '422 seconds'::interval > > * > > ERROR at line 1: > > ORA-00923: FROM keyword not found where expected > > Those don't look like PostgreSQL error messages. I think you are using > some other database. Yes - he's using Oracle. I think Narendra is rather confused... Chris ---(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] PostgreSQL - contrib/xml
The usual way to install a contrib is to run configure with whatever args you want in the root postgres source dir. Then: cd contrib/xml gmake all gmake install Sort of thing. Chris > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED]]On Behalf Of Rudi Starcevic > Sent: Friday, 12 July 2002 7:27 AM > To: [EMAIL PROTECTED] > Subject: [SQL] PostgreSQL - contrib/xml > > > Hello :-) > > I'm not sure how to compile in the contrib/xml into Posgtresql. > I do compile Postgres on my Debian box but I'm not sure about the flags > etc to > also compile the contrib stuff. > > I know the standard compile commands / options so in order to get the xml > compiled can you tell me what to add. > I'm using: > > ./configure --with-perl --with-openssl > make > make install > > Thanks kindly > Regards > Rudi. > > > > ---(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 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] Quick Question
How do you get this to work? I'm creating a view of a table, but I'm trying to do something like this: create table t ( id integer not null, amount numeric(7,2) ); create view v as select id as v_id, 'paid amount: ' || amount as v_comment from t ; You get this: ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] Sorry..
Ignore previous half-completed email. How do you get this to work in 7.2.1? I'm creating a view of a table, but I'm trying to do something like this: create table t ( id integer not null, amount numeric(7,2) ); create view v as select id as v_id, 'paid amount: ' || amount as v_comment from t ; You get this: ERROR: Unable to identify an operator '||' for types 'unknown' and 'numeric' You will have to retype this query using an explicit cast None of these work: CAST(amount AS text) CAST(amount AS varchar) CAST(amount AS char) and this: CAST(amount AS real) works, but if amount is 12.00, then you just get '12' - which is not cool. Any ideas? Chris ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Sequence name length
Hi Rudi,
Unless you redefine it before compiling, postgres has a built-in limit of 31
characters for names. Increasing this has a performance penalty, but it
might happen for 7.3 due to some improvements in the performance area.
BTW, the best way to do a sequence primary key is lik ethis:
create table blah (
maincat_id SERIAL
);
Chris
> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED]]On Behalf Of Rudi Starcevic
> Sent: Wednesday, 17 July 2002 10:39 AM
> To: [EMAIL PROTECTED]
> Subject: [SQL] Sequence name length
>
>
> Hi,
>
> Just a quick one on the length of the name of a sequence.
>
> I have some table with long(ish) names like : eselect_maincategory
> The primary key is named : maincat_id
>
> When I create the table the sequence sql looks like
> nextval('"eselect_maincategory_maincat_id_seq"'::text)
>
> However the sequence created is named :
> eselect_maincategory_maincat_id ( the '_seq' is lopped off )
>
> This looke like a max of 31 characters.
>
> Is a sequence name length limited to 31 characters or is it the tool I'm
> using - phpPgAdmin, I wonder ?
>
> Your time and thoughts are very much appreciated.
> Thank you.
> Rudi Starcevic.
>
>
>
> ---(end of broadcast)---
> TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
>
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Sequence name length
> > However the sequence created is named : > > eselect_maincategory_maincat_id ( the '_seq' is lopped off ) > > What version are you running? Anything recent will keep the '_seq' > and lop elsewhere. Not if he's manually creating a sequence name that's too long - it will just truncate it methinks... Chris ---(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] id and ID in CREATE TABLE
> Can somebody explain me a bit about: > > 1. As far as I know column names in Tables are not case > sensitive. Correct ? > So I know if I pickup ID is not a clever idea but for this > example it is ok. I think your examples have proved that column names are in fact very much case sensitive. However, you will need to double quote mixed case names: eg. SELECT "ID" FROM ttt2; > As well if I have name and Name it should not matter for SQL. Well it does in Postgresql. I highly recommend you just use lowercase field names. > 2. Why psql converts from upper case in lower case column name ID ? > Like in the first case. Because you didn't double quote it. > 3. How comes that first using psql I cannot create the column name ID but > using pgaccess I can ? Is this a bug ? Because pgaccess is doing the double quoting for you. Chris ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Queries not using Index
> I inserted the data via 30 "COPY t2002_06 from stdin" (one per > day) So it > was grouped by station and then day for each insert. (My script dumped > the data from each station for the day and then repeated for each station > and then finally dumped the entire day into the DB. Are you saying that > this process has tricked pgsql into not believing it needs to use an > INDEX? Sorry for my ignorance here. I have done similar processes with > PG7.1.2 and it seemed to use the INDEX. > > In fact, I just repeated the dumping scripts on a machine with 7.1.2 and > the "explain select" reports to be using the Index Scan. H You _have_ actually run ANALYZE on the table, right? Chris ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Queries not using Index
Have you tried playing with the statistics gatherer? >From the ANALYZE docs: "The extent of analysis can be controlled by adjusting the per-column statistics target with ALTER TABLE ALTER COLUMN SET STATISTICS (see ALTER TABLE). The target value sets the maximum number of entries in the most-common-value list and the maximum number of bins in the histogram. The default target value is 10, but this can be adjusted up or down to trade off accuracy of planner estimates against the time taken for ANALYZE and the amount of space occupied in pg_statistic. In particular, setting the statistics target to zero disables collection of statistics for that column. It may be useful to do that for columns that are never used as part of the WHERE, GROUP BY, or ORDER BY clauses of queries, since the planner will have no use for statistics on such columns. " Just a thought... Also, what is the result of: select indexdef from pg_indexes where indexname='t2002_06_station_idx'; > Any thoughts? I am sorry to be causing all this trouble. I just want my > queries to voom-voom!! Interestingly enough, I see that the SEQ SCAN is > now estimated at 1730.63, when I first posted to this list, it > was 3900.00 > or so. E It's no trouble. Cases where the planner fails are essential to improving the planner. Ideally this query should use your index automatically... Chris ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Queries not using Index
> This SELECT causes a sequention scan 'cause your index > is not HASH type, but likely a BTREE one. > BTREE index is to interval searches (station = 'SAMI4%') > not precise searchs. (station = 'SAMI4'). In Postgres, the hash index is slow and inefficient (it's a bit better in7.3), and I believe btree is generally recommended over hash even for '=' instances. Chris ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Case in-sensitive
> Can I config the Postgresql so that it can match case > in-sensitive pattern > automatically? i.e. I don't need to explicit convert the pattern to lower > case like this: .WHERE lower(textfield) LIKE lower(pattern) All you need to do is this: ..WHERE textfield ILIKE pattern Chris ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Extremely slow query
Did you know that you can probably change your GROUP BY clause to use a column ref, rather than repeating the CASE statement: GROUP BY gmmid, gmmname, divid, divname, feddept, deptname, fedvend, vendor_name, masterid, master_desc, pageid, oz_description, 13, price_original, price_owned_retail, cur_price, oz_color, oz_size, pageflag, itemnumber, mkd_status, option4_flag Doesn't help performance, but does help clarity :) Chris > query: > SELECT gmmid, gmmname, divid, divname, feddept, deptname, fedvend, > vendor_name, masterid, master_desc, pageid, oz_description, ( > CASE > WHEN (masterid IS NULL) THEN pageid > ELSE masterid END)::character varying(15) AS pagemaster, > CASE > WHEN (masterid IS NULL) THEN oz_description > ELSE master_desc > END AS pagemaster_desc, > CASE > WHEN (masterid IS NULL) THEN price_original > ELSE NULL::float8 > END AS org_price_display, > CASE > WHEN (masterid IS NULL) THEN cur_price > ELSE NULL::float8 > END AS cur_price_display, price_original, price_owned_retail, > cur_price, oz_color, oz_size, pageflag, itemnumber, > sum(cur_demandu + cur_returnu) AS cur_net_units, > sum(cur_demanddol + wtd_returndol) AS cur_net_dollar, > sum(wtd_demandu + wtd_returnu) AS wtd_net_units, > sum(wtd_demanddol + wtd_returndol) AS wtd_net_dollar, > sum(lw_demand + lw_returnu) AS lw_net_units, > sum(lw_demanddollar + lw_returndollar) AS lw_net_dollar, > sum(ptd_demanddollar + ptd_returndollar) AS ptd_net_dollar, > sum(ptd_demand + ptd_returnu) AS ptd_net_units, > sum(std_demanddollar + std_returndollar) AS std_net_dollar, > sum(std_demand + std_returnu) AS std_net_units, > sum(total_curoh) AS total_curoh, > sum(total_curoo) AS total_curoo, > sum((float8(total_curoh) * price_owned_retail)) AS curoh_dollar, > sum((float8(total_curoo) * price_owned_retail)) AS curoo_dollar, > sum(total_oh) AS total_oh, > sum(total_oo) AS total_oo, > sum((float8(total_oh) * price_owned_retail)) AS oh_dollar, > sum((float8(total_oh) * price_owned_retail)) AS oo_dollar, > mkd_status, > option4_flag > FROM tbldetaillevel_report detaillevel_report_v > GROUP BY gmmid, gmmname, divid, divname, feddept, deptname, fedvend, > vendor_name, masterid, master_desc, pageid, oz_description, > CASE > WHEN (masterid IS NULL) THEN pageid > ELSE masterid > END, > CASE > WHEN (masterid IS NULL) THEN oz_description > ELSE master_desc > END, > CASE > WHEN (masterid IS NULL) THEN price_original > ELSE NULL::float8 > END, > CASE > WHEN (masterid IS NULL) THEN cur_price > ELSE NULL::float8 > END, price_original, price_owned_retail, cur_price, > oz_color, oz_size, > pageflag, itemnumber, mkd_status, option4_flag ---(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] Returning PK of first insert for second insert use.
You need to do something like this: SELECT proj_id FROM t_proj WHERE oid=xxx; To find value of primary key from oid. Chris > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED]]On Behalf Of Peter Atkins > Sent: Tuesday, 30 July 2002 2:33 AM > To: '[EMAIL PROTECTED]' > Subject: [SQL] Returning PK of first insert for second insert use. > > > All, > > I have two tables t_proj, t_task see below: > > CREATE TABLE t_proj ( > proj_id SERIAL NOT NULL, > PRIMARY KEY (proj_id), > task_id integer(12), > user_id integer(6), > title varchar(35), > description varchar(80) > ); > > CREATE TABLE t_task ( > task_id SERIAL NOT NULL, > PRIMARY KEY (task_id), > title varchar(35), > description varchar(80) > ); > > When I insert into t_task I need to return the task_id (PK) for > that insert > to be used for the insert into the t_proj table. > > I tried using RESULT_OID but I have no idea how to obtain the > true PK using > this opague id. Below is the procedure I tried to use. > > CREATE OR REPLACE FUNCTION insertTask (varchar, varchar) > RETURNS INTEGER AS ' > > DECLARE > -- local variables > oid1 INTEGER; > retval INTEGER; > > BEGIN > INSERT INTO t_task (title, description) VALUES ($1, $2); > > -- Get the oid of the row just inserted. > GET DIAGNOSTICS oid1 = RESULT_OID; > > retval := oid1; > > -- Everything has passed, return id as pk > RETURN retval; > END; > ' LANGUAGE 'plpgsql'; > > > Any help would be great! > > Thanks Again, > -p > > > > ---(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] negative queries puzzle
> but then to get the entirely new items out i use a sub query which takes > for ever > > SELECT DISTINCT * FROM v_postcode_new WHERE postcode NOT IN ( SELECT > postcode FROM v_postcode_old ) ORDER BY postcode ASC; NOT IN is known to be very, very slow in Postgres. Use NOT EXISTS instead: SELECT DISTINCT * FROM v_postcode_new vpn WHERE NOT EXISTS (SELECT TRUE FROM v_postcode_old vpo WHERE vpo.postcode=vpn.postcode) ORDER BY postcode ASC; Chris ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] License
Postgres is totally, absolutely free. It comes under the terms of the BSD license which means you have the right to use and modify the source code in any way you wish, so long as you acknowledge that the code originated in Postgres. It is even more free than MySQL. Chris -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Waheed RahumanSent: Friday, 2 August 2002 1:41 AMTo: [EMAIL PROTECTED]Subject: [SQL] License Hello Can anyone say about license of postgresql database How much it cost around. Thanks in advance waheed
Re: [SQL] Please Help me
I have no experience with ColdFusion, but if you ask a question like whether MySQL or Postgres is better on a Postgres mailing list - we're going to say Postgres. Postgres is in many, many ways vastly superior to MySQL. However, if you are a newbie or are doing this on Win32 platform, you may still choose to use MySQL. Postgres has these things that MySQL doesn't: * SQL compatible date & time handling * subselects * stored procedures * proper foreign keys * proper transactions * views * custom types, aggregates, operators and functions * 4 procedural languages * ...and much, much more... You haven't provided us with very much information, however... Chris -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Waheed RahumanSent: Friday, 2 August 2002 1:11 AMTo: [EMAIL PROTECTED]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 Michelle, Have you tried it with Postgres 7.1 yet, which removed such limitations? Chris -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Michelle MurrainSent: Thursday, 1 August 2002 10:48 PMTo: Chad Thompson; Waheed Rahuman; [EMAIL PROTECTED]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] Random resultset retrieving -> performance bottleneck
> I'm running into a performance problem when considering the following > scenario: I have a fairly large table (1mio rows) related to other smaller > tables (between 100 and 1 rows) and would like to retrieve the joined > data (through a view) in random order. In order to do so, the main table > contains a 'Random' field (which is updated on a regular basis, in order to > re-randomize the data set), on which an index is created: Have you tried adding ORDER BY RANDOM() onto your select query? Chris ---(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] What about this?
That's what your crontab is for. Chris - Original Message - From: "Wei Weng" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Saturday, August 03, 2002 5:40 AM Subject: [SQL] What about this? > Why can't postmaster run VACUUM ANALYZE automatically every once in a > while? Since it is a very useful feature... > > > -- > Wei Weng > Network Software Engineer > KenCast Inc. > > > > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html > ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Bug with dump/restore when using UNION and ORDER BY in views
> Yeah. This is fixed in current sources, and I back-patched it into > the REL7_2 branch, but current plans don't seem to include a 7.2.2 > release --- we'll be going straight to 7.3 beta instead. Is it worth doing a 7.2.2 patch that will dump people's foreign keys as ALTER TABLE/ADD FOREIGN KEY instead of a bunch of CREATE CONSTRAINT TRIGGERs, so that they actually become constraints in 7.3? Also Tom - did you check if you can CREATE CONSTRAINT TRIGGER on a dropped column - I think I neglected to look at that in the patch I submitted originally. Chris ---(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] Conversion
Is the int4 a UNIX epoch? ie. seconds since 1970? If so, then this will generally work: SELECT CAST(int4field AS abstime); or SELECT int4field::abstime; Chris -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Lonh SENGSent: Tuesday, 13 August 2002 11:14 AMTo: [EMAIL PROTECTED]Subject: [SQL] Conversion Hi all, How can I convert from int4 to date time? Regards, Lonh
Re: [SQL] Conversion
> On Tue, Aug 13, 2002 at 11:32:25AM +0800, Christopher Kings-Lynne wrote: > > Is the int4 a UNIX epoch? ie. seconds since 1970? > > > > If so, then this will generally work: > > > > SELECT CAST(int4field AS abstime); > > > > or > > > > SELECT int4field::abstime; > > http://www.postgresql.org/idocs/index.php?datatype-datetime.html says: > > The types abstime and reltime are lower precision types which are used > internally. You are discouraged from using any of these types in new > applications and are encouraged to move any old ones over when > appropriate. Any or all of these internal types might disappear in a > future release. Yes, but in absence of: SELECT EXTRACT(TIMESTAMP FROM EPOCH '12341234234'); (Hint Hint Thomas!!!) It's all he can do. I suggest using the syntax above to convert his integer column to a timestamp column. Chris ---(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] Function error
> Hi , > > Thanx a lot it worked . > > Is there any equivalent of dateadd function in postgres ? I highly recommend you actually read the Postgres manual's entries on date and time manipulation. You can just add intervals to dates: SELECT datefield + INTERVAL '1 month'; Chris ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] [email protected]
Why not spend 5 minutes reading the documentation and trying to do it
yourself before asking us to do your job for you?
http://www.us.postgresql.org/users-lounge/docs/7.2/postgres/plpgsql.html
Chris
> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED]]On Behalf Of philip johnson
> Sent: Wednesday, 14 August 2002 3:29 PM
> To: [EMAIL PROTECTED]
> Subject: [SQL] [EMAIL PROTECTED]
> Importance: High
>
>
> I've a stored procedure I'd like to migrate from MS SqlServer
> to Postgresql 7.2.1. How can I do ?
> here is the stored procedure
> CREATE PROCEDURE ws_doc_tree_new
> @parent_node_id int,
> @label varchar(50),
> @publication varchar(32)
> AS
>
> DECLARE
> @doc_exists int,
> @new_node_id int,
> @parent_doc_id int,
> @parent_node_path varchar(250),
> @parent_tree_level int
>
> /* check if label does not already exists */
> SELECT
>@doc_exists = count(*)
> FROM
>ws_doc_tree
> WHERE
>ws_doc_tree.parent_node_id = @parent_node_id
>AND ws_doc_tree.label = @label
>
> IF (@doc_exists > 0)
> BEGIN
> RAISERROR ('Could not create child node, a folder with the same name
> already exists',16,1)
> RETURN 0
> END
>
> SELECT
> @parent_node_path = ws_doc_tree.node_path +
> ws_doc_tree.label + '/',
> @parent_tree_level = ws_doc_tree.tree_level
> FROM
> ws_doc_tree
> WHERE
> ws_doc_tree.node_id = @parent_node_id
>
> BEGIN TRANSACTION
> SELECT @new_node_id = max(ws_doc_tree.node_id) + 1 FROM ws_doc_tree
>
> INSERT INTO ws_doc_tree
> (node_id,
>label,
>node_path,
>parent_node_id,
>tree_level,
>publication,
>creation_dt)
> VALUES
> (@new_node_id,
>@label,
>@parent_node_path,
>@parent_node_id,
>@parent_tree_level +1,
>@publication,
>current_timestamp) COMMIT TRANSACTION
>
> RETURN @new_node_id
>
>
> ---(end of broadcast)---
> TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
>
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
[SQL] DISTINCT peformance differences
If art_id is the primary key of the la table, is the latter faster? SELECT DISTINCT la.* FROM ... or SELECT DISTINCT ON (la.art_id) la.* FROM ... ie. Does this offer a performance improvement by only doing the distinct on the primary key or not? Thanks, Chris ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] tsearch vs. fulltextindex
Hi, I've just done some performance comparisons between contrib/fulltextindex and contrib/tsearch. Even with every optimisation I can think of for fulltextindex, tsearch is 300 times faster ;) Plus it doesn't require a separate table or complicated queries. I think we should strongly encourage people to use tsearch instead of fulltextindex. I hope to commit some change to fulltextindex in the near future, so I'll add a note to the readme then. Chris eg: australia=# explain analyse select food_id, category_id, description from test_foods where not pending and fulltextidx ## 'baskin&fruit'; NOTICE: QUERY PLAN: Index Scan using fulltextidx_idx on test_foods (cost=0.00..45.93 rows=11 width=40) (actual time=0.22..1.53 rows=8 loops=1) Total runtime: 1.70 msec EXPLAIN australia=# explain analyze SELECT distinct(f.food_id), f.category_id, f.description, f.brand FROM food_foods f, food_foods_fti f0, food_foods_fti f1 WHERE NOT f.pending AND f0.id=f.oid AND f0.string ~ '^baskin' AND f1.id=f.oid AND f1.string ~ '^fruit'; NOTICE: QUERY PLAN: Unique (cost=12.10..12.11 rows=1 width=66) (actual time=532.11..532.25 rows=8 loops=1) -> Sort (cost=12.10..12.10 rows=1 width=66) (actual time=532.10..532.14 rows=8 loops=1) -> Nested Loop (cost=0.00..12.09 rows=1 width=66) (actual time=292.41..531.89 rows=8 loops=1) -> Nested Loop (cost=0.00..6.07 rows=1 width=8) (actual time=292.35..531.35 rows=8 loops=1) -> Index Scan using food_foods_fti_string_idx on food_foods_fti f0 (cost=0.00..3.03 rows=1 width=4) (actual time=0.07..0.45 rows=23 loops=1) -> Index Scan using food_foods_fti_string_idx on food_foods_fti f1 (cost=0.00..3.03 rows=1 width=4) (actual time=0.04..16.52 rows=1092 loops=23) -> Index Scan using food_foods_oid_idx on food_foods f (cost=0.00..6.01 rows=1 width=58) (actual time=0.03..0.04 rows=1 loops=8) Total runtime: 532.49 msec EXPLAIN ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] [HACKERS] tsearch vs. fulltextindex
Well, I think it shouldn't disappear for a few releases yet... Chris > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED]]On Behalf Of Bruce Momjian > Sent: Wednesday, 14 August 2002 12:43 PM > To: Christopher Kings-Lynne > Cc: Hackers; [EMAIL PROTECTED]; [EMAIL PROTECTED] > Subject: Re: [HACKERS] tsearch vs. fulltextindex > > > > Good point. Some said fulltextindex was better for certain queries, but > if no one can come up with such a case, we can remove it. > > ---------- > - > > Christopher Kings-Lynne wrote: > > Hi, > > > > I've just done some performance comparisons between > contrib/fulltextindex > > and contrib/tsearch. Even with every optimisation I can think of for > > fulltextindex, tsearch is 300 times faster ;) > > > > Plus it doesn't require a separate table or complicated queries. > > > > I think we should strongly encourage people to use tsearch instead of > > fulltextindex. I hope to commit some change to fulltextindex > in the near > > future, so I'll add a note to the readme then. > > > > Chris > > > > eg: > > > > australia=# explain analyse select food_id, category_id, > description from > > test_foods where not pending and fulltextidx ## 'baskin&fruit'; > > NOTICE: QUERY PLAN: > > > > Index Scan using fulltextidx_idx on test_foods > (cost=0.00..45.93 rows=11 > > width=40) (actual time=0.22..1.53 rows=8 loops=1) > > Total runtime: 1.70 msec > > > > EXPLAIN > > australia=# explain analyze SELECT distinct(f.food_id), f.category_id, > > f.description, f.brand FROM food_foods f, food_foods_fti f0, > food_foods_fti > > f1 WHERE NOT f.pending AND f0.id=f.oid AND f0.string ~ '^baskin' AND > > f1.id=f.oid AND f1.string ~ '^fruit'; > > NOTICE: QUERY PLAN: > > > > Unique (cost=12.10..12.11 rows=1 width=66) (actual time=532.11..532.25 > > rows=8 loops=1) > > -> Sort (cost=12.10..12.10 rows=1 width=66) (actual > time=532.10..532.14 > > rows=8 loops=1) > > -> Nested Loop (cost=0.00..12.09 rows=1 width=66) (actual > > time=292.41..531.89 rows=8 loops=1) > > -> Nested Loop (cost=0.00..6.07 rows=1 width=8) (actual > > time=292.35..531.35 rows=8 loops=1) > > -> Index Scan using food_foods_fti_string_idx on > > food_foods_fti f0 (cost=0.00..3.03 rows=1 width=4) (actual > time=0.07..0.45 > > rows=23 loops=1) > > -> Index Scan using food_foods_fti_string_idx on > > food_foods_fti f1 (cost=0.00..3.03 rows=1 width=4) (actual > time=0.04..16.52 > > rows=1092 loops=23) > > -> Index Scan using food_foods_oid_idx on food_foods f > > (cost=0.00..6.01 rows=1 width=58) (actual time=0.03..0.04 > rows=1 loops=8) > > Total runtime: 532.49 msec > > > > EXPLAIN > > > > > > ---(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 > > > > -- > Bruce Momjian| http://candle.pha.pa.us > [EMAIL PROTECTED] | (610) 359-1001 > + If your life is a hard drive, | 13 Roberts Road > + Christ can be your backup.| Newtown Square, > Pennsylvania 19073 > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster > ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Casting integer to boolean
> select not count(*) = 0 from my_table; > > Basically, for any integer i, convert to boolean with: not i = 0 Or i != 0 of course... Chris ---(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] performance comparison: DISTINCT and GROUP BY
Hi Devrim, You can use the EXPLAIN ANALYZE syntax to find out quite easily. Chris - Original Message - From: "Devrim GUNDUZ" <[EMAIL PROTECTED]> To: "PostgreSQL Mailing Lists-SQL" <[EMAIL PROTECTED]> Sent: Monday, August 19, 2002 7:22 PM Subject: [SQL] performance comparison: DISTINCT and GROUP BY > > Hi, > > We have two different queries: > > SELECT name,surname FROM my_table GROUP BY name; > > and > > SELECT DISTINCT on(name) name,surname * FROM my_table; > > which gives us the same result. > > Do these queries differ by their performances? I mean, which one works > faster? DISTINCT or GROUP BY? > > Best regards. > > -- > > Devrim GUNDUZ > > [EMAIL PROTECTED] Tel : (312) 295 9318 > [EMAIL PROTECTED] Faks : (312) 295 9494 > > Web : http://devrim.oper.metu.edu.tr > - > > > ---(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 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] recursive function returning "setof"
> The problem is: > A SQL-Function cannot be recursive because it cannot call itself, and it can > perform no loops. > A PLPGSQL-Function cannot return sets. It can perform loops. Check the manual- you can do FOR and WHILE loops. 7.3 will be able to return sets from PLPGSQL funcs it seems. Chris ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] recursive function returning "setof"
http://www3.us.postgresql.org/users-lounge/docs/7.2/postgres/plpgsql-control -structures.html Chris - Original Message - From: "Fritz Lehmann-Grube" <[EMAIL PROTECTED]> To: "Christopher Kings-Lynne" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Monday, August 19, 2002 11:22 PM Subject: Re: [SQL] recursive function returning "setof" > > Thanx Chris, > > but I can't find it. > My Programmer's Guide (.../pgsql/doc/html/xfunc-sql.html) says > >"12.2. Query Language (SQL) Functions > SQL functions execute an arbitrary list of SQL statements," > > Nothing about control structures in SQL functions additional to the normal > command-line syntax. Where can I find it ? > > and (.../pgsql/doc/html/plpgsql.html#PLPGSQL-ADVANTAGES) > >"23.1.1.1. Better Performance >SQL is the language PostgreSQL (and most other Relational Databases) use as > query language. It's portable and easy to learn. But every SQL statement > must be executed individually by the database server. > > That means that your client application must send each query to the database > server, wait for it to process it, receive the results, do some computation, > then send other queries to the server." > > Isn't it just the thing in plpgsql to add control structures to SQL ? > > Fritz > > Christopher Kings-Lynne wrote: > >>The problem is: > >>A SQL-Function cannot be recursive because it cannot call itself, and it > > > > can > > > >>perform no loops. > >>A PLPGSQL-Function cannot return sets. > > > > > > It can perform loops. Check the manual- you can do FOR and WHILE loops. > > 7.3 will be able to return sets from PLPGSQL funcs it seems. > > > > Chris > > > > > > > > ---(end of broadcast)--- > > TIP 4: Don't 'kill -9' the postmaster > > > > > > > ---(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 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 refer to tables in another database( or schema as oracle refers to)
Hi Jiaqing, Basically - you can't. There is a program in the contrib/dblink directory that can help you though. Regards, Chris > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED]]On Behalf Of Jiaqing > Sent: Tuesday, 20 August 2002 5:53 AM > To: [EMAIL PROTECTED] > Subject: [SQL] how to refer to tables in another database( or schema as > oracle refers to) > > > Hello, > I'm still new here and new to PostgreSQL, I'd like to know that after I > have created two databases on my site, such as one is called backend, and > another one is called admin, how do I refer(query) the table from backend > while I'm connected to admin database, or is it possible to do that in > PostgreSQL? any answer is appreciated. > > JJW. > 8/19/2002 > > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster > ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Modify column type
Hi Scott, I believe you can hack the catalogs: (disclaimer) update pg_attribute set atttypmod=104 where attname='email' and attrelid=(select oid from pg_class where relname='student'); Do this in a transaction and then \d the table to check that it has worked before committing. Chris > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED]]On Behalf Of Scott David Walter > Sent: Tuesday, 20 August 2002 12:46 AM > To: [EMAIL PROTECTED] > Subject: [SQL] Modify column type > > > Is there a way to modify the type of an existing column? All I actually > want to do is extend the length of the type from a VARCHAR(6) to > VARCHAR(100). > > > Column | Type | Modifiers > +---+ > email | character varying(6) | not null > > > gold_program=> ALTER TABLE student MODIFY email varchar(100); > ERROR: parser: parse error at or near "modify" > > > > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.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] LIMIT 1 FOR UPDATE or FOR UPDATE LIMIT 1?
> OK, no one has commented on this, so I guess I am going to have to guess > the group's preference. > > My guess, seeing as very few probably use LIMIT and FOR UPDATE together, > is to swap them and document it in the release notes. Was I correct in > my guess? I'm sure very few people do it - but are you sure you can't just allow both syntaxes? Chris ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Calculation Error on Epoch?
What if you try this:
SELECT date_part('epoch','2002-08-28'::TIMESTAMP WITH TIME ZONE)
It's probably 8 hours time different to GMT or somethign like that...
Chris
> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED]]On Behalf Of Josh Berkus
> Sent: Wednesday, 28 August 2002 11:31 AM
> To: [EMAIL PROTECTED]
> Subject: [SQL] Calculation Error on Epoch?
>
>
> Folks,
>
> I'm having a problem with:
>
> SELECT date_part('epoch','2002-08-28'::TIMESTAMP)
>
> Which is consistently returning an epoch timestamp that evaluates to
> 8.27.2002. Is this a known issue? A cross-platform problem?
> Suggestions?
>
> -Josh Berkus
>
> ---(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 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] signed/unsigned integers
> Is it true, that I can't define unsigned integers in a table definition? > > CU Roger I'm not aware of any unsigned types in Postgres. You could probably define your own one though. Chris ---(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] Transaction Newbie
> if the primary key is table_id, with default value
> "nextval('table_seq') - then these two statements:
>
> insert into table (field1,field2,field3) values (value1,value2,value3)
> select currval('table_seq')
>
> work to get me the value I need. Except, of course if someone else
> has inserted a row inbetween these two statements.
Hmmm - I'm not sure currval has that problem - have you actually tried it
with two psql windows?
> I tried a transaction test, and this is what I got:
>
> pew=# begin work;
You can just go 'begin;'
> BEGIN
> pew=# insert into categories values
> ('23423423','test','testing','3','today','today','mpm','test
> category');
> INSERT 83910 1
> pew=# select currval('category_id');
> NOTICE: current transaction is aborted, queries ignored until end of
> transaction block
> *ABORT STATE*
As soon as you see this, it means you have made a syntax error or something
in your sql, which causes an automatic abort.
> pew=# commit work
> pew-# ;
You can't commit once the transaction is aborted, you need to ROLLBACK;
> COMMIT
> pew=# select * from categories;
>
> And the insert didn't happen.
It didn't happen because something caused the whole transaction to be
aborted.
> Am I thinking about this right? Is there a better way to get the
> value of a newly inserted record?
Chris
---(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] writing a function to mimic mysql last_insert_id
But in Postgres, you can have as many "autoincrement" columns in a table as you want - how will you handle that? And what's wrong with select currval()? Chris > I am wondering if I can get some input on the logic that I would need to > code a function that would mimic mysql's last_insert_id().. > > Here is what last_insert_id() does in mysql: > Returns the last automatically generated value that was inserted into an > AUTO_INCREMENT column. > mysql> SELECT LAST_INSERT_ID(); > -> 195 ---(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] DISTINCT
> SELECT > DISTINCT phone, > company_name, > address, > state, > idc_desc_1, > street_number, > street_name, > street_xtra, > suburb, > postcode, > area_code, > ac_phone, > fax_area_code, > fax_phone, > fax_ac_phone, > latitude, > longitude > INTO export_temp > FROM export; SELECT DISTINCT ON (phone) phone, company_name, address, state, idc_desc_1, street_number, street_name, street_xtra, suburb, postcode, area_code, ac_phone, fax_area_code, fax_phone, fax_ac_phone, latitude, longitude INTO export_temp FROM export; ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] DISTINCT ON
Don't know. I've seen it elsewhere so it might even be "standard". Chris > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED]]On Behalf Of Rudi Starcevic > Sent: Friday, 13 September 2002 10:50 AM > To: [EMAIL PROTECTED] > Subject: [SQL] DISTINCT ON > > > Hi, > > Just quickly, DISTINCT ON - Is that Postgresql only ? > Thanks > > > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html > ---(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] Table Copy.
> As on insert to table one I should get the same insert on table two. > As on delete to table one I should get the same delete on table two. > As on update to table one I should get the same update on table two. > > Can someone provide the examples i can study ? Look up 'CREATE TRIGGER' and 'CREATE RULE' in the postgres documentation. Chris ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Running tally
Hi guys, If I have a table that is just a single column full of numbers, how can I select all the rows from the table with a second column that is the running tally so far down the result set? eg: Num Tally so far 0.3 0.3 1.2 1.5 2.0 3.5 ... Does this require PL/PgSQL coding? If so, how do you actually construct an arbitrary row for returning? The docs are somewhat unclear on this. Chris ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] [PERFORM] sql performance and cache
I have two very similar queries which I need to execute. They both have exactly the same from / where conditions. When I execute the first, it takes about 16 seconds. The second is executed almost immediately after, it takes 13 seconds. In short, I'd like to know why the query result isn't being cached and any ideas on how to improve the execution. OK - so I could execute the query once, and get the maximum size of the array and the result set in one. I know what I am doing is less than optimal but I had expected the query results to be cached. So the second execution would be very quick. So why aren't they ? I have increased my cache size - shared_buffers is 2000 and I have doubled the default max_fsm... settings (although I am not sure what they do). sort_mem is 8192. PostgreSQL does not have, and has never had a query cache - so nothing you do is going to make that second query faster. Perhaps you are confusing it with the MySQL query cache? Chris ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] [SQL] sql performance and cache
Nested Loop (cost=0.00..147.11 rows=1 width=148) (actual time=84.00..12323.00 rows=67 loops=1) The planner estimate doesn't seem to match reality in that particular step. Are you sure you've run: ANALYZE oscar_node; ANALYZE oscar_point; And you could even run VACUUM FULL on them just to make sure. Does that make any difference? Chris ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
