Re: [SQL] how do i provide array parameters for my functions in php
On Thu, 13 Jun 2002 [EMAIL PROTECTED] wrote:
> i have just finished creating a function that has an array of integers as its
> parameter. what i would like to know is how do i declare/execute my function
> in php. i tried several syntax
> e.g.
> $arrvalue - an array of integers
>
> $strquery = "select functionname($arrvalue)";
> $strquery = "select functionname($arrvalue[])";
If you wanna do a select i think is safe to construct the select statement
as a string (including your arrays).
E.g.
$strquery = "select func('{1,2,3,4}')";
Now if your function outputs an array, thats a little bit more complicated
lies in the php field and you need to ask pgsql-php or something like
that.
>
> what is the right syntax
> TIA
> joseph
>
> ---(end of broadcast)-------
> TIP 4: Don't 'kill -9' the postmaster
>
--
Achilleus Mantzios
S/W Engineer
IT dept
Dynacom Tankers Mngmt
tel:+30-10-8981112
fax:+30-10-8981877
email: [EMAIL PROTECTED]
[EMAIL PROTECTED]
---(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] function text_ge(text, text), how to use on version 7.2
On Tue, 18 Jun 2002 [EMAIL PROTECTED] wrote: > Hello there > > I use the function text_ge(text, text) under version 7.03 and it was just > fine. After upgrading to version 7.2 this function doesn't work anymore. It is quite possible that text_ge uses local encodings. However I think it is working fine, but it is unwise to use strings for dates representation. You could use timestamps with resolution to milisecond (which means storing actual UNIX dates), or dates. What you do with text_ge is a lexicographical comparison of the two texts. Hence, this method will produce different results for different encodings, and thus it is not portable. i'd say convert the 'DD-MM-' ul_datum fields with the correct SQL dates, or timestamps, and then use normal date comparison <,>,=. > > this statement shows all the records even if the greatest year is 2002 in > ul_datum : > select * from userlog where text_ge(ul_datum, '01.01.2004') > > Thanks in advance ... Juerg Rietmann > > > > __ > > PFISTER + PARTNER, SYSTEM - ENGINEERING AG > Juerg Rietmann > Grundstrasse 22a > 6343 Rotkreuz > Switzerland > > internet : www.pup.ch > phone : +4141 790 4040 > fax : +4141 790 2545 > mobile: +4179 211 0315 > __ > > > > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html > -- Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt tel:+30-10-8981112 fax:+30-10-8981877 email: [EMAIL PROTECTED] [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] text vs varchar
On Tue, 18 Jun 2002, Josh Berkus wrote: > Wei, > > > Is there any disadvantage of using TEXT datatype as opposed to a VARCHAR > > datatype with a maximum length, especially when I do searches on them? > > Yes. You can't index TEXT because it's of potentially unlimited length. Well indexing text works fine for me. Table "repdat" Column |Type | Modifiers -+-+--- vslid | integer | vslname | character varying(15) | orderno | integer | not null date_in | timestamp without time zone | port_landed | character varying(15) | subject | text| catid | integer | dynacom=# EXPLAIN ANALYZE SELECT subject from repdat where subject='UTM FOR SPECIAL SURVEY JUNE2000'; NOTICE: QUERY PLAN: Index Scan using repdat_subject_idx on repdat (cost=0.00..7.40 rows=1 width=28) (actual time=0.05..0.06 rows=1 loops=1) Total runtime: 0.10 msec EXPLAIN dynacom=# dynacom=# SET enable_indexscan = off; SET VARIABLE dynacom=# EXPLAIN ANALYZE SELECT subject from repdat where subject='UTM FOR SPECIAL SURVEY JUNE2000'; NOTICE: QUERY PLAN: Seq Scan on repdat (cost=0.00..388.59 rows=1 width=28) (actual time=0.03..8.14 rows=1 loops=1) Total runtime: 8.19 msec EXPLAIN dynacom=# > > -- Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt tel:+30-10-8981112 fax:+30-10-8981877 email: [EMAIL PROTECTED] [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Aggregates not allowed in WHERE clause?
On 19 Jun 2002, Joachim Trinkwitz wrote: > Hi all, > > I have a table (lv) with a field "semester" and I'm trying to fish out all > rows which have a value corresponding to a max() value of another > table's (lf_sem) "semester" field. The intention is to keep a value > with the current term in lf_sem so I can get all rows which concern > this term. > > Example tables: > > lv > == > semester | kvvnr > -+-- > 2001ss | 4185 > 2001ss | 4203 > 2002ws | 4163 > 2002ws | 4190 > > lf_sem > == > semester > > 2001ws > 2002ss > 2002ws > > At first I tried this query: > > SELECT kvvnr > FROM lv, lf_sem > WHERE lv.semester = max(lf_sem.semester); > > This yields the message: '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? SELECT lv.kvvnr,lv.semester from lv where lv.semester = (select max(semester) from lf_sem); > > Clueless, > joachim > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) > -- Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt tel:+30-10-8981112 fax:+30-10-8981877 email: [EMAIL PROTECTED] [EMAIL PROTECTED] ---(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] date_ge and time_ge
On Fri, 21 Jun 2002 [EMAIL PROTECTED] wrote: > Hello > > I have a question regarding date_ge() and time_ge(). > > This statement works : > > select * from userlog where date_ge(ul_timestamp, '20.06.2002') > > > This statement doesn't work : > > select * from userlog where time_ge(ul_timestamp, '08:00:00') > See the large list of functions of pgsql. Use date_part, castings, etc... > > Here are some records from userlog : > > (See attached file: userlog.htm) > > __ > > PFISTER + PARTNER, SYSTEM - ENGINEERING AG > Juerg Rietmann > Grundstrasse 22a > 6343 Rotkreuz > Switzerland > > internet : www.pup.ch > phone : +4141 790 4040 > fax : +4141 790 2545 > mobile: +4179 211 0315 > __ > -- Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt tel:+30-10-8981112 fax:+30-10-8981877 email: [EMAIL PROTECTED] [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] CASE Select, referring to selected value
On Mon, 1 Jul 2002, Nick Riemondi wrote: Just rewrite your case statement in the where clause. > Hi! > I've got 2 date fields in table which constitute a term. On select I'm > calculating that term, BUT I can't figure out how to refer to it in the > sql - or if it's possible. I'd like to be able to use the case returned > value for order, comparing, etc. Here's an example: > > select case when (current_date > available) then ((end_date - > current_date) / 30) > when (current_date < available) then ((end_date - available) / > 30) >end >from listing > > This yields something along the line of: > > case > -- > 2 >39 >11 >64 > ... > > which is great. Now, I'd like to be able to do this for example: > > 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? > > Thanks in Advance! > Nick > > > > > > ---(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 > > -- Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt tel:+30-10-8981112 fax:+30-10-8981877 email: [EMAIL PROTECTED] [EMAIL PROTECTED] ---(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] pg_restore cannot restore function
In the case that you moved your backup to another system where possibly the shared library (.so) where the function exists is on a different location then thats the problem, in which case you only need to recreate the function (with the same isstrict,iscachable attributes). -- Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt tel:+30-10-8981112 fax:+30-10-8981877 email: [EMAIL PROTECTED] [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Why doesn't it use indexes?
On Tue, 2 Jul 2002, Ahti Legonkov wrote: Check the actual time by explain analyze. If sequential scan (your table is small e.g.) is faster then there is no need for index use. Also check the enable_indexscan variable. > 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? > > -- Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt tel:+30-10-8981112 fax:+30-10-8981877 email: [EMAIL PROTECTED] [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] how to write procedures
On Thu, 4 Jul 2002, srikanth wrote: You are looking for functions :) Study them a bit and then you will feel grateful for PostgreSQL! > Hi, I am using postgre sql server on linux server but for my database I am > using storedprocedures which i need to create , but there are no commands to > create procedures it says it does not support is there any way to work with > stored procedures in postgre sql server. > thanks, > srikanth. > > > > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org > > -- Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt tel:+30-10-8981112 fax:+30-10-8981877 email: [EMAIL PROTECTED] [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] how to write procedures
On Thu, 4 Jul 2002, Rajesh Kumar Mallah. wrote: Just to add stored procedures in pgsql are implemented thru functions. Popular languages are pg/plsql and "C". > > Hi , > > Stored procedures are supported in pgsql for > quite a long time > > consult postgresql docs on website > http://www.postgresql.org/idocs/index.php?xplang.html > > or your local installations. > > regds > malz. > > > > On Thursday 04 July 2002 16:15, srikanth wrote: > > Hi, I am using postgre sql server on linux server but for my database I am > > using storedprocedures which i need to create , but there are no commands > > to create procedures it says it does not support is there any way to work > > with stored procedures in postgre sql server. > > thanks, > > srikanth. > > > > > > > > > > ---(end of broadcast)--- > > TIP 6: Have you searched our list archives? > > > > http://archives.postgresql.org > > -- Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt tel:+30-10-8981112 fax:+30-10-8981877 email: [EMAIL PROTECTED] [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] newbie question
On Sun, 7 Jul 2002, Mirco D'Angelo wrote: > Hi > > I am going to learn MySql 'cause I have to, but would it be better, or let's > say, more interesting, to learn postgressql? Is it newer, more common, etc.? > Well IMO you just cant compare them. Think of a porche that does 100km/h better than a Mirage F1 military aircraft, but F1 can do much higher accelerations at higher speeds, move in 3D space rather than 2D, etc... If you wanna travel fast on the ground go with the porche. If you want the skies go with F1!! > greets > mirco > > > > > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org > > -- Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt tel:+30-10-8981112 fax:+30-10-8981877 email: [EMAIL PROTECTED] [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] export plpgsql function to file
On 9 Jul 2002, Joseph Syjuco wrote: select prosrc from pg_proc where proname='foofunc'; The other way is dumping the database and extracting afterwards only the create statement of your function > how can i export my postgresql function to a file? > > > > > > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) > > -- Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt tel:+30-10-8981112 fax:+30-10-8981877 email: [EMAIL PROTECTED] [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Error with DISTINCT and AS keywords
On Wed, 10 Jul 2002, Andreas Schlegel wrote: > Hi, > > I need some help to let this sql statement run with Postgres 7.2.1 > > Doesn't work: > select DISTINCT tnr, titel, 'TEST' AS testcol from tTitel; > ERROR: Unable to identify an ordering operator '<' for type 'unknown' > Use an explicit ordering operator or modify the query Do it as select DISTINCT tnr, titel, 'TEST'::text AS testcol from tTitel; > > If I remove the DISTINCT keyword it works: > select tnr, titel, 'TEST' AS testcol from tTitel; > > > > Greetings, > Andreas > > > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org > -- Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt tel:+30-10-8981112 fax:+30-10-8981877 email: [EMAIL PROTECTED] [EMAIL PROTECTED] ---(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] list of tables ?
On Thu, 11 Jul 2002, Steve Brett wrote: > can anyone point me in the right direction ? > > i need to list all the tables in a database. > > i've looked at pgadmin_tables which is empty and pga_schema whihc contains a > sinlge row i don't want to parse ... > > is there an easier way t get a list of tables ? > > i'm on 7.2 select * from pg_tables; > > ta, > > Steve Brett > > > > ---(end of broadcast)------- > TIP 4: Don't 'kill -9' the postmaster > -- Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt tel:+30-10-8981112 fax:+30-10-8981877 email: [EMAIL PROTECTED] [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] A SQL Training
On Tue, 16 Jul 2002, Devrim GUNDUZ wrote:
You need to identify your problem: preorder traversal on a tree
using pointers for the fathers.
Then you have to prove (or prove the opposite) that SQL alone
is capable of expressing what you need.
BTW, Nested Sets are only good for "static" databases.
Pointers are good for intensively "dynamic" databases, but have some
performance problems (for instance traversing to the root).
A genealogical approach (just like Oleg's and Teodor's one in
contrib/tree) seems the most attractive.
Also you can have your own setup using postgresql arrays
(The arrays could contain the path of ids of the nodes from
the specific node to the root).
All these probably have little to do with your problem
(which is a computational theory problem), but maybe useful
in other cases.
>
> Hi,
>
> Today, one of my teacher asked me a question for a practice... I could not
> solve it :)
>
> Here it comes:
>
> We are given the name of the grandfather in a family. Let's call him A. A
> has n sons, each son has children .
>
> for example.
> A -> Grandfather
>/ \
> / \
> B C
> /\ / \ \
>/ \ D E F
> G H
> ...
>
> We are given the data like ('A','B'),('C','D')...
>
> Now, we do not know how many children are in the list. How could we list
> this family tree in "tree" formt by only using SQL?
>
> Best regards.
>
>
>
>
>
>
--
Achilleus Mantzios
S/W Engineer
IT dept
Dynacom Tankers Mngmt
Nikis 4, Glyfada
Athens 16610
Greece
tel:+30-10-8981112
fax:+30-10-8981877
email: [EMAIL PROTECTED]
[EMAIL PROTECTED]
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] How to find out if an index is unique?
On Wed, 17 Jul 2002, Dirk Lutzebaeck wrote: Yes. Select the oid from pg_class where relname is your index name. Then search in pg_index for column "indisunique" using the previous oid as "indexrelid". > > Hello, > > is there a way to ask the system tables if a given index was created > with the unique qualification? I don't want to insert data to try. > > Greetings, > > Dirk > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org > -- Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt Nikis 4, Glyfada Athens 16610 Greece tel:+30-10-8981112 fax:+30-10-8981877 email: [EMAIL PROTECTED] [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
[SQL] pg_dump and sequences
Hi, i am using postgreSQL 7.2.1 on a redhat 7.2 (kernel
2.4.9-31,glibc-2.2.4-24).
It seems that pg_dump -t "tablename" dumps correctly the sequence of a
table's column,when the column is named after "id".
For example,
dynacom=# CREATE TABLE foo2 (fid serial NOT NULL UNIQUE PRIMARY KEY,id
serial);
[postgres@pc216 ~]% pg_dump -t foo2 > foo2DUMP.sql
[postgres@pc216 ~]% cat foo2DUMP.sql
--
-- Selected TOC Entries:
--
\connect - postgres
--
-- TOC Entry ID 2 (OID 2009757)
--
-- Name: foo2_id_seq Type: SEQUENCE Owner: postgres
--
CREATE SEQUENCE "foo2_id_seq" start 1 increment 1 maxvalue
9223372036854775807 minvalue 1 cache 1;
--
-- TOC Entry ID 4 (OID 2009759)
--
-- Name: foo2 Type: TABLE Owner: postgres
--
CREATE TABLE "foo2" (
"fid" integer DEFAULT nextval('"foo2_fid_seq"'::text) NOT NULL,
"id" integer DEFAULT nextval('"foo2_id_seq"'::text) NOT NULL,
Constraint "foo2_pkey" Primary Key ("fid")
);
--
-- Data for TOC Entry ID 6 (OID 2009759)
--
-- Name: foo2 Type: TABLE DATA Owner: postgres
--
COPY "foo2" FROM stdin;
\.
--
-- TOC Entry ID 5 (OID 2009762)
--
-- Name: "foo2_id_key" Type: INDEX Owner: postgres
--
CREATE UNIQUE INDEX foo2_id_key ON foo2 USING btree (id);
--
-- TOC Entry ID 3 (OID 2009757)
--
-- Name: foo2_id_seq Type: SEQUENCE SET Owner: postgres
--
SELECT setval ('"foo2_id_seq"', 1, false);
--
Any ideas??
Achilleus Mantzios
S/W Engineer
IT dept
Dynacom Tankers Mngmt
Nikis 4, Glyfada
Athens 16610
Greece
tel:+30-10-8981112
fax:+30-10-8981877
email: [EMAIL PROTECTED]
[EMAIL PROTECTED]
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] pg_dump and sequences
On Tue, 23 Jul 2002, Achilleus Mantzios wrote: Just a correction > Hi, i am using postgreSQL 7.2.1 on a redhat 7.2 (kernel > 2.4.9-31,glibc-2.2.4-24). > > It seems that pg_dump -t "tablename" dumps correctly the sequence of a > table's column,when the column is named after "id". > I meant "when ONLY" the column is named after "id", whereas it does not dump the sequence when the column is named after something different than "id". -- Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt Nikis 4, Glyfada Athens 16610 Greece tel:+30-10-8981112 fax:+30-10-8981877 email: [EMAIL PROTECTED] [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] pg_dump and sequences
On Wed, 24 Jul 2002, Tom Lane wrote: > Achilleus Mantzios <[EMAIL PROTECTED]> writes: > >> It seems that pg_dump -t "tablename" dumps correctly the sequence of a > >> table's column,when the column is named after "id". > > > I meant "when ONLY" the column is named after "id", > > Yeah, you're right. I'm hoping to see a better answer in 7.3, but > right now it's a horrible kluge ... No harm done! > > regards, tom lane > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) > -- Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt Nikis 4, Glyfada Athens 16610 Greece tel:+30-10-8981112 fax:+30-10-8981877 email: [EMAIL PROTECTED] [EMAIL PROTECTED] ---(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] 3-tier
On Wed, 31 Jul 2002, Elielson Fontanezi wrote: Well since you are already using good free software (pgsql) you should probably consider jboss. Not much fancy GUI staff, but serious J2EE work. You can easily run a postgres service inside jboss. > > I would like to know which application server is recommended to work > on PostGRE. > I intend to use WebSphere. Maybe other one is preferred. > > I am looking forward from you as soon as possible. > > Thanks all. > > .. > A Question... > Since before your sun burned hot in space and before your race was born, I > have awaited a question. > > Elielson Fontanezi > DBA Technical Support - PRODAM > Parque do Ibirapuera s/n - SP - BRAZIL > +55 11 5080 9493 > > > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org > -- Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt Nikis 4, Glyfada Athens 16610 Greece tel:+30-10-8981112 fax:+30-10-8981877 email: [EMAIL PROTECTED] [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] expressions operating on arrays
look at /usr/local/src/postgresql-7.2.1/contrib/intarray -- Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt Nikis 4, Glyfada Athens 16610 Greece tel:+30-10-8981112 fax:+30-10-8981877 email: [EMAIL PROTECTED] [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] [GENERAL] arrays
On Mon, 30 Sep 2002, Bruno Wolff III wrote: > > It is unusual to want to store arrays in a database. Normally you want to > use additional tables instead. For example multilanguage titles is something > I would expect to be in a table that had a column referencing back to > another table defining the object a title was for, a column with the > title and a column with the language. I think arrays are one of the cool features of postgres (along with gist indexes). Here are some common uses: - Tree representation (the genealogical from child to ancestors approach) - Storing of polynomial formulae of arbitary degree checkout the intarray package in contrib for further info. I think pgsql arrays provide a natural solution to certain problems where it fits. ====== Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt Nikis 4, Glyfada Athens 16610 Greece tel:+30-10-8981112 fax:+30-10-8981877 email: [EMAIL PROTECTED] [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] [GENERAL] arrays
I was wondering why is such a rage against arrays. I posted 2 very common problems where arrays provide the only natural (and efficient) fit. (and got no responses) So it seems to me that: - Arrays implementation (along with the intarray package) in postgresql is well performing and stable. - Some problems shout out for array usage. - The Array interface is defined in java.sql package. (I dont know if sql arrays is in some standard but it seems that Java sees it that way, at least). - The Array interface is implemented in the official postgresql java package. - In some problems replacing arrays according the tradition relational paradigm would end up in a such a performance degradation, that some applications would be unusable. - Oleg and Teodor did a great job in intarray, making array usage easy and efficient. Thanx! == Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt Nikis 4, Glyfada Athens 16610 Greece tel:+30-10-8981112 fax:+30-10-8981877 email: [EMAIL PROTECTED] [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Formatting current_time output
SELECT to_char(now(), 'HH24:MI AM'); (in 7.2.1) == Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt Nikis 4, Glyfada Athens 16610 Greece tel:+30-10-8981112 fax:+30-10-8981877 email: [EMAIL PROTECTED] [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] epoch to date
On Thu, 3 Oct 2002, Roberto Mello wrote: > Hi all, > > Maybe I missed something obvious, but is there a built-in function to > convert epoch to date? I couldn't find it in the documentation for extract > and friends. well to compute epoch timestamp # SELECT (now() -(extract(epoch from now())::int4)/86400)::datetime ; if thats what you asked :) > > Thanks, > > -Roberto > > -- > +|Roberto Mello -http://www.brasileiro.net/ |--+ > + Computer Science Graduate Student, Utah State University + > + USU Free Software & GNU/Linux Club - http://fslc.usu.edu/ + > All in all, you're just another brick on the wall... > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster > == Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt Nikis 4, Glyfada Athens 16610 Greece tel:+30-10-8981112 fax:+30-10-8981877 email: [EMAIL PROTECTED] [EMAIL PROTECTED] ---(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] Can Postgres cache a table in memory?
On Thu, 3 Oct 2002, Kevin Traub wrote: > Hello all; > > I'm trying to speed up a query which returns the majority of a table so and > index isn't helpful. > I've got more than enough RAM to hold my table so, can anyone tell me if > there is there a way to force Postgres to cache a table in RAM? > > Any help would be appreciated. You need to tune first of all shared_buffers. Change the postgres.conf file usual in $PGDATA directory. Bare in mind that one buffer holds 8k of data. > > Thanks; > -Kevin Traub > > > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) > == Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt Nikis 4, Glyfada Athens 16610 Greece tel:+30-10-8981112 fax:+30-10-8981877 email: [EMAIL PROTECTED] [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] rows in order
How do you plan to keep your tree represented?? There are quite a few options for this. Extensive talk has been made in this list. Also search the archives. Basically you can follow - nested trees (pure sql) aproach - Genealogical tree representation approach (either using text to represent the path to the parent (pure sql), or arrays) - The contrib/tree implementation == Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt Nikis 4, Glyfada Athens 16610 Greece tel:+30-10-8981112 fax:+30-10-8981877 email: [EMAIL PROTECTED] [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Viewing stored procedure code
On Thu, 10 Oct 2002, Ludwig Lim wrote: > Hi : > > 1) How do I view the body of a stored procedure in > psql? SELECT prosrc from pg_proc where proname = 'your pl/pgsql procedure name'; > > 2) How do I know get the corresponding stored > procedure of a particular trigger in psql? > SELECT t.tgname,f.proname from pg_trigger t,pg_proc f where t.tgname = 'your trigger name' and t.tgfoid = f.oid; > thanks in advance, > > ludwig. > > __ > Do you Yahoo!? > New DSL Internet Access from SBC & Yahoo! > http://sbc.yahoo.com > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org > == Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt Nikis 4, Glyfada Athens 16610 Greece tel:+30-10-8981112 fax:+30-10-8981877 email: [EMAIL PROTECTED] [EMAIL PROTECTED] ---(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] SQL Error
select t0_o.scheduler_action_id from scheduler_action t0_o where t0_o.is_done = 0 and t0_o.invocation_date <= 1034033214921::numeric; seems to work in PostgreSQL 7.2.1 on i686-pc-linux-gnu, compiled by GCC 2.96 The reason that this query worked in hpux without the explicit casting is maybe hpux runs on a 64-bit architecture processor == Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt Nikis 4, Glyfada Athens 16610 Greece tel:+30-10-8981112 fax:+30-10-8981877 email: [EMAIL PROTECTED] [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] triggers
On Fri, 11 Oct 2002, Richard Huxton wrote: > > The customary way is to have a "helper" process that sits there LISTENing for > a NOTICE and then calls the external program as required. Cleaner and means > the other program doesn't have any direct connection to the Postgresql > backend. > > - Richard Huxton Yet another cool feature (ala java listeners) of postgresql. P.S. Is the word "thank you" too difficult to be written by people posting and reading?? If anything else, a "thank you" is a motive. ====== Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt Nikis 4, Glyfada Athens 16610 Greece tel:+30-10-8981112 fax:+30-10-8981877 email: [EMAIL PROTECTED] [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: Fwd: Re: [SQL] Can I search for an array in csf?
On Mon, 21 Oct 2002, Vernon Wu wrote: > > > > > Hi, Richard, > > Thanks for your response and see below. > > 10/21/2002 3:13:57 AM, Richard Huxton <[EMAIL PROTECTED]> wrote: > > >On Friday 18 Oct 2002 1:58 pm, Vernon Wu wrote: > >> One field of a table stores an array of characters in a string fromat as > >> "a,b,c,d". Is anyway to apply a select statement without using stored > >> procedure? > >> > >> Thanks for your input. > > > >Not really, and I can't think any way of accessing an index on this either. > >Are you sure you wanted the characters stored this way? Even if the overhead > >of a separate table isn't woth it, you might want to look into arrays and the > >intarray stuff in contrib/ > > > > The reason I use this format for an array is that the array is dynamic. I have quite >few cases of this type of situation. The > maximize length in some cases is known, is unknown in others. I have learnt the >comment separated format is one way > to solve the problem. Someone also suggested to store the array as an object. I am >not sure whether it works or not. > The application is written in Java, by the way. > > I have taken a look at intarray by searching on the postgres.org web site as well as >in google. (I use cypwin and unable > to find the contrib directory). My impression is it isn't a standard SQL data type. >And its element is integer only. > > It is my first time doing DB table design. Any helps will be gracfully appreciated. Well some comments, since i have done a lot of work with arrays, postgresql and java. Arrays are supported by the postgresql jdbc driver just fine. Arrays can be of any valid builtin or user defined type. Postgresql Arrays along with intarray package are ideal for doing small set manipulations, where the design fits the natural model of your data (e.g. storing the factors of a polynomial formula). It would be nice if you had yourself a crash course on relational db design. Also i would advise you compiling and running postgres on a unix system. Note that in order to compile the intarray package you need to have the sources installed. > > Thanks, > > Vernon > > > > >-- > > Richard Huxton > > > >---(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 forwarded message > > > > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster > == Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt Nikis 4, Glyfada Athens 16610 Greece tel:+30-10-8981112 fax:+30-10-8981877 email: [EMAIL PROTECTED] [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: Fwd: Re: [SQL] Can I search for an array in csf?
On Mon, 21 Oct 2002, Josh Berkus wrote: > > Vernon, > > > >> One field of a table stores an array of characters in a string fromat as > > >> "a,b,c,d". Is anyway to apply a select statement without using stored > > >> procedure? > > > The reason I use this format for an array is that the array is dynamic. I > have quite few cases of this type of situation. The > > maximize length in some cases is known, is unknown in others. I have learnt > the comment separated format is one way > > to solve the problem. Someone also suggested to store the array as an > object. I am not sure whether it works or not. > > The application is written in Java, by the way. > > You should store this data in a sub-table linked through a foriegn key. > Period. Messing with arrays will only lead you to heartache ... It depends. I can tell you of situations that doing it with child tables will hurt performance really bad. Its just a matter of complexity. One of the apps we run over here, deals with bunker analysis of the vessels of our fleet. For each vessel there are 4 formulas that describe the parameters of the consumption of fuel oil under some given conditions. I have implemented this using arrays. The app is written in J2EE. On a dual xeon 2.2 GHz with 1 GB for postgres, it takes about 900 miliseconds to compute some statistics (average, std deviation,etc..) of the consumption of all vessels (about 20 of them) for a period of 3 years (the values are stored for each day). Before going with the formulas, we had a rather primitive scheme originated from the previous cobol application, based on subtable look ups, (and there was no serious computations involved just table lookups). I can tell you the performance boost was remarkable. > > Try the book "Database Design For Mere Mortals" for a primer on SQL DB design. > > -- > -Josh Berkus > Aglio Database Solutions > San Francisco > > > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html > == Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt Nikis 4, Glyfada Athens 16610 Greece tel:+30-10-8981112 fax:+30-10-8981877 email: [EMAIL PROTECTED] [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Locking that will delayed a SELECT
On Fri, 18 Oct 2002, Christoph Haller wrote: > >Suppose I have a transaction (T1) which executes a > > complicated stored procedure. While T1 is executing, > > trasaction #2 (T2) begins to execute. > > > > T1 take more time to execute that T2 in such a way > > that T2 finished earlier than T1. The result is that > > t2 returns set of data before it can be modified by > > T1. > > > >Given the above scenario. Is there a way such that > > while T2 will only read that value updated by T1 (i.e. > > T2 must wait until T1 is finished) ? What locks should > > I used since a portion of T1 contains SELECT > > statements? Should I used the "SERIALIZABLE > > isolation". > > What's wrong about this question? > I'm interested in an answer, too. > > Regards, Christoph Second small xaction T2's select statemenst will use values commited before these select statements started. That is, these queries will NOT see values updated by T1. The problem is solved a) Using SERIALIZABLE XACTION ISOLATION LEVEL b) in T2 using "select for update" instead of select. That way T2's queries will wait untill T1's statements commit or rollback. The SERIALIZABLE XACTION ISOLATION LEVEL scheme is heavier and thus maybe less efficient. See http://www.postgresql.org/idocs/index.php?mvcc.html == Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt Nikis 4, Glyfada Athens 16610 Greece tel:+30-10-8981112 fax:+30-10-8981877 email: [EMAIL PROTECTED] [EMAIL PROTECTED] ---(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] Locking that will delayed a SELECT
On Fri, 18 Oct 2002, Achilleus Mantzios wrote: > Second small xaction T2's select statemenst will use values commited > before these select statements started. That is, these queries > will NOT see values updated by T1. > > The problem is solved > > a) Using SERIALIZABLE XACTION ISOLATION LEVEL > b) in T2 using "select for update" instead of select. That way T2's > queries will wait untill T1's statements commit or rollback. > > The SERIALIZABLE XACTION ISOLATION LEVEL scheme is heavier > and thus maybe less efficient. Also the serialization must be secured from the application side. In your case the program invoking T2 must be prepared to retry the transaction if T1 commits in the meantime. With SERIALIZABLE XACTION ISOLATION LEVEL T2's select statements will use values commited before T2 began (and not before these select statements began as in the READ COMMITED (default) XACTION ISOLATION LEVEL case) ====== Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt Nikis 4, Glyfada Athens 16610 Greece tel:+30-10-8981112 fax:+30-10-8981877 email: [EMAIL PROTECTED] [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Sum of Every Column
Hi tom.
In postgresql you cannot have functions with
a variable number of parameters.
(pgsql supports some kind of method overloading
based on the type and number of parameters,
thats the reason why).
But if you run Unix (with freebsd and linux it is trivial
as you will see, with solaris you have to be more formal)
there is a hack.
I had the same problem as yours, but in my case
i wanted the minimum, maximum of double numbers (maximum 14 of them).
In BSD,linux you can navigate the process' stack with no problems.
The idea is to write a variable parameter number function,
with the first parameter denoting the number of the rest
parameters, (just like printf)
and then define a pgsql function with the maximum number
of parameters that you will ever have.
I attach the code (tested under RedHat 7.1, kernel 2.4.7,
glibc-2.2.2-10,gcc-2.96-81 and FreeBSD 4.6.1-RC2, both with
postgresql 7.2.1),
==
Achilleus Mantzios
S/W Engineer
IT dept
Dynacom Tankers Mngmt
Nikis 4, Glyfada
Athens 16610
Greece
tel:+30-10-8981112
fax:+30-10-8981877
email: [EMAIL PROTECTED]
[EMAIL PROTECTED]
#include
#include
int minihack(argc)
int argc;
{
int i;
int temp = *(&argc + 1);
int tval;
for (i=1;i temp)
temp = tval;
}
return temp;
}
int4 maxi(t1,t2,t3,t4,t5,t6,t7,t8,t9,t10,t11,t12,t13,t14)
int4 t1;
int4 t2;
int4 t3;
int4 t4;
int4 t5;
int4 t6;
int4 t7;
int4 t8;
int4 t9;
int4 t10;
int4 t11;
int4 t12;
int4 t13;
int4 t14;
{
return maxihack(14,t1,t2,t3,t4,t5,t6,t7,t8,t9,t10,t11,t12,t13,t14);
}
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] ORDER the result of a query by date
On Thu, 24 Oct 2002, andres javier garcia garcia wrote: > Hello; > I've got the result of a query that appears as: > cod_variable | cod_station | year | month | day | rain > -+---++--+--+-- > 30201 | 7237 | 1953 |1 | 1 |2 > 30201 | 7237 | 1953 |2 | 1 |5 > 30201 | 7237 | 1953 |3 | 1 |0 > 30201 | 7237 | 1953 |4 | 1 | -3 > . > > (Of course, thanks to Stephan Szabo for the method to obtain this from my > strange source data. I didn't think this was possible.) > > After have done this query I've realized that I need the data to be ordered > by date. Do you have any suggestion? select ,year,month,day from order by year,month,day. Also take a look at date,timestamp data types. Note that you can process your initial "strange" source using tools as awk,perl,sh,C programs to format your datasets in sql insert statements or copy format. > > Best regards > -- > Javier > > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html > == Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt Nikis 4, Glyfada Athens 16610 Greece tel:+30-10-8981112 fax:+30-10-8981877 email: [EMAIL PROTECTED] [EMAIL PROTECTED] ---(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] Upper / lower cases on table and column names
On Fri, 25 Oct 2002, Reiner Dassing wrote: > (PostgreSQL) 7.1.1: > > Hello all! > > I was trying to adopt a database application to PostgreSQL. > (It is written for MySQL and Oracle using perl) > > During this process I recognized the phenomena that upper case letters > of table names and column names are not preserved > in PostgreSQL. > Is this a "featue" of PostgreSQL or do I miss something? Both :) You may double quote the literals of names in pgsql, but then in every statement you must retain the double quotes, which is annoying. Change the table name to something more meaningful. > > Example: > Id and textId as a column name should conserve upper case letters 'I' > but \d data converts it to lower case words: > > psql ... > create table data ( Id int not null, textId int not null); > > \d data >Table "data" > Attribute | Type | Modifier > ---+-+-- > id| integer | not null > textid| integer | not null > > and > create table Data ( Id int not null, textId int not null); > > results in: > ERROR: Relation 'data' already exists > > In the interpretation of my application table 'data' and 'Data' is something > different. > > -- > -- > Mit freundlichen Gruessen / With best regards > Reiner Dassing > > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] > == Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt Nikis 4, Glyfada Athens 16610 Greece tel:+30-10-8981112 fax:+30-10-8981877 email: [EMAIL PROTECTED] [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Returning a recordset and filling datatable in a .NET
On Thu, 31 Oct 2002, Jonas Wouters wrote:
Hi altho i doubt anybody here has any .net experience,
i'll give my bet.
try to place your sql commands in such a way the the
"FETCH ALL" command is the last in the stream,
(that is get rid of begin,commit statements)
>
>
> Hi,
>
> I have a problem with using .NET and PostgreSQL. In a previous thread called
> "" I noticed that it is possible to use and create functions that return
> tuples or RecordSets. I now want to use them in .NET.
>
> I followed the instructions which are available at :
> http://developer.postgresql.org/docs/postgres/plpgsql-cursors.html these
> work in the standard psql client :
>
> I created the function using the following :
>
> CREATE FUNCTION public.p0012_fetch_supplier(varchar, varchar, refcursor)
> RETURNS refcursor AS '
> BEGIN
> OPEN $3 FOR SELECT * FROM "SUPP_T" WHERE "CODE" = $1 AND "DSRCODE" =
> $2;
> RETURN $3;
> END;
> ' LANGUAGE 'plpgsql' VOLATILE;
>
>
> Then I did these in psql :
>
> SBA=# BEGIN;
> BEGIN
> SBA=# SELECT p0012_fetch_supplier('1','1','funccursor');
> p0012_fetch_supplier
> --
> funccursor
> (1 row)
>
> SBA=# FETCH ALL IN funccursor;
> DSRCODE | CODE
> 1 | 1
> (1 row)
>
> SBA=# COMMIT;
> COMMIT
>
> The results tell me that the function works. (whoohoo)
>
> But when I do the same thing in a .Net application, I get an empty DataTable
> (row count = 0)
>
> This is what I do in .Net 5and I do know that most of you people dislike
> .NET and actually .. that is not the issue for me :), I just want this to
> work because we are going to need this for our application)
>
>
> Dim CN As New Microsoft.Data.Odbc.OdbcConnection("DSN=PostgreSQL30")
> Dim CM As New Microsoft.Data.Odbc.OdbcCommand("BEGIN; SELECT
> p0012_fetch_supplier('1','1','funccursor'); FETCH ALL IN
> funccursor; COMMIT;", CN)
> Dim DA As New Microsoft.Data.Odbc.OdbcDataAdapter(CM)
> Dim DT As New DataTable()
> Try
>
> CM.CommandType = CommandType.Text
> DA.SelectCommand.Connection.Open()
> DA.Fill(DT)
> DA.SelectCommand.Connection.Close()
> Catch ex As Microsoft.Data.Odbc.OdbcException
> Debug.WriteLine(ex.Message)
> Debug.WriteLine(ex.Source)
> Debug.WriteLine(ex.HelpLink)
> Finally
> CN.Close()
>
> It does not raise an exception so there are no real 'errors'; it just does
> not give 'data' to the ADO.NET container.
>
> If I am asking this in the wrong mailinglist, then please point me in the
> right direction. I don't think that I will get a answer at Microsoft.com so
> that is why I ask it here ..
>
>
> Thx in advance
> Jonas
>
>
> ---(end of broadcast)---
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>
==
Achilleus Mantzios
S/W Engineer
IT dept
Dynacom Tankers Mngmt
Nikis 4, Glyfada
Athens 16610
Greece
tel:+30-10-8981112
fax:+30-10-8981877
email: [EMAIL PROTECTED]
[EMAIL PROTECTED]
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] How do you write this query?
On 31 Oct 2002, Wei Weng wrote: and yet another equivalent query: SELECT f1.data1 from test f1,test f2 where f1.data=3 and f1.data2 = f2.data2 and f2.data1='pooh'; > I have a table > > Table "test" > Column|Type| Modifiers > ++-- > data| integer| not null > data1 | character varying(128) | not null > data2 | character varying(128) | not null > > (Note: data is NOT the primary key.) > > And > select * from test > returns > > > data | data1 | data2 > --+---+--- > 1 | foo | bar > 2 | greg | bar > 3 | pooh | bar > 4 | dah | peng > > I need a query that returns me the "data1" that satisfies the logic of > the following pseudo code: > > 1: select data2 into @out from test where data1 = 'pooh' > 2: select data1 from test where data2 = @out and data = 3 > > > What do I do? > > Thanks! > > -- > Wei Weng > Network Software Engineer > KenCast Inc. > > > > ---(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 > == Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt Nikis 4, Glyfada Athens 16610 Greece tel:+30-10-8981112 fax:+30-10-8981877 email: [EMAIL PROTECTED] [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
[SQL] Problem: Referential Integrity Constraints lost
Hi i think a hit a major problem on 7.2.1. I run 3 systems with postgresql 7.2.1. Its a redhat 7.1 for development, a redhat 7.3 for production and a FreeBSD 4.6.1RC2 for testing. After long runs (with periodic (daily) vacuum analyze's) i noticed that some of the triggers that implement referential integrity constraints just disapeared. Some of these triggers were still present on the FreeBSD system (which has been idle for a month or so), whereas on the linux systems they were absent. Has any one have a clue?? Any comment would be really valuable at this moment of darkness. Thanx. == Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt Nikis 4, Glyfada Athens 16610 Greece tel:+30-10-8981112 fax:+30-10-8981877 email: [EMAIL PROTECTED] [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Problem: Referential Integrity Constraints lost
Also i must that these lost triggers implement the parent table
side of the constraint, e.g.
CREATE TABLE VslSections(
id serial NOT NULL UNIQUE PRIMARY KEY,
name varchar(20) NOT NULL UNIQUE);
CREATE TABLE MachClasses(
id serial NOT NULL UNIQUE PRIMARY KEY,
name varchar(20) NOT NULL UNIQUE,
vslsecid int4 NOT NULL,
FOREIGN KEY (vslsecid) REFERENCES VslSections (id));
Then the triggers created are :
1)
CREATE CONSTRAINT TRIGGER "" AFTER INSERT OR UPDATE ON
"machclasses" FROM "vslsections" NOT DEFERRABLE INITIALLY IMMEDIATE FOR
EACH ROW EXECUTE PROCEDURE "RI_FKey_check_ins" ('',
'machclasses', 'vslsections', 'UNSPECIFIED', 'vslsecid', 'id');
2)
CREATE CONSTRAINT TRIGGER "" AFTER DELETE ON "vslsections" FROM
"machclasses" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE
PROCEDURE "RI_FKey_noaction_del" ('', 'machclasses',
'vslsections', 'UNSPECIFIED', 'vslsecid', 'id');
3)
CREATE CONSTRAINT TRIGGER "" AFTER UPDATE ON "vslsections" FROM
"machclasses" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE
PROCEDURE "RI_FKey_noaction_upd" ('', 'machclasses',
'vslsections', 'UNSPECIFIED', 'vslsecid', 'id');
The *LOST* triggers are 2 and 3.
==
Achilleus Mantzios
S/W Engineer
IT dept
Dynacom Tankers Mngmt
Nikis 4, Glyfada
Athens 16610
Greece
tel:+30-10-8981112
fax:+30-10-8981877
email: [EMAIL PROTECTED]
[EMAIL PROTECTED]
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Problem: Referential Integrity Constraints lost: Correction
I was wrong about parent side triggers only having disappeared. Triggers of both sides are missing. == Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt Nikis 4, Glyfada Athens 16610 Greece tel:+30-10-8981112 fax:+30-10-8981877 email: [EMAIL PROTECTED] [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Problem: Referential Integrity Constraints lost
On Wed, 6 Nov 2002, Stephan Szabo wrote: > On Wed, 6 Nov 2002, Achilleus Mantzios wrote: > > > > > Hi i think a hit a major problem on 7.2.1. > > I run 3 systems with postgresql 7.2.1. > > Its a redhat 7.1 for development, a redhat 7.3 for production > > and a FreeBSD 4.6.1RC2 for testing. > > > > After long runs (with periodic (daily) vacuum analyze's) > > i noticed that some of the triggers that implement referential integrity > > constraints just disapeared. > > Some of these triggers were still present on the FreeBSD system > > (which has been idle for a month or so), whereas on the linux > > systems they were absent. > > > > Has any one have a clue?? > > Hmm, you haven't done anything like cluster or an incomplete dump and > reload have you? No, Also the FreeBSD system's database was populated with data from the production on 2002-10-22, so the problem on the FreeBSD was partially inherited from the production databse. > > > == Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt Nikis 4, Glyfada Athens 16610 Greece tel:+30-10-8981112 fax:+30-10-8981877 email: [EMAIL PROTECTED] [EMAIL PROTECTED] ---(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] Problem: Referential Integrity Constraints lost
On Thu, 7 Nov 2002, Achilleus Mantzios wrote: > On Wed, 6 Nov 2002, Stephan Szabo wrote: > > > On Wed, 6 Nov 2002, Achilleus Mantzios wrote: > > > > > > > > Hi i think a hit a major problem on 7.2.1. > > > I run 3 systems with postgresql 7.2.1. > > > Its a redhat 7.1 for development, a redhat 7.3 for production > > > and a FreeBSD 4.6.1RC2 for testing. > > > > > > After long runs (with periodic (daily) vacuum analyze's) > > > i noticed that some of the triggers that implement referential integrity > > > constraints just disapeared. > > > Some of these triggers were still present on the FreeBSD system > > > (which has been idle for a month or so), whereas on the linux > > > systems they were absent. > > > > > > Has any one have a clue?? > > > > Hmm, you haven't done anything like cluster or an incomplete dump and > > reload have you? > > No, > Also the FreeBSD system's database was populated with data from > the production on 2002-10-22, so the problem on the FreeBSD > was partially inherited from the production databse. Also i must add, that the database on the production system was never dumped/reloaded since the creation of the system. The production 7.2.1 pgsql db was created and loaded on 2002-04-20, from a 7.1.3 pgsql on our previous Solaris box (which we replaced with a new linux one). The production pgsql is started/stopped only during system shutdowns/boots. We had some unexpected system failures due to some Linux/MotherBoard/BIOS problems. (I was too enthusiastic about pgsql and its stability that i was overconfident about our database's state after these failures). BTW, could that be the cause of the problem?? The problem is that i didnt realize the problem until yesterday. The only thing i am sure, is that some of the triggers lost one both linux'es are present on the FreeBSD system, which was populated on Oct 22, and had NO deletion activity at all. I plan to make a huge map of all my tables, and configure all the pairs of tables with inter-referential integrity constraints, pg_dump --schema-only, see which triggers are missing and then create them by hand. Has anyone got a better idea?? After recreating the missing triggers should i upgrade to 7.2.3?? Thanx. > > > > > > > > > == > Achilleus Mantzios > S/W Engineer > IT dept > Dynacom Tankers Mngmt > Nikis 4, Glyfada > Athens 16610 > Greece > tel:+30-10-8981112 > fax:+30-10-8981877 > email: [EMAIL PROTECTED] > [EMAIL PROTECTED] > > > -------(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) > == Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt Nikis 4, Glyfada Athens 16610 Greece tel:+30-10-8981112 fax:+30-10-8981877 email: [EMAIL PROTECTED] [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] primary keys
On Wed, 6 Nov 2002, Huub wrote: > Hi, > > I want to create a table which has 2 columns, and both columns have to > be primary key (or: together they are the primary key). How can I do > this using SQL? Using pgAdminII for Postgres7.2.2 on RH8. I dont know the way in pgAdminII, but from psql simply give: CREATE TABLE foo( name varchar(20) NOT NULL, id int4 NOT NULL, PRIMARY KEY (name,id)); > > Thanks > > Huub > > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster > ====== Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt Nikis 4, Glyfada Athens 16610 Greece tel:+30-10-8981112 fax:+30-10-8981877 email: [EMAIL PROTECTED] [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] how to get the source table & field name of a view field
On Thu, 7 Nov 2002, Prime Ho wrote: > Hi, > > Could you tell me how to get view field's source table and field name? > another word, how could I know the view field come from? SELECT definition from pg_views where viewname=''; > > Regards, > Ho > > > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] > ====== Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt Nikis 4, Glyfada Athens 16610 Greece tel:+30-10-8981112 fax:+30-10-8981877 email: [EMAIL PROTECTED] [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] [SQL] Problem: Referential Integrity Constraints lost
On Thu, 7 Nov 2002, Tom Lane wrote: > Achilleus Mantzios <[EMAIL PROTECTED]> writes: > > After recreating the missing triggers should i upgrade > > to 7.2.3?? > > Make that "before". > > I frankly suspect pilot error here. Triggers do not simply disappear. > If you did have crash-induced corruption leading to loss of some rows > in pg_trigger, it would be exceedingly obvious because *no* operations > on the affected tables would work --- relcache would complain about the > fact that pg_class.reltriggers didn't match the number of rows in > pg_trigger. I think the missing triggers must have been removed or > disabled deliberately. (Which is not to say that it couldn't have been > a software bug, but you're barking up the wrong tree to blame it on a > crash.) > Did all the triggers of the affected tables disappear, or only some > of them? Just some of them. I really dont know what happened. Looking back at july backups the problem was already there. I never played with system tables in production. I hope to be able somehow to reproduce the problem, or convince myself its my fault for some reason. P.S. I was surprized when i looked up in my english dictionary the word "deliberately" :) > > regards, tom lane > == Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt Nikis 4, Glyfada Athens 16610 Greece tel:+30-10-8981112 fax:+30-10-8981877 email: [EMAIL PROTECTED] [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] parse bug
On Mon, 11 Nov 2002 [EMAIL PROTECTED] wrote:
>
> seems like an error in the sql parser to me:
>
> # create table test (acol smallint[]);
> CREATE
> # insert into test (acol) values ('{ 0 }');
> ERROR: pg_atoi: error in "0 ": can't parse " "
> # insert into test (acol) values ('{ 0}');
> INSERT 28472 1
>
> the only difference is the trailing " " after the 0;
traditionally atoi ignores only leading spaces, so
insert into test (acol) values ('{ 0}');
should work too.
btw why do you need trailing spaces for??
>
>
> Regards,
>
> Floyd Shackelford
> 4 Peaks Technology Group, Inc.
> VOICE: 334.735.9428
> FAX: 916.404.7125
> EMAIL: [EMAIL PROTECTED]
> ICQ #: 161371538
>
> acta non verba
>
>
>
> ---(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
>
==
Achilleus Mantzios
S/W Engineer
IT dept
Dynacom Tankers Mngmt
Nikis 4, Glyfada
Athens 16610
Greece
tel:+30-10-8981112
fax:+30-10-8981877
email: [EMAIL PROTECTED]
[EMAIL PROTECTED]
---(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] SET DEFAULT
On Wed, 13 Nov 2002, Archibald Zimonyi wrote: > > Hi everyone, > > I have a problem with adding a column to an existing table. I want to add > a column named modified which is of datatype TIMESTAMP and has a DEFAULT > CURRENT_TIMESTAMP as it's initial value. I cannot do this with ALTER > TABLE, it tells me to user ALTER TABLE SET DEFAULT instead but I cannot > figure out the syntax with the help of the manuals. > > My ALTER TABLE looked like this: > > alter table decks add column modified timestamp default current_timestamp; ALTER TABLE decks alter column modified SET DEFAULT current_timestamp; > > and the error I get is this: > > ERROR: Adding columns with defaults is not implemented. > Add the column, then use ALTER TABLE SET DEFAULT. > > Thanks in advance, > > Archie > > > ---(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 > == Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt Nikis 4, Glyfada Athens 16610 Greece tel:+30-10-8981112 fax:+30-10-8981877 email: [EMAIL PROTECTED] [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] importing a 7.2 db with contrib/tsearch to 7.3
Thats what i do for intarray: -Install all contrib packages you want to use (*before* restoring your DB) -Restore Your DB -Look in log for errors -Correct them. Normally all you shoud get is a bunch of notices that somethings are allready defined. I had the same problem as you with gist indexes in intarray. == Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt Nikis 4, Glyfada Athens 16610 Greece tel:+30-10-8981112 fax:+30-10-8981877 email: [EMAIL PROTECTED] [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] psql on FreeBSD 4.7-RELEASE-p2 and greek (iso8859-7) chars
Hi, I noticed that psql on FreeBSD (i checked also fbsd 4.5 with pgsql port installed) does not accept 8bit iso8859-* chars > 128 (where the greek chars are). In linux that works ok, and i can update/insert/select values using greek strings. I know it must be a fbsd/locale issue, but it would be nice if someone knew something about it. == Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt Nikis 4, Glyfada Athens 16610 Greece tel:+30-10-8981112 fax:+30-10-8981877 email: [EMAIL PROTECTED] [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] SQL -select count-
On Tue, 19 Nov 2002, Giannis wrote: > when I do : > > select count(column_name) from table_name > > should I get the count for all columns or just those which are not null? &Ggr;&igr;&aacgr;&ngr;&ngr;&eegr;, &thgr;&agr; &pgr;&aacgr;&rgr;&egr;&igr;&sfgr; &tgr;&ogr; &pgr;&lgr;&eeacgr;&thgr;&ogr;&sfgr; &tgr;&ohgr;&ngr; &kgr;&ogr;&lgr;&oacgr;&ngr;&ohgr;&ngr; !! &pgr;&ogr;&ugr; &dgr;&egr;&ngr; &egr;&iacgr;&ngr;&agr;&igr; null. &Ggr;&igr;&agr;&tgr;&igr; &dgr;&egr;&ngr; &kgr;&aacgr;&ngr;&egr;&igr;&sfgr; &eacgr;&ngr;&agr; &tgr;&eacgr;&sgr;&tgr; &mgr;&oacgr;&ngr;&ogr;&sfgr;? > __ > Do you Yahoo!? > Yahoo! Web Hosting - Let the expert host your site > http://webhosting.yahoo.com > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) > == Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt Nikis 4, Glyfada Athens 16610 Greece tel:+30-10-8981112 fax:+30-10-8981877 email: [EMAIL PROTECTED] [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] [GENERAL] FreeBSD, Linux: select, select count(*) performance
On Wed, 27 Nov 2002, Tom Lane wrote: > Achilleus Mantzios <[EMAIL PROTECTED]> writes: > > Linux q1 > > > > dynacom=# EXPLAIN ANALYZE SELECT count(*) from noon; > > NOTICE: QUERY PLAN: > > > Aggregate (cost=20508.19..20508.19 rows=1 width=0) (actual > > time=338.17..338.17 > > rows=1 loops=1) > > -> Seq Scan on noon (cost=0.00..20237.95 rows=108095 width=0) (actual > > time=0.01..225.73 rows=108095 loops=1) > > Total runtime: 338.25 msec > > > Linux q2 > > > > dynacom=# EXPLAIN ANALYZE SELECT * from noon; > > NOTICE: QUERY PLAN: > > > Seq Scan on noon (cost=0.00..20237.95 rows=108095 width=1960) (actual > > time=1.22..67909.31 rows=108095 loops=1) > > Total runtime: 68005.96 msec > > You didn't say what was *in* the table, exactly ... but I'm betting > there are a lot of toasted columns, and that the extra runtime > represents the time to fetch (and perhaps decompress) the TOAST entries. 278 columns of various types. namely, Table "noon" Column | Type | Modifiers ++--- v_code | character varying(4) | log_no | bigint | report_date| date | report_time| time without time zone | voyage_no | integer| charterer | character varying(12) | port | character varying(24) | duration | character varying(4) | rotation | character varying(9) | me_do_cons | double precision | reason | character varying(12) | ancorage_date | date | ancorage_time | time without time zone | exp_berth_date | date | exp_berth_time | time without time zone | berth_date | date | berth_time | time without time zone | exp_sail_date | date | exp_sail_time | time without time zone | draft_fw | double precision | draft_aft | double precision | etc_date | date | etc_time | time without time zone | completion_date| date | completion_time| time without time zone | load_quantity | double precision | discharging_quantity | double precision | delivery_date | date | delivery_place | character varying(12) | redelivery_date| date | redelivery_time| time without time zone | redelivery_place | character varying(12) | rob_ifo| double precision | rob_mdo| double precision | log_ifo| double precision | log_mdo| double precision | rcv_ifo| double precision | rcv_mdo| double precision | rcv_me | double precision | rcv_cyl| double precision | rcv_gen| double precision | rob_me | double precision | rob_cyl| double precision | rob_gen| double precision | voyage_sub_no | integer| voyage_activity| character varying(3) | remarks| character varying(60) | latitude | character varying(6) | longitude | character varying(6) | speed | double precision | wind_direction | character varying(1) | rpm| double precision | fuelconsumption| double precision | me_bearing_oil_presure | double precision | me_bearing_amber | double precision | ambere | character varying(8) | remarks2 | character varying(12) | steam_hours| double precision | ifoconsboilerheat | double precision | ae_mdo_consumption | double precision | cyl_me_exh_temp01 | double precision | cyl_me_exh_temp02 | double precision | cyl_me_exh_temp03 | double precision | cyl_me_exh_temp04 | double precision | cyl_me_exh_temp05 | double precision | cyl_me_exh_temp06 | double precision | cyl_me_exh_temp07 | double precision | cyl_me_exh_temp08 | double precision | cyl_me_exh_temp09 | double precision | cyl_me_exh_temp10 | double precision | cyl_me_exh_temp11 | double precision | cyl_me_exh_temp12 | double precision | cyl_me_exh_temp13 | double precisi
Re: [SQL] master-detail relationship and count
On Fri, 29 Nov 2002, Gary Stainburn wrote:
> Hi folks.
>
> I've got a master detail relationship where I have a railway route table
> listing landmarks along the route, and a Links table listing URL's
> associated with that landmark. Listed below:
>
> How can I do a query showing the landmark ID, the landmark name, and a count
> of links associated with that landmark. Below is a SQL statement that
> although is illegal, gives a good idea of what I'm looking for.
>
> select r.rtid, r.rtname, l.count(*) from route r, links l where l.lktype = 'R'
> and l.lklid = r.rtid;
select r.rtid,r.rtname,subsel.cnt from route r,
(select r2.rtid as rid,count(lnk.lkid) as cnt from route r2,links lnk
where lnk.type='R'
and lnk.lklid = r2.rtid group by r2.rtid) as subsel
where r.rtid = subsel.rid
or something like that.
>
> nymr=# \d route
> Table "route"
> Attribute | Type | Modifier
>
>+---+--
> rtid | integer | not null default
> nextval('route_rtid_seq'::text)
> rtmile | integer | not null
> rtyards| integer | not null
> rtname | character varying(40) |
> rtspeed| integer |
> rtgradient | integer |
> rtsection | integer |
> rtphone| character(1) |
> rtcomments | text |
> Indices: route_index,
> route_rtid_key
>
> nymr=# select r.rtid, l.count(*) from route r, links l where
> nymr=# \d links
> Table "links"
> Attribute | Type |Modifier
> ---+---+-
> lkid | integer | not null default
> nextval('staff_sid_seq'::text)
> lkdesc| character varying(40) |
> lkurl | character varying(40) |
> lktype| character(1) |
> lklid | integer |
> Index: links_lkid_key
>
> lktype indicates the link type - 'R' indicates a route entry
> lklid indicates the link ID. For a 'R' it is the rtid of the route entry
> --
> Gary Stainburn
>
> This email does not contain private or confidential material as it
> may be snooped on by interested government parties for unknown
> and undisclosed purposes - Regulation of Investigatory Powers Act, 2000
>
>
> ---(end of broadcast)---
> TIP 4: Don't 'kill -9' the postmaster
>
==
Achilleus Mantzios
S/W Engineer
IT dept
Dynacom Tankers Mngmt
Nikis 4, Glyfada
Athens 16610
Greece
tel:+30-10-8981112
fax:+30-10-8981877
email: [EMAIL PROTECTED]
[EMAIL PROTECTED]
---(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] master-detail relationship and count
On Fri, 29 Nov 2002, Gary Stainburn wrote:
> As you can see from the extract below, your statement has worked for all
> landmarks that have links, but ignores any landmarks with out links. How can
> I adjust this so that all landmarks are listed, but with a zero count where
> appropriate?
Then, use LEFT OUTER JOIN ... USING (),
in combination with COALESCE().
(read the docs)
>
> select r.rtid, r.rtname, subsel.cnt from route r,
> (select r2.rtid as rid, count(lnk.lkid) as cnt from route r2,links lnk
> where lnk.lktype='R'
> and lnk.lklid = r2.rtid group by r2.rtid) as subsel
> where r.rtid = subsel.rid;
> [gary@larry gary]$ psql -d nymr rtid | rtname | cnt
> --++-
> 1 | The Grange | 1
> (1 row)
> [gary@larry gary]$
>
> Gary
>
> On Friday 29 Nov 2002 10:36 am, Achilleus Mantzios wrote:
> > On Fri, 29 Nov 2002, Gary Stainburn wrote:
> > > Hi folks.
> > >
> > > I've got a master detail relationship where I have a railway route table
> > > listing landmarks along the route, and a Links table listing URL's
> > > associated with that landmark. Listed below:
> > >
> > > How can I do a query showing the landmark ID, the landmark name, and a
> > > count of links associated with that landmark. Below is a SQL statement
> > > that although is illegal, gives a good idea of what I'm looking for.
> > >
> > > select r.rtid, r.rtname, l.count(*) from route r, links l where l.lktype
> > > = 'R' and l.lklid = r.rtid;
> >
> > select r.rtid,r.rtname,subsel.cnt from route r,
> > (select r2.rtid as rid,count(lnk.lkid) as cnt from route r2,links lnk
> > where lnk.type='R'
> > and lnk.lklid = r2.rtid group by r2.rtid) as subsel
> > where r.rtid = subsel.rid
> >
> > or something like that.
> >
> > > nymr=# \d route
> > > Table "route"
> > > Attribute | Type | Modifier
> > > +---+
> > >-- rtid | integer | not null default
> > > nextval('route_rtid_seq'::text)
> > > rtmile | integer | not null
> > > rtyards| integer | not null
> > > rtname | character varying(40) |
> > > rtspeed| integer |
> > > rtgradient | integer |
> > > rtsection | integer |
> > > rtphone| character(1) |
> > > rtcomments | text |
> > > Indices: route_index,
> > > route_rtid_key
> > >
> > > nymr=# select r.rtid, l.count(*) from route r, links l where
> > > nymr=# \d links
> > > Table "links"
> > > Attribute | Type |Modifier
> > > ---+---+-
> > > lkid | integer | not null default
> > > nextval('staff_sid_seq'::text)
> > > lkdesc| character varying(40) |
> > > lkurl | character varying(40) |
> > > lktype| character(1) |
> > > lklid | integer |
> > > Index: links_lkid_key
> > >
> > > lktype indicates the link type - 'R' indicates a route entry
> > > lklid indicates the link ID. For a 'R' it is the rtid of the route entry
> > > --
> > > Gary Stainburn
> > >
> > > This email does not contain private or confidential material as it
> > > may be snooped on by interested government parties for unknown
> > > and undisclosed purposes - Regulation of Investigatory Powers Act, 2000
> > >
> > >
> > > ---(end of broadcast)---
> > > TIP 4: Don't 'kill -9' the postmaster
> >
> > ==
> > Achilleus Mantzios
> > S/W Engineer
> > IT dept
> > Dynacom Tankers Mngmt
> > Nikis 4, Glyfada
> > Athens 16610
> > Greece
> > tel:+30-10-8981112
> > fax:+30-10-8981877
> > email: [EMAIL PROTECTED]
> > [EMAIL PROTECTED]
>
> --
> Gary Stainburn
>
> This email does not contain private or confidential material as it
> may be snooped on by interested government parties for unknown
> and undisclosed purposes - Regulation of Investigatory Powers Act, 2000
>
>
==
Achilleus Mantzios
S/W Engineer
IT dept
Dynacom Tankers Mngmt
Nikis 4, Glyfada
Athens 16610
Greece
tel:+30-10-8981112
fax:+30-10-8981877
email: [EMAIL PROTECTED]
[EMAIL PROTECTED]
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
[SQL] FreeBSD, Linux: select, select count(*) performance
Hi, i run 2 queries on 2 similar boxes (one running Linux 2.4.7, redhat 7.1 and the other running FreeBSD 4.7-RELEASE-p2) The 2 boxes run postgresql 7.2.3. I get some performance results that are not obvious (at least to me) i have one table named "noon" with 108095 rows. The 2 queries are: q1: SELECT count(*) from noon; q2: SELECT * from noon; Linux q1 dynacom=# EXPLAIN ANALYZE SELECT count(*) from noon; NOTICE: QUERY PLAN: Aggregate (cost=20508.19..20508.19 rows=1 width=0) (actual time=338.17..338.17 rows=1 loops=1) -> Seq Scan on noon (cost=0.00..20237.95 rows=108095 width=0) (actual time=0.01..225.73 rows=108095 loops=1) Total runtime: 338.25 msec Linux q2 dynacom=# EXPLAIN ANALYZE SELECT * from noon; NOTICE: QUERY PLAN: Seq Scan on noon (cost=0.00..20237.95 rows=108095 width=1960) (actual time=1.22..67909.31 rows=108095 loops=1) Total runtime: 68005.96 msec FreeBSD q1 == dynacom=# EXPLAIN ANALYZE SELECT count(*) from noon; NOTICE: QUERY PLAN: Aggregate (cost=20508.19..20508.19 rows=1 width=0) (actual time=888.93..888.94 rows=1 loops=1) -> Seq Scan on noon (cost=0.00..20237.95 rows=108095 width=0) (actual time=0.02..501.09 rows=108095 loops=1) Total runtime: 889.06 msec FreeBSD q2 == dynacom=# EXPLAIN ANALYZE SELECT * from noon; NOTICE: QUERY PLAN: Seq Scan on noon (cost=0.00..20237.95 rows=108095 width=1975) (actual time=1.08..53470.93 rows=108095 loops=1) Total runtime: 53827.37 msec The pgsql configuration for both systems is identical (the FreeBSD system has less memory but vmstat dont show any paging activity so i assume this is not an issue here). The interesting part is that FreeBSD does better in select *, whereas Linux seem to do much better in select count(*). Paging and disk IO activity for both systems is near 0. When i run the select count(*) in Linux i notice a small increase (15%) in Context Switches per sec, whereas in FreeBSD i notice a big increase in Context Switches (300%) and a huge increase in system calls per second (from normally 9-10 to 110,000). (Linux vmstat gives no syscall info). The same results come out for every count(*) i try. Is it just the reporting from explain analyze?? Has any hacker some light to shed?? Thanx. ====== Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt Nikis 4, Glyfada Athens 16610 Greece tel:+30-10-8981112 fax:+30-10-8981877 email: [EMAIL PROTECTED] [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] EXIST / NOT EXIST
On Tue, 3 Dec 2002, Rachel.Vaudron wrote: > Hi, > > I would like to know if the keyword EXIST can be used with PostgreSQL ? > I have search in the Reference Manuel et tried a query using EXIST in > pgsql, but no result... it is EXISTS . > > Thanks a lot > > Rachel > > ** >[EMAIL PROTECTED] > Laboratoire de prehistoire du Lazaret > 33 bis bd Franck Pilatte 06300 Nice > tel:04-92-00-17-37/fax:04-92-00-17-39 > Windows a bug's life > > > > ---(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 > == Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt Nikis 4, Glyfada Athens 16610 Greece tel:+30-10-8981112 fax:+30-10-8981877 email: [EMAIL PROTECTED] [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] SQL QUERY
On Tue, 3 Dec 2002, Pedro Igor wrote: > I have the following function: > CREATE FUNCTION public.auto_incremento() RETURNS trigger AS ' > begin > new.id = (select (max(id) + 1) from teste); > return new; > end;' LANGUAGE 'plpgsql' > > I created a trigger that uses this function, but i want to the function be usefull >for all tables and not only to tbale teste. > > Someone know how ??? Take a look at sequences. (In Addition, they are autimatically created when a column is of type 'SERIAL'). > > > > Pedro Igor > > > > == Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt Nikis 4, Glyfada Athens 16610 Greece tel:+30-10-8981112 fax:+30-10-8981877 email: [EMAIL PROTECTED] [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] SQL QUERY
On Tue, 3 Dec 2002, Pedro Igor wrote: > But i want implement a trigger that auto-increments the id field. This > options is personal, i don´t like to depend from the database. > Then use table name as parameter to your function. But trully its an error prone policy the one you try to follow, what if a table has a primary key named "fobarid"?? Your function will not work correctly. Or you could programatically read the system tables and find out which column is the primary key for a given table. But unfortunately that way you depend on the database even worse than simply and nicely use sequences. > Pedro Igor > > - Original Message - > From: "Achilleus Mantzios" <[EMAIL PROTECTED]> > To: "Pedro Igor" <[EMAIL PROTECTED]> > Cc: <[EMAIL PROTECTED]> > Sent: Tuesday, December 03, 2002 11:05 AM > Subject: Re: [SQL] SQL QUERY > > > > On Tue, 3 Dec 2002, Pedro Igor wrote: > > > > > I have the following function: > > > CREATE FUNCTION public.auto_incremento() RETURNS trigger AS ' > > > begin > > > new.id = (select (max(id) + 1) from teste); > > > return new; > > > end;' LANGUAGE 'plpgsql' > > > > > > I created a trigger that uses this function, but i want to the function > be usefull for all tables and not only to tbale teste. > > > > > > Someone know how ??? > > > > Take a look at sequences. > > (In Addition, they are autimatically created when a column is of type > > 'SERIAL'). > > > > > > > > > > > > > > Pedro Igor > > > > > > > > > > > > > > > > == > > Achilleus Mantzios > > S/W Engineer > > IT dept > > Dynacom Tankers Mngmt > > Nikis 4, Glyfada > > Athens 16610 > > Greece > > tel:+30-10-8981112 > > fax:+30-10-8981877 > > email: [EMAIL PROTECTED] > > [EMAIL PROTECTED] > > == Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt Nikis 4, Glyfada Athens 16610 Greece tel:+30-10-8981112 fax:+30-10-8981877 email: [EMAIL PROTECTED] [EMAIL PROTECTED] ---(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] save data from views
On Mon, 23 Dec 2002, [iso-8859-1] [EMAIL PROTECTED] wrote: > > > > first of all, thanx for anyone who answered my previous question... i've understood >what was wrong... > now, i've got another question, i know it could sound stupid, but i have not such a >great practice with postgres. i'm asking you: is it that a way to save values from a >view? > i'm using postgres to calculate pollutant emissions by cars, and i make some queries >and create views. i would like to save the results from the views but i can't find a >way to export them. > once again thanx in advance for your help, massimo You could write a program in your favourite language that selects and prints the rows of your views. Also you could # create table tempviewdata as select * from ; Why would you want to save the values of your views?? > > > > > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster > == Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt Nikis 4, Glyfada Athens 16610 Greece tel:+30-10-8981112 fax:+30-10-8981877 email: [EMAIL PROTECTED] [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] 7.3.1 index use / performance
Hi, i am just in the stage of having migrated my test system to 7.3.1 and i am experiencing some performance problems. i have a table "noon" Table "public.noon" Column | Type | Modifiers ++--- v_code | character varying(4) | log_no | bigint | report_date| date | report_time| time without time zone | voyage_no | integer| charterer | character varying(12) | port | character varying(24) | duration | character varying(4) | rotation | character varying(9) | .. with a total of 278 columns. it has indexes: Indexes: noonf_date btree (report_date), noonf_logno btree (log_no), noonf_rotation btree (rotation text_ops), noonf_vcode btree (v_code), noonf_voyageno btree (voyage_no) On the test 7.3.1 system (a FreeBSD 4.7-RELEASE-p2, Celeron 1.2GHz 400Mb, with 168Mb for pgsql), i get: dynacom=# EXPLAIN ANALYZE select FUELCONSUMPTION,rpm,Steam_Hours,voyage_activity,ldin from noon where v_code='4500' and rotation='NOON ' and report_date between '2002-01-07' and '2003-01-07'; QUERY PLAN --- Index Scan using noonf_date on noon (cost=0.00..4.46 rows=1 width=39) (actual time=0.27..52.89 rows=259 loops=1) Index Cond: ((report_date >= '2002-01-07'::date) AND (report_date <= '2003-01-07'::date)) Filter: ((v_code = '4500'::character varying) AND (rotation = 'NOON '::character varying)) Total runtime: 53.98 msec (4 rows) after i drop the noonf_date index i actually get better performance cause the backend uses now the more appropriate index noonf_vcode : dynacom=# EXPLAIN ANALYZE select FUELCONSUMPTION,rpm,Steam_Hours,voyage_activity,ldin from noon where v_code='4500' and rotation='NOON ' and report_date between '2002-01-07' and '2003-01-07'; QUERY PLAN - Index Scan using noonf_vcode on noon (cost=0.00..3122.88 rows=1 width=39) (actual time=0.16..13.92 rows=259 loops=1) Index Cond: (v_code = '4500'::character varying) Filter: ((rotation = 'NOON '::character varying) AND (report_date >= '2002-01-07'::date) AND (report_date <= '2003-01-07'::date)) Total runtime: 14.98 msec (4 rows) On the pgsql 7.2.3 development system (a RH linux 2.4.7, PIII 1 GHz, 1Mb, with 168M for pgsql), i always get the right index use: dynacom=# EXPLAIN ANALYZE select FUELCONSUMPTION,rpm,Steam_Hours,voyage_activity,ldin from noon where v_code='4500' and rotation='NOON ' and report_date between '2002-01-07' and '2003-01-07'; NOTICE: QUERY PLAN: Index Scan using noonf_vcode on noon (cost=0.00..3046.38 rows=39 width=39) (actual time=0.09..8.55 rows=259 loops=1) Total runtime: 8.86 msec EXPLAIN Is something i am missing?? Is this reasonable behaviour?? P.S. Yes i have vaccumed analyzed both systems before the queries were issued. == Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt Nikis 4, Glyfada Athens 16610 Greece tel:+30-10-8981112 fax:+30-10-8981877 email: [EMAIL PROTECTED] [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] 7.3.1 index use / performance
On Tue, 7 Jan 2003, Tomasz Myrta wrote: > Maybe it is not an answer to your question, but why don't you help > Postgres by yourself? Thanx, i dont think that the issue here is to help postgresql by myself. I can always stick to 7.2.3, or use indexes that 7.3.1 will acknowledge, like noonf_vcode_date on noon (v_code,report_date). (unfortunately when i create the above noonf_vcode_date index, it is only used until the next vacuum analyze, hackers is this an issue too???), but these options are not interesting from a postgresql perspective :) > For this kind of queries it's better to drop index on report_date - your > report period is one year and answer to this condition is 10% records (I > suppose) I cannot drop the index on the report_date since a lot of other queries need it. > It would be better to change 2 indexes on v_code and rotation into one > index based on both fields. > What kind of queries do you have? How many records returns each "where" > condition? Use indexes on fields, on which condition result in smallest > amount of rows. > > Regards, > Tomasz Myrta > ====== Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt Nikis 4, Glyfada Athens 16610 Greece tel:+30-10-8981112 fax:+30-10-8981877 email: [EMAIL PROTECTED] [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] A problem about alter table
On Wed, 8 Jan 2003, jack wrote:
> But on postgreSQL 7.2 reference manual, there is a statement for alter table
> such as, ALTER TABLE [ ONLY ] table [ * ]
> ALTER [ COLUMN ] column { SET | DROP } NOT NULL
>
> Do you mean this one hasn't been implemented?
>
> Jack
This syntax is valid in 7.3 (not 7.2)
>
> - Original Message -
> From: "Tomasz Myrta" <[EMAIL PROTECTED]>
> To: "jack" <[EMAIL PROTECTED]>
> Cc: <[EMAIL PROTECTED]>
> Sent: Tuesday, January 07, 2003 11:31 PM
> Subject: Re: [SQL] A problem about alter table
>
>
> > jack wrote:
> >
> > > Hi, all
> > > I'm using postgreSQL 7.2.3. The following statement always cuases a
> parser
> > > error, "parse error at or near NOT". Please adivse, thank you in
> advance.
> > >
> > > ALTER TABLE _acct_group1
> > > ALTER groupkey SET NOT NULL;
> > >
> > > Jack
> > >
> > From Postgres 7.2 documentation:
> >
> > "In the current implementation of ADD COLUMN, default and NOT NULL
> > clauses for the new column are not supported. You can use the SET
> > DEFAULT form of ALTER TABLE to set the default later. (You may also want
> > to update the already existing rows to the new default value, using
> > UPDATE.)"
> >
> > There is nothing about setting not null fields.
> > I think you have to create trigger instead of altering table.
> >
> > Regards,
> > Tomasz Myrta
> >
>
>
> ---(end of broadcast)---
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
>
==
Achilleus Mantzios
S/W Engineer
IT dept
Dynacom Tankers Mngmt
Nikis 4, Glyfada
Athens 16610
Greece
tel:+30-10-8981112
fax:+30-10-8981877
email: [EMAIL PROTECTED]
[EMAIL PROTECTED]
---(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] [PERFORM] 7.3.1 index use / performance
On Tue, 7 Jan 2003, Stephan Szabo wrote:
>
> On Tue, 7 Jan 2003, Achilleus Mantzios wrote:
>
> > i am just in the stage of having migrated my test system to 7.3.1
> > and i am experiencing some performance problems.
> >
> > i have a table "noon"
> > Table "public.noon"
> > Column | Type | Modifiers
> > ++---
> > v_code | character varying(4) |
> > log_no | bigint |
> > report_date| date |
> > report_time| time without time zone |
> > voyage_no | integer|
> > charterer | character varying(12) |
> > port | character varying(24) |
> > duration | character varying(4) |
> > rotation | character varying(9) |
> > ..
> >
> > with a total of 278 columns.
> >
> > it has indexes:
> > Indexes: noonf_date btree (report_date),
> > noonf_logno btree (log_no),
> > noonf_rotation btree (rotation text_ops),
> > noonf_vcode btree (v_code),
> > noonf_voyageno btree (voyage_no)
> >
> > On the test 7.3.1 system (a FreeBSD 4.7-RELEASE-p2, Celeron 1.2GHz
> > 400Mb, with 168Mb for pgsql),
> > i get:
> > dynacom=# EXPLAIN ANALYZE select
> > FUELCONSUMPTION,rpm,Steam_Hours,voyage_activity,ldin from noon where
> > v_code='4500' and rotation='NOON ' and report_date between
> > '2002-01-07' and '2003-01-07';
> > QUERY PLAN
> >
>
> >
>---
> > Index Scan using noonf_date on noon (cost=0.00..4.46 rows=1 width=39)
> > (actual time=0.27..52.89 rows=259 loops=1)
>
>
> > Index Scan using noonf_vcode on noon (cost=0.00..3122.88 rows=1
> > width=39) (actual time=0.16..13.92 rows=259 loops=1)
>
>
> What do the statistics for the three columns actually look like and what
> are the real distributions and counts like?
The two databases (test 7.3.1 and development 7.2.3) are identical
(loaded from the same pg_dump).
About the stats on these 3 columns i get: (see also attachment 1 to avoid
identation/wraparound problems)
schemaname | tablename | attname | null_frac | avg_width | n_distinct |
most_common_vals
| most_common_freqs
|
histogram_bounds |
correlation
+---+-+---+---++-++-+-
public | noon | v_code | 0 | 8 |109 |
{4630,4650,4690,4670,4520,4610,4550,4560,4620,4770}
|
{0.028,0.028,0.0256667,0.024,0.024,0.0236667,0.023,0.023,0.0226667,0.0226667}
| {2070,3210,4330,4480,4570,4680,4751,4820,4870,4940,6020}
| -0.249905
public | noon | report_date | 0 | 4 | 3408 |
{2001-11-14,1998-10-18,2000-04-03,2000-07-04,2000-12-20,2000-12-31,2001-01-12,2001-10-08,2001-12-25,1996-01-23}
|
{0.002,0.0017,0.0017,0.0017,0.0017,0.0017,0.0017,0.0017,0.0017,0.0013}
| {"0001-12-11
BC",1994-09-27,1996-03-26,1997-07-29,1998-08-26,1999-03-29,1999-11-30,2000-09-25,2001-05-25,2002-01-17,2002-12-31}
| -0.812295
public | noon | rotation| 0 |13 | 6 | {"NOON
","PORT LOG ","ARRIVAL ",DEPARTURE,"SEA ","NEXT PORT"}
| {0.460333,0.268667,0.139,0.119667,0.007,0.0053}
|
|
0.119698
(3 rows)
About distributions, i
Re: [SQL] [PERFORM] 7.3.1 index use / performance
On Tue, 7 Jan 2003, Tom Lane wrote:
> Achilleus Mantzios <[EMAIL PROTECTED]> writes:
> > About the stats on these 3 columns i get:
>
> Does 7.2 generate the same stats? (minus the schemaname of course)
Not absolutely but close:
(See attachment)
>
> Also, I would like to see the results of these queries on both versions,
> so that we can see what the planner thinks the index selectivity is:
>
> EXPLAIN ANALYZE select * from noon where
> v_code='4500';
>
> EXPLAIN ANALYZE select * from noon where
> report_date between '2002-01-07' and '2003-01-07';
>
On 7.3.1 (On a FreeBSD)
===
dynacom=# EXPLAIN ANALYZE select * from noon where v_code='4500';
QUERY PLAN
-
Index Scan using noonf_vcode on noon (cost=0.00..3066.64 rows=829
width=1974) (actual time=2.02..1421.14 rows=792 loops=1)
Index Cond: (v_code = '4500'::character varying)
Total runtime: 1424.82 msec
(3 rows)
dynacom=# EXPLAIN ANALYZE select * from noon where report_date between
'2002-01-07' and '2003-01-07';
QUERY PLAN
-
Index Scan using noonf_date on noon (cost=0.00..15919.50 rows=11139
width=1974) (actual time=2.05..13746.17 rows=7690 loops=1)
Index Cond: ((report_date >= '2002-01-07'::date) AND (report_date <=
'2003-01-07'::date))
Total runtime: 13775.48 msec
(3 rows)
On 7.2.3 (Linux)
==
dynacom=# EXPLAIN ANALYZE select * from noon where v_code='4500';
NOTICE: QUERY PLAN:
Index Scan using noonf_vcode on noon (cost=0.00..3043.45 rows=827
width=1974) (actual time=19.59..927.06 rows=792 loops=1)
Total runtime: 928.86 msec
dynacom=# EXPLAIN ANALYZE select * from noon where report_date between
'2002-01-07' and '2003-01-07';
NOTICE: QUERY PLAN:
Index Scan using noonf_date on noon (cost=0.00..16426.45 rows=11958
width=1974) (actual time=29.64..8854.05 rows=7690 loops=1)
Total runtime: 8861.90 msec
EXPLAIN
> regards, tom lane
>
==
Achilleus Mantzios
S/W Engineer
IT dept
Dynacom Tankers Mngmt
Nikis 4, Glyfada
Athens 16610
Greece
tel:+30-10-8981112
fax:+30-10-8981877
email: [EMAIL PROTECTED]
[EMAIL PROTECTED]
tablename | attname | null_frac | avg_width | n_distinct |
most_common_vals
| most_common_freqs
|
histogram_bounds | correlation
---+-+---+---++-+---+-+-
noon | v_code | 0 | 8 |109 |
{4550,4630,4650,4800,4520,4770,4690,4620,4610,4560}
|
{0.027,0.026,0.026,0.0256667,0.025,0.025,0.0246667,0.0226667,0.022,0.022}
| {1030,3210,4360,4500,4570,4670,4740,4820,4870,4940,6020}
| -0.260377
noon | report_date | 0 | 4 | 3402 |
{1999-01-22,2000-12-26,1998-09-29,1998-10-11,1999-02-24,1999-05-19,1999-09-08,1999-09-13,2000-01-19,2000-02-03}
|
{0.002,0.002,0.0017,0.0017,0.0017,0.0017,0.0017,0.0017,0.0017,0.0017}
| {"0001-11-07
BC",1994-10-22,1996-04-05,1997-06-19,1998-07-31,1999-04-01,1999-12-15,2000-09-29,2001-05-31,2002-02-06,2003-01-02}
| -0.821627
noon | rotation| 0 |13 | 6 | {"NOON ","PORT LOG
","ARRIVAL ",DEPARTURE,"SEA ","NEXT PORT"}
| {0.478,0.253667,0.138333,0.121667,0.006,0.0023}
|
|0.147822
(3 rows)
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] 7.3.1 function problem: ERROR: cache lookup failed for type 0
Hi i had written a C function to easily convert an int4 to its equivalent 1x1 int4[] array. It worked fine under 7.1,7.2. Now under 7.3.1 i get the following message whenever i try to: dynacom=# select itoar(3126); ERROR: cache lookup failed for type 0 Surprisingly though when i do something like : dynacom=# select defid from machdefs where itoar(3126) ~ parents and level(parents) = 1 order by description,partno; defid --- 3137 3127 3130 3129 3133 3136 3135 3128 3131 3132 3134 3138 (12 rows) it works fine, but then again when i try to EXPLAIN the above (successful) statement i also get: dynacom=# EXPLAIN select defid from machdefs where itoar(3126) ~ parents and level(parents) = 1 order by description,partno; ERROR: cache lookup failed for type 0 Any clues of what could be wrong?? The definition of the function is: CREATE FUNCTION "itoar" (integer) RETURNS integer[] AS '$libdir/itoar', 'itoar' LANGUAGE 'c' WITH ( iscachable,isstrict ); I also tried without the iscachable option with no luck (since it seems to complain about *type* 0) ====== Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt Nikis 4, Glyfada Athens 16610 Greece tel:+30-10-8981112 fax:+30-10-8981877 email: [EMAIL PROTECTED] [EMAIL PROTECTED] ---(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] [GENERAL] 7.3.1 function problem: ERROR: cache lookup failed
On Tue, 7 Jan 2003, Tom Lane wrote: > Achilleus Mantzios <[EMAIL PROTECTED]> writes: > > Hi i had written a C function to easily convert an int4 to its > > equivalent 1x1 int4[] array. > > Does your function know about filling in the elemtype field that was > recently added to struct ArrayType? She has no clue :) Any pointers would be great. Thanx Tom. > > regards, tom lane > == Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt Nikis 4, Glyfada Athens 16610 Greece tel:+30-10-8981112 fax:+30-10-8981877 email: [EMAIL PROTECTED] [EMAIL PROTECTED] ---(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] [PERFORM] 7.3.1 index use / performance
On Tue, 7 Jan 2003, Tom Lane wrote: > There is no way that adding the filter condition should have reduced the > estimated runtime for this plan --- reducing the estimated number of > output rows, yes, but not the runtime. And in fact I can't duplicate My case persists: After clean install of the database, and after vacuum analyze, i get dynacom=# EXPLAIN ANALYZE select FUELCONSUMPTION,rpm,Steam_Hours,voyage_activity,ldin from noon where report_date between '2002-01-07' and '2003-01-07'; QUERY PLAN - Index Scan using noonf_date on noon (cost=0.00..16458.54 rows=10774 width=39) (actual time=0.13..205.86 rows=7690 loops=1) Index Cond: ((report_date >= '2002-01-07'::date) AND (report_date <= '2003-01-07'::date)) Total runtime: 233.22 msec dynacom=# EXPLAIN ANALYZE select FUELCONSUMPTION,rpm,Steam_Hours,voyage_activity,ldin from noon where report_date between '2002-01-07' and '2003-01-07' and v_code='4500'; QUERY PLAN Index Scan using noonf_vcode on noon (cost=0.00..3092.52 rows=83 width=39) (actual time=0.15..15.08 rows=373 loops=1) Index Cond: (v_code = '4500'::character varying) Filter: ((report_date >= '2002-01-07'::date) AND (report_date <= '2003-01-07'::date)) Total runtime: 16.56 msec (4 rows) I thought PostgreSQL in some sense (hub.org) used FreeBSD, is there any 4.7 FreeBSD server with pgsql 7.3.1 you could use? == Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt Nikis 4, Glyfada Athens 16610 Greece tel:+30-10-8981112 fax:+30-10-8981877 email: [EMAIL PROTECTED] [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] [PERFORM] 7.3.1 index use / performance
On Tue, 7 Jan 2003, Tom Lane wrote: > Achilleus Mantzios <[EMAIL PROTECTED]> writes: > > My case persists: > > After clean install of the database, and after vacuum analyze, > > i get > > Um ... is it persisting? That looks like it's correctly picked the > vcode index this time. Strange behavior though. By "clean install" > do you mean you rebuilt Postgres, or just did dropdb/createdb/reload > data? Just dropdb/createdb/reload. > > regards, tom lane > ====== Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt Nikis 4, Glyfada Athens 16610 Greece tel:+30-10-8981112 fax:+30-10-8981877 email: [EMAIL PROTECTED] [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] SQL list table names
On Wed, 8 Jan 2003, Alan Gutierrez wrote: > alviN wrote: > > is it possible to execute an sql query to be able to list the tables's > > names? well, you can do it on psql using \dt. but im talking about the SQL > > statement, because i want to execute that query from a script. > > Oh, even *I* know the answer to this one! Run psql with the -E argument to see > the SQL used to run \dt. Look at man psql for for info for just: > > psql -E template1 or SELECT tablename from pg_tables where tablename NOT LIKE 'pg%'; > > Alan Gutierrez > > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org > ====== Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt Nikis 4, Glyfada Athens 16610 Greece tel:+30-10-8981112 fax:+30-10-8981877 email: [EMAIL PROTECTED] [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] [PERFORM] 7.3.1 index use / performance
On Tue, 7 Jan 2003, Tom Lane wrote: > Rod Taylor <[EMAIL PROTECTED]> writes: > >> I am wondering about a compiler bug, or some other peculiarity on your > >> platform. Can anyone else using FreeBSD try the above experiment and > >> see if they get different results from mine on 7.3.* (or CVS tip)? > > > On FreeBSD 4.7 I received the exact same results as Tom using the > > statements shown by Tom. > > On looking at the code, I do see part of a possible mechanism for this > behavior: cost_index calculates the estimated cost for qual-clause > evaluation like this: > This bizarre index decreased cost (when adding conditions) behaviour maybe was due to some vacuums. (i cant remember how many reloads and vacuums i did to the database in the period petween the two emails). However my linux machine with the same pgsql 7.3.1, with a full clean installation also gives the same symptoms: Choosing the slow index, and after some (random) vacuums choosing the right index, and then after some vacuums chooses the bad index again. > > regards, tom lane > == Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt Nikis 4, Glyfada Athens 16610 Greece tel:+30-10-8981112 fax:+30-10-8981877 email: [EMAIL PROTECTED] [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
[SQL] http://www.postgresql.org site problem
Warning: pg_connect() unable to connect to PostgreSQL server: FATAL 1: No pg_hba.conf entry for host 64.49.215.82, user portal, database 186_portal in /usr/local/www/www.postgresql.org/globals.php on line 130 == Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt Nikis 4, Glyfada Athens 16610 Greece tel:+30-10-8981112 fax:+30-10-8981877 email: [EMAIL PROTECTED] [EMAIL PROTECTED] ---(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] SQL function parse error ?
On Thu, 9 Jan 2003, Radu-Adrian Popescu wrote: > > > Why is that ? Because the >$ does not exist, not in the default operator > list (also there is no operator defined > using $ anywhere within). And because whitespacing the code solves the > problem, which is rather thin, i must say. > Radu-Adrian, i think the parser is built with yacc, (not "from scratch code") so maybe finding if ">$" is in the specific DB's operators would require code that whould slower the whole parsing process (imagine what it means for performance). ====== Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt Nikis 4, Glyfada Athens 16610 Greece tel:+30-10-8981112 fax:+30-10-8981877 email: [EMAIL PROTECTED] [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] Performance of request of type WHERE ... IN ( ... )
On Wed, 15 Jan 2003, Th Templ wrote: > Hello, > > I have performance problem of an request of type ... WHERE ... IN ( ... ). > How to improve performance of this type of request when a group of id in the > 'in' is important. Try WHERE ... EXISTS (...). > Thanks for your answers. > Templth > > > _ > Add photos to your e-mail with MSN 8. Get 2 months FREE*. > http://join.msn.com/?page=features/featuredemail > > > ---(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 > == Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt Nikis 4, Glyfada Athens 16610 Greece tel:+30-10-8981112 fax:+30-10-8981877 email: [EMAIL PROTECTED] [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] index on to_char(created, 'YYYY') doesn't work
On Wed, 15 Jan 2003, Andreas Joseph Krogh wrote: > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > On Wednesday 15 January 2003 11:37, you wrote: > > The following does not work: > > > > create index session_u_idx on session (to_char(created, '')); > > ERROR: parser: parse error at or near "''" at character 57 > > > > Can I make a function to do this and index using the result of that > > funtion? Do anyone have an example of such a function? > > I tried the following function: > - - > create function drus (timestamp) returns varchar AS' > DECLARE > str_created VARCHAR; > created ALIAS FOR $1; > BEGIN > str_created:= to_char(created, ''''); > RETURN str_created; > END; > ' LANGUAGE 'plpgsql'; add WITH (iscachable) > > create index session_u_idx on session (drus(created)); > - - > But it failes with: > ERROR: DefineIndex: index function must be marked isImmutable > > Now the question is how do I mark an index function isImmutable? > > - -- > Andreas Joseph Krogh <[EMAIL PROTECTED]> > There will always be someone who agrees with you > but is, inexplicably, a moron. > > gpg public_key: http://dev.officenet.no/~andreak/public_key.asc > -BEGIN PGP SIGNATURE- > Version: GnuPG v1.0.7 (GNU/Linux) > > iD8DBQE+JUtlUopImDh2gfQRAl1XAKCkEDKxRDys/Di1gLLRDx6h0TGiPwCeI4FN > DNdajyaQTd27f8MeaWZ+xUE= > =T3we > -END PGP SIGNATURE- > > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster > == Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt Nikis 4, Glyfada Athens 16610 Greece tel:+30-10-8981112 fax:+30-10-8981877 email: [EMAIL PROTECTED] [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] index on to_char(created, 'YYYY') doesn't work
On Wed, 15 Jan 2003, Andreas Joseph Krogh wrote: > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > On Wednesday 15 January 2003 16:12, you wrote: > > On Wed, 15 Jan 2003, Andreas Joseph Krogh wrote: > > > -BEGIN PGP SIGNED MESSAGE- > > > Hash: SHA1 > > > > > > On Wednesday 15 January 2003 11:37, you wrote: > > > > The following does not work: > > > > > > > > create index session_u_idx on session (to_char(created, '')); > > > > ERROR: parser: parse error at or near "''" at character 57 > > > > > > > > Can I make a function to do this and index using the result of that > > > > funtion? Do anyone have an example of such a function? > > > > > > I tried the following function: > > > - - > > > create function drus (timestamp) returns varchar AS' > > > DECLARE > > > str_created VARCHAR; > > > created ALIAS FOR $1; > > > BEGIN > > > str_created:= to_char(created, ''''); > > > RETURN str_created; > > > END; > > > ' LANGUAGE 'plpgsql'; > > > > add > > WITH (iscachable) > > Thank you, not _that_ works:-) > But now this doesn't work: > create index session_u_idx on session (drus(created), username); Functinal indexes are single column indexes. Why dont you change your function to: create function drus (timestamp,varchar) returns varchar A and return the concatenation of to_char(created, '''')||$2 and then create the index as usual (passing the date and the username as params to your function) > > Can't I have multicolumn-indexes with functions? Any idea how to rewrite that > so it works? > Here is my session table: > CREATE TABLE session ( > session_id varchar(256) NOT NULL PRIMARY KEY, > created timestamp DEFAULT 'now' NOT NULL, > last_accessed timestamp NOT NULL, > destroyed timestamp NOT NULL, > username varchar -- Allow sessions from not logged in users > ); > > Here is my query I wish to optimize using indexes: > SELECT to_char(created, 'IW') as week, count(session_id) from session WHERE > username IS NULL and to_char(created, '') = '2002' group by week ORDER BY > week; > > Any hints on optimizing this query, index-usage etc? > > - -- > Andreas Joseph Krogh <[EMAIL PROTECTED]> > There will always be someone who agrees with you > but is, inexplicably, a moron. > > gpg public_key: http://dev.officenet.no/~andreak/public_key.asc > -BEGIN PGP SIGNATURE- > Version: GnuPG v1.0.7 (GNU/Linux) > > iD8DBQE+JU8hUopImDh2gfQRAuA5AKCXyqCZk92d6oCgyJ/Auf8c4xkSaQCgr4Lq > /+r2WSydbYWXNomMvbmt2E8= > =N6NQ > -END PGP SIGNATURE- > > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] > == Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt Nikis 4, Glyfada Athens 16610 Greece tel:+30-10-8981112 fax:+30-10-8981877 email: [EMAIL PROTECTED] [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] index on to_char(created, 'YYYY') doesn't work
On Wed, 15 Jan 2003, Andreas Joseph Krogh wrote: > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > On Wednesday 15 January 2003 18:55, Achilleus Mantzios wrote: > > On Wed, 15 Jan 2003, Andreas Joseph Krogh wrote: > > > -BEGIN PGP SIGNED MESSAGE- > > > Hash: SHA1 > > > > > > On Wednesday 15 January 2003 16:12, you wrote: > > > > On Wed, 15 Jan 2003, Andreas Joseph Krogh wrote: > > > > > -BEGIN PGP SIGNED MESSAGE- > > > > > Hash: SHA1 > > > > > > > > > > On Wednesday 15 January 2003 11:37, you wrote: > > > > > > The following does not work: > > > > > > > > > > > > create index session_u_idx on session (to_char(created, '')); > > > > > > ERROR: parser: parse error at or near "''" at character 57 > > > > > > > > > > > > Can I make a function to do this and index using the result of that > > > > > > funtion? Do anyone have an example of such a function? > > > > > > > > > > I tried the following function: > > > > > - - > > > > > create function drus (timestamp) returns varchar AS' > > > > > DECLARE > > > > > str_created VARCHAR; > > > > > created ALIAS FOR $1; > > > > > BEGIN > > > > > str_created:= to_char(created, ''''); > > > > > RETURN str_created; > > > > > END; > > > > > ' LANGUAGE 'plpgsql'; > > > > > > > > add > > > > WITH (iscachable) > > > > > > Thank you, not _that_ works:-) > > > But now this doesn't work: > > > create index session_u_idx on session (drus(created), username); > > > > Functinal indexes are single column indexes. > > > > Why dont you change your function to: > > > > create function drus (timestamp,varchar) returns varchar A > > > > and return the concatenation of to_char(created, '''')||$2 > > > > and then create the index as usual (passing the date and the username > > as params to your function) > > OK, thank you. > Let me explain what I want to do: You didnt try it!! Change your to_char(created, '''')||$2 to to_char(created, '''')||(coalesce($2,'''') (provided there is no user named mister '' :) then perform your query like: select to_char(created, 'IW') as week, count(session_id) from session WHERE drus(created,username) = '2002' group by week ORDER BY week; do a explain analyze to see index and performance issues. > I have the following schema for gathering statistics from the web: > > CREATE TABLE session ( > session_id varchar(256) NOT NULL PRIMARY KEY, > created timestamp DEFAULT 'now' NOT NULL, > last_accessed timestamp NOT NULL, > destroyed timestamp NOT NULL, > username varchar -- Allow sessions from not logged in users > ); > > create or replace function drus (timestamp) returns varchar AS' > DECLARE > str_created VARCHAR; > created ALIAS FOR $1; > BEGIN > str_created:= to_char(created, ''''); > RETURN str_created; > END; > ' LANGUAGE 'plpgsql' WITH (iscachable); > > create index session_u_idx on session (drus(created)) where username is null; > > Now I want to get statistics for number of hits pr. week where users are not > lnogged in(username IS NULL) for the year 2002: > > select to_char(created, 'IW') as week, count(session_id) from session WHERE > username IS NULL and drus(created) = '2002' group by week ORDER BY week; > week | count > - --+--- > 01 | 6321 > 18 |74 > 19 | 12153 > 20 | 17125 > 21 | 22157 > 22 | 25316 > 23 | 24265 > 24 | 26234 > 25 | 28583 > 26 | 29156 > 27 | 28335 > 28 | 23587 > 29 | 23203 > > This table is quite large(900 000 rows) and the query takes several minutes to > run, which makes the browser timeout. > Do I have a design-issue here, should I rather batch-generate the stats in its > own table so I don't have to process all the data(900 000 rows) each time. > > Is there any way to optimize/rewrite this query? Is the use of to_char on the > timestamp wrong, should I use another compara
Re: [SQL] optimal sql
On Wed, 22 Jan 2003, Michael Hostbaek wrote:
> Hi,
I would suggest looking at the problem in three directions:
a) PostgreSQL system wise
b) PostgreSQL sql wise
c) FreeBSD wise.
For a) do all the necessary tuning on PostgreSQL.
With 1GB of Mem, you could set a value of shared_buffers to 10.
Also check the fsync setting.
Minimising logging may be a good idea.
Read the docs on the site.
For b) do explain analyze to be sure you have the right index
usage, or create indexes where appropriate.
Check the statitistics of your tables, distributions,
counts etc...
For c) check all kern.ipc.shm* and kern.ipc.sem* kernel variables.
(you will need to set some of those in order to get the desired
shared_buffers in a))
Rebuild a custom kernel fitting your needs. Check
http://www.freebsd.org/doc/en_US.ISO8859-1/books/handbook/config-tuning.html
Also do man 7 tuning.
And, check
http://www.freebsd.org/doc/en_US.ISO8859-1/books/developers-handbook/index.html
(Look at DMA access in your kernel CONFIG, consider turning on IDE write
caching, etc).
Also during your perl script, a good idea is to have
iostat 3 , vmstat 3, running.
This will give you hints of where your system starves.
If for instance your system cache is small, and CPU usage is small and you
have
a lot of IO, then increase shared_buffers, and tune your disks.
(also do man 8 tunefs)
IF you have nearly ~ 100% CPU usage, then the system may look
healthier but your query not.
>
> I am running postgresql 7.2.3 on a test server (with potential of
> becoming my production server).
>
> On the server I have a perl script, that is grabbing some data from a
> inventory database (local) - with some subselects.
> The query is like this:
>
>
> my $sth = $ppdb->prepare("
> select partno, create_date, mfg, condition, gescode, qty,
> cmup,(SELECT partno_main FROM partno_lookup where
> partno_lookup.partno_alias ilike (?|| inventory.partno ||?) and mfg
> ilike ? limit 1)
> as partno_main, (SELECT subcat FROM partno_lookup where
> partno_lookup.partno_alias ilike
> (?|| inventory.partno ||?) and mfg ilike ? limit 1) as subcat,
> (SELECT key_search FROM partno_lookup where
> partno_lookup.partno_alias ilike (?|| inventory.partno ||?) and
> mfg ilike ? limit 1) as key_search,
> (SELECT text_desc FROM descriptions where
> descriptions.partno=(SELECT partno_main FROM partno_lookup
> where partno_lookup.partno_alias ilike (?|| inventory.partno ||?)
> and mfg ilike ? limit 1)
> limit 1) as descri from inventory where mfg ilike ? and ? <
> create_date $refurbed order by key_search,
> subcat, partno_main, status DESC ");
>
>
> It takes quite a while for the query to get processed - and the script
> to return my values.
> The inventory table has approx. 23000 records - and the partno_lookup
> has approx. 1100.
>
> Is there anyway I can optimize the sql - og perhaps optimize my
> postgresql db settings ? ( I am running my postgresql on FreeBSD, on a
> fairly adequite machine with 1GB RAM)
>
> I look forward to any pointers or hints you might have.
>
> Thanks.
>
> /mich
>
> --
> Best Regards,
> Michael Landin Hostbaek
> FreeBSDCluster.org - an International Community
>
> */ PGP-key available upon request /*
>
> ---(end of broadcast)---
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>
==
Achilleus Mantzios
S/W Engineer
IT dept
Dynacom Tankers Mngmt
Nikis 4, Glyfada
Athens 16610
Greece
tel:+30-10-8981112
fax:+30-10-8981877
email: [EMAIL PROTECTED]
[EMAIL PROTECTED]
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] optimal sql
On Wed, 22 Jan 2003, Michael Paesold wrote: > Achilleus Mantzios <[EMAIL PROTECTED]> wrote: > > > For a) do all the necessary tuning on PostgreSQL. > > With 1GB of Mem, you could set a value of shared_buffers to 10. > > Perhaps just a type, but that is way to much! It would mean about 800 Mb > shared buffers! I would rather suggest a value between 1000 and 1. See > recent descussions on -performance and -hackers mailing lists. Personally i found only performance improvement when increasing shared_buffers. (but then again i speak for me and my queries). The 100,000 value was certainly not a typo (provided he doesnt run X11,KDE, mozilla, etc... on his server) but maybe too high. Some people say 25% of the total Mem is a good rule of thumb, but testing for his specific query must be made. > > Best Regards, > Michael Paesold > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org > == Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt Nikis 4, Glyfada Athens 16610 Greece tel:+30-10-8981112 fax:+30-10-8981877 email: [EMAIL PROTECTED] [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Scheduling Events?
On Thu, 23 Jan 2003, David Durst wrote: > Is there anyway to schedule DB Events based on time? Yes! cron > So lets say I had a table w/ depreciation schedules in it, > I would like the DB to apply the formula and make the entries on the END > of every month. > > > > ---(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 > ====== Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt Nikis 4, Glyfada Athens 16610 Greece tel:+30-10-8981112 fax:+30-10-8981877 email: [EMAIL PROTECTED] [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Scheduling Events?
On Fri, 24 Jan 2003, David Durst wrote: > > On Thu, 23 Jan 2003, David Durst wrote: > > > > Here is the basic problem w/ using CRON in an accounting situation. > > I can't be sure that cron will always be up when the DB is up, > so lets say crond goes down for some random reason (User, System error, > Etc..) > I you cannot depend on your system to run crond then you should not depend on it to run postgresql either. > > > > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html > ====== Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt Nikis 4, Glyfada Athens 16610 Greece tel:+30-10-8981112 fax:+30-10-8981877 email: [EMAIL PROTECTED] [EMAIL PROTECTED] ---(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] import error
On Tue, 28 Jan 2003, Oliver Vecernik wrote: > > 1,1,"07.08.2001","11:35",1,1,2220,0,6.0,0.0,12,0,"",20,0,0,0,1,"Sarigerme","Asche > Bucht","wolkenlos",">20m",35,0,30,"","Mehmet > Semerkant","","",0.0,1,0.,0.,0,0.,0,"","","","","",12,0,0,0 Why dont you get rid of "'s ? e.g. 1,1,2001-08-07,11:35,1,1,...etc.. (Also see if there exists a DateStyle like yours 07.08.2001 in order to avoid the date format conversion) > > My import gives following error: > > sport=# copy dives from '/var/n/tmp/ov.CSV' delimiters ','; > ": can't parse "ne 1, pg_atoi: error in "0 > > My first thought was that the date and time is wrong (because it's the > only "0), but even after removing it for a test results in the same > error. How can I find out, which field exactly causes this error? > > My PostgreSQL version is (on Debian/Woody): > > sport=# select version(); >version > --- > PostgreSQL 7.2.1 on i686-pc-linux-gnu, compiled by GCC 2.95.4 > (1 row) > > Regards, > Oliver > > -- > VECERNIK Datenerfassungssysteme > A-2560 Hernstein, Hofkogelgasse 17 > Tel.: +43 2633 47530, Fax: DW 50 > http://members.aon.at/vecernik > > > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) > == Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt Nikis 4, Glyfada Athens 16610 Greece tel:+30-10-8981112 fax:+30-10-8981877 email: [EMAIL PROTECTED] [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] import error
On Tue, 28 Jan 2003, Achilleus Mantzios wrote: > On Tue, 28 Jan 2003, Oliver Vecernik wrote: > > > > > 1,1,"07.08.2001","11:35",1,1,2220,0,6.0,0.0,12,0,"",20,0,0,0,1,"Sarigerme","Asche > > Bucht","wolkenlos",">20m",35,0,30,"","Mehmet > > Semerkant","","",0.0,1,0.,0.,0,0.,0,"","","","","",12,0,0,0 > > Why dont you get rid of "'s ? > e.g. > 1,1,2001-08-07,11:35,1,1,...etc.. > (Also see if there exists a DateStyle like yours 07.08.2001 > in order to avoid the date format conversion) I think German DateStyle will do the trick. > > > > > My import gives following error: > > > > sport=# copy dives from '/var/n/tmp/ov.CSV' delimiters ','; > > ": can't parse "ne 1, pg_atoi: error in "0 > > > > My first thought was that the date and time is wrong (because it's the > > only "0), but even after removing it for a test results in the same > > error. How can I find out, which field exactly causes this error? > > > > My PostgreSQL version is (on Debian/Woody): > > > > sport=# select version(); > >version > > --- > > PostgreSQL 7.2.1 on i686-pc-linux-gnu, compiled by GCC 2.95.4 > > (1 row) > > > > Regards, > > Oliver > > > > -- > > VECERNIK Datenerfassungssysteme > > A-2560 Hernstein, Hofkogelgasse 17 > > Tel.: +43 2633 47530, Fax: DW 50 > > http://members.aon.at/vecernik > > > > > > > > ---(end of broadcast)--- > > TIP 2: you can get off all lists at once with the unregister command > > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) > > > > ====== > Achilleus Mantzios > S/W Engineer > IT dept > Dynacom Tankers Mngmt > Nikis 4, Glyfada > Athens 16610 > Greece > tel:+30-10-8981112 > fax:+30-10-8981877 > email: [EMAIL PROTECTED] > [EMAIL PROTECTED] > > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] > == Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt Nikis 4, Glyfada Athens 16610 Greece tel:+30-10-8981112 fax:+30-10-8981877 email: [EMAIL PROTECTED] [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: CR/LF conversion (was: [SQL] import error)
On Tue, 28 Jan 2003, Oliver Vecernik wrote: > Oliver Vecernik schrieb: > > > Hi all! > > > > I'm trying to import a text file from Suunto Dive Manager. I've got > > following table structure: > > Arghh ... > > It's always the same problem with CR/LF conversions ... > > After changing it to just LFs, everthing worked like a charm. Is there > an elegant way to handle this automatically? If your dates were german in the first place, keep them german during the copy, otherwise you will have wrong dates. > > Oliver > > -- > VECERNIK Datenerfassungssysteme > A-2560 Hernstein, Hofkogelgasse 17 > Tel.: +43 2633 47530, Fax: DW 50 > http://members.aon.at/vecernik > > > > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] > == Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt Nikis 4, Glyfada Athens 16610 Greece tel:+30-10-8981112 fax:+30-10-8981877 email: [EMAIL PROTECTED] [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] CSV import
On Tue, 28 Jan 2003, Oliver Vecernik wrote: > Hi again! > > After investigating a little bit further my CSV import couldn't work > because of following reasons: > > 1. CSV files are delimited with CR/LF See below > 2. text fields are surrounded by double quotes in vi :1,$ s/"//g > > Is there a direct way to import such files into PostgreSQL? > > I would like to have something like MySQL provides: > > LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name.txt' > [REPLACE | IGNORE] > INTO TABLE tbl_name > [FIELDS > [TERMINATED BY '\t'] > [[OPTIONALLY] ENCLOSED BY ''] > [ESCAPED BY '\\' ] > ] > [LINES TERMINATED BY '\n'] make it [LINES TERMINATED BY '\r\n'] > [IGNORE number LINES] > [(col_name,...)] > > Has anybody written such a function already? > > Regards, > Oliver > > -- > VECERNIK Datenerfassungssysteme > A-2560 Hernstein, Hofkogelgasse 17 > Tel.: +43 2633 47530, Fax: DW 50 > http://members.aon.at/vecernik > > > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster > == Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt Nikis 4, Glyfada Athens 16610 Greece tel:+30-10-8981112 fax:+30-10-8981877 email: [EMAIL PROTECTED] [EMAIL PROTECTED] ---(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] Delete 1 Record of 2 Duplicate Records
On Thu, 30 Jan 2003 [EMAIL PROTECTED] wrote: > How do I delete only 1 of the duplicate records? Do select oid,* from test where column_id = 5; then choose which oid to delete and do delete from test where oid = ...; > > > column_name | column_id > -+-- > test1 | 5 > test1 | 5 > > > I've tried this: > > tmp_test=# delete from test where column_id = 5 limit 1; > ERROR: parser: parse error at or near "limit" > > I'm using version 7.2.1 > > Thank you. > > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html > == Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt Nikis 4, Glyfada Athens 16610 Greece tel:+30-10-8981112 fax:+30-10-8981877 email: [EMAIL PROTECTED] [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] automatic time/user stamp - rule or trigger?
On Wed, 5 Feb 2003, Neal Lindsay wrote: > I have a table that I want to keep track of the user who last modified > it and the timestamp of the modification. Should I use a trigger or a rule? > > CREATE TABLE stampedtable ( > stampedtableid SERIAL PRIMARY KEY, > updatestamp timestamp NOT NULL DEFAULT now(), > updateuser name NOT NULL DEFAULT current_user, > sometext text > ); > > I suspect that I want a rule, but all the examples in the documentation > seem to update a separate table and not override (or add) the > insert/update to the timestamp and name columns. You may want to use rules if you need rewriting. What you actually need is some sort of driver to a specific table. You could create a view to that table (to hide the accounting columns), and then create rules on that view that do the job as you wish. > > Thanks, > -Neal > > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster > ====== Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt Nikis 4, Glyfada Athens 16610 Greece tel:+30-10-8981112 fax:+30-10-8981877 email: [EMAIL PROTECTED] [EMAIL PROTECTED] ---(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] PostgreSQL 7.3.1 multiple schema select query error:
.invoke(StandardPipeline.java:472) > 15:43:40,108 ERROR [STDERR] at > org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:943) > 15:43:40,118 ERROR [STDERR] at > org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java > :174) > 15:43:40,118 ERROR [STDERR] at > org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:5 > 66) > 15:43:40,118 ERROR [STDERR] at > org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:472) > 15:43:40,128 ERROR [STDERR] at > org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:943) > 15:43:40,128 ERROR [STDERR] at > org.apache.catalina.connector.http.HttpProcessor.process(HttpProcessor.java: > 1027) > 15:43:40,128 ERROR [STDERR] at > org.apache.catalina.connector.http.HttpProcessor.run(HttpProcessor.java:1125 > ) > 15:43:40,128 ERROR [STDERR] at java.lang.Thread.run(Thread.java:536) > > > What would the possible cause of the error?? > The above query run fine when using pgadmin2 client. > > > > > > --- > Outgoing mail is certified Virus Free. > Checked by AVG anti-virus system (http://www.grisoft.com). > Version: 6.0.449 / Virus Database: 251 - Release Date: 1/27/2003 > > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org > == Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt Nikis 4, Glyfada Athens 16610 Greece tel:+30-10-8981112 fax:+30-10-8981877 email: [EMAIL PROTECTED] [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Hex Integer Input
On Fri, 7 Mar 2003, Tom Lane wrote: > Achilleus Mantzios <[EMAIL PROTECTED]> writes: > > Hi, is there a way to enter integer data by their HEX > > representation?? > > I'm not sure that this is SQL-spec, but at least as of 7.3, you can > coerce a bitstring literal to int, so: > > z=# select x'0f'; > ?column? > -- > > (1 row) > > z=# select x'0f'::int4; > int4 > -- >15 > (1 row) Thanx. > > Looks like it works for int8 as well. > > regards, tom lane > -- == Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt Nikis 4, Glyfada Athens 16610 Greece tel:+30-210-8981112 fax:+30-210-8981877 email: [EMAIL PROTECTED] [EMAIL PROTECTED] ---(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] Hex Integer Input
On Fri, 7 Mar 2003, Joe Conway wrote: > Achilleus Mantzios wrote: > > Hi, is there a way to enter integer data by their HEX > > representation?? > > > > Is this what you want? > > regression=# select x''::int4; > int4 > --- > 65535 > (1 row) Sure. Thanx. > > > Joe > -- == Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt Nikis 4, Glyfada Athens 16610 Greece tel:+30-210-8981112 fax:+30-210-8981877 email: [EMAIL PROTECTED] [EMAIL PROTECTED] ---(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] cast of integer to bool doesn't work (anymore?)
On Fri, 21 Mar 2003, Eric Veldhuyzen wrote:
> On Fri, Mar 21, 2003 at 04:46:17PM -0200, Achilleus Mantzios wrote:
> >
> > Currently (7.3) all input can be handled if fed as text.
> > So what you can do is simply:
> >
> > my $sth = $dbh->prepare(
> > "INSERT INTO object_def (name, meant_as_subobject) VALUES (?,?)");
> > $sth->execute('test', '0');
>
> Ah, thanks, that seems to work, with only minor modifications to our
> code. Is there any reason why integers are no longer convertable to
> booleans?
There has been some general rectification on the casting system in 7.3.*.
>
>
--
==
Achilleus Mantzios
S/W Engineer
IT dept
Dynacom Tankers Mngmt
Nikis 4, Glyfada
Athens 16610
Greece
tel:+30-210-8981112
fax:+30-210-8981877
email: [EMAIL PROTECTED]
[EMAIL PROTECTED]
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Listing Users
On Wed, 19 Mar 2003, Kyle wrote: > What is the SQL command to list users? > > CREATE USER, DROP USER, ALTER USER, USER > > I just can't seem to find the command to list them. SELECT * from pg_user ; > > -Kyle > > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] > -- ====== Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt Nikis 4, Glyfada Athens 16610 Greece tel:+30-210-8981112 fax:+30-210-8981877 email: [EMAIL PROTECTED] [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] How can I quit this: "Unique " to my table?
On Tue, 1 Apr 2003, Luis Mix wrote: > Hello pgsql-sql, > > When my table was create I wrote this: > > MyDatabase=# create table b_prestamo(no_inventa char(16) unique not > null, cve_area char(10), date1 date); > I need now that my table can accept repeated records, but not Null. > How can I do that? > SomeBody can help me please? > My table has information at the moment,what can I do for maintain > it? drop index b_prestamo_no_inventa_idx; > > > > -- ====== Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt Nikis 4, Glyfada Athens 16610 Greece tel:+30-210-8981112 fax:+30-210-8981877 email: [EMAIL PROTECTED] [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] DELETE FROM A BLACK LIST
On Wed, 5 Mar 2003 [EMAIL PROTECTED] wrote: > > Hi all, > I have a Blacklist table containing about 1000 ID to delete from another > table (T1 about 1440294 records) > If i use the SQL > > DELETE FROM T1 WHERE T1.ID IN (SELECT BLACKLIST.ID FROM BLACKLIST) > > the operation is very slow . > > There is a faster way to do the same operation?? use EXISTS > > Thanks > Luca > > > > > > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] > -- ====== Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt Nikis 4, Glyfada Athens 16610 Greece tel:+30-210-8981112 fax:+30-210-8981877 email: [EMAIL PROTECTED] [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] To ListAdms: Is pgsql-sql operating?
Is there any problem with [EMAIL PROTECTED] list? -- == Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt Nikis 4, Glyfada Athens 16610 Greece tel:+30-210-8981112 fax:+30-210-8981877 email: achill at matrix dot gatewaynet dot com mantzios at softlab dot ece dot ntua dot gr ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] how to determine array size
On Mon, 9 Jun 2003, Forest Wilkinson wrote:
> I need to enumerate the constraints on any given column in a table, so
> I'm examining pg_constraint to get the relevant information. The
> conkey array contains a list of constrained columns, and although I am
> able to check conkey[1] for constraints on a single column, I would
> like to properly handle multi-column constraints.
>
> How do I determine the size of the conkey array? I haven't found any
> field that looks like it contains the number of values in conkey. Do
> I have to check each element of the array sequentially, until I get a
> NULL value from one of them? (Section 5.12 of the User's Guide seems
> to forbid this: "A limitation of the present array implementation is
> that individual elements of an array cannot be SQL null values.")
> Moreover, that method doesn't give me a nice way of selecting all
> constraints on a specific column, as I would have to write clauses
> like this:
>
> ... WHERE conkey[1] = blah OR conkey[2] = blah OR conkey[3] = blah OR
> conkey[4] = blah ...
>
> Can somone offer a better way?
Well if you are willing to extend contrib package intarray
to something like smallintarray
you could simply do
SELECT conname from pg_constraint where conrelid= and
'{blah}' ~ conkey;
Or as a quick solution create your own function
boolean isinarr(smallint,smallint[])
that performs this task, and do
SELECT conname from pg_constraint where conrelid= and
isinarr(blah,conkey);
>
>
> ---(end of broadcast)---
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html
>
--
==
Achilleus Mantzios
S/W Engineer
IT dept
Dynacom Tankers Mngmt
Nikis 4, Glyfada
Athens 16610
Greece
tel:+30-210-8981112
fax:+30-210-8981877
email: achill at matrix dot gatewaynet dot com
mantzios at softlab dot ece dot ntua dot gr
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] (no subject)
On Thu, 12 Jun 2003, A. Van Hook wrote: > When updating from 7.2.3 to 7.3.3 we have lost the ability to see the > passwords in pg_shadow. Is there a way to decrypt the passwords? The default case (with md5 method in pg_hba.conf) is encrypted passwds. But you still can do ALTER USER foo UNENCRYPTED password 'bar'; dynacom=# SELECT * from pg_shadow where usename='foo'; usename | usesysid | usecreatedb | usesuper | usecatupd | passwd | valuntil | useconfig -+--+-+--+---++--+--- foo | 100 | f | f| f | bar| | (1 row) dynacom=# > > thanks > > -- ====== Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt Nikis 4, Glyfada Athens 16610 Greece tel:+30-210-8981112 fax:+30-210-8981877 email: achill at matrix dot gatewaynet dot com mantzios at softlab dot ece dot ntua dot gr ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] CREATE table1 FROM table2
On 17 Jun 2003, Rado Petrik wrote: > Hi, > > How I create table1 from other table2 . > > "cp table1 table2" create table table2 as select * from table1; > > Thanks. > > -- ====== Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt Nikis 4, Glyfada Athens 16610 Greece tel:+30-210-8981112 fax:+30-210-8981877 email: achill at matrix dot gatewaynet dot com mantzios at softlab dot ece dot ntua dot gr ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] create view error
On Mon, 7 Jul 2003, Gary Stainburn wrote:
> Hi folks,
>
> I know I'm missing something blindingly obvious, can someone point it out to
> me please.
>
> create table locos ( -- Locos table - contains details of locos
> lid int4 default nextval('loco_lid_seq'::text) unique not null,
> lclassint4 references lclass(lcid), -- Loco Class
> lbuiltdate, -- Date off-shed
> lcme int4 references cme(cmid), -- Chief Mechanical Engineer
> lname character varying(30), -- Name of Loco
> lcomments text-- free text comments
> );
> NOTICE: CREATE TABLE / UNIQUE will create implicit index 'locos_lid_key' for
> table 'locos'
> NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
> CREATE
> create table lnumbers ( -- alternate loco numbers
> lnid int4 not null references locos(lid),
> lnumber character varying(10),
> lncurrent bool,
> primary key (lnid, lnumber)
> );
> NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'lnumbers_pkey'
> for table 'lnumbers'
> NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
> CREATE
> create view loco_dets as
> select * from locos l
> left outer join
> (select * from lclass) lc on lc.lcid = l.lclass
> left outer join
> (select lnumber from lnumbers) ln on ln.lnid = l.lid and ln.lncurrent
^^^
select also lnid
> = true
> left outer join
> (select * from company) c on c.coid = lc.lcompany;
> ERROR: No such attribute or function ln.lnid
>
--
==
Achilleus Mantzios
S/W Engineer
IT dept
Dynacom Tankers Mngmt
Nikis 4, Glyfada
Athens 16610
Greece
tel:+30-210-8981112
fax:+30-210-8981877
email: achill at matrix dot gatewaynet dot com
mantzios at softlab dot ece dot ntua dot gr
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] Logging select statements
Thats why people who want entreprise apps must use enterprise frameworks. In J2EE for instance you could use LOG4J which is sorta equivalent of syslog for java. See if there is a logging module for PHP. PgSQL has no clue of who the user is. I dont think delegating this logging task to pgSQL is a good idea. Dont get me wrong, I like and use php myself, but only when i know the exact limits of how far the specific project will go in the future. On Wed, 9 Jul 2003, Rudi Starcevic wrote: > Hi, > > I have an application where user's can view records in a short form with > their first select > and view a long form with a second select. > The first view I term an impression. > The second view I term a click. > > I'd like to log the impression's and click's. > I'm wondering which is the most effiecient way to do this. > > I know I can do it in the application, PHP, by looping through the > result set and inserting into a logging table but > am wondering if it quicker to write a rule or trigger so that each > individual select is logged into a logging table > as it's selected. > > For example: > If I have a table of 3000 row's and the user submits a query which > retrieve's 100 rows. > In the first senario I could loop through the 100, using a language PHP > or Perl, and make 100 inserts after the first select is complete. > Thus 1 select plus 100 inserts. > > Can you see a way to do this all in SQL that would be better/faster/more > efficient without using PHP/Perl ? > > Many thanks > Regards > Rudi. > > > > > > > > > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster > -- == Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt Nikis 4, Glyfada Athens 16610 Greece tel:+30-210-8981112 fax:+30-210-8981877 email: achill at matrix dot gatewaynet dot com mantzios at softlab dot ece dot ntua dot gr ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
