By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,660 Members | 1,267 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,660 IT Pros & Developers. It's quick & easy.

psql and blob

P: n/a
Hi @ all,

i'm sure there was a psql-function to transfere my Blob-Data to the
server but I can't remember.

I have a script like this :

UPDATE xy SET z = lo_import('localpath_and_file');

but i want to execute this script from the client and so my blob-data is
on the client and lo_import fails (the server doesn't have this file).
Hm.

Thanks for tips - Daniel.
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 11 '05 #1
Share this Question
Share on Google+
15 Replies


P: n/a
Hi everybody

To convert PostgreSQL timestmp to UNIX timestamp I use

select ((today::abstime)::int4) from table ...

In the documentation there is a notice that abstime shouldn't be used.
Is there another way to convert?

Also I tried

select timetz_hash(today) from table ..

and got negative values for some timestamps. Does anybody know why?

RedHat 7.3, PostgreSQL 7.2.1
Regards
Conni
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 11 '05 #2

P: n/a
On Wednesday 17 September 2003 18:32, Daniel Schuchardt wrote:
Hi @ all,

i'm sure there was a psql-function to transfere my Blob-Data to the
server but I can't remember.

I have a script like this :

UPDATE xy SET z = lo_import('localpath_and_file');

but i want to execute this script from the client and so my blob-data is
on the client and lo_import fails (the server doesn't have this file).
Hm.

Thanks for tips - Daniel.

http://www.postgresql.org/docs/7.3/i...geobjects.html
is what You need.

Regards !

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 11 '05 #3

P: n/a
"Cornelia Boenigk" <po******@cornelia-boenigk.de> writes:
To convert PostgreSQL timestmp to UNIX timestamp I use
select ((today::abstime)::int4) from table ...
In the documentation there is a notice that abstime shouldn't be used.
Is there another way to convert?
"SELECT EXTRACT(EPOCH FROM timestamp)" is the recommended way to do it.
Also I tried
select timetz_hash(today) from table ..
and got negative values for some timestamps. Does anybody know why?


Why not? If the hash function didn't use all 32 available bits, I'd
think it faulty ...

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 11 '05 #4

P: n/a
Hm, but lo_import/lo_export imports/exports from the local filesystem.
I want to upload/download a clientfile to/from the Server Database with
psql. I think this is a feature in psql. But i can't remember...

Daniel

-----Ursprüngliche Nachricht-----
Von: pg************************************************ *@postgresql.org
[mailto:pg***************************************** ********@postgresql.o
rg] Im Auftrag von Darko Prenosil
Gesendet: Mittwoch, 17. September 2003 20:07
An: Daniel Schuchardt; pg***********@postgresql.org
Betreff: Re: [GENERAL] psql and blob
http://www.postgresql.org/docs/7.3/i...geobjects.html
is what You need.

Regards !

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 11 '05 #5

P: n/a
CoL
Hi,

if you, then write a program for that. psql is a database server, not a
client program.

C.

Daniel Schuchardt wrote, On 9/17/2003 8:37 PM:
Hm, but lo_import/lo_export imports/exports from the local filesystem.
I want to upload/download a clientfile to/from the Server Database with
psql. I think this is a feature in psql. But i can't remember...

Daniel


Nov 11 '05 #6

P: n/a
On Wed, 17 Sep 2003, Daniel Schuchardt wrote:
Hm, but lo_import/lo_export imports/exports from the local filesystem.
I want to upload/download a clientfile to/from the Server Database with
psql. I think this is a feature in psql. But i can't remember...

Yes it is. You want to look up \lo_import in the help/manpage/manual of psql.
Nigel Andrews

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 11 '05 #7

P: n/a
> if you, then write a program for that. psql is a database server, not a
client program.
No, psql is a client program. postmaster is the database server.

Jon


C.

Daniel Schuchardt wrote, On 9/17/2003 8:37 PM:
Hm, but lo_import/lo_export imports/exports from the local filesystem.
I want to upload/download a clientfile to/from the Server Database with
psql. I think this is a feature in psql. But i can't remember...

Daniel

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 11 '05 #8

P: n/a
CoL
hi,
you are right: I wrote it in wrong way. psql is a client program, but he
wants to get the file not from the place where psql runs.

He says:
" but i want to execute this script from the client and so my blob-data
is on the client and lo_import fails (the server doesn't have this file)."
That's why he needs a program, an application, or something which can
communicate with client. Or another way, if he runs the psql from client
where the file is, than connecting to postgres server :)

Sorry for my ambiguous letter.

C.

Jonathan Bartlett wrote, On 9/17/2003 9:48 PM:
if you, then write a program for that. psql is a database server, not a
client program.


No, psql is a client program. postmaster is the database server.

Jon


Nov 11 '05 #9

P: n/a
Daniel Schuchardt <da***************@web.de> writes:
Hi @ all,

i'm sure there was a psql-function to transfere my Blob-Data to the
server but I can't remember.


The psql function to use is \lo_import--this reads the file from the
client side.

-Doug

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 11 '05 #10

P: n/a
On Wednesday 17 September 2003 20:37, Daniel Schuchardt wrote:
Hm, but lo_import/lo_export imports/exports from the local filesystem.
I want to upload/download a clientfile to/from the Server Database with
psql. I think this is a feature in psql. But i can't remember...

Daniel

I'm afraid that You did not understand me.
In that documentation are *all* the ways to import and export large objects.
Check lo_write/lo_read and You'll see that they not depend on server
filesystem.

Regards !

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 11 '05 #11

P: n/a
Yes thats it. Thanks.

Am Mi, 2003-09-17 um 22.42 schrieb Doug McNaught:
Daniel Schuchardt <da***************@web.de> writes:
Hi @ all,

i'm sure there was a psql-function to transfere my Blob-Data to the
server but I can't remember.


The psql function to use is \lo_import--this reads the file from the
client side.

-Doug

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 11 '05 #12

P: n/a
Yes thats it.

Ok - one last question.
My Script looks like this and actually i can run it only on the server (so i have to copy all my data to the server each time i want to update my blobs):

INSERT INTO tablexy (BLOBFIELD) VALUES (lo_import('BLOBFILE')).

Now we know if I want to upload a clientfile I have to use \lo_import BUT i cant use this inside the script.

INSERT INTO tablexy (BLOBFIELD) VALUES (\lo_import('BLOBFILE')). is not possible because \lo_import is a unknown command for the server.

So I have to do 2 steps manually in psql:

\lo_import(ClientFile) -> Returns OID

INSERT INTO tablexy (BLOBFIELD) VALUES (Returned OID)

Is there a way to do this automatically?
Means my Clientside script should upload a local file (from the same computer where the script is executed) to the server and insert this file in a special table automatically.

Thanks
Am Mi, 2003-09-17 um 22.42 schrieb Doug McNaught:
Daniel Schuchardt <da***************@web.de> writes:
Hi @ all,

i'm sure there was a psql-function to transfere my Blob-Data to the
server but I can't remember.


The psql function to use is \lo_import--this reads the file from the
client side.

-Doug

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 11 '05 #13

P: n/a
Daniel Schuchardt <da***************@web.de> writes:
So I have to do 2 steps manually in psql:
\lo_import(ClientFile) -> Returns OID
INSERT INTO tablexy (BLOBFIELD) VALUES (Returned OID) Is there a way to do this automatically?


See psql's :LASTOID (I think that's the name) variable.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 11 '05 #14

P: n/a

----- Original Message -----
From: "Daniel Schuchardt" <da***************@web.de>
To: "Doug McNaught" <do**@mcnaught.org>
Cc: <pg***********@postgresql.org>
Sent: Thursday, September 18, 2003 4:44 PM
Subject: Re: [GENERAL] psql and blob

Yes thats it.

Ok - one last question.
My Script looks like this and actually i can run it only on the server (so i have to copy all my data to the server each time i want to update my
blobs):
INSERT INTO tablexy (BLOBFIELD) VALUES (lo_import('BLOBFILE')).

Now we know if I want to upload a clientfile I have to use \lo_import BUT i cant use this inside the script.
INSERT INTO tablexy (BLOBFIELD) VALUES (\lo_import('BLOBFILE')). is not possible because \lo_import is a unknown command for the server.
So I have to do 2 steps manually in psql:

\lo_import(ClientFile) -> Returns OID

INSERT INTO tablexy (BLOBFIELD) VALUES (Returned OID)

Is there a way to do this automatically?
Means my Clientside script should upload a local file (from the same

computer where the script is executed) to the server and insert this file in
a special table automatically.

Maybe Your problem is only to find last inserted oid ?
See: http://www.postgresql.org/docs/7.3/i.../app-psql.html and look
for LASTOID.
Part from docs:

LASTOID
The value of the last affected OID, as returned from an INSERT or lo_insert
command. This variable is only guaranteed to be valid until after the result
of the next SQL command has been displayed.
So solution might be :
\lo_import(ClientFile)
INSERT INTO tablexy (BLOBFIELD) VALUES (:LASTOID)
I'm not shure what exactly you want to acomplish, but this might work.

I repeat: lo_read/lo_write from libpq are the only true "client side" way I
know. You can write small "C" program that reads the file from local
filesystem and writes it directly to sql server with no "uploads" or nothing
like that.
Look at : http://www.postgresql.org/docs/7.3/i.../lo-libpq.html
Note that \lo_import and \lo_export PSQL INSTRUCTIONS are using the same
technique, and they act different than server side lo_import() and
lo_export() SERVER SIDE FUNCTIONS. See:
http://www.postgresql.org/docs/7.3/i.../app-psql.html
Hope this helps.

Regards !

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 11 '05 #15

P: n/a
Am Do, 2003-09-18 um 20.21 schrieb Darko Prenosil:
----- Original Message -----
From: "Daniel Schuchardt" <da***************@web.de>
To: "Doug McNaught" <do**@mcnaught.org>
Cc: <pg***********@postgresql.org>
Sent: Thursday, September 18, 2003 4:44 PM
Subject: Re: [GENERAL] psql and blob

Yes thats it.

Ok - one last question.
My Script looks like this and actually i can run it only on the server (so

i have to copy all my data to the server each time i want to update my
blobs):

INSERT INTO tablexy (BLOBFIELD) VALUES (lo_import('BLOBFILE')).

Now we know if I want to upload a clientfile I have to use \lo_import BUT

i cant use this inside the script.

INSERT INTO tablexy (BLOBFIELD) VALUES (\lo_import('BLOBFILE')). is not

possible because \lo_import is a unknown command for the server.

So I have to do 2 steps manually in psql:

\lo_import(ClientFile) -> Returns OID

INSERT INTO tablexy (BLOBFIELD) VALUES (Returned OID)

Is there a way to do this automatically?
Means my Clientside script should upload a local file (from the same

computer where the script is executed) to the server and insert this file in
a special table automatically.

Maybe Your problem is only to find last inserted oid ?
See: http://www.postgresql.org/docs/7.3/i.../app-psql.html and look
for LASTOID.
Part from docs:

LASTOID
The value of the last affected OID, as returned from an INSERT or lo_insert
command. This variable is only guaranteed to be valid until after the result
of the next SQL command has been displayed.
So solution might be :
\lo_import(ClientFile)
INSERT INTO tablexy (BLOBFIELD) VALUES (:LASTOID)
I'm not shure what exactly you want to acomplish, but this might work.

I repeat: lo_read/lo_write from libpq are the only true "client side" way I
know. You can write small "C" program that reads the file from local
filesystem and writes it directly to sql server with no "uploads" or nothing
like that.
Look at : http://www.postgresql.org/docs/7.3/i.../lo-libpq.html
Note that \lo_import and \lo_export PSQL INSTRUCTIONS are using the same
technique, and they act different than server side lo_import() and
lo_export() SERVER SIDE FUNCTIONS. See:
http://www.postgresql.org/docs/7.3/i.../app-psql.html
Hope this helps.

Regards !

Ok i will try this. Thats my aim :

I have a masterdatabase located on a computer in our firm. All chages
and so are located in this db. This means also icons/pictures my
application uses. So this data changes during the time because of
development. If we install our app on a new server or update the version
I have 2 scripts. First makes a dump. So I dump my administrative tables
and blobs in our firm and than i want to upload / install the dumped
information to the customers server. Actually this is ugly because of
the local pathes. So I have to upload my dump (it consists of seperate
text (dump as INSERT WITH COLUMN NAMES) and blob files to the server to
reload the data. My scripts look like this :
to prepare

pg_dump -i -h $1 -a -D -t mainmenu -f "/Sql/Dump/ZZ_7 MainMenu.sql" $2
pg_dump -i -h $1 -a -D -t reports -f "/Sql/Dump/ZZ_10 Reports.sql" $2

psql -h $1 -c "SELECT lo_export(mm_picture,
'/Sql/Dump/Blobs/mainmenu.mm_id.'|| CAST(mm_id AS VARCHAR) || '.blob')
FROM mainmenu" $2
psql -h $1 -c "SELECT lo_export(r_blob,
'/Sql/Dump/Blobs/reports.r_id.'|| CAST(r_id AS VARCHAR) || '.blob') FROM
reports WHERE r_blob IS NOT NULL" $2

to reload

psql -h $1 -c "DELETE FROM mainmenu;" $2
psql -h $1 -c "DELETE FROM reports;" $2

psql -h $1 -f "/var/tmp/dump/ZZ_7 MainMenu.sql" $2
psql -h $1 -f "/var/tmp/dump/ZZ_10 Reports.sql" $2

psql -h $1 -c "UPDATE mainmenu SET
mm_picture=lo_import('/var/tmp/dump/Blobs/mainmenu.mm_id.'|| CAST(mm_id
AS VARCHAR) || '.blob') WHERE mm_picture IS NOT NULL" $2
psql -h $1 -c "UPDATE reports SET
r_blob=lo_import('/var/tmp/dump/Blobs/reports.r_id.'|| CAST(r_id AS
VARCHAR) || '.blob') WHERE r_blob IS NOT NULL" $2

perhaps I should try to dump / reload with oid's

Nov 11 '05 #16

This discussion thread is closed

Replies have been disabled for this discussion.