473,722 Members | 2,242 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

bytea

when bytea, text, and varchar(no limit entered) columns are used, do
they ALWAYS use an extra table/file? Or do they only do it after a
certain size of input?

Also, if I wanted to put a *.pdf file in a bytea column, what functions
do I use to escape any characters in it?

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 23 '05 #1
7 4453
> Also, if I wanted to put a *.pdf file in a bytea column, what functions
do I use to escape any characters in it?
What programming language are you using?

In Perl, you do something like:

$sth->bind_param(1 , $file_data, DBI::SQL_BINARY ); #DBI::SQL_BINAR Y is
deprecated, but it works

In php you do:

$file_data = pg_escape_bytea ($file_data);

$db->query("inser t into blah(blahh) values ('${file_data}' ::bytea);

To retrieve the info in Perl, it's just a regular fetchrow()

my ($file_data) = $sth->fetchrow();

In php, you have to run stripcslashes() on the data.

list($file_data ) = $query->fetchrow();
$file_data = stripcslashes($ file_data);

Jon


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster


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

Nov 23 '05 #2
I forgot, please CC me, I am on digest.
Dennis Gearon wrote:
when bytea, text, and varchar(no limit entered) columns are used, do
they ALWAYS use an extra table/file? Or do they only do it after a
certain size of input?

Also, if I wanted to put a *.pdf file in a bytea column, what
functions do I use to escape any characters in it?

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

Nov 23 '05 #3
On Tue, May 11, 2004 at 09:30:15AM -0700, Jonathan Bartlett wrote:
Also, if I wanted to put a *.pdf file in a bytea column, what functions
do I use to escape any characters in it?


What programming language are you using?


Apparently if you are using C and libpq, you can use the version 3
protocol functions to send binary data without having to escape it. I
haven't done it though so I may be wrong, but if I'm right please
somebody tell me because I usually recommend this :-)

Also if it works it would be useful to update the Perl/PHP/etc
interfaces to use it.

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"El sudor es la mejor cura para un pensamiento enfermo" (Bardia)

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

Nov 23 '05 #4
Thanks for all the answers everybody, but I need to know also an answer
to the other question:

Does the bytea make its own files automatically for large objects?

Also, how about backups with tables having bytea columns.?

Jonathan Bartlett wrote:
Also, if I wanted to put a *.pdf file in a bytea column, what functions
do I use to escape any characters in it?


What programming language are you using?

In Perl, you do something like:

$sth->bind_param(1 , $file_data, DBI::SQL_BINARY ); #DBI::SQL_BINAR Y is
deprecated, but it works

In php you do:

$file_data = pg_escape_bytea ($file_data);

$db->query("inser t into blah(blahh) values ('${file_data}' ::bytea);

To retrieve the info in Perl, it's just a regular fetchrow()

my ($file_data) = $sth->fetchrow();

In php, you have to run stripcslashes() on the data.

list($file_dat a) = $query->fetchrow();
$file_data = stripcslashes($ file_data);

Jon

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster


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

Nov 23 '05 #5
On Tue, 11 May 2004, Dennis Gearon wrote:
Thanks for all the answers everybody, but I need to know also an answer
to the other question:

Does the bytea make its own files automatically for large objects?
Bytea doesn't use large objects, which are kind of an artifact left over
from the days of the 8k row limit. They use what are called "toast"
tables. Toast tables allow for text/varchar/bytea types to overflow from
the base table as needed to occupy up to ~2 gigabytes of drive space per
field max. Note that I'm pretty sure no one's really tried to put 2 gig
in one field, as that would probably take quite some time, and I'm not
sure how well most clients are gonna handle getting back a row with a 2
gig field in it. :-)

And yes, toasting is fully automatic. Just insert a large
text/varchar/bytea field and the database does the rest. which is why
they are generally recommended over using large objects, which require
specialized handling.
Also, how about backups with tables having bytea columns.?


Just like any other field. pg_dump will escape them as needed to make a
usable backup.

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

Nov 23 '05 #6
On Tue, May 11, 2004 at 11:50:06AM -0700, Dennis Gearon wrote:
Thanks for all the answers everybody, but I need to know also an answer
to the other question:

Does the bytea make its own files automatically for large objects?
No, they are stored in tables.
Also, how about backups with tables having bytea columns.?


What about them? They should work just fine.

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"La vida es para el que se aventura"

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postg resql.org

Nov 23 '05 #7
> Does the bytea make its own files automatically for large objects?

I do not believe so. You'd have to check with someone else for sure,
though.
Also, how about backups with tables having bytea columns.?


Regular pg_dump handles bytea columns just fine.

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

Nov 23 '05 #8

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
2884
by: mPath Records | last post by:
Hello, I hope someone can help me with this problem - I'm getting nowhere fast and have been working on it for a week. All I'm trying to do is upload an image, store the image in a bytea column of a postrgresql database, pull the image out of the datbase, and verify that the image is still the same and it can be viewed by a browser - that's all. The upload part works fine... I'm having problems getting out what I put in. The file...
48
11898
by: Edwin Quijada | last post by:
Hi !! Everybody I am developing app using Delphi and I have a question: I have to save pictures into my database. Each picture has 20 o 30k aprox. What is the way more optimus? That 's table will have 500000 records around. Somebody said the best way to do that was encoder the picture to field bytea but I dont know about this. Another way is save the path to the picture file but I dont like so much because I need to write to disk by OS...
1
6656
by: Baldur Norddahl | last post by:
Hi, I need to do something like this: select * from sms where message ilike 'foo%'; Message is a bytea field with UTF-8 content. The charset is actually not important for me, it would be enough if it could just treat it as us ascii. The statement does not work because postgresql can not convert bytea to text. But this works:
2
22051
by: Alvar Freude | last post by:
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Hi, I want to change a column from text to bytea; since it seems that alter table can't change the column type, i have to add a temporary column and copy the data from the old one to the new, delete the old and rename the new.
0
2381
by: Alvar Freude | last post by:
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Hi, the following I posted already on pgsql-bugs -- perhaps someone has a good workaround or fix or can say me that I'm wrong? There seems to be a bug in handling bytea columns with index and the like-operator.
4
3676
by: David Garamond | last post by:
May I request that connectby() supports BYTEA keys too? My keys are GUID (16-byte stored in BYTEA). In this case, branch_delim does not make sense because the keys should be fixed-length anyway, unless if connectby() also wants to support outputing the branch as encoded text. Btw, is recursive join (CONNECT BY ...) in SQL standard? (I have a copy of the 1992 draft and it doesn't seem to be there). -- dave
7
2785
by: C G | last post by:
Dear All, What's the best way to store jpgs in postgresql to use in a web page? I tried to use large objects, but how would you extract them from a table to be viewed in a web-page without having to write them to a scratch file somewhere first? Thanks
0
1507
by: Peter Wang | last post by:
I have a function and am using cursor. All the columns I select in the cursor clause are BYTEA datatype. I need to compare the after-fetch-value for two BYTEA columns which is temp2 and temp3 shown as below. I don't think I can compare it because there is no record in temp table which I use for debug purpose. Does anyone know how to compare the after-fetched-value for two BYTEA datatype in pgsql's function? CREATE OR REPLACE...
4
11435
by: Jerry LeVan | last post by:
Hi, I am adding image and large object support in my Cocoa postgresql browser. Are there going to be any enhanced bytea support functions coming along? It seems sorta silly to have to write customized C code to import a file into a bytea field.
0
8860
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, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
1
9151
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9086
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 protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
8042
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6680
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
4501
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
3207
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2600
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2146
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.