[SQL] datediff is there something like it?
Hi all working my way through our views and all is going very well. We use datediff in MSSQL a bit and I read about the field1::date – field2::date to return the days numerically. Is there any way to get months and years besides guessing days / 30 for months etc? Joel Fradkin Wazagua, Inc. 2520 Trailmate Dr Sarasota, Florida 34243 Tel. 941-753-7111 ext 305 [EMAIL PROTECTED] www.wazagua.com Powered by Wazagua Providing you with the latest Web-based technology & advanced tools. © 2004. WAZAGUA, Inc. All rights reserved. WAZAGUA, Inc This email message is for the use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply email and delete and destroy all copies of the original message, including attachments.
Re: [SQL] datediff is there something like it?
O Joel Fradkin έγραψε στις Jan 25, 2005 :
> Hi all working my way through our views and all is going very well.
>
> We use datediff in MSSQL a bit and I read about the field1::date -
> field2::date to return the days numerically.
>
> Is there any way to get months and years besides guessing days / 30 for
> months etc?
Go to
http://www.postgresql.org/docs/7.4/interactive/functions-datetime.html
Check out smth like...
SELECT 'Achilleus is ' || date_part('years',age(timestamp '1969-01-31'))
|| ' years and ' || date_part('months',age(timestamp '1969-01-31')) || '
months old';
?column?
-
Achilleus is 35 years and 11 months old
(1 row)
>
>
>
> Joel Fradkin
>
>
>
> Wazagua, Inc.
> 2520 Trailmate Dr
> Sarasota, Florida 34243
> Tel. 941-753-7111 ext 305
>
>
>
> [EMAIL PROTECTED]
> www.wazagua.com
> Powered by Wazagua
> Providing you with the latest Web-based technology & advanced tools.
> C 2004. WAZAGUA, Inc. All rights reserved. WAZAGUA, Inc
> This email message is for the use of the intended recipient(s) and may
> contain confidential and privileged information. Any unauthorized review,
> use, disclosure or distribution is prohibited. If you are not the intended
> recipient, please contact the sender by reply email and delete and destroy
> all copies of the original message, including attachments.
>
>
>
>
>
>
>
>
>
--
-Achilleus
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] difficult JOIN
Hi, i have the following SQL-Problem: We are using 2 tables. The first, called plan, is holding planned working times for employees per tour: plan.id_tour plan.id_employee plan.begin_time plan.end_time The second table 'work' stores the actual worked times for employees per tour: work.id_tour work.id_employee work.begin_time work.end_time Employees can be multiple times assigned to one tour. One record will be created for every assignment. They can also work multiple times in one tour. Now i wanna merge this infos into one report. I wanna join the first plan entry for one employee in one tour with the first work entry for one employee in one tour and so on. How can i obtain that? A simply USING(id_tour, id_employee) -JOIN will not doit. Thanks for any hints, Thomas ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] How to find out programmatically whether a query on a view will use an index?
On Mon, Jan 24, 2005 at 16:34:09 -, Martin Schäfer <[EMAIL PROTECTED]> wrote: > > I'm using the PostGIS spatial extension. Some of my spatial queries (like > live zooming and panning) should only be performed when the column > containing the spatial data is spatially indexed, otherwise the first query > takes almost forever and users will just kill the application out of > frustration. If the real problem is long running queries, maybe using a statement timeout will solve your problem? ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] converting Oracle scripts to PostgreSQL
Hi, I have a database in Oracle that I want to convert to PostgreSQL. I have exported the scripts used to create the tables, constraints and sequences in Oracle and wish to convert these to postgreSQL scripts now. Is there an easy way to do this? Regards, Gary. This email (and any attachments) is private and confidential, and is intended solely for the addressee. If you have received this communication in error please remove it and inform us via telephone or email. Although we take all possible steps to ensure mail and attachments are free from malicious content, malware and virii, we cannot accept any responsibility whatsoever for any changes to content outwith our administrative bounds. The views represented within this mail are solely the view of the author and do not reflect the views of Graham Technology as a whole. Graham Technology plc http://www.gtnet.com ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] "How do I ..." SQL question
Hi there: Thank you for the response, which gave me what I wanted. Here is a follow-up question.. First a recap: Table a contains the names of individuals, the places they have visited and the year in which they were visited. Let's see who has visited where and when: SELECT * FROM a; name place year -- --- -- kimnorth 2004 kimsouth 2003 kimsouth 2003 bobwest2004 bobwest2004 bobwest2003 joesouth 2004 joesouth 2005 suewest2004 bobeast2003 joeeast2004 joeeast2004 suesouth 2004 bobnorth 2004 bobnorth 2005 Summarize data by number of places visited by year: SELECT count(*) AS count, name, year FROM a GROUP BY name, year ORDER BY count DESC, name ASC; count name year --- -- -- 3 bob2004 3 joe2004 2 bob2003 2 kim2003 2 sue2004 1 bob2005 1 joe2005 1 kim2004 Return only four rows beginning at second row into temporary table: CREATE TEMPORARY TABLE output AS SELECT count(*) AS count, name, year FROM a GROUP BY name, year ORDER BY count DESC, name ASC LIMIT 4 OFFSET 1; SELECT * FROM output; count name year --- -- -- 3 joe2004 s,e,e 2 bob2003 w,e 2 kim2003 s,s 2 sue2004 s,w Select only places visited included in LIMITed query: SELECT DISTINCT a.place FROM a, output WHERE a.name=output.name AND a.year=output.year; place --- south west east Here is the new question. I want to use the above result to update another table which contains unique places visited and also has a field to indicate recently visited places already present. SELECT * FROM places; refresh place - --- 0 south 0 west 0 southwest (The following two queries do not work right and are what I need help with) Add new places: INSERT INTO places (refresh, place) SELECT DISTINCT 1, a.place FROM a, output LEFT JOIN places ON places.place=a.place WHERE a.name=output.name AND a.year=output.year AND places.place IS NULL; Update refresh flag for existing places. Note: the refresh field can have one of several values and I only want to change it when it has a particular value. UPDATE places SET refresh=1 FROM output, a WHERE places.refresh=0 AND places.place=a.place AND a.name=output.name AND a.year=output.year; (The last query never updates the places table and I'm not sure how to do this) I want this result: SELECT * FROM places; refresh place - --- 1 south 1 west 0 southwest 1 east Any help appreciated. -Bob ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] datediff is there something like it?
Yes I am using datepart, but not seeing how with a datediff.
Maybe I just need to do the date math and put a date part on the result.
I will play around a bit when I hit one not looking for days.
Joel Fradkin
Wazagua, Inc.
2520 Trailmate Dr
Sarasota, Florida 34243
Tel. 941-753-7111 ext 305
[EMAIL PROTECTED]
www.wazagua.com
Powered by Wazagua
Providing you with the latest Web-based technology & advanced tools.
© 2004. WAZAGUA, Inc. All rights reserved. WAZAGUA, Inc
This email message is for the use of the intended recipient(s) and may
contain confidential and privileged information. Any unauthorized review,
use, disclosure or distribution is prohibited. If you are not the intended
recipient, please contact the sender by reply email and delete and destroy
all copies of the original message, including attachments.
-Original Message-
From: Achilleus Mantzios [mailto:[EMAIL PROTECTED]
Sent: Tuesday, January 25, 2005 11:34 AM
To: Joel Fradkin
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] datediff is there something like it?
O Joel Fradkin έγραψε στις Jan 25, 2005 :
> Hi all working my way through our views and all is going very well.
>
> We use datediff in MSSQL a bit and I read about the field1::date -
> field2::date to return the days numerically.
>
> Is there any way to get months and years besides guessing days / 30 for
> months etc?
Go to
http://www.postgresql.org/docs/7.4/interactive/functions-datetime.html
Check out smth like...
SELECT 'Achilleus is ' || date_part('years',age(timestamp '1969-01-31'))
|| ' years and ' || date_part('months',age(timestamp '1969-01-31')) || '
months old';
?column?
-
Achilleus is 35 years and 11 months old
(1 row)
>
>
>
> Joel Fradkin
>
>
>
> Wazagua, Inc.
> 2520 Trailmate Dr
> Sarasota, Florida 34243
> Tel. 941-753-7111 ext 305
>
>
>
> [EMAIL PROTECTED]
> www.wazagua.com
> Powered by Wazagua
> Providing you with the latest Web-based technology & advanced tools.
> C 2004. WAZAGUA, Inc. All rights reserved. WAZAGUA, Inc
> This email message is for the use of the intended recipient(s) and may
> contain confidential and privileged information. Any unauthorized review,
> use, disclosure or distribution is prohibited. If you are not the
intended
> recipient, please contact the sender by reply email and delete and destroy
> all copies of the original message, including attachments.
>
>
>
>
>
>
>
>
>
--
-Achilleus
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] "How do I ..." SQL question
PFC wrote: > Return only four rows beginning at second row: > > > > SELECT count(*) AS count, name, year FROM a > > GROUP BY name, year > > ORDER BY count DESC, name ASC > > LIMIT 4 OFFSET 1; > > > > count name year > > --- -- -- > >3 joe2004 s,e,e > >2 bob2003 w,e > >2 kim2003 s,s > >2 sue2004 s,w > > > > Select only places visited included in LIMITed query: > > > Is this : > > SELECT DISTINCT place FROM a,( > SELECT count(*) AS count, name, year FROM a > GROUP BY name, year > ORDER BY count DESC, name ASC > LIMIT 4 OFFSET 1 > ) as foo WHERE name=foo.name AND year=foo.year > > Problem with this approach is that you'll have to run the query twice, > one to get the hitlist by user, one for the places... > > > > > SELECT DISTINCT place FROM a ; > > > > place > > --- > > south > > west > > east Thanks, this worked. As it happens, I am already creating a temporary table for the hitlist for other uses so that isn't a problem. -Bob ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Moving from Transact SQL to PL/pgSQL
Hello: I am starting a project using Postgres. The requirements are very similar to work I have done in the past using M$-SQL. Therefore, there are many Transact SQL stored procedures I need to port over to PL/pgSQL. Where would I find documentation on PL/pgSQL, with examples? How close to Oracle PL-SQL is Postgres? Would a Oracle PL-SQL book cover the basics? Remember be kind to the newbee. Kevin Duffy
Re: [SQL] converting Oracle scripts to PostgreSQL
Gary Broadbent wrote: Hi, I have a database in Oracle that I want to convert to PostgreSQL. I have exported the scripts used to create the tables, constraints and sequences in Oracle and wish to convert these to postgreSQL scripts now. Is there an easy way to do this? These aren't too hard (stored procedures are what can be a bit more tricky). Here are some hints I've gotten in my notes. I'm sure there is more: data types - number can be changed to decimal date should be changed to timestamp (oracle's date has time, postgresql's doesn't) varchar2 needs to be changed to varchar There may be optimizer hints for oracle that are part of the table definitions - you'll need to remove those. Sequences are pretty close. I think I had to get rid of "NOORDER" and change "NOCYCLE" to "NO CYCLE" and add "MINVALUE 0" since the Oracle sequence specified "START WITH 0" in this example: Oracle: CREATE SEQUENCE ABC_DEP_SEQ INCREMENT BY 1 START WITH 0 NOCYCLE CACHE 20 NOORDER; Postgresql: CREATE SEQUENCE ABC_DEP_SEQ INCREMENT BY 1 START WITH 0 MINVALUE 0 NO CYCLE CACHE 20 ; Queries: Queries may need to be rewritten if they use the Oracle syntax for outer joins. Also the NVL function can be replaced with coalesce and DECODE will need to be rewritten with CASE. SYSDATE can be replaced with NOW() Also check out this doc for more hints: http://www-2.cs.cmu.edu/~pmerson/docs/OracleToPostgres.pdf Dennis Sacks [EMAIL PROTECTED] ---(end of broadcast)--- TIP 8: explain analyze is your friend
[SQL] Moving from Transact SQL to PL/pgsql
Hello: I am implementing a new system using Postgres. Well most of the system will be new, but some parts will be very similar to a system currently running under M$-SQL. The first issue will be getting up to speed on PL/pgsql. Where can I find primer on PL/pgsql, with lots of examples? How silimar is PL/pgsql to PL/SQL under Oracle? Thanks in Advance. kd ---(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] update from multiple rows
Thanks a lot.. That is what i searched.. In fact your query is very good for little changes, but i will have to use another method when updating all my rows because the performance is not very good alas. My data set contains something like 4 rows to update in 1+ million records and data_raw, data_sys are of type "real"... The complete update took 40 minutes on a 256Mo, athlon 2400, kernel 2.6 and with no charge during the execution of the query. Is this normal ? The number of columns of the table does it matter a lot (the table contains 12 reals and 4 integers) ? I found that using an intermediate table which stock for every row the value before and the value after helps to gain speed... But it is not a very nice way i think.. Thanks again :) Etienne ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] Looking up table names by REFERENCES
Hello all. I'm trying to write a recursive procedure to allow me to handle some data backup routines and deal with conflicts when restoring from within the program. Essentially, what I'd like to be able to do is if a table called "image" has a column called "file_id" which references a column called "file_id" in another table called "file" I want to be able to determine that pragmatically. If I wanted to backup all of the information about images in the database, I would need to backup all of the information about the file(s) each image corresponds to. For instance, I can get a list of all (user) table names with: SELECT relname AS table_name, oid FROM pg_class WHERE NOT relname ~ 'pg_.*' AND NOT relname ~ 'pga_.*' AND NOT relname ~ '.*_pkey' AND NOT relname ~ '.*_id_key' ORDER BY relname; and I can get a list of column names and their types (for the "image" table) with: SELECT a.attname AS field, t.typname AS type FROM pg_class c, pg_attribute a, pg_type t WHERE c.relname = 'image' and a.attnum > 0 and a.attrelid = c.oid and a.atttypid = t.oid ORDER BY a.attnum; Surely there's a simple way I can trace REFERENCES in a particular column across tables? Any help would be most appreciated, especially if I could be cc'd directly. Cheers Steve Castellotti
Re: [SQL] converting Oracle scripts to PostgreSQL
Hi Gary, I went through a conversion from Oracle to PostgreSQL about 9 months ago. There's a little bit of documentation in the PostgreSQL manual http://www.postgresql.org/docs/7.4/interactive/plpgsql-porting.html or http://www.postgresql.org/docs/8.0/interactiveplpgsql-porting.html I had ment to submit docs to the manual but never got around to it since they could definately be expanded. The biggest issues I had for my project was that there were no statement level triggers and you couldn't do instead of triggers on views if my memory serves correct. plpgsql is really similar to Oracle plsql and in many cases you don't need to even change syntax. I could even you send the work I did since it was for school if you are interested. The reference manual for plpgsql can be found at: http://www.postgresql.org/docs/7.4/interactive/plpgsql.html or http://www.postgresql.org/docs/8.0/interactive/plpgsql.html Installing procedural languages: http://www.postgresql.org/docs/7.4/interactive/xplang.html or http://www.postgresql.org/docs/8.0/interactive/xplang.html triggers: http://www.postgresql.org/docs/7.4/interactive/triggers.html or http://www.postgresql.org/docs/8.0/interactive/triggers.html rules: http://www.postgresql.org/docs/7.4/interactive/rules.html or http://www.postgresql.org/docs/8.0/interactive/rules.html sql command syntax (for scripts): http://www.postgresql.org/docs/7.4/interactive/sql-commands.html or http://www.postgresql.org/docs/8.0/interactive/sql-commands.html Also try searching the mailing here: http://archives.postgresql.org/pgsql-sql/ You will probably find many of my old posts when I was converting Oracle to PostgreSQL. Hope this helps you get started. -Clint Original Message Follows From: "Gary Broadbent" <[EMAIL PROTECTED]> Reply-To: <[EMAIL PROTECTED]> To: Subject: [SQL] converting Oracle scripts to PostgreSQL Date: Wed, 19 Jan 2005 12:37:48 - Hi, I have a database in Oracle that I want to convert to PostgreSQL. I have exported the scripts used to create the tables, constraints and sequences in Oracle and wish to convert these to postgreSQL scripts now. Is there an easy way to do this? Regards, Gary. This email (and any attachments) is private and confidential, and is intended solely for the addressee. If you have received this communication in error please remove it and inform us via telephone or email. Although we take all possible steps to ensure mail and attachments are free from malicious content, malware and virii, we cannot accept any responsibility whatsoever for any changes to content outwith our administrative bounds. The views represented within this mail are solely the view of the author and do not reflect the views of Graham Technology as a whole. Graham Technology plc http://www.gtnet.com ---(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] Moving from Transact SQL to PL/pgSQL
On Mon, Jan 24, 2005 at 12:14:22PM -0500, Kevin Duffy wrote: > Where would I find documentation on PL/pgSQL, with examples? On the PostgreSQL web site, or perhaps on your own server if you've installed the documentation. http://www.postgresql.org/ Follow the "Documentation" link, then follow the "Online Manuals" link for whatever version of PostgreSQL you're running. The PL/pgSQL documentation is under "Server Programming." > How close to Oracle PL-SQL is Postgres? Would a Oracle PL-SQL book cover the > basics? The PL/pgSQL documentation has a "Porting from Oracle PL/SQL" section. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] How to update dependent tables AND design considerations
Hi!
Because i got no answer in pgsql.novice, i'll try it here. But maybe it
was even too easy for .novice?
Lets say there are three tables:
CREATE TABLE c (id SERIAL, data VARCHAR(20));
CREATE TABLE b (id SERIAL, c_id INTEGER REFERENCES c, data VARCHAR(20));
CREATE TABLE a (id SERIAL, b_id INTEGER REFERENCES b, data VARCHAR(20));
Now i have to insert some data into this table structure.
In my old mysql days i would have inserted into c, look after
the id, insert it into b, look after the id, insert into a...
Of course this could be done here too, but i think it's the worst
case.
Another idea was to create a VIEW which is updatable and
insertable by RULES. But this solution simply shifts the Problem
to the rule definition.
Next idea was using a pl/pgsql function. But still the same
problem: how to do?
Idea:
INSERT INTO TABLE c VALUES (DEFAULT, 'asdfasdfasfd');
INSERT INTO TABLE b VALUES (DEFAULT, currval('c_id_seq'), 'asdfasfasf');
Good Idea? Is this the normal way?
But what about this case:
There is the following table:
CREATE TABLE old (data_a VARCHAR(20), data_b VARCHAR(20), data_c VARCHAR(20);
containig Data that should be incorporated to the above mentioned
table structure. With my solution i have to read the data
with an application and split it into subsequent INSERT
statements. This could not be a good Idea.
Of course i could define the already mentioned VIEW, write
some rules for updating and inserting and INSERT the data
from old table into the VIEW. But is this the
usual way?
Isn't there something available like an INSERT to multiple
tables?
With real updates this should be easier, because the
datasets are already existing and can be joined
within FROM of the UPDATE Statement. But what about
this case:
I get a dataset: ('data a', 'data b', 'data c'). But
the corresponding subset in table a and b already exists.
Do i have to check in my application wheather the
Dataset in table c exists or not and do an seperate
INSERT myself? What about if the Data is coming from
another table and not from an application?
Should i make my UPDATE rule of a possible VIEW
doing this JOB?
Unfortunately the available tutorials cover only quite
simple cases. But my projekt may have up to five or more
dependency layers. It would be nice to have a
readable, manageable and extensible solution.
But i'm afraid my ideas so far are not.
Thank you in advance!
Klaus
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [SQL] Looking up table names by REFERENCES
On Tue, Jan 25, 2005 at 04:09:09AM +1300, Steve Castellotti wrote: > Surely there's a simple way I can trace REFERENCES in a particular > column across tables? The pg_constraint table contains foreign key constraints. Here's an example query that appears to work in trivial tests: SELECT c.conname, c.conrelid::regclass, a1.attname, c.confrelid::regclass, a2.attname AS fattname FROM pg_constraint AS c JOIN pg_attribute AS a1 ON a1.attrelid = c.conrelid AND a1.attnum = ANY (c.conkey) JOIN pg_attribute AS a2 ON a2.attrelid = c.confrelid AND a2.attnum = ANY (c.confkey) WHERE c.contype = 'f'; -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] datediff is there something like it?
On Tue, 25 Jan 2005, Joel Fradkin wrote:
Yes I am using datepart, but not seeing how with a datediff.
Maybe I just need to do the date math and put a date part on the result.
I will play around a bit when I hit one not looking for days.
http://www.pgsql.ru/db/pgsearch/index.html?q=datediff
Joel Fradkin
Wazagua, Inc.
2520 Trailmate Dr
Sarasota, Florida 34243
Tel. 941-753-7111 ext 305
[EMAIL PROTECTED]
www.wazagua.com
Powered by Wazagua
Providing you with the latest Web-based technology & advanced tools.
© 2004. WAZAGUA, Inc. All rights reserved. WAZAGUA, Inc
This email message is for the use of the intended recipient(s) and may
contain confidential and privileged information. Any unauthorized review,
use, disclosure or distribution is prohibited. If you are not the intended
recipient, please contact the sender by reply email and delete and destroy
all copies of the original message, including attachments.
-Original Message-
From: Achilleus Mantzios [mailto:[EMAIL PROTECTED]
Sent: Tuesday, January 25, 2005 11:34 AM
To: Joel Fradkin
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] datediff is there something like it?
O Joel Fradkin ?? Jan 25, 2005 :
Hi all working my way through our views and all is going very well.
We use datediff in MSSQL a bit and I read about the field1::date -
field2::date to return the days numerically.
Is there any way to get months and years besides guessing days / 30 for
months etc?
Go to
http://www.postgresql.org/docs/7.4/interactive/functions-datetime.html
Check out smth like...
SELECT 'Achilleus is ' || date_part('years',age(timestamp '1969-01-31'))
|| ' years and ' || date_part('months',age(timestamp '1969-01-31')) || '
months old';
?column?
-
Achilleus is 35 years and 11 months old
(1 row)
Joel Fradkin
Wazagua, Inc.
2520 Trailmate Dr
Sarasota, Florida 34243
Tel. 941-753-7111 ext 305
[EMAIL PROTECTED]
www.wazagua.com
Powered by Wazagua
Providing you with the latest Web-based technology & advanced tools.
C 2004. WAZAGUA, Inc. All rights reserved. WAZAGUA, Inc
This email message is for the use of the intended recipient(s) and may
contain confidential and privileged information. Any unauthorized review,
use, disclosure or distribution is prohibited. If you are not the
intended
recipient, please contact the sender by reply email and delete and destroy
all copies of the original message, including attachments.
Regards,
Oleg
_
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [SQL] Moving from Transact SQL to PL/pgSQL
Kevin Duffy wrote: Hello: I am starting a project using Postgres. The requirements are very similar to work I have done in the past using M$-SQL. Therefore, there are many Transact SQL stored procedures I need to port over to PL/pgSQL. Where would I find documentation on PL/pgSQL, with examples? How close to Oracle PL-SQL is Postgres? Would a Oracle PL-SQL book cover the basics? Oracle PL-SQL and PL/pgSQL seem very similar. There are definitely differences however. Most of what you want to know is included in the PostgreSQL document. I'd recommend reading through the PL/pgSQL chapter several times before you begin. _PostgreSQL_ by Douglas and Douglas has a chapter on PL/pgSQL. I'm not sure what that chapter is like as I don't own this book. It might be worth looking into. I don't know of any documents that give hints for porting from TSQL to PL/pgSQL, but then I've not looked for any. They may exist. Best of luck, Dennis Sacks [EMAIL PROTECTED]
Re: [SQL] How to update dependent tables AND design considerations
Have you looked at the documentation on triggers?
-tfo
--
Thomas F. O'Connell
Co-Founder, Information Architect
Sitening, LLC
http://www.sitening.com/
110 30th Avenue North, Suite 6
Nashville, TN 37203-6320
615-260-0005
On Jan 23, 2005, at 11:23 AM, Klaus W. wrote:
Hi!
Because i got no answer in pgsql.novice, i'll try it here. But maybe it
was even too easy for .novice?
Lets say there are three tables:
CREATE TABLE c (id SERIAL, data VARCHAR(20));
CREATE TABLE b (id SERIAL, c_id INTEGER REFERENCES c, data
VARCHAR(20));
CREATE TABLE a (id SERIAL, b_id INTEGER REFERENCES b, data
VARCHAR(20));
Now i have to insert some data into this table structure.
In my old mysql days i would have inserted into c, look after
the id, insert it into b, look after the id, insert into a...
Of course this could be done here too, but i think it's the worst
case.
Another idea was to create a VIEW which is updatable and
insertable by RULES. But this solution simply shifts the Problem
to the rule definition.
Next idea was using a pl/pgsql function. But still the same
problem: how to do?
Idea:
INSERT INTO TABLE c VALUES (DEFAULT, 'asdfasdfasfd');
INSERT INTO TABLE b VALUES (DEFAULT, currval('c_id_seq'),
'asdfasfasf');
Good Idea? Is this the normal way?
But what about this case:
There is the following table:
CREATE TABLE old (data_a VARCHAR(20), data_b VARCHAR(20), data_c
VARCHAR(20);
containig Data that should be incorporated to the above mentioned
table structure. With my solution i have to read the data
with an application and split it into subsequent INSERT
statements. This could not be a good Idea.
Of course i could define the already mentioned VIEW, write
some rules for updating and inserting and INSERT the data
from old table into the VIEW. But is this the
usual way?
Isn't there something available like an INSERT to multiple
tables?
With real updates this should be easier, because the
datasets are already existing and can be joined
within FROM of the UPDATE Statement. But what about
this case:
I get a dataset: ('data a', 'data b', 'data c'). But
the corresponding subset in table a and b already exists.
Do i have to check in my application wheather the
Dataset in table c exists or not and do an seperate
INSERT myself? What about if the Data is coming from
another table and not from an application?
Should i make my UPDATE rule of a possible VIEW
doing this JOB?
Unfortunately the available tutorials cover only quite
simple cases. But my projekt may have up to five or more
dependency layers. It would be nice to have a
readable, manageable and extensible solution.
But i'm afraid my ideas so far are not.
Thank you in advance!
Klaus
---(end of
broadcast)---
TIP 8: explain analyze is your friend
---(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] Moving from Transact SQL to PL/pgsql
On Jan 23, 2005, at 10:22 PM, Duffy House wrote: The first issue will be getting up to speed on PL/pgsql. Where can I find primer on PL/pgsql, with lots of examples? How silimar is PL/pgsql to PL/SQL under Oracle? The PostgreSQL documentation is the place to start: http://www.postgresql.org/docs/8.0/interactive/plpgsql.html The PostgreSQL distribution has a file with some examples in it: src/test/regress/sql/plpgsql.sql The pgEdit distribution (http://pgedit.com/download) has a fairly extensive plpgsql example for importing and analyzing web server logs. I'm not familiar with Oracle, but there is a porting section in the documentation: http://www.postgresql.org/docs/8.0/interactive/plpgsql-porting.html John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL ---(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] update from multiple rows
updating 40.000 records should take no longer than a couple of minutes. I think you should optimise your query before going any further. You have an inner SELECT sentence that executes before anything. It joins EVERY row in your table (1,000,000+) with at most 3 other rows in the same table, so you will end up with about 3,000,000+ rows... but you are interested in only 40,000 rows! To make it simple, add a WHERE condition to fetch only the 40.000 rows you are interested in and discard the others. Make sure also you have indexed the attributes you are filtering on, and the date attribute too. You should use EXPLAIN ANALYZE on the inner query to check how it improves. Once your SELECT query runs fast enough, the UPDATE should go much faster too. The number of columns matters, but as I said, I don't think it's an UPDATE problem. If you don't find the way to speed your query up, try posting to the performance list. [EMAIL PROTECTED] wrote: Thanks a lot.. That is what i searched.. In fact your query is very good for little changes, but i will have to use another method when updating all my rows because the performance is not very good alas. My data set contains something like 4 rows to update in 1+ million records and data_raw, data_sys are of type "real"... The complete update took 40 minutes on a 256Mo, athlon 2400, kernel 2.6 and with no charge during the execution of the query. Is this normal ? The number of columns of the table does it matter a lot (the table contains 12 reals and 4 integers) ? I found that using an intermediate table which stock for every row the value before and the value after helps to gain speed... But it is not a very nice way i think.. Thanks again :) Etienne ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] [SQL] OFFSET impact on Performance???
I am also very interesting in this very question.. Is there any way to declare a persistant cursor that remains open between pg sessions? This would be better than a temp table because you would not have to do the initial select and insert into a fresh table and incur those IO costs, which are often very heavy, and the reason why one would want to use a cursor. Alex Turner NetEconomist On Thu, 20 Jan 2005 15:20:59 +, Richard Huxton wrote: > Andrei Bintintan wrote: > >> If you're using this to provide "pages" of results, could you use a > >> cursor? > > > > What do you mean by that? Cursor? > > > > Yes I'm using this to provide "pages", but If I jump to the last pages > > it goes very slow. > > DECLARE mycursor CURSOR FOR SELECT * FROM ... > FETCH FORWARD 10 IN mycursor; > CLOSE mycursor; > > Repeated FETCHes would let you step through your results. That won't > work if you have a web-app making repeated connections. > > If you've got a web-application then you'll probably want to insert the > results into a cache table for later use. > > -- >Richard Huxton >Archonet Ltd > > ---(end of broadcast)--- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to [EMAIL PROTECTED] so that your > message can get through to the mailing list cleanly > ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] [SQL] OFFSET impact on Performance???
On Thu, 2005-01-20 at 11:59 -0500, Greg Stark wrote: > The best way to do pages for is not to use offset or cursors but to use an > index. This only works if you can enumerate all the sort orders the > application might be using and can have an index on each of them. > > To do this the query would look something like: > > SELECT * FROM tab WHERE col > ? ORDER BY col LIMIT 50 > > Then you take note of the last value used on a given page and if the user > selects "next" you pass that as the starting point for the next page. this will only work unchanged if the index is unique. imagine , for example if you have more than 50 rows with the same value of col. one way to fix this is to use ORDER BY col,oid gnari ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] [SQL] OFFSET impact on Performance???
On Thu, 2005-01-20 at 19:12 +, Ragnar Hafstað wrote: > On Thu, 2005-01-20 at 11:59 -0500, Greg Stark wrote: > > > The best way to do pages for is not to use offset or cursors but to use an > > index. This only works if you can enumerate all the sort orders the > > application might be using and can have an index on each of them. > > > > To do this the query would look something like: > > > > SELECT * FROM tab WHERE col > ? ORDER BY col LIMIT 50 > > > > Then you take note of the last value used on a given page and if the user > > selects "next" you pass that as the starting point for the next page. > > this will only work unchanged if the index is unique. imagine , for > example if you have more than 50 rows with the same value of col. > > one way to fix this is to use ORDER BY col,oid and a slightly more complex WHERE clause as well, of course gnari ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] [SQL] OFFSET impact on Performance???
Isn't this a prime example of when to use a servlet or something similar in function? It will create the cursor, maintain it, and fetch against it for a particular page. Greg -Original Message- From: Richard Huxton [mailto:[EMAIL PROTECTED] Sent: Thursday, January 20, 2005 10:21 AM To: Andrei Bintintan Cc: pgsql-sql@postgresql.org; pgsql-performance@postgresql.org Subject: Re: [PERFORM] [SQL] OFFSET impact on Performance??? Andrei Bintintan wrote: >> If you're using this to provide "pages" of results, could you use a >> cursor? > > What do you mean by that? Cursor? > > Yes I'm using this to provide "pages", but If I jump to the last pages > it goes very slow. DECLARE mycursor CURSOR FOR SELECT * FROM ... FETCH FORWARD 10 IN mycursor; CLOSE mycursor; Repeated FETCHes would let you step through your results. That won't work if you have a web-app making repeated connections. If you've got a web-application then you'll probably want to insert the results into a cache table for later use. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] [SQL] OFFSET impact on Performance???
Alex Turner <[EMAIL PROTECTED]> writes: > I am also very interesting in this very question.. Is there any way to > declare a persistant cursor that remains open between pg sessions? > This would be better than a temp table because you would not have to > do the initial select and insert into a fresh table and incur those IO > costs, which are often very heavy, and the reason why one would want > to use a cursor. TANSTAAFL. How would such a persistent cursor be implemented if not by building a temporary table somewhere behind the scenes? There could be some advantage if the data were stored in a temporary table marked as not having to be WAL logged. Instead it could be automatically cleared on every database start. -- greg ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] same question little different test MSSQL vrs Postgres
I also tried a simple select * from tblcase where clientum = ‘SAKS’ On both MSSQL and Postgres. MSSQL was 3 secs, Postgres was 27 secs. There is a key for clientnum, but it appeared on both systems (identical Dell Desktops Postgres is running Linux MSSQL is XP) it did not do a indexed search. Joel Fradkin Wazagua, Inc. 2520 Trailmate Dr Sarasota, Florida 34243 Tel. 941-753-7111 ext 305 [EMAIL PROTECTED] www.wazagua.com Powered by Wazagua Providing you with the latest Web-based technology & advanced tools. © 2004. WAZAGUA, Inc. All rights reserved. WAZAGUA, Inc This email message is for the use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply email and delete and destroy all copies of the original message, including attachments.
[SQL] Sorry I see my first question did not get posted (maybe because of the attatchments)
Basically the question was why would a view use an indexed search on one result set but a seq search on a larger result set. Same view only difference is how many rows are returned. The large result set was doing a seq search and did not return after several minutes. The same sql ran in 135 seconds on my MSSQL system. Joel Fradkin Wazagua, Inc. 2520 Trailmate Dr Sarasota, Florida 34243 Tel. 941-753-7111 ext 305 [EMAIL PROTECTED] www.wazagua.com Powered by Wazagua Providing you with the latest Web-based technology & advanced tools. © 2004. WAZAGUA, Inc. All rights reserved. WAZAGUA, Inc This email message is for the use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply email and delete and destroy all copies of the original message, including attachments. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] same question little different test MSSQL vrs Postgres
Joel Fradkin wrote: I also tried a simple select * from tblcase where clientum = ‘SAKS’ On both MSSQL and Postgres. MSSQL was 3 secs, Postgres was 27 secs. There is a key for clientnum, but it appeared on both systems (identical Dell Desktops Postgres is running Linux MSSQL is XP) it did not do a indexed search. One of the things you'll want to do regularly is run a "vacuum analyze". You can read up on this in the postgresql docs. This is essential to the indexes being used properly. At a bare minimum, after you import a large amount of data, you'll want to run vacuum analyze. Dennis Sacks [EMAIL PROTECTED]
Re: [SQL] same question little different test MSSQL vrs Postgres
"Joel Fradkin" <[EMAIL PROTECTED]> writes: > I also tried a simple select * from tblcase where clientum = 'SAKS' Try: explain analyze select * from tblcase where clientum = 'SAKS' Send the output. -- greg ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] working with multidimensional arrays in plpgsql
hello everyone i am having problem with multidimensional arrays in plpgsql following is the source code of the function which i am trying to run CREATE OR REPLACE FUNCTION test() RETURNS VOID AS $$ DECLARE x INTEGER[10][10]; tmp VARCHAR(40); BEGIN x[3][1] := '20'; --i have even tried x[3][1] = 20 tmp := x[3][1]; RAISE NOTICE '%', tmp; RETURN; END; $$LANGUAGE 'plpgsql'; As you might have observed here, the actual problem is how to do assignment to multidimensional array locations using the subscript operater. Thank you ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
