Re: [SQL] Create Primary Key?
Thus spake Stephan Szabo > We don't currently support the SQL syntax for adding > a PK to a table. However, if you have the columns > as NOT NULL already, adding a unique index to the > columns in question has the same general effect. Except for interfaces such as PyGreSQL that recognize the primary key and use it. > > Is there syntax to create a primary key after the > > table has been defined and populated? I think I could > > speed things up quite a bit by not having any indexes > > at all when I do my mass copies. Why not just use the copy function? That's what it's for. -- D'Arcy J.M. Cain| Democracy is three wolves http://www.druid.net/darcy/| and a sheep voting on +1 416 425 1212 (DoD#0082)(eNTP) | what's for dinner.
Re: [SQL] Create Primary Key?
On Wed, 30 Aug 2000, D'Arcy J.M. Cain wrote: > Thus spake Stephan Szabo > > We don't currently support the SQL syntax for adding > > a PK to a table. However, if you have the columns > > as NOT NULL already, adding a unique index to the > > columns in question has the same general effect. > > Except for interfaces such as PyGreSQL that recognize the primary key > and use it. True. You'd have to see what it was doing to determine the primary key and try to do the same things.
Re: [SQL] Viewing a function
Try "SELECT prosrc FROM pg_proc WHERE proname = 'funcname'", where funcname is the name of the function you want to see. - Original Message - From: "stuart" <[EMAIL PROTECTED]> To: "PG-SQL" <[EMAIL PROTECTED]> Sent: Tuesday, August 29, 2000 3:20 AM Subject: Fw: [SQL] Viewing a function > > -Original Message- > From: Stuart Foster <[EMAIL PROTECTED]> > To: PG-SQL <[EMAIL PROTECTED]> > Date: Wednesday, 30 August 2000 2:25 > Subject: [SQL] Viewing a function > > Helllo Stuart, > > Good question. > I have been fiddly with a function editor using zeos controls and I have > lots of little problems, so that I have given up for a while. > > I successfully parsed function information. (The following is from memory > and I dont have postgres with me right now). > The body of the function is in pg_lang.prosrc (I think) or something like > that > (I found out most stuff by looking in the c code for psql. I work in > delphi) > You can work out param numbers from another field and work out the field > types > (I confess Icheated here. You can get field type out of system tables but I > never actually worked out how). > > I could reconstruct a nice looking create function string using data in the > data base. > When I tried it execute things from windows things went wrong but it work in > psql. > I ended up just using it as a utility to create separated create function > files wich I then loaded server side. > > Anyway... then problems. > I dont think I ever had a successful update from client side. > Once I committed a change I often could not successfully drop and create a > function and vacuum would fail. > To recover I had to drop all functions and re-create them. > Using PERFORM seemed to cause particular problems. (Think that anything > that is called by perform must be compiled in before anything that calls it. > Order of creation seems to important. A full dump and create would fix > these sort of problems) > The cr/lf thing also gave problems although I wrote what I thought was a > client side fix for this. > > You can have my source in Delphi (such as it is) if you are interested. > > I think there must be flags or dependencies with function managemnt which I > dont understand. > > PS I have not had success editing function in pgaccess either (similar > problems) > > A simple way to view functions is pg_dump -c -s database to just dump schema > and drop statements. Cut and paste function definitions. > > The difficulty of function editing has retarded my putting tasks server-side > which belong there. > > >How can a view a function after it's created ? > >I've created a SQL function that I need to review and possibly change. What > >is the best way to go about this. > > > > > >TIA > > > > Another stuart >
Re: [SQL] Optimizing huge inserts/copy's
Hi, there, I tried different ways, include vaccum table , ensure index works, it still is as slow as ~100rows per minute. Stephan Szabo wrote: > On Tue, 29 Aug 2000, Jie Liang wrote: > > > Hi, there, > > > > 1. use copy ... from '.'; > > 2. write a PL/pgSQL function and pass multiple records as an array. > > > > However, if your table have a foreign key constraint, it cannot be speed > > up, > > > > I have same question as you, my table invloving 9-13 million rows, I > > don't > > know how can I add a foreign key them also? > > I haven't tried it on really large tables, but does it turn out faster to > use ALTER TABLE ADD CONSTRAINT to add the foreign key constraint after the > data is loaded and the indexes are created? -- Jie LIANG Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.ipinc.com
Re: [SQL] Optimizing huge inserts/copy's
I am experimenting with this too. If I have any indexes at all, the copy's get VERY SLOW as the table gets big. Delete ALL your indexes, do your copy's, and then create your indexes again. Good luck. --- Jie Liang <[EMAIL PROTECTED]> wrote: > Hi, there, > > I tried different ways, include vaccum table , > ensure index works, it > still is as slow as ~100rows per minute. > > > Stephan Szabo wrote: > > > On Tue, 29 Aug 2000, Jie Liang wrote: > > > > > Hi, there, > > > > > > 1. use copy ... from '.'; > > > 2. write a PL/pgSQL function and pass multiple > records as an array. > > > > > > However, if your table have a foreign key > constraint, it cannot be speed > > > up, > > > > > > I have same question as you, my table invloving > 9-13 million rows, I > > > don't > > > know how can I add a foreign key them also? > > > > I haven't tried it on really large tables, but > does it turn out faster to > > use ALTER TABLE ADD CONSTRAINT to add the foreign > key constraint after the > > data is loaded and the indexes are created? > > -- > Jie LIANG > > Internet Products Inc. > > 10350 Science Center Drive > Suite 100, San Diego, CA 92121 > Office:(858)320-4873 > > [EMAIL PROTECTED] > www.ipinc.com > > > __ Do You Yahoo!? Yahoo! Mail - Free email you can access from anywhere! http://mail.yahoo.com/
Re: [SQL] Optimizing huge inserts/copy's
Hi, I knew that if no constarint, it populate very quick, my question is: when two tables have been reloaded, then I want to add a foreign key constraint to it, say: tableA has primary key column (id) tableB has a column (id) references it, so I say: ALTER TABLE tableB ADD CONSTRAINT distfk FOREIGN KEY (id) REFERENCES tableA(id) ON DELETE CASCADE ; It just seems takes forever. Thanks anyway. Webb Sprague wrote: > I am experimenting with this too. If I have any > indexes at all, the copy's get VERY SLOW as the table > gets big. Delete ALL your indexes, do your copy's, > and then create your indexes again. > > Good luck. > --- Jie Liang <[EMAIL PROTECTED]> wrote: > > Hi, there, > > > > I tried different ways, include vaccum table , > > ensure index works, it > > still is as slow as ~100rows per minute. > > > > > > Stephan Szabo wrote: > > > > > On Tue, 29 Aug 2000, Jie Liang wrote: > > > > > > > Hi, there, > > > > > > > > 1. use copy ... from '.'; > > > > 2. write a PL/pgSQL function and pass multiple > > records as an array. > > > > > > > > However, if your table have a foreign key > > constraint, it cannot be speed > > > > up, > > > > > > > > I have same question as you, my table invloving > > 9-13 million rows, I > > > > don't > > > > know how can I add a foreign key them also? > > > > > > I haven't tried it on really large tables, but > > does it turn out faster to > > > use ALTER TABLE ADD CONSTRAINT to add the foreign > > key constraint after the > > > data is loaded and the indexes are created? > > > > -- > > Jie LIANG > > > > Internet Products Inc. > > > > 10350 Science Center Drive > > Suite 100, San Diego, CA 92121 > > Office:(858)320-4873 > > > > [EMAIL PROTECTED] > > www.ipinc.com > > > > > > > > __ > Do You Yahoo!? > Yahoo! Mail - Free email you can access from anywhere! > http://mail.yahoo.com/ -- Jie LIANG Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.ipinc.com
Re: [SQL] Optimizing huge inserts/copy's
On Wed, 30 Aug 2000, Jie Liang wrote: > Hi, > > I knew that if no constarint, it populate very quick, my question is: > when two tables have been > reloaded, then I want to add a foreign key constraint to it, say: > tableA has primary key column (id) > tableB has a column (id) references it, so I say: > ALTER TABLE tableB ADD CONSTRAINT distfk FOREIGN KEY (id) REFERENCES > tableA(id) ON DELETE CASCADE ; Yeah, the alter table has to check that the constraint is valid. There might be a faster way than the current "scan through table calling trigger function" mechanism, although doing most of them starts pulling logic for the obeying constraint into multiple places.
Re: [SQL] Optimizing huge inserts/copy's
Webb Sprague <[EMAIL PROTECTED]> writes: > I am experimenting with this too. If I have any > indexes at all, the copy's get VERY SLOW as the table > gets big. Delete ALL your indexes, do your copy's, > and then create your indexes again. Do you have a lot of equal index keys in the data you're inserting? I've recently been swatting some performance problems in the btree index code for the case of large numbers of equal keys. regards, tom lane
[SQL] 7.0.2 questions on encoding and compilation
Currently, I'm using the the 7.0.2 rpms from the postgresql.org on a RH6.2 install. I have a few questions on it and the use of the -E flag. 1 - can 7.0.2 be optimized for i686 architecture or is it only possible to compile for i386 architecture? 2 - Can createdb -E someencoding be used "straight out of the box" with Postgresql 7.0.2 or does support need to be compiled into the function? 3 - What are the list of arguments which can be taken with the command createdb -E ? Thanks! Max Pyziur BRAMA - Gateway Ukraine [EMAIL PROTECTED] http://www.brama.com/
[SQL] Re: [ADMIN] 7.0.2 questions on encoding and compilation
On Wed, 30 Aug 2000, Dana Hudes wrote: > I have compiled for i686 by hacking up my RPM compile environment. Exactly, what settings do you use? MP > On Wed, 30 Aug 2000 [EMAIL PROTECTED] wrote: > > > Currently, I'm using the the 7.0.2 rpms from the postgresql.org > > on a RH6.2 install. > > > > I have a few questions on it and the use of the -E flag. > > > > 1 - can 7.0.2 be optimized for i686 architecture or is > > it only possible to compile for i386 architecture? > > > > 2 - Can createdb -E someencoding be used "straight out of the box" > > with Postgresql 7.0.2 or does support need to be compiled into > > the function? > > > > 3 - What are the list of arguments which can be taken with the > > command createdb -E ? > > > > > > > > Thanks! > > > > > > > > > > Max Pyziur BRAMA - Gateway Ukraine > > [EMAIL PROTECTED] http://www.brama.com/ > > > >
[SQL] function
Hi all,
how can I write function which takes text from one field, replaces
some characters and puts it in other field? I have array with old and
new values.
For example:
old array = {'r', 'Z', 'o'}
new array = {'s', 'm', 't'}
old field value = 'Zorro'
new field value which must calculate this function = 'mtsst'
This function will be used in update trigger.
Any ideas?
Normunds
[SQL] Re: [GENERAL] function
Normunds wrote:
> Hi all,
>
> how can I write function which takes text from one field, replaces
> some characters and puts it in other field? I have array with old and
> new values.
>
> For example:
> old array = {'r', 'Z', 'o'}
> new array = {'s', 'm', 't'}
> old field value = 'Zorro'
> new field value which must calculate this function = 'mtsst'
>
> This function will be used in update trigger.
> Any ideas?
Tcl has a "map" functionality as subcommand of "string". I
think it appeared in version 8.1. To use the example below,
your Tcl installation must be 8.1 or newer when rebuilding
PostgreSQL configured with "--with-tcl" and enable PL/Tcl
with "createlang".
CREATE FUNCTION string_map(text, text) RETURNS text AS '
return [string map $2 $1]
' LANGUAGE 'pltcl';
CREATE
SELECT string_map('Zorro', 'r s Z m o t');
string_map
mtsst
(1 row)
As you see, the old and new arrays are both coded into the
second argument. It is a key, value pair list, with usual Tcl
quoting for lists. Can you convert your arrays to that?
Jan
--
#==#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#== [EMAIL PROTECTED] #
