Re: [SQL] script for unidirectional database update
From: "Markus Wagner" <[EMAIL PROTECTED]> > I need to periodically transfer the content of one db into another. The > target db should be deleted before and there should be one ascii file > containing the data, because there's a firewall between the two db's and > file transfer ist the most simple thing to do. Does anyone have a script > to automate this? > Look into pg_dumpall - Richard Huxton
Re: [SQL] Archival of Live database to Historical database
From: "Stef Telford" <[EMAIL PROTECTED]> > Hello everyone, > I have hit on a limit in my knowledge and i am looking for > some guidance. Currently I have two seperate databases, one for > live data, the other for historical data. The only difference really > being that the historical data has a Serial in it so that the tables > can keep more than one 'version history'. > > What i would like to do, is after my insert transaction to the > live database, i would like the information also transferred to the > historical one. Now. I can do this via perl (and i have been doing it > this way) and using two database handles. This is rather clumsy and > I know there must be a 'better' or more 'elegant' solution. Not really (AFAIK) - this crops up fairly regularly but there's no way to do a cross-database query. You could use rules/triggers to set a "dirty" flag for each record that needs copying - but it sounds like you're already doing that. If you wanted things to be more "real-time" you could look at LISTEN/NOTIFY - Richard Huxton
[SQL] Hierarchical Queries
hi there, in the database i'm currently trying to implement i make heavy use of self joins and therefore i do often need to make hierarchical queries. oracle offers the connect by clause to do this. how can i do this with, postgresql? clemens
[SQL] Re: [HACKERS] How to modify type in table?
"Jaruwan Laongmal" wrote: >Would you like to inform me how to modify type in table? >For example , I define type as varchar(14) , but I want to modify to varcha= >r(120). How to do this. There is no facility to do this directly. (Allowing columns to change their type would possibly involve rewriting the entire table.) You can use pg_dump to dump either a database or a particular table. Then you can edit the CREATE TABLE command in the dump output and then create a new database or table from the dump. If the table in question is not referenced by any other objects, dump it (pg_dump -t table database >dump); edit the dump; delete or rename the old table; and finally restore the dump (psql -d database http://www.lfix.co.uk/oliver PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47 GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C "The Lord knoweth how to deliver the godly out of temptations, and to reserve the unjust unto the day of judgment to be punished;"II Peter 2:9
[SQL] binary operators
Hello, I am looking for the binary AND and OR ... SHIFT as well. Do they exist for postgresql ? Tanks, Fred
[SQL] Getting Results From Trigger
Hello everyone, I was hoping someone could help me with this... I'm running postgres 7.02 on redhat 6.2, apache 1.3.14 and mod_perl 1.24_01. I'm also using perl modules DBI 1.14 and DBD-Pg-0.95 to acces the Postgres database. Everytime my inventory file gets updated, I would like to have the quantity on hand returned to my perl script. I set up the following function to do this: CREATE FUNCTION inv_lookup () RETURNS OPAQUE AS ' BEGIN RAISE NOTICE ''%'', NEW.qtyonhand; RETURN new; end; 'language 'plpgsql'; Then I create the trigger like this... CREATE TRIGGER qty after inventory for each row execute procedure inv_lookup(); If I do any sort of updates from within psql I get the correct data which is the Qtyonhand field in this format: NOTICE: 15 However, in my perl program, I'm unsure as to how to get this information back. The Postgres Documentation says that the results from a Notice get sent back to the application, but I check the DBI->err, DBI->errstr, and DBI->state and they are empty. The result code for the actual SQL command I run is just 1 for success. Does anyone know how to get these results from within a perl scripts??? Thanks in advance for any help. Chris.
Re: [SQL] Archival of Live database to Historical database
Richard wrote: > > Hello everyone, > > I have hit on a limit in my knowledge and i am looking for > > some guidance. Currently I have two seperate databases, one for > > live data, the other for historical data. The only difference really > > being that the historical data has a Serial in it so that the tables > > can keep more than one 'version history'. > > > > What i would like to do, is after my insert transaction to the > > live database, i would like the information also transferred to the > > historical one. Now. I can do this via perl (and i have been doing > > this way) and using two database handles. This is rather clumsy and > > I know there must be a 'better' or more 'elegant' solution. > > Not really (AFAIK) - this crops up fairly regularly but there's no way > to do a cross-database query. > After going through the mailing list archive, i can see that yes, this is asked a lot and that no, there is no real solution to it at present. a shame to be sure. > You could use rules/triggers to set a "dirty" flag for each record > that needs copying - but it sounds like you're already doing that. > > If you wanted things to be more "real-time" you could look at > LISTEN/NOTIFY What i would ideally like to do, is have the live database have a trigger setup after an insert, so that the data will also be copied across using a function. However, if cross database functions or triggers are not possible, then i cant do this and will have to stick with the current scheme (two database handles). Its not pretty, but it works. which is the main thing. Can i ask the postgreSQL powers that be, how hard would it be to have the ability to reference different databases on the same machine ? I know it might make sense to have the two on seperate machines, but that would require hostname resolution and other silly things. All that is really needed is the ability to reference another database on the SAME machine. Of course, i can see this is a loaded gun. It would be very easy to do some very nasty things and more than a few race conditions spring to mind. Anyway, i look forward to getting screamed at for such a silly preposterous idea ;) regards, Steff
[SQL] Is this feature a bug?
Greets folks.
Put psql in html mode with \H
execute a query. e.g.:-
school=# \H
Output format is html.
school=# select timestamp('now');
timestamp
2001-01-31 11:24:21+13
(1 row)
Is the "( 1 row)" string really supposed to be there?
imho it should not be.
How can I turn it off?
--
Sincerely etc.,
NAME Christopher Sawtell
CELL PHONE 021 257 4451
ICQ UIN45863470
EMAIL csawtell @ xtra . co . nz
CNOTES ftp://ftp.funet.fi/pub/languages/C/tutorials/sawtell_C.tar.gz
-->> Please refrain from using HTML or WORD attachments in e-mails to me <<--
[SQL] DROP Column
Is DROP Column implemented in 7.x? Keith
[SQL] Automated scripting...
Hi, I'm building a script to create a relatively large database. At some point in the script I would like to be able to save values into variables so that I can use them to populate rows. Is this possible with SQL? My understanding is that it is not possible but thought I'd ask anyway. Thanks, Jamu. -- Jamu Kakar (Developer) Expressus Design Studio, Inc. [EMAIL PROTECTED]708-1641 Lonsdale Avenue V: (604) 903-6994 North Vancouver, BC, V7M 2J5
[SQL] Permissions for foreign keys
I'm using 7.0 and have noticed that I need to grant SELECT and UPDATE permissions on any referentially-related tables. Can/should I get around this? A somewhat contrived example: CREATE TABLE emp ( id integer PRIMARY KEY, salary integer ); CREATE TABLE proj ( id integer PRIMARY KEY, emp_id integer REFERENCES emp ); CREATE TABLE bill ( id integer PRIMARY KEY, proj_id integer REFERENCES proj ); INSERT INTO emp VALUES (1, 10); INSERT INTO proj VALUES (1, 1); INSERT INTO bill VALUES (1, 1); GRANT ALL ON proj TO someone; Connect as someone: => INSERT INTO proj VALUES (2, 1); ERROR: emp: Permission denied. => UPDATE proj SET id = 2; ERROR: bill: Permission denied. It appears that I need to grant: SELECT,UPDATE on emp to UPDATE or INSERT into proj. SELECT,UPDATE on bill to UPDATE proj. When I grant these permissions, the above statements succeed. If I don't want users to have UPDATE (or even SELECT) access on the other tables (bill and emp), but I want referential integrity, what can I do? -- Rick Delaney
Re: [SQL] binary operators
On Wed, 31 Jan 2001 04:12, Frederic Metoz wrote: > Hello, > > I am looking for the binary AND and OR ... SHIFT as well. > Do they exist for postgresql ? AND and OR . yes. SHIFT I don't think so. -- Sincerely etc., NAME Christopher Sawtell CELL PHONE 021 257 4451 ICQ UIN45863470 EMAIL csawtell @ xtra . co . nz CNOTES ftp://ftp.funet.fi/pub/languages/C/tutorials/sawtell_C.tar.gz -->> Please refrain from using HTML or WORD attachments in e-mails to me <<--
Re: [SQL] Automated scripting...
On Wed, 31 Jan 2001 12:54, [EMAIL PROTECTED] wrote: > Hi, > > I'm building a script to create a relatively large database. At some > point in the script I would like to be able to save values into > variables so that I can use them to populate rows. > > Is this possible with SQL? My understanding is that it is not > possible but thought I'd ask anyway. I know it seems a bit messy, but you could always use a temporary table to store the values. -- Sincerely etc., NAME Christopher Sawtell CELL PHONE 021 257 4451 ICQ UIN45863470 EMAIL csawtell @ xtra . co . nz CNOTES ftp://ftp.funet.fi/pub/languages/C/tutorials/sawtell_C.tar.gz -->> Please refrain from using HTML or WORD attachments in e-mails to me <<--
Re: [SQL] DROP Column
Keith Gray wrote: >Is DROP Column implemented in 7.x? No -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight http://www.lfix.co.uk/oliver PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47 GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C "The Lord knoweth how to deliver the godly out of temptations, and to reserve the unjust unto the day of judgment to be punished;"II Peter 2:9
Re: [SQL] Is this feature a bug?
Christopher Sawtell wrote: >Is the "( 1 row)" string really supposed to be there? >imho it should not be. >How can I turn it off? \pset tuples_only or \t or start psql with the -t option. -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight http://www.lfix.co.uk/oliver PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47 GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C "The Lord knoweth how to deliver the godly out of temptations, and to reserve the unjust unto the day of judgment to be punished;"II Peter 2:9
[SQL] Rownum/ row Id
Hi, Is there a provision to delete the duplicate records using row num/ row id as available in Oracle??? Thanx in advance padmajha
[SQL] Re: binary operators
Frederic Metoz wrote: > I am looking for the binary AND and OR ... SHIFT as well. > Do they exist for postgresql ? Depending on what you're doing...you might get away with using mathematical operators to accomplish the above... A right SHIFT would be dividing by 2. A left shift would be multiplying by 2. I don't know off the top of my head about AND and OR. Doesn't the manual cover this? -Ken
