Thread: psql and blob

psql and blob

From

Daniel Schuchardt

Date:

17 September 2003, 16:33:08

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.

converting PostgreSQ- timestamp to UNIX-timestamp ?

From

"Cornelia Boenigk"

Date:

17 September 2003, 17:11:34

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

Re: psql and blob

From

Darko Prenosil

Date:

17 September 2003, 18:07:19

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/interactive/largeobjects.html
is what You need.

Regards !

Re: converting PostgreSQ- timestamp to UNIX-timestamp ?

From

Tom Lane

Date:

17 September 2003, 18:26:11

"Cornelia Boenigk" <poppcorn@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

Re: psql and blob

From

"Daniel Schuchardt"

Date:

17 September 2003, 18:39:09

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: pgsql-general-owner+M49349=daniel_schuchardt=web.de@postgresql.org
[mailto:pgsql-general-owner+M49349=daniel_schuchardt=web.de@postgresql.o
rg] Im Auftrag von Darko Prenosil
Gesendet: Mittwoch, 17. September 2003 20:07
An: Daniel Schuchardt; pgsql-general@postgresql.org
Betreff: Re: [GENERAL] psql and blob


http://www.postgresql.org/docs/7.3/interactive/largeobjects.html
is what You need.

Regards !

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

Re: psql and blob

From

CoL

Date:

17 September 2003, 19:37:54

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
>

Re: psql and blob

From

"Nigel J. Andrews"

Date:

17 September 2003, 19:42:59

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

Re: psql and blob

From

Jonathan Bartlett

Date:

17 September 2003, 19:49:40

> 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
>

Re: psql and blob

From

Doug McNaught

Date:

17 September 2003, 20:43:08

Daniel Schuchardt <daniel_schuchardt@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

Re: psql and blob

From

Darko Prenosil

Date:

18 September 2003, 07:51:13

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 !

Re: psql and blob

From

CoL

Date:

18 September 2003, 13:16:25

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

Re: psql and blob

From

Daniel Schuchardt

Date:

18 September 2003, 14:45:37

Yes thats it. Thanks.

Am Mi, 2003-09-17 um 22.42 schrieb Doug McNaught:
> Daniel Schuchardt <daniel_schuchardt@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

Re: psql and blob

From

Daniel Schuchardt

Date:

18 September 2003, 14:45:59

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
eachtime 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
commandfor 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
serverand insert this file in a special table automatically. 

Thanks

Am Mi, 2003-09-17 um 22.42 schrieb Doug McNaught:
> Daniel Schuchardt <daniel_schuchardt@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

Re: psql and blob

From

Tom Lane

Date:

18 September 2003, 16:04:51

Daniel Schuchardt <daniel_schuchardt@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

Re: psql and blob

From

"Darko Prenosil"

Date:

18 September 2003, 18:15:45

----- Original Message -----
From: "Daniel Schuchardt" <daniel_schuchardt@web.de>
To: "Doug McNaught" <doug@mcnaught.org>
Cc: <pgsql-general@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/interactive/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/interactive/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/interactive/app-psql.html
Hope this helps.

Regards !

Re: psql and blob

From

Daniel Schuchardt

Date:

19 September 2003, 12:55:15

Am Do, 2003-09-18 um 20.21 schrieb Darko Prenosil:

----- Original Message -----
From: "Daniel Schuchardt" <daniel_schuchardt@web.de>
To: "Doug McNaught" <doug@mcnaught.org>
Cc: <pgsql-general@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/interactive/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/interactive/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/interactive/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