Re: [SQL] sql programming
Make it text, then import it. You will have lots of tables. Send the check to :) --- Michael Wagner <[EMAIL PROTECTED]> wrote: > We need to export an SQL database to Excel. Is this > within your scope and what might your cost be? > > Please respond to Dan Beach > > [EMAIL PROTECTED] > __ Do You Yahoo!? Yahoo! Mail Free email you can access from anywhere! http://mail.yahoo.com/
Re: [SQL] Continuous inserts...
Hi All. Shouldn't Postgres block while vacuuming, and then continue inserting starting where it left off? Is the time lag too much? I am curious because I am going to build a similar app soon, basically parsing and inserting log file entries. W --- Stephan Szabo <[EMAIL PROTECTED]> wrote: > > On Thu, 17 Aug 2000, Joerg Hessdoerfer wrote: > > > Hi! > > > > I have an application, where I have to insert data > into a table at several > > rows per second, 24 hours a day, 365 days a year. > > > > After some period (a week, maybe a month) the data > will be reducted to some > > degree and deleted from the table. > > > > As far as I understood, I would have to use VACUUM > to really free the table > > from deleted rows - but VACUUM (esp. on a table > with several million rows) > > takes some time and prevents me from inserting new > data. > > > > Now, I thought I could just rename the table, > inserting into a temp table, and > > switch the tables back after VACUUMing. Ideally, > this should work unnoticed > > (and thus without prog. effort) on the client > (inserter) side. > > > > Question: would it work to use a transaction to > perform the rename? > > > > i.e.: continuous insert into table 'main' from > client. > > > > From somewhere else, execute: > > > > begin; > > alter table main rename to vac_main; > > create table main (...); > > end; > > > > would the inserter notice this? Read: would ALL > inserts AT ANY TIME succeed? > > Unfortunately -- no. Also, bad things can happen if > the transaction > errors since the rename happens immediately. > There's been talk on > -hackers about this subject in the past. > > However, you might be able to do something like > this, but > I'm not sure it'll work and it's rather wierd: > > Have three tables you work with, a and b and c > > Set up rule on a to change insert to insert on b. > Insert into a. > When you want to vacuum, change the rule to insert > to c. > Vacuum b > Change rule back > move rows from a and c into b > vacuum c > > [you will slowly lose space in a, but it should only > be an occasional row since you should only insert > into > a while you've deleted the insert rule to b, but > haven't yet added the insert rule to c -- not too > many > rows here] > > __ Do You Yahoo!? Send instant messages & get email alerts with Yahoo! Messenger. http://im.yahoo.com/
Re: [SQL] Best way to create DML/DDL log?
I think there is either a switch to the psql client or a "\" command in the session that allows you to log to a file. Even if you have to do some redirection, it seems a lot better than modifying postgres.c (though I am quite impressed that you would think about it...) --- "Ingram, Bryan" <[EMAIL PROTECTED]> wrote: > I would like to create a long of all successful > DML/DDL statements. > > Has anyone accomplished this before? No need to > reinvent the wheel ... > > If not ..I'm wondering what the best approach might > be. > > I have looked at the tcop/postgres.c source, > thinking I might start by > modifying some of the -dX routines for generating > debugging output. > > I've also tried working a little with the -E option > to the postgres backend, > (passing it through postmaster as -o -E) but can't > seem to redirect its > output. > > Finally, another option might be to create a > trigger, but I haven't really > explored this option. > > One of the main requirements is that any function > which returns a value > which is determined by system environment must be > written in the log as the > evaluated statement, and not written as the function > itself. e.g. now() > returns a text string of the current datetime which > is written to the log in > place of the literal character string 'now()' > > As far as modifying postgres.c, I'm fairly confident > I can create a log of > the query buffer, but I haven't yet enough exposure > to the source to find > where I should check to see whether or not a > statement was successful, or > from where I might be able to pull text data from > evaluated functions. > > Any commentary on this would be much appreciated. > > Thanks, > Bryan Ingram > > > > __ Do You Yahoo!? Yahoo! Mail Free email you can access from anywhere! http://mail.yahoo.com/
Re: [SQL] Re: Argument variables for select
I think you have to run "createlang pltcl db-foo" from the command line. As for your boss, here are three things I can think of off the top of my head: 1. All new products take some time to learn. If you can't use Postgres perfectly in a week or so, remember that you probably can't learn to be an Oracle or MS-SQL DBA in a week or so either. 2. Postgres is FREE. NO MONEY. 3. Postgres integrates very easily into a Unix environment. 4. Proprietary software is going the way of the Do-Do. Well that's four. At my job, we are building a database in Postgres to replace an MS-SQL thing. My version takes 12 hours to upload a months worth of data. You think that is bad--the SQL Server took 15 DAYS. Good luck, and sorry for the rant. W --- Andreas Tille <[EMAIL PROTECTED]> wrote: > On Mon, 28 Aug 2000, Yury Don wrote: > > > > Create Function VarSelect ( varchar, varchar ) > > >returns int > > >As ' > > > Declare num int ; > > > > > > Begin > > >Select Into num Count(*) From $1 Where $2 > ; > > >return num; > > > End ; > > >' language 'plpgsql' ; > > > > > > Could someone please explain who to type the > exact syntax so that > > > I can ship the table to select from as $1 and > the condition to select > > > what as $2? Or do I have to concatenate a > string with the whole > > > select statement and how to call this string? > In MS SQL server this > > > could be done with > > >Exec ( query ) > > > > > AFAIK it's impossible with plpgsql, but it's > possible in pltcl. > > It is really hard to believe that I'm in so very > deep trouble with > PostgreSQL. It seems to me that stored procedures > are far from beeing > as usefull as I learned them to know in MS SQL > server. Once I try > to follow one hint I'm standing in frot of the next > even harder problem. > > > web=# create function testfunc( ) > web-# returns int > web-# As ' > web'# spi_exec "SELECT count(*) AS $num FROM > testtable" > web'# > web'# return $num ; > web'# End; ' > web-# language 'pltcl' ; > ERROR: Unrecognized language specified in a CREATE > FUNCTION: 'pltcl'. Recognized languages are sql, C, > internal and the created procedural languages. > > > I have installed the pgtcl package of my Debian > distribution, so I guess > it should be available. Once more the question: Is > it really necessary to > use a further interpreter instead of sticking with > SQL commands to use > the original problem. > > I have to admit that my boss wonders why I'm > switching from a working > solution (MS SQL) to so much trouble :-(. I really > hope to convince him > to OpenSource but it's much harder than I expected. > > Kind regards > > Andreas. > __ Do You Yahoo!? Yahoo! Mail - Free email you can access from anywhere! http://mail.yahoo.com/
[SQL] Optimizing huge inserts/copy's
Hi all, Does anybody have any thoughts on optimizing a huge insert, involving something like 3 million records all at once? Should I drop my indices before doing the copy, and then create them after? I keep a tab-delimited file as a buffer, copy it, then do it again about 400 times. Each separate buffer is a few thousand records. We do this at night, so it's not the end of the world if it takes 8 hours, but I would be very grateful for some good ideas... Thanks W __ Do You Yahoo!? Yahoo! Mail - Free email you can access from anywhere! http://mail.yahoo.com/
[SQL] Create Primary Key?
Apropos of my last question: 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. Thanks, and my apologies if that is a totally stupid question. W __ Do You Yahoo!? Yahoo! Mail - Free email you can access from anywhere! http://mail.yahoo.com/
Re: [SQL] Create Primary Key + Massive Copy's?
This is my next approach--I got rid of all indexes and PK's, and then created an index with unique after everything is added. Wish me luck. W --- Stephan Szabo <[EMAIL PROTECTED]> wrote: > > 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. > > Stephan Szabo > [EMAIL PROTECTED] > > On Tue, 29 Aug 2000, Webb Sprague wrote: > > > Apropos of my last question: > > > > 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. > __ Do You Yahoo!? Yahoo! Mail - Free email you can access from anywhere! http://mail.yahoo.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/
[SQL] Convert from Seconds-Since-Epoch to Timestamp
Hi all, How do I convert from seconds (an integer) to timestamp? I am sure it is easy, but I can't find it in the docs, so far. Thanks Webb __ Do You Yahoo!? Send instant messages & get email alerts with Yahoo! Messenger. http://im.yahoo.com/
[SQL] Getting number of days
Hi All, Does anybody know how to convert an interval into a number of days. Say I have something like '2 years 4 months 4 ago', I need to get the total number of days, something like 800 (ish). Is there a built in function? Thanks W __ Do You Yahoo!? Send instant messages & get email alerts with Yahoo! Messenger. http://im.yahoo.com/
[SQL] Simultaneous Connection Problem
I have a table that is sequence_number (my PK), session_start_time, and session_stop_stime. I would like to query it to determine the max number of simultaneous sessions. Has anyone conquered a problem like this? It seems like it should be in a book somewhere, but I haven't found it yet. Thanks, -- Webb Sprague Programmer O1 Communications
Re: [SQL] \copy...
Finally--a question I think I can answer! You need to specify what delimiters you use in your .dat file; the default for COPY is tab, but you can change that to | with copy tablename from '/home/ed/import.dat' delimiters '|' Hope this works...(oops--I mean 'helps') --- Ed <[EMAIL PROTECTED]> wrote: > Hi, > > I have a file that look like this : > > firstname|lastname|[EMAIL PROTECTED] > firstname2|lastname2|[EMAIL PROTECTED] > > and a table foo like : > > firstname varchar(30), > lastname varchar(30), > emailvarchar(50) > > and I would like to do a : > > copy tablename from '/home/ed/import.dat'; > > But then, everything goes in the first field... what > does i'm doing > wrong?!?! > > Frédéric Boucher > [EMAIL PROTECTED] > Programmation, Support technique > Jetumele Communications inc. > > __ Do You Yahoo!? Send instant messages with Yahoo! Messenger. http://im.yahoo.com/
