473,394 Members | 1,715 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,394 software developers and data experts.

psql and blob

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
15 12271
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
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
"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
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
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
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
> 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
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
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
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
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
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
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

----- 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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: Daniel Schuchardt | last post by:
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'); ...
2
by: Howard Lowndes | last post by:
I'm working psql v7.2.2 to postmaster v7.2.2 and want to use the \lo_import function. The psql manual says that the syntax is \lo_import '<filename>' 'comment' This loads the blob OK and...
4
by: Phil Campaigne | last post by:
Hi All, I just installed postgresql 7.3.4 as an upgrade to 7.3.2 and all went well untill I tried to log into a database that I successfully created. Here are the steps in question: bash-2.05b$...
7
by: Willem Herremans | last post by:
I am developing a client application for postgreSQL in Tcl/Tk (see http://gborg.postgresql.org/project/pfm ). It mainly uses PgTcl or pgintcl. I don't have any problems with those, but I am also...
4
by: Brendan Jurd | last post by:
Hello all, I just wanted to pass on some information about compatibility between the psql client and the postgres server. On a particular network, my workstation and the server are both debian...
1
by: Josué Maldonado | last post by:
Hello List, I'm having this issue with beta 8.0 C:\pgsql\bin>pg_dump -U postgres farmacia > xfar.sql Password: C:\pgsql\bin>psql -U postgres farmacia2 < xfar.sql Password: psql: FATAL: ...
2
by: Russ Brown | last post by:
Hello, Today I tried connecting to my database locally via psql. I got the usual welcome & basic help messages, but it never got to the prompt: it just hung. So I checked top and the psql...
3
by: oksofar | last post by:
Hello - I'm running PG 8.1 on Windows XP. I've installed the server to run as a service. The psql command fails to connect to the server, although I can connect with other clients. When I...
0
by: gezerpunta | last post by:
Hi folks :) I need an example which is about pdo connection with bytea type .I searched all day.but I can t find. It looks like blob format.because it shows resorce id when I fetch forum pdo....
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...
0
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
0
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.