Re: [SQL] A form of inheritance with PostgreSQL
Just a little example of what I've been using on version 8.0.3 with total
satisfaction.
CREATE TABLE "public"."tblMovementDetails" (
"ID" INTEGER NOT NULL,
"PlanningDetailID" INTEGER NOT NULL,
"MovementID" INTEGER NOT NULL,
"UserID" VARCHAR(5) NOT NULL,
"Number" INTEGER DEFAULT 0 NOT NULL,
"ChangeDate" DATE NOT NULL,
CONSTRAINT "PK_tblMovementDetails" PRIMARY KEY("ID")
) WITHOUT OIDS;
CREATE RULE "disallow_delete" AS ON DELETE TO "public"."tblMovementDetails"
DO INSTEAD NOTHING;
CREATE RULE "disallow_insert" AS ON INSERT TO "public"."tblMovementDetails"
DO INSTEAD NOTHING;
CREATE RULE "disallow_update" AS ON UPDATE TO "public"."tblMovementDetails"
DO INSTEAD NOTHING;
CREATE TABLE "public"."tblDeliveryDetails" (
"InvoiceAmount" NUMERIC(8,2) DEFAULT 0 NOT NULL,
CONSTRAINT "PK_tblDeliveryDetails" PRIMARY KEY("ID"),
CONSTRAINT "CH_tblDeliveryDetails_InvoiceAmount" CHECK ("InvoiceAmount" >=
(0)::numeric),
CONSTRAINT "CH_tblDeliveryDetails_Number" CHECK ("Number" >= 0),
CONSTRAINT "FK_tblDeliveryDetails_tblMovement" FOREIGN KEY ("MovementID")
REFERENCES "public"."tblDelivery"("ID")
ON DELETE NO ACTION
ON UPDATE NO ACTION
NOT DEFERRABLE,
CONSTRAINT "FK_tblDeliveryDetails_tblPlanningDetails" FOREIGN KEY
("PlanningDetailID")
REFERENCES "public"."tblPlanningDetails"("ID")
ON DELETE NO ACTION
ON UPDATE NO ACTION
NOT DEFERRABLE
) INHERITS ("public"."tblMovementDetails")
WITHOUT OIDS;
CREATE INDEX "IDX_tblDeliveryDetails_PlanningDetailID" ON
"public"."tblDeliveryDetails"
USING btree ("PlanningDetailID");
CREATE TRIGGER "TRIG_tblDeliveryDetails_Archive" AFTER INSERT OR UPDATE OR
DELETE
ON "public"."tblDeliveryDetails" FOR EACH ROW
EXECUTE PROCEDURE "public"."func_archive_delivery"();
CREATE TRIGGER "TRIG_tblDeliveryDetails_ChangeDate" BEFORE INSERT OR UPDATE
ON "public"."tblDeliveryDetails" FOR EACH ROW
EXECUTE PROCEDURE "public"."func_set_changedate"();
CREATE RULE "get_pkey_on_insert" AS ON INSERT TO "public"."tblDeliveryDetails"
DO (SELECT currval('"tblDeliveryDetails_ID_seq"'::text) AS "ID",
new."PlanningDetailID";);
CREATE TABLE "public"."tblOrderDetails" (
"QuoteID" INTEGER NOT NULL,
CONSTRAINT "PK_tblOrderDetails" PRIMARY KEY("ID"),
CONSTRAINT "CH_tblOrderDetails_Number" CHECK ("Number" >= 0),
CONSTRAINT "FK_tblOrderDetails_tblMovement" FOREIGN KEY ("MovementID")
REFERENCES "public"."tblOrder"("ID")
ON DELETE NO ACTION
ON UPDATE NO ACTION
NOT DEFERRABLE,
CONSTRAINT "FK_tblOrderDetails_tblPlanningDetails" FOREIGN KEY
("PlanningDetailID")
REFERENCES "public"."tblPlanningDetails"("ID")
ON DELETE NO ACTION
ON UPDATE NO ACTION
NOT DEFERRABLE,
CONSTRAINT "FK_tblOrderDetails_tblQuote" FOREIGN KEY ("QuoteID")
REFERENCES "public"."tblQuote"("ID")
ON DELETE NO ACTION
ON UPDATE NO ACTION
NOT DEFERRABLE
) INHERITS ("public"."tblMovementDetails")
WITHOUT OIDS;
CREATE TRIGGER "TRIG_tblOrderDetails_ChangeDate" BEFORE INSERT OR UPDATE
ON "public"."tblOrderDetails" FOR EACH ROW
EXECUTE PROCEDURE "public"."func_set_changedate"();
CREATE RULE "get_pkey_on_insert" AS ON INSERT TO "public"."tblOrderDetails"
DO (SELECT currval('"tblOrderDetails_ID_seq"'::text) AS "ID",
new."PlanningDetailID";);
CREATE TABLE "public"."tblSendingDetails" (
"HandlingCode" VARCHAR(10) DEFAULT ''::character varying,
CONSTRAINT "PK_tblSendingDetails" PRIMARY KEY("ID"),
CONSTRAINT "CH_tblSendingDetails_Number" CHECK ("Number" >= 0),
CONSTRAINT "FK_tblSendingDetails_tblMovement" FOREIGN KEY ("MovementID")
REFERENCES "public"."tblSending"("ID")
ON DELETE NO ACTION
ON UPDATE NO ACTION
NOT DEFERRABLE,
CONSTRAINT "FK_tblSendingDetails_tblPlanningDetails" FOREIGN KEY
("PlanningDetailID")
REFERENCES "public"."tblPlanningDetails"("ID")
ON DELETE NO ACTION
ON UPDATE NO ACTION
NOT DEFERRABLE
) INHERITS ("public"."tblMovementDetails")
WITHOUT OIDS;
CREATE INDEX "IDX_tblSendingDetails_PlanningDetailID" ON
"public"."tblSendingDetails"
USING btree ("PlanningDetailID");
CREATE TRIGGER "TRIG_tblSendingDetails_Archive" AFTER INSERT OR UPDATE OR DELETE
ON "public"."tblSendingDetails" FOR EACH ROW
EXECUTE PROCEDURE "public"."func_archive_sending"();
CREATE TRIGGER "TRIG_tblSendingDetails_ChangeDate" BEFORE INSERT OR UPDATE
ON "public"."tblSendingDetails" FOR EACH ROW
EXECUTE PROCEDURE "public"."func_set_changedate"();
CREATE RULE "get_pkey_on_insert" AS ON INSERT TO "public"."tblSendingDetails"
DO (SELECT currval('"tblSendingDetails_ID_seq"'::text) AS "ID",
new."PlanningDetailID";);
>>> Greg Toombs <[EMAIL PROTECTED]> 2007-03-08 19:01 >>>
Hello.
I'm trying to figure out how to nicely implement a C++ class-like system with
PostgreSQL. Consider the following:
Tables Fruit, Apple, Orange
I want to design the foreign key scheme such that there are relations between
fruit and apple, and fruit and orange, that imply that apple is a fruit, and
orange is a fruit.
I don't
Re: [SQL] Creating views
Kashmira Patel (kupatel) wrote: Hi all, Is it possible to create views that take parameters? Basically, I have to create some very complex historical reports, and as of now I am using temporary tables to store intermediate query results. I calculate values based on the content of these temporary tables and use them in the final result. I would like to eliminate the need for temporary tables and use views instead, as the data copy between the temp tables is taking quite some time. You can write a function (in SQL even) that would basically be a parameterised view. However, views don't "store" anything, so whether you see an improvement in speed will depend on how long it takes to do your calculations. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[SQL] Table inherit & foreign key problem
Hi all, i have problems with tables an foreign keys. i created the following tables: (i did not copy all the command, only the important (in my point of view) CREATE TABLE element ( id bigint NOT NULL, site_id bigint, type_id bigint NOT NULL, name character varying(512), description text, active boolean NOT NULL ); CREATE TABLE crmuser ( username character varying(32) NOT NULL, firstname character varying(64), lastname character varying(64) ) INHERITS (element); CREATE TABLE "comment" ( user_id bigint, created timestamp without time zone, content text, element_id bigint NOT NULL ) INHERITS (element); CREATE INDEX idx_comment_id ON "comment" USING btree (id); CREATE INDEX idx_comment_user_id ON "comment" USING btree (user_id); CREATE INDEX idx_comment_element_id ON "comment" USING btree (element_id); ALTER TABLE ONLY element ADD CONSTRAINT element_pkey PRIMARY KEY (id); ALTER TABLE ONLY crmuser ADD CONSTRAINT crmuser_pkey PRIMARY KEY (id); ALTER TABLE ONLY "comment" ADD CONSTRAINT comment_pkey PRIMARY KEY (id); ALTER TABLE ONLY "comment" ADD CONSTRAINT "$3" FOREIGN KEY (user_id) REFERENCES crmuser(id); ALTER TABLE ONLY "comment" ADD CONSTRAINT "$4" FOREIGN KEY (element_id) REFERENCES element(id); when i try to insert an comment with element_id = id of an user which is visible when i call "select * from element", i get an foreign key violation error: element_id=XY is not available in element. i would like to reference to the element table, because i have some tables which inherit from element and i would like to add comments to these elements. is this impossible or did i do a silly mistake? i did not find a solution unsing google. - Moritz -- Wissen ist das einzige Gut, das sich vermehrt, wenn man es teilt. -- http://www.lagerkochbuch.ch ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] SHA-1 vs MD5
Thank you so much for your information. I installed the pgCrypto. Now I have
more than 40 functions (i believe all are from pgcrypto) but when I try to
run your query:
select encode(digest('blahblah', 'sha256'), 'hex');
I got the error:
ERROR: Cannot use "sha256": No such hash algorithm
SQL state: 22023
Any suggestion
Ezequias
2007/3/8, Chad Wagner <[EMAIL PROTECTED]>:
On 3/8/07, Ezequias Rodrigues da Rocha <[EMAIL PROTECTED]> wrote:
>
> I really don't have the pgcrypto. It could be a nice alternative. Could
> you tell me the steps to install it ?
This should help you out:
http://www.postgresql.org/docs/8.2/static/external-extensions.html
http://developer.postgresql.org/cvsweb.cgi/pgsql/contrib/README?rev=1.91;content-type=text%2Fplain
I am very concerned about security in my application becouse we are going
> to moviment a large ammount of information and money. As much i take care of
> it as good.
SHA1 and MD5 are hashing algorithms, they are typically used for passwords
and in conjunction with public key encryption or over-the-wire encryption to
sign the message. If you are really concerned about security, you may want
to hire an experienced person in the security engineering field. Especially
if you are talking about financial information.
I know some problem of MD5 and know it is very good too. If someone could
> tell me where MD5 is used I could be more relaxed.
The impression I get is that SHA-256, SHA-384, or SHA-512 are the
preferred hashing algorithms, but I really don't keep up on it. Many many
password systems use MD5, I think it is reasonably safe.
--
Ezequias Rodrigues da Rocha
http://ezequiasrocha.blogspot.com/
use Mozilla Firefox:http://br.mozdev.org/firefox/
Re: [SQL] Table inherit & foreign key problem
Moritz Kobel wrote: i would like to reference to the element table, because i have some tables which inherit from element and i would like to add comments to these elements. is this impossible or did i do a silly mistake? i did not find a solution unsing google. Foreign-key limitations with inheritance I'm afraid. See the manuals for details: http://www.postgresql.org/docs/8.2/static/ddl-inherit.html 5.8.1 Caveats ... A serious limitation of the inheritance feature is that indexes (including unique constraints) and foreign key constraints only apply to single tables, not to their inheritance children. This is true on both the referencing and referenced sides of a foreign key constraint. Thus, in the terms of the above example: ... -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] SHA-1 vs MD5
On 3/9/07, Ezequias Rodrigues da Rocha <[EMAIL PROTECTED]> wrote:
Thank you so much for your information. I installed the pgCrypto. Now I
have more than 40 functions (i believe all are from pgcrypto) but when I try
to run your query:
select encode(digest('blahblah', 'sha256'), 'hex');
I got the error:
ERROR: Cannot use "sha256": No such hash algorithm
SQL state: 22023
It may not be available in your version of the database/pgcrypto module, my
test was performed on 8.2.3.
[SQL] CREATE TABLE
Hello >From my asp page i create a table TableName = "CON01" strSQL = "CREATE TABLE " & TableName & " ( ID text, N_GEN serial not null);" But the problem i have is that when i go and open my database in pgadmin the table name and coloumn name is written in lowercase :( How do i ask it to write them in uppercase (like in my strSQL) ? Thanks Shavonne Wijesinghe http://www.studioform.it Le informazioni contenute nella presente comunicazione e i relativi allegati possono essere riservate e sono, comunque destinate esclusivamente alle persone o alla Società sopra indicati. La diffusione, distribuzione e/o copiature del documento trasmesso da parte di qualsiasi soggetto diverso dal destinatario è proibita, sia ai sensi dell'art. 616 c.p., che ai sensi del D. Lgs. n. 196/2003. Se avete ricevuto questo messaggio per errore, Vi preghiamo di distruggerlo e di informarci immediatamente per telefono allo 0039362595044 o inviando un messaggio all'indirizzo e-mail [EMAIL PROTECTED] The informations in this communication is confidential and may also be legally privileged. It is intended for the addressee only. Access to this e-mail by anyone else is unauthorized. It is not to be relied upon by any person other than the addressee, except with our prior written approval. If you received this message please send an e-mail to the se
Re: [SQL] CREATE TABLE
On 3/9/07, Shavonne Marietta Wijesinghe <[EMAIL PROTECTED]> wrote: Hello From my asp page i create a table TableName = "CON01" strSQL = "CREATE TABLE " & TableName & " ( ID text, N_GEN serial not null);" But the problem i have is that when i go and open my database in pgadmin the table name and coloumn name is written in lowercase :( How do i ask it to write them in uppercase (like in my strSQL) ? Thanks Shavonne Wijesinghe http://www.studioform.it Quote them. See: http://www.postgresql.org/docs/8.2/static/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS The informations in this communication is confidential and may also be legally privileged. It is intended for the addressee only. Access to this e-mail by anyone else is unauthorized. It is not to be relied upon by any person other than the addressee, except with our prior written approval. If you received this message please send an e-mail to the se Please, lose the legaleze filler. If not possible for you, a workaround is to use a public email service to send emails to the list. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Fw: [SQL] CREATE TABLE
The problem i'm having is i can't pass them in the strSQL string. coz when i write " i close the string :\ Shavonne Wijesinghe http://www.studioform.it Le informazioni contenute nella presente comunicazione e i relativi allegati possono essere riservate e sono, comunque destinate esclusivamente alle persone o alla Società sopra indicati. La diffusione, distribuzione e/o copiature del documento trasmesso da parte di qualsiasi soggetto diverso dal destinatario è proibita, sia ai sensi dell'art. 616 c.p., che ai sensi del D. Lgs. n. 196/2003. Se avete ricevuto questo messaggio per errore, Vi preghiamo di distruggerlo e di informarci immediatamente per telefono allo 0039362595044 o inviando un messaggio all'indirizzo e-mail [EMAIL PROTECTED] The informations in this communication is confidential and may also be legally privileged. It is intended for the addressee only. Access to this e-mail by anyone else is unauthorized. It is not to be relied upon by any person other than the addressee, except with our prior written approval. If you received this message please send an e-mail to the se - Original Message - From: "Rodrigo De León" <[EMAIL PROTECTED]> To: Cc: "Shavonne Marietta Wijesinghe" <[EMAIL PROTECTED]> Sent: Friday, March 09, 2007 3:09 PM Subject: Re: [SQL] CREATE TABLE On 3/9/07, Shavonne Marietta Wijesinghe <[EMAIL PROTECTED]> wrote: Hello From my asp page i create a table TableName = "CON01" strSQL = "CREATE TABLE " & TableName & " ( ID text, N_GEN serial not null);" But the problem i have is that when i go and open my database in pgadmin the table name and coloumn name is written in lowercase :( How do i ask it to write them in uppercase (like in my strSQL) ? Thanks Shavonne Wijesinghe http://www.studioform.it Quote them. See: http://www.postgresql.org/docs/8.2/static/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS The informations in this communication is confidential and may also be legally privileged. It is intended for the addressee only. Access to this e-mail by anyone else is unauthorized. It is not to be relied upon by any person other than the addressee, except with our prior written approval. If you received this message please send an e-mail to the se Please, lose the legaleze filler. If not possible for you, a workaround is to use a public email service to send emails to the list. ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Fw: [SQL] CREATE TABLE
false alarm.. sorry i just forgot about the """ Thanks Shavonne Wijesinghe http://www.studioform.it Le informazioni contenute nella presente comunicazione e i relativi allegati possono essere riservate e sono, comunque destinate esclusivamente alle persone o alla Società sopra indicati. La diffusione, distribuzione e/o copiature del documento trasmesso da parte di qualsiasi soggetto diverso dal destinatario è proibita, sia ai sensi dell'art. 616 c.p., che ai sensi del D. Lgs. n. 196/2003. Se avete ricevuto questo messaggio per errore, Vi preghiamo di distruggerlo e di informarci immediatamente per telefono allo 0039362595044 o inviando un messaggio all'indirizzo e-mail [EMAIL PROTECTED] The informations in this communication is confidential and may also be legally privileged. It is intended for the addressee only. Access to this e-mail by anyone else is unauthorized. It is not to be relied upon by any person other than the addressee, except with our prior written approval. If you received this message please send an e-mail to the se - Original Message - From: "Rodrigo De León" <[EMAIL PROTECTED]> To: Cc: "Shavonne Marietta Wijesinghe" <[EMAIL PROTECTED]> Sent: Friday, March 09, 2007 3:09 PM Subject: Re: [SQL] CREATE TABLE On 3/9/07, Shavonne Marietta Wijesinghe <[EMAIL PROTECTED]> wrote: Hello From my asp page i create a table TableName = "CON01" strSQL = "CREATE TABLE " & TableName & " ( ID text, N_GEN serial not null);" But the problem i have is that when i go and open my database in pgadmin the table name and coloumn name is written in lowercase :( How do i ask it to write them in uppercase (like in my strSQL) ? Thanks Shavonne Wijesinghe http://www.studioform.it Quote them. See: http://www.postgresql.org/docs/8.2/static/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS The informations in this communication is confidential and may also be legally privileged. It is intended for the addressee only. Access to this e-mail by anyone else is unauthorized. It is not to be relied upon by any person other than the addressee, except with our prior written approval. If you received this message please send an e-mail to the se Please, lose the legaleze filler. If not possible for you, a workaround is to use a public email service to send emails to the list. ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] CREATE TABLE
Shavonne Marietta Wijesinghe wrote: > Hello > > From my asp page i create a table > > TableName = "CON01" > strSQL = "CREATE TABLE " & TableName & " ( ID text, N_GEN serial not > null);" > > But the problem i have is that when i go and open my database in pgadmin the > table name and coloumn name is written in lowercase :( > > How do i ask it to write them in uppercase (like in my strSQL) ? > You should quote the name. Please read more about it here - http://www.postgresql.org/docs/current/static/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS Excerpt: "Quoting an identifier also makes it case-sensitive, whereas unquoted names are always folded to lower case." -- Milen A. Radev ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] CREATE TABLE
On Fri, Mar 09, 2007 at 02:56:06PM +0100, Shavonne Marietta Wijesinghe wrote: > > But the problem i have is that when i go and open my database in > pgadmin the table name and coloumn name is written in lowercase :( Unquoted identifiers in PostgreSQL are folded to lower case. This is contrary to the SQL spec, but it normally doesn't matter because the rule is applied consistently. Therefore, you have two possibilities: 1. _Never_ quote identifiers. If you do it this way, everything will always be folded to lower case, so your queries will always work correctly. 2. _Always_ quote identifiers. This way, you always get upper case, or mixed case, or lower case, or whatever. Indeed, you can actually have two tables named "mytable" and "MyTable" this way, if you were so inclined/completely mad. The important thing to remember is that you have to pick one style, and be absolutely certain to use it consistently. If you mix the styles, you'll get surprises. A -- Andrew Sullivan | [EMAIL PROTECTED] The whole tendency of modern prose is away from concreteness. --George Orwell ---(end of broadcast)--- TIP 1: 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: Fw: [SQL] CREATE TABLE
On Fri, Mar 09, 2007 at 03:15:54PM +0100, Shavonne Marietta Wijesinghe wrote: > The problem i'm having is i can't pass them in the strSQL string. coz when > i write " i close the string :\ You likely need to escape the double-quotes. How you do it in your environment is a topic for the manuals for that environment. A -- Andrew Sullivan | [EMAIL PROTECTED] "The year's penultimate month" is not in truth a good way of saying November. --H.W. Fowler ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] CREATE TABLE
Because being consistent is easily overlooked I would advise not to quote the table names Instead of calling your table thisTableIsBig call it this_table_is_big >>> Andrew Sullivan <[EMAIL PROTECTED]> 2007-03-09 15:21 >>> On Fri, Mar 09, 2007 at 02:56:06PM +0100, Shavonne Marietta Wijesinghe wrote: > > But the problem i have is that when i go and open my database in > pgadmin the table name and coloumn name is written in lowercase :( Unquoted identifiers in PostgreSQL are folded to lower case. This is contrary to the SQL spec, but it normally doesn't matter because the rule is applied consistently. Therefore, you have two possibilities: 1._Never_ quote identifiers. If you do it this way, everything will always be folded to lower case, so your queries will always work correctly. 2._Always_ quote identifiers. This way, you always get upper case, or mixed case, or lower case, or whatever. Indeed, you can actually have two tables named "mytable" and "MyTable" this way, if you were so inclined/completely mad. The important thing to remember is that you have to pick one style, and be absolutely certain to use it consistently. If you mix the styles, you'll get surprises. A -- Andrew Sullivan | [EMAIL PROTECTED] The whole tendency of modern prose is away from concreteness. --George Orwell ---(end of broadcast)--- TIP 1: 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] index not being used. Why?
Hi all. I have this 2 relations
gse=# \d pages
Table "public.pages"
Column| Type| Modifiers
-+---+
id | integer | not null default
nextval('pages_id_seq'::regclass)
fullpath| character varying |
last_modified_stamp | bigint|
title | character varying |
Indexes:
"pages_pkey" PRIMARY KEY, btree (id)
"pages_fullpath_idx" UNIQUE, btree (fullpath)
"pages_id_idx" btree (id)
gse=# \d words
Table "public.words"
Column | Type| Modifiers
---+---+---
page_id | integer |
word | character varying |
word_position | integer |
Indexes:
"words_idx" btree (word)
"words_page_id_idx" btree (page_id)
"words_page_id_word_position_id" btree (page_id, word_position)
"words_upper_idx" btree (upper(word::text) varchar_pattern_ops)
Now, when i execute
gse=# explain select * from words, pages where words.page_id = pages.id and
upper(word) like 'TEST%';
QUERY PLAN
--
Hash Join (cost=18.29..916.33 rows=698 width=72)
Hash Cond: ("outer".page_id = "inner".id)
-> Bitmap Heap Scan on words (cost=8.19..885.64 rows=698 width=17)
Filter: (upper((word)::text) ~~ 'TEST%'::text)
-> Bitmap Index Scan on words_upper_idx (cost=0.00..8.19 rows=698
width=0)
Index Cond: ((upper((word)::text) ~>=~ 'TEST'::character varying)
AND (upper((word)::text) ~<~'TESU'::character varying))
-> Hash (cost=9.08..9.08 rows=408 width=55)
-> Seq Scan on pages (cost=0.00..9.08 rows=408 width=55)
(8 rows)
Watch the last row of the explain command. It makes a sequential scan on the pages table,
like it is not using the index on the "id" field.
The result itself is OK, but i will populate the tables so i think that later
that sequential scan would be a problem.
I have not idea why this is happening, hope you guys could give me a clue or
make me understand the situation.
Im using postgres 8.1.3
Thanks!
Gerardo
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [SQL] View Vs. Table
On Tue, Mar 06, 2007 at 09:32:19 -0500, Radhika Sambamurti <[EMAIL PROTECTED]> wrote: > > From the application's perspective the View is much better as data is > being stored in one place, reducing errors and also storage. > But from the db point of view, is there a bigger performace hit when I > query a view Vs a table or is there no difference. In postgres, views are essentially macros and there shouldn't be a big performance hit for using them over issuing the equivalent query. It sounds like what you might be asking is comparing views to storing data in denormallized tables (esentially materialized views). You generally don't want to go there unless you need to, to get acceptible performance. (And note that denormallizing data doesn't always get you better performance.) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] [Re: PRIMARY KEY]
On Wed, Mar 07, 2007 at 23:20:12 +1100, Phillip Smith <[EMAIL PROTECTED]> wrote: > If you actually need to know the value of N_GEN in your ASP application, > you will need to query the database first and select the NEXTVAL from > the sequence that the "serial" data type will create, then use that > returned value in your insert - ie, DON'T exclude it from the insert, > otherwise it will default to NEXTVAL again and return a different value. In 8.2, you can also use the RETURNING clause to get those values. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] index not being used. Why?
Gerardo Herzig wrote: -> Seq Scan on pages (cost=0.00..9.08 rows=408 width=55) Watch the last row of the explain command. It makes a sequential scan on the pages table, like it is not using the index on the "id" field. You only have 408 rows in the table - it's probably not worth the trouble of using an index and *then* fetching the rows. Especially since it's going to match most of the pages anyway. Try adding a few thousand rows, analyse and see if it decides to use the index then. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] index not being used. Why?
On Fri, Mar 09, 2007 at 12:01:30PM -0300, Gerardo Herzig wrote: > Hi all. I have this 2 relations How big are they? > -> Hash (cost=9.08..9.08 rows=408 width=55) > -> Seq Scan on pages (cost=0.00..9.08 rows=408 width=55) The planner thinks it will get 408 rows. How big a percentage of the table is that? Also, what does EXPLAIN ANALYSE say about this? A -- Andrew Sullivan | [EMAIL PROTECTED] If they don't do anything, we don't need their acronym. --Josh Hamilton, on the US FEMA ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] index not being used. Why?
Gerardo Herzig <[EMAIL PROTECTED]> writes: > Watch the last row of the explain command. It makes a sequential scan on the > pages table, like it is not using the index on the "id" field. > The result itself is OK, but i will populate the tables so i think that later > that sequential scan would be a problem. Why do you think that the plans won't change when the tables get bigger? regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] A form of inheritance with PostgreSQL
Hi Greg, While not in a C++ framework, you might find that it's not too hard to implement something similar in your system - It's called "Single Table Inheritance." References to the Ruby on Rails implementation here: http://wiki.rubyonrails.org/rails/pages/SingleTableInheritance It's based on Martin Fowler's Patterns of Enterprise Architecture book - please find references to his original patterns here: http://www.martinfowler.com/eaaCatalog/singleTableInheritance.html The key, I believe, is simply adding a "type" and a "parent_id" to the "class" table, so you can model all your types and their hierarchical relations. Fowler's diagram is pretty clear. I think then you would store the data in another table (or tables) and link into this inheritance structure to establish ancestry for any piece of data (some people try to store the data in this table too, but I think that's a mistake personally). If I understand what you're trying to do, you can use this design pattern in your application language to implement an inheritance scheme without any special database features (i.e. in a SQL-standard manner). I hope this is helpful, Steve At 12:28 AM 3/9/2007, [EMAIL PROTECTED] wrote: Date: Thu, 08 Mar 2007 13:01:51 -0500 From: Greg Toombs <[EMAIL PROTECTED]> To: pgsql-sql@postgresql.org Subject: A form of inheritance with PostgreSQL Message-ID: <[EMAIL PROTECTED]> Hello. I'm trying to figure out how to nicely implement a C++ class-like system with PostgreSQL. Consider the following: Tables Fruit, Apple, Orange I want to design the foreign key scheme such that there are relations between fruit and apple, and fruit and orange, that imply that apple is a fruit, and orange is a fruit. I don't want to eliminate the existence of Apple and Orange tables, because there will be columns specific to both Apple and Orange; if I include these columns in Fruit, then if Fruit is an Orange, the Apple columns will be needlessly present in Apple rows. The different ways of implementing this scheme that I've thought of (some uglier than others): - Have Fruit contain foreign keys to both Apple and Orange, and write a check constraint in Fruit specifying that exactly one of (Apple FK, Orange FK) needs to be non-null. The disadvantage of this method is that it isn't exactly loosely coupled. For every other fruit type table I implemented I'd have to go back and add a foreign key in Fruit. - Have a foreign key in Apple to Fruit, and in Orange to Fruit; then somehow create a constraint that imposes uniqueness on the union of foreign keys in both Apple and Orange. To figure out what type of fruit a Fruit row is, run a query for foreign keys in Orange and Apple matching the primary key of Fruit. You'd also want to somehow create a constraint that the result of this query should always return exactly one row (perhaps with a trigger?) Any advice will be appreciated! As I'm relatively new to Postgre, I might need some help with the actual implementation as well. Thank you. - Greg ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] index not being used. Why?
Someday i will ask some question that will makes you think a little more. You'll see. I will populate the tables and redo the explain. Thanks dudes!! Gerardo Gerardo Herzig <[EMAIL PROTECTED]> writes: Watch the last row of the explain command. It makes a sequential scan on the pages table, like it is not using the index on the "id" field. The result itself is OK, but i will populate the tables so i think that later that sequential scan would be a problem. Why do you think that the plans won't change when the tables get bigger? regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
[SQL] Statistics
Hi list, Does someone have statistcs from PostgreSQL ? Numbers from the list, performance statistics. I must argue with another person the idea of do not put Oracle in our organization. We are quite well with postgresql and I have no plans to change my plataform. Regards -- Ezequias Rodrigues da Rocha http://ezequiasrocha.blogspot.com/ use Mozilla Firefox:http://br.mozdev.org/firefox/
Re: [SQL] SHA-1 vs MD5
You are correct. My pg (8.1.3)
Now what I do to remove it ?
Just delete the functions ?
Ezequias
2007/3/9, Chad Wagner <[EMAIL PROTECTED]>:
On 3/9/07, Ezequias Rodrigues da Rocha <[EMAIL PROTECTED]> wrote:
>
> Thank you so much for your information. I installed the pgCrypto. Now I
> have more than 40 functions (i believe all are from pgcrypto) but when I try
> to run your query:
>
> select encode(digest('blahblah', 'sha256'), 'hex');
>
> I got the error:
>
> ERROR: Cannot use "sha256": No such hash algorithm
> SQL state: 22023
>
It may not be available in your version of the database/pgcrypto module,
my test was performed on 8.2.3.
--
Ezequias Rodrigues da Rocha
http://ezequiasrocha.blogspot.com/
use Mozilla Firefox:http://br.mozdev.org/firefox/
Re: [SQL] Statistics
On Fri, 2007-03-09 at 14:22 -0300, Ezequias Rodrigues da Rocha wrote: > Does someone have statistcs from PostgreSQL ? Numbers from the list, > performance statistics. I must argue with another person the idea of > do not put Oracle in our organization. > > We are quite well with postgresql and I have no plans to change my > plataform. Allow me to quote from my response to your previous message back in November: On Fri, 2006-11-24 at 11:07 -0200, Ezequias Rodrigues da Rocha wrote: > Does anybody have numbers of PostgreSQL in action ? > > Numbers like the biggest insert in mileseconds, the larger database etc ? First, you may want to post this in the GENERAL or in the PERFORMANCE lists since this isn't really about SQL. Second, you may want to look at the case studies page: http://www.postgresql.org/about/casestudies/. Third, the companies like EnterpriseDB and Pervasive may have some of what you're looking for since they have to measure themselves against the competition. And remember to take any numbers with a large grain of salt, YMMV, etc. Joe ---(end of broadcast)--- TIP 1: 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] SHA-1 vs MD5
On 3/9/07, Ezequias Rodrigues da Rocha <[EMAIL PROTECTED]> wrote: You are correct. My pg (8.1.3) Now what I do to remove it ? Just delete the functions ? There is usually an uninstall_pgcrypto.sql script you can run against the database. But 8.1 probably supports at least sha1, or you can consider upgrading to 8.2.
Re: [SQL] SHA-1 vs MD5
You are correct SH1 is supported. So I get the output size from 128 to 160 ok ? I saw at wikipedia. What does "With flaws" colisions means ? Does it means some ? in MD5 the put only "yes" Regards Ezequias 2007/3/9, Chad Wagner <[EMAIL PROTECTED]>: On 3/9/07, Ezequias Rodrigues da Rocha <[EMAIL PROTECTED]> wrote: > > You are correct. My pg (8.1.3) > > Now what I do to remove it ? > > Just delete the functions ? > There is usually an uninstall_pgcrypto.sql script you can run against the database. But 8.1 probably supports at least sha1, or you can consider upgrading to 8.2. -- Ezequias Rodrigues da Rocha http://ezequiasrocha.blogspot.com/ use Mozilla Firefox:http://br.mozdev.org/firefox/
[SQL] PostgreSQL to Oracle
Hi list, Is it a simple action to convert a database from PostgreSQL to Oracle ? I mean a simple database with 33 tables 8 functions 31 sequencies 2 triggers 1 type 3 views Has someone any idea ? -- Ezequias Rodrigues da Rocha http://ezequiasrocha.blogspot.com/ use Mozilla Firefox:http://br.mozdev.org/firefox/
Re: [SQL] Statistics
At 12:22 PM 3/9/07, Ezequias Rodrigues da Rocha wrote: Does someone have statistcs from PostgreSQL ? Numbers from the list, performance statistics. I must argue with another person the idea of do not put Oracle in our organization. Performance should not be the *only* consideration when comparing products. Any test to compare products done by someone else may not reflect the type of work you plan to do with the product. Different tests could easily favour different products. Your hardware and OS might favour a different result than my hardware and OS. Therefore, only your own comparisons (performance or otherwise) can produce meaningful results. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] PostgreSQL to Oracle
At 12:54 PM 3/9/07, Ezequias Rodrigues da Rocha wrote: Is it a simple action to convert a database from PostgreSQL to Oracle ? I mean a simple database with 33 tables 8 functions 31 sequencies 2 triggers 1 type 3 views Has someone any idea ? Depends on what's actually in the above objects; simple check each one to see if Oracle has an equivalent. Another consideration - do the SQL statements in your app adhere to standards? ---(end of broadcast)--- TIP 1: 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] Statistics
Thank you all. Those are quite important questions to considerate. About the late time of requisitions. I allways have replyes becouse 3:00 am in brazil does not means 3:00 in many other countries. I experiment it in other free communities too. My Brat Regards Ezequias 2007/3/9, Frank Bax <[EMAIL PROTECTED]>: At 12:22 PM 3/9/07, Ezequias Rodrigues da Rocha wrote: >Does someone have statistcs from PostgreSQL ? Numbers from the list, >performance statistics. I must argue with another person the idea of do >not put Oracle in our organization. Performance should not be the *only* consideration when comparing products. Any test to compare products done by someone else may not reflect the type of work you plan to do with the product. Different tests could easily favour different products. Your hardware and OS might favour a different result than my hardware and OS. Therefore, only your own comparisons (performance or otherwise) can produce meaningful results. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster -- Ezequias Rodrigues da Rocha http://ezequiasrocha.blogspot.com/ use Mozilla Firefox:http://br.mozdev.org/firefox/
Re: [SQL] PostgreSQL to Oracle
On 3/9/07, Ezequias Rodrigues da Rocha <[EMAIL PROTECTED]> wrote: Is it a simple action to convert a database from PostgreSQL to Oracle ? Yes, relatively. Has someone any idea ? There's a couple ways to do this, but I'd recommend first using pg_dump to export schema only. Your functions and triggers would need to be rewritten, but assuming they're in PL/pgSQL, it's a fairly trivial task to translate them into PL/SQL. As far as the views and sequences are concerned, pull them out of the pg_dump export and re-run them in TOAD, SQL*Plus, or your favorite tool. As far as the type goes, I'm not quite sure what you're doing with it or how it's used, but it should also be easy to migrate. To copy the data and table definitions, I'd use a database link (on the Oracle side) with hsodbc connecting to your PostgreSQL system via ODBC. Now that my advice is done with, could you explain why you need to move to Oracle from PostgreSQL? -- Jonah H. Harris, Software Architect | phone: 732.331.1324 EnterpriseDB Corporation| fax: 732.331.1301 33 Wood Ave S, 3rd Floor| [EMAIL PROTECTED] Iselin, New Jersey 08830| http://www.enterprisedb.com/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
[SQL] Running in single instance mode
Hi Everybody, We are using postgres 8.1.0. I want to do some maintenance work. Hence, I want to run postgres in single user mode so that external people won't be able to access the database. How can I run the postgres in single user mode?. Any idea? Regards skarthi _ Get a FREE Web site, company branded e-mail and more from Microsoft Office Live! http://clk.atdmt.com/MRT/go/mcrssaub0050001411mrt/direct/01/ ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] Running in single instance mode
On 3/9/07, Karthikeyan Sundaram <[EMAIL PROTECTED]> wrote: Hi Everybody, We are using postgres 8.1.0. I want to do some maintenance work. Hence, I want to run postgres in single user mode so that external people won't be able to access the database. How can I run the postgres in single user mode?. Any idea? Regards skarthi See: http://www.postgresql.org/docs/8.2/static/app-postgres.html ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] index not being used. Why?
On Fri, 2007-03-09 at 09:01, Gerardo Herzig wrote: > Hi all. I have this 2 relations > SNIP >Index Cond: ((upper((word)::text) ~>=~ 'TEST'::character > varying) AND (upper((word)::text) ~<~'TESU'::character varying)) >-> Hash (cost=9.08..9.08 rows=408 width=55) > -> Seq Scan on pages (cost=0.00..9.08 rows=408 width=55) > > (8 rows) > > > Watch the last row of the explain command. It makes a sequential scan > on the pages table, like it is not using the index on the "id" field. > > The result itself is OK, but i will populate the tables so i think > that later that sequential scan would be a problem. Welcome to the world of tomorrow! hehe. PostgreSQL uses a cost based planner. It decided that an index would cost more than a seq scan, so it chose the seq scan. As mentioned in other posts, you'll need to do an analyze. Also, look up things like vacuum / autovacuum as well. > Im using postgres 8.1.3 You need to upgrade to 8.1.8 or whatever the latest version is by the time this email gets to you :) 8.1.3 is about a year out of date. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] index not being used. Why?
Thanks all you guys. Indeed, populating the tables with 10.000 entrys make the things different, and now it uses all the indexes as i spect. It was just a matter of being pacient and loading more data to test it out and see. And, yes, i need to upgrade psql now. Actually the real server has an 8.2.0 engine. Thanks all you guys! Gerardo > On Fri, 2007-03-09 at 09:01, Gerardo Herzig wrote: >> Hi all. I have this 2 relations >> > > SNIP > >>Index Cond: ((upper((word)::text) ~>=~ 'TEST'::character >> varying) AND (upper((word)::text) ~<~'TESU'::character >> varying)) >>-> Hash (cost=9.08..9.08 rows=408 width=55) >> -> Seq Scan on pages (cost=0.00..9.08 rows=408 width=55) >> >> (8 rows) >> >> >> Watch the last row of the explain command. It makes a sequential scan >> on the pages table, like it is not using the index on the "id" field. >> >> The result itself is OK, but i will populate the tables so i think >> that later that sequential scan would be a problem. > > Welcome to the world of tomorrow! hehe. PostgreSQL uses a cost based > planner. It decided that an index would cost more than a seq scan, so > it chose the seq scan. As mentioned in other posts, you'll need to do > an analyze. Also, look up things like vacuum / autovacuum as well. > >> Im using postgres 8.1.3 > > You need to upgrade to 8.1.8 or whatever the latest version is by the > time this email gets to you :) 8.1.3 is about a year out of date. > > > -- Gerardo Herzig Direccion General de Organizacion y Sistemas Facultad de Medicina U.B.A. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] PostgreSQL to Oracle
Thank you Jonah, That isn't a decision taken but I will need to argue with the new team of my new company. I can't see why but I will see how the things occurs. Thank you again Ezequias 2007/3/9, Jonah H. Harris <[EMAIL PROTECTED]>: On 3/9/07, Ezequias Rodrigues da Rocha <[EMAIL PROTECTED]> wrote: > Is it a simple action to convert a database from PostgreSQL to Oracle ? Yes, relatively. > Has someone any idea ? There's a couple ways to do this, but I'd recommend first using pg_dump to export schema only. Your functions and triggers would need to be rewritten, but assuming they're in PL/pgSQL, it's a fairly trivial task to translate them into PL/SQL. As far as the views and sequences are concerned, pull them out of the pg_dump export and re-run them in TOAD, SQL*Plus, or your favorite tool. As far as the type goes, I'm not quite sure what you're doing with it or how it's used, but it should also be easy to migrate. To copy the data and table definitions, I'd use a database link (on the Oracle side) with hsodbc connecting to your PostgreSQL system via ODBC. Now that my advice is done with, could you explain why you need to move to Oracle from PostgreSQL? -- Jonah H. Harris, Software Architect | phone: 732.331.1324 EnterpriseDB Corporation| fax: 732.331.1301 33 Wood Ave S, 3rd Floor| [EMAIL PROTECTED] Iselin, New Jersey 08830| http://www.enterprisedb.com/ -- Ezequias Rodrigues da Rocha http://ezequiasrocha.blogspot.com/ use Mozilla Firefox:http://br.mozdev.org/firefox/
Re: [SQL] [Re: PRIMARY KEY]
Of course - my bad... That's the main reason I upgraded to 8.2! On Fri, 2007-03-09 at 09:03 -0600, Bruno Wolff III wrote: > On Wed, Mar 07, 2007 at 23:20:12 +1100, > Phillip Smith <[EMAIL PROTECTED]> wrote: > > If you actually need to know the value of N_GEN in your ASP application, > > you will need to query the database first and select the NEXTVAL from > > the sequence that the "serial" data type will create, then use that > > returned value in your insert - ie, DON'T exclude it from the insert, > > otherwise it will default to NEXTVAL again and return a different value. > > In 8.2, you can also use the RETURNING clause to get those values. ***Confidentiality and Privilege Notice*** The material contained in this message is privileged and confidential to the addressee. If you are not the addressee indicated in this message or responsible for delivery of the message to such person, you may not copy or deliver this message to anyone, and you should destroy it and kindly notify the sender by reply email. Information in this message that does not relate to the official business of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta. Weatherbeeta, its employees, contractors or associates shall not be liable for direct, indirect or consequential loss arising from transmission of this message or any attachments
