Re(2): [SQL] Large Object dump ?
You can try the script I made for exporting all my Pg database.
Ideas were borrowed from pg_dumplo-0.0.5.
Make it sure that you have "Perl5 extension for PostgreSQL - Pg.pm"
installed.
sherwin
#!/usr/bin/perl -w
#
# Export large objects of all Pg database
# - Sherwin T. Daganato ([EMAIL PROTECTED])
# - October 8, 2000
#
use strict;
use Pg;
my $space = shift || die "Usage: $0 outputdir";
# make sure the directory doesn't end in '/'
$space =~ s/\/$//;
my $conn_all = Pg::connectdb("dbname=template1");
die $conn_all->errorMessage unless PGRES_CONNECTION_OK eq
$conn_all->status;
# find all database
my $sql = "SELECT datname FROM pg_database ORDER BY datname";
my $res_all = $conn_all->exec($sql);
die $conn_all->errorMessage unless PGRES_TUPLES_OK eq
$res_all->resultStatus;
my $counter = 0;
while (my ($database) = $res_all->fetchrow) {
my $conn_db = Pg::connectdb("dbname=$database");
die $conn_db->errorMessage unless PGRES_CONNECTION_OK eq
$conn_db->status;
# find any candidate tables with columns of type oid
$sql = qq/SELECT c.relname, a.attname
FROM pg_class c, pg_attribute a, pg_type t
WHERE a.attnum > 0
AND a.attrelid = c.oid
AND a.atttypid = t.oid
AND t.typname = 'oid'
AND c.relname NOT LIKE 'pg_%';
/;
my $res_db = $conn_db->exec($sql);
die $conn_db->errorMessage unless PGRES_TUPLES_OK eq
$res_db->resultStatus;
my $path;
local (*F);
while (my ($table, $fld) = $res_db->fetchrow) {
# find large object id
$sql = sprintf ("SELECT x.%s FROM %s x WHERE EXISTS (SELECT c.oid FROM
pg_class c WHERE c.relkind = 'l' AND x.%s = c.oid)",
$fld, $table, $fld);
# find large object id
#$sql = sprintf ("SELECT x.%s FROM %s x, pg_class c WHERE x.%s = c.oid
and c.relkind = 'l'",
#$fld, $table, $fld);
my $res_tbl = $conn_db->exec($sql);
die $conn_db->errorMessage unless PGRES_TUPLES_OK eq
$res_tbl->resultStatus;
my $tuples;
next unless (($tuples = $res_tbl->ntuples) > 0);
$counter += $tuples;
$path = sprintf ("%s/%s", $space, $database);
if ( -d $path) {
# creates file if it don't exist and appends to it
open(F,">>$path/lo_dump.index") || die "\n $0 Cannot open $! \n";
} else {
# create dir for database
mkdir($path, 0755) || die "\n Can't create $path: $! \n";
# opens file for writing. overwrite existing file
open(F, ">$path/lo_dump.index") || die "\n $0 Cannot open $! \n";
# temporarily turn off warnings
# there might be undef
local $^W = 0;
print F "#\n# This is the PostgreSQL large object dump index\n#\n";
printf F "#\tDate: %s\n", scalar(localtime);
printf F "#\tHost: %s\n", $conn_db->host;
printf F "#\tDatabase: %s\n", $conn_db->db;
printf F "#\tUser: %s\n", $conn_db->user;
printf F "#\n# oid\ttable\tattribut\tinfile\n#\n";
}
$path = sprintf ("%s/%s", $path, $table);
# create dir for table
mkdir($path, 0755) || die "\n Can't create $path: $! \n";
$path = sprintf ("%s/%s", $path, $fld);
# create dir for field
mkdir($path, 0755) || die "\n Can't create $path: $! \n";
printf "dump %s.%s (%d large obj)\n", $table, $fld, $tuples;
while (my ($lobjOid) = $res_tbl->fetchrow) {
$path = sprintf ("%s/%s/%s/%s/%s",
$space, $database, $table, $fld, $lobjOid);
my $res_lobj = $conn_db->exec("BEGIN");
die $conn_db->errorMessage unless PGRES_COMMAND_OK eq
$res_lobj->resultStatus;
# export large object
if ( 1 == $conn_db->lo_export($lobjOid, $path) ) {
printf F "%s\t%s\t%s\t%s/%s/%s/%s\n",
$lobjOid, $table, $fld, $database, $table, $fld, $lobjOid;
} else {
printf STDERR "%s: %s\n", $conn_db->errorMessage, $0;
}
$res_lobj = $conn_db->exec("END");
die $conn_db->errorMessage unless PGRES_COMMAND_OK eq
$res_lobj->resultStatus;
}
close(F);
}
undef $conn_db;
}
printf "\nExported %d large objects.\n\n", $counter;
undef $conn_all;
[EMAIL PROTECTED] writes:
>
>
>On Mon, 30 Oct 2000, [EMAIL PROTECTED] wrote:
>
>>Large objects are not dumped. It should be
>>in the documentation for large objects.
>>
>>You need to write a script which writes them to
>>disk and then imports them back in after you have
>>installed your dbs.
>>
>>
>>Troy
>
>CREATE TABLE news -- { chiave: id news ,newarchivio, newsnuove}
>(
> "idnews"SERIAL primary key,
> "oidnotizia"OID,-- news as large object
> "autore"TEXTx -- author
>);
>
>How should be done the script for this table ?
>
>I found something about large object only onthe programmesg guide are they
>present in other docs?
>
>Thanks in advance
>
>Alex
>
>
>
Re: Re(2): [SQL] Large Object dump ?
On Wed, 1 Nov 2000, pgsql-sql wrote: > You can try the script I made for exporting all my Pg database. > Ideas were borrowed from pg_dumplo-0.0.5. > Make it sure that you have "Perl5 extension for PostgreSQL - Pg.pm" > installed. Why you re-write pg_dumplo to perl and not use directly it? Perl love? :-) Karel
Re: [SQL] Outer Joins
Marc, I did not look at your queries, but outer joins are supported in the latest development version of PostgreSQL, and will be supported in the upcoming 7.1 release of the software - beta due soon. Regards, Grant Marc Rohloff wrote: > I've been looking at the open-source databases for a project I am working on and >while reading about Postgres I saw that they do not support outer joins yet. I was >intrigued by their solution of using a union query. > > Something Like: > select a.col1, b.col2 from a,b where a.col1 = b.col2 > union > select a.col1, NULL from a where a.col1 not in (select b.col2 from b) > > But I was wondering if the following would work (it does in some other databases) > > select a.col1, b.col2 from a,b > where a.col1 = b.col2 >or b.col2 is null > > or maybe even > > select a.col1, b.col2 from a,b > where a.col1 = b.col2 >or a.col1 not in (select b.col2 from b) > > These would seem to be far more efficient than a union query > (I would try this but I don't have a Unix box at the moment to install PostgreSQL >on!) > > Marc Rohloff -- > Poorly planned software requires a genius to write it > and a hero to use it. Grant Finnemore BSc(Eng) (mailto:[EMAIL PROTECTED]) Software Engineer Universal Computer Services Tel (+27)(11)712-1366PO Box 31266 Braamfontein 2017, South Africa Cell (+27)(82)604-553620th Floor, 209 Smit St., Braamfontein Fax (+27)(11)339-3421Johannesburg, South Africa
Re(2): Re(2): [SQL] Large Object dump ?
because of some problems (see below). i was also thinking that it was just a compatibility problem of pg_dumplo-0.0.5 to postgresql 6.5.3. and because i needed it badly, i wrote a "quick & dirty" perl script. 1. When I tried showing all LO of my_db, I got this message: pg_dumplo -w -d my_db NOTICE: (transaction aborted): queries ignored until END NOTICE: (transaction aborted): queries ignored until END NOTICE: (transaction aborted): queries ignored until END Database 'my_db' contains 0 large objects. My observation: my_db actually contains large objects. 2. When I tried this pg_dumplo -a -d my_db -s /my_dump/dir I got this. << CUT >> dump for_payment.application (218 large obj) lo_export: can't open inv object 4654657: /fs/linux/work/tools/pg_dumplo-0.0.5/pg_dumplo lo_export: can't open inv object 4654689: /fs/linux/work/tools/pg_dumplo-0.0.5/pg_dumplo lo_export: can't open inv object 4654881: /fs/linux/work/tools/pg_dumplo-0.0.5/pg_dumplo lo_export: can't open inv object 4654081: /fs/linux/work/tools/pg_dumplo-0.0.5/pg_ << CUT >> Exported 1131 large objects. NOTICE: LockReleaseAll: xid loop detected, giving up My observation: The tree (directories) were created but 1131 large objects were not there. The lo_dump.index file is not readable (contains garbage). peace, sherwin [EMAIL PROTECTED] writes: > >On Wed, 1 Nov 2000, pgsql-sql wrote: > >> You can try the script I made for exporting all my Pg database. >> Ideas were borrowed from pg_dumplo-0.0.5. >> Make it sure that you have "Perl5 extension for PostgreSQL - Pg.pm" >> installed. > > Why you re-write pg_dumplo to perl and not use directly it? Perl >love? :-) > > Karel >
Re: [SQL] Problem with coalesce..
Try: SELECT COALESCE(CURVALUE,1) FROM TABLEKEYS WHERE TABLENAME = 'BUYER'; - Original Message - From: George Henry C. Daswani To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Tuesday, October 31, 2000 5:52 PM Subject: [SQL] Problem with coalesce.. Hello, Was wondering if such a call is unsupported, or a bug? 7.0.2 (postgresql, linux redhat 7.0) SELECT COALESCE ((SELECT NULL,1) returns a 1, correct.. however.. SELECT COALESCE ((SELECT CURVALUE FROM TABLEKEYS WHERE TABLENAME = 'BUYER'),1) returns a "UNKNOWN expression type 501" SELECT CURVALUE FROM TABLEKEYS WHERE TABLENAME = 'BUYER'; returns a '0' Thanks.. George
Re: [SQL] Outer Joins
Marc, > select a.col1, b.col2 from a,b > where a.col1 = b.col2 >or b.col2 is null The above would select each record in a that matched a record in b, plus each record in b for every possible a where b.col2 was null - not where there was no match. > select a.col1, b.col2 from a,b > where a.col1 = b.col2 >or a.col1 not in (select b.col2 from b) This would work, but it would be *much* slower than a UNION query. "Not In" queries are perhaps the slowest you can run; see the earlier thread "Query Problem" for a discussion. UNION queries are, in fact, very fast ... just awkward to code and manipulate. -Josh -- __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 436-9166 for law firms, small businesses fax 436-0137 and non-profit organizations. pager 338-4078 San Francisco
[SQL] Timespan / interval calculations?
I'm using PostgreSQL 6.5. I have a table with a timestamp field. I
want to extract all rows with a timestamp less then an hour ago. I've
tried several things, but nothing seem to work. How should it be
done?
I've tried
select * from syslog
where stamp between timestamp('now') - interval('1 hour') and 'now';
select * from syslog
where stamp between timestamp('now') - timestamp('1 hour') and 'now';
but none of these works.
It seem to be something wrong with the interval and timespan types.
pere=> select interval('1 hour');
ERROR: parser: parse error at or near "("
pere=> select timespan('1 hour');
ERROR: Function 'timespan(unknown)' does not exist
Unable to identify a function which satisfies the given argument types
You will have to retype your query using explicit typecasts
pere=>
Should this work, or is it my mistake?
(I'm not on the mailing list, please copy replies to me. :-)
--
##> Petter Reinholdtsen <## | [EMAIL PROTECTED]
Re: [SQL] INSERT [IGNORE] INTO TABLE
The proper fix for this is subtransactions, which we will have someday. You do the INSERT in a subtransaction. > Edmar Wiggers wrote: > > > > If I try to insert some row which in the table already exist the > > > postgre don't insert it and tell some error. Everything ok. > > > I'll insert only if there is not the same (same key) record, > > > else don't insert and don't tell me errors about it. > > > In MySQL is a switch [IGNORE]. > > > > Not a good feature for me. What do you do if the record already exists? > > Update it? > > > > Check existence and then insert or update. If you want, I guess you could > > wrap that inside a stored procedure. > > Hi, here is an example of using function using pl/pgsql for inserting and > checking whether an instance exists or not. > CREATE FUNCTION add_new_user(text,text) RETURNS bool AS' DECLARE > oldUser RECORD; > USR ALIAS FOR $1; > PWORD ALIAS FOR $2; > > BEGIN > SELECT INTO oldUser * > FROM users > where username=USR AND password= PWORD; > IF FOUND > THEN > RETURN ''f''; > ELSE > INSERT INTO USERS(username,password) > values(USR,PWORD); > RETURN ''t''; > END IF; > > END;' > LANGUAGE 'plpgsql'; > > Regards. > Najm > > -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
Re: [SQL] Outer Joins
>> select a.col1, b.col2 from a,b >> where a.col1 = b.col2 >>or a.col1 not in (select b.col2 from b) >This would work, but it would be *much* slower than a UNION query. "Not >In" queries are perhaps the slowest you can run; see the earlier thread >"Query Problem" for a discussion. UNION queries are, in fact, very fast >... just awkward to code and manipulate. Why should this be slower since the UNION Query still has an identical not in clause? This is far easier (for me) to read. Marc
Antw: [SQL] Outer Joins
>>> "Marc Rohloff" <[EMAIL PROTECTED]> 01.11.2000 09.02 Uhr >>> > > select a.col1, b.col2 from a,b > where a.col1 = b.col2 > or b.col2 is null > This query has nothing to do with an outer join. See the following example: table a c1 --- x y and table b c2 --- x Then an outer join gives: select a,c1, b.c2 from a left outer join b on a.c1 = b.c2 c1 | c2 x | x y | (null) but your query gives: select a.c1, b.c2 from a, b where a.c1 = b.c2 or b.c2 is null c1 | c2 x | x because there are no rows in table b with c2 is null - Gerhard
[SQL] Re: [GENERAL] Problem with coalesce..
There are still some contexts in which subqueries in expressions are wierd. The example below appears to work in current sources however. Stephan Szabo [EMAIL PROTECTED] On Tue, 31 Oct 2000, George Henry C. Daswani wrote: > Hello, > > Was wondering if such a call is unsupported, or a bug? > > 7.0.2 (postgresql, linux redhat 7.0) > > SELECT COALESCE ((SELECT NULL,1) > > returns a 1, correct.. > > however.. > > SELECT COALESCE ((SELECT CURVALUE FROM TABLEKEYS WHERE TABLENAME = 'BUYER'),1) > > returns a "UNKNOWN expression type 501" > > SELECT CURVALUE FROM TABLEKEYS WHERE TABLENAME = 'BUYER'; returns a '0' > > Thanks.. > > George > > >
