Questions tagged [bytea]
Bytea is a binary string type for PostgreSQL.
38 questions
0
votes
1
answer
945
views
Slow performance on representing select bytea column query of table in PostgreSQL
Is it possible to improve representing data to client when they send command select bytea from table limit 150;. It consumes one minute and half but in pg_activity I see "client_write" ...
- 284
0
votes
1
answer
4k
views
Error message from server: ERROR: invalid memory alloc request size
I am running with the below issue while taking pg_dump(PG14) of a table with bytea column.
pg_dump: error: Error message from server: ERROR: invalid memory alloc request size 1460154641
The command ...
- 154
0
votes
1
answer
576
views
How to keep Image data in file system with Postgres database?
I am wondering is there any way to store image type data (binary data) in postgresql not directly into the columns but resides in the file system but not gonna store the path link as text in database ...
1
vote
1
answer
285
views
Why does PostgreSQL mangle my hex input in a LIKE query?
On some LIKE queries on a bytea column, PostgreSQL is mangling my input byte sequence. Why could this be happening?
Expected query plan: b >= '\x4cbf996e2c527620d067571c6f650643edfc5cdc0bd39c29':...
- 131
0
votes
1
answer
1k
views
What is the most efficient way to check whether a BYTEA column contains data, in PostgreSQL?
We have an online platform where we sell eBooks. In the database, we have a "books" table, which contains the books' information. It also contains the book file, stored as BYTEA.
In various ...
- 1
0
votes
1
answer
434
views
Create, Insert, and return BYTEA value
I'm creating vector tiles using ST_AsMVT and I would like to store them immediately after they are created in addition to returning the object.
The below code works fine at creating a bytea response.
...
1
vote
1
answer
1k
views
LIKE or match by beginning for binary strings in PostgreSQL
I need to match a binary column with its beginning.
For text columns it's easy: LIKE 'image/%'. This uses BTREE index if any.
Is it possible to do this also using an index for a binary column?
BTW, I ...
- 745
1
vote
0
answers
2k
views
Optimal index for storing crypto addresses using bytea
I have a simple table that stores Ethereum addresses:
CREATE TABLE address (
id SERIAL PRIMARY KEY,
address bytea NOT NULL,
attributes text[] NOT NULL DEFAULT '{}'::text[]
);
CREATE ...
- 1,274
1
vote
1
answer
956
views
How to convert bit(n) to bytea when n >= 256?
How can I convert a BIT(256) into 64-byte BYTEA? More generally, how can I convert from BIT(n) to BYTEA when n is large enough that I can't cast the bits through an INT8?
Example: I've got a bunch of ...
- 73
10
votes
2
answers
39k
views
How to convert a bytea column to text?
how to convert a bytea column to text in PostgreSQL so that I can read the column properly in PGADMIN?
I have the following SQL query in the PGADMIN's query editor:
SELECT event_type, created_at, ...
- 375
1
vote
1
answer
6k
views
How to prevent implicit casting from bytea to text during the current connection in postgres?
In Postgres bytea values are automatically converted to text when inserted to text/varchar columns, based on the bytea_output setting.
I am working with some program code which automatically converts ...
- 115
0
votes
1
answer
299
views
How to prevent different connections treating bytea in same query differently?
I find that identical postgresql queries issued by two different clients are handled differently. Specifically, bytea values are inserted differently.
A query that demonstrated the behaviour is this:
...
- 115
0
votes
2
answers
112
views
Optimize user segmentation by bytea column
First i would too explain my problem:
I have around 10 millions of customers in the table. Based on customer attributes, we are creating segments.
My table what joins customer with segments looks like
...
- 101
0
votes
0
answers
503
views
Writing CSV directly to a BYTEA column from PostgreSQL tables
Assuming these two tables:
CREATE TABLE data (day DATE, kpi INTEGER);
CREATE TABLE reports (csv BYTEA);
Is it possible to (pseudocode):
INSERT INTO reports (csv) (SELECT to_csv(SELECT day, kpi FROM ...
- 1
1
vote
1
answer
3k
views
Why does ENCODE on BYTEA not seem to have any performance impact?
I am working on a scenario to store strings in hex format of fixed length 64 to database. Obviously the choices are BYTEA and CHAR(64).
Initial thought was to enforce a valid hex string storing it ...
- 175
0
votes
1
answer
178
views
Which Index for WHERE UPPER("my_col"::text) LIKE "FOO" search on bytea
I use the unusual bytea column type in a PostgreSQL DB because the data in this column is like a blob. I mostly contains text, but sometimes it contains binary data.
Which index would fit if the ...
- 1,561
4
votes
2
answers
7k
views
LIKE search in bytea datatype (PostgreSQL)
Table:
\d blob
Column | Type | Collation | Default
---------+--------------+-----------+------------------------------------------
id | integer ...
- 1,561
0
votes
1
answer
2k
views
Slow updates of PostgreSql ByteA fields with just 1KB of data [closed]
TL;DR: Inserting or updating table, with putting 1024 bytes into bytea column works quite slow.
I have a PostgreSql 9.5.12 installation on my Desktop Linux Mint 18, with SSD drive, Core-i5, and 16GB ...
- 111
2
votes
1
answer
3k
views
Compare byte value to bytea column
Is there a way to compare a single byte value to a bytea field in PostgreSQL?
For example: I have a bytea field with the value \x11AA22BB. The comparison to a byte value of AA should be true while ...
- 175
2
votes
1
answer
2k
views
PostgreSQL: streaming binary data to a bytea field is very slow
I am trying to stream binary data into a bytea field.
The process is quite simple:
loop until the end of the incoming stream
UPDATE myTable SET data = data || $chunk WHERE id = myId
$chunk is a ...
1
vote
1
answer
2k
views
Chunking data into a PostgreSQL bytea column?
I have column of type bytea in PostgreSQL database.
If I update a row, is it possible to chunk the data into the bytea column in several small steps?
The size of one bytea entry will be mostly below ...
- 1,561
1
vote
1
answer
257
views
How to manipulate bytea data when using backup script as a template
I have a project with a web app that has many configurable options, with the configurations stored in a PostgreSQL database. Most fields are text or ints, but there are a couple configurations stored ...
- 13
9
votes
8
answers
5k
views
Reverse Byte-Order of a postgres bytea field
I'm currently working on a table that contains hashes, stored in bytea format. Converting the hashes to hex-strings however yields the wrong order of bytes. Example:
SELECT encode(hash, 'hex') FROM ...
- 113
6
votes
2
answers
21k
views
Export image file from bytea column
I am trying to export an image file from a PostgreSQL database.The users_data_circulation table has a photo (bytea) column.
My command:
copy (select encode(photo,'hex') from users_data_circulation ...
- 284
3
votes
1
answer
5k
views
oid vs bytea in postgres
I have a table called tblA and its having two columns
id_ (numeric 50,0), obj_ oid
For example, tblA having 1 entries
id_ | obj_
1 | 1001
In pg_largeobject table, there are three entries ...
- 815
4
votes
1
answer
6k
views
Postgres: SELECTing bytea data partially with offset and length
I have a table containing a bytea column, with an average entry size of 5MB. Is it possible to query from random points in the bytea data (given an offset and length of data required) instead of ...
- 41
1
vote
2
answers
2k
views
Storing blobs outside the database
I had a PostgreSQL database with loads of scanned documents, as a document bytea column in the table scans, with hundreds of thousands of documents that was large and inconvenient to backup. Also, ...
- 5,028
9
votes
1
answer
2k
views
PostgreSQL bytea vs smallint[]
I'm looking to import large (100Mb -- 1 GB) multi-channel time-series data into a PostgreSQL database. The data comes from EDF format files that chunks the data into "records" or "epochs" of typically ...
- 1,730
5
votes
1
answer
5k
views
Dump all binary columns from a table as files
I have a table in a postgresql database with a column of type bytea. I want to drop each binary entry as a file using the id as filename.
What would a SQL query look like for this?
- 151
1
vote
1
answer
797
views
What determines if a bytea has 1 or 4 extra bytes?
When inserting a BYTEA string in PostgreSQL the documentation says that such a string will occupy 1 or 4 bytes plus byte string length.
The question that I can't seem to find the answer to is what ...
- 365
6
votes
2
answers
54k
views
Postgresql and UTF8 to Latin1 conversion?
We have a Postgresql database where the data is in Latin1. Apparently someone loaded some UTF8 encoded data into it since when I select data from some of the columns, I get stuff like:
SELECT ...
- 455
7
votes
1
answer
3k
views
Optimal hash technique to index large text
Erwin Brandstetter thankfully saved me from myself when I tried to create unique indexes on textual columns with large lengths.
The upper limit for the insertion rate is tens of billions of rows per ...
user32234
0
votes
1
answer
9k
views
Insert a file into PostgreSQL BYTEA field using PHP [closed]
The previous answers have been horribly complex for the scope of this project.
I want to take the $_POST result as a variable and insert it into a BYTEA field of a table.
$_SESSION['username'] = $...
- 21
12
votes
1
answer
43k
views
Optimize PostgreSQL for a lot of INSERTS and bytea updates
What we have (software):
PostrgeSQL 9.3 with base configuration (no changes in postgresql.conf)
Windows 7 64 bit
Hardware:
Intel Core i7-3770 3.9 Ghz
32 Gb RAM
WDC WD10EZRX-00L4HBAta Drive (1000Gb, ...
- 557
5
votes
1
answer
1k
views
Limiting PostgreSQL logging
I have a table that can contain reasonably large BYTEA values (the same could apply to large TEXT values). It looks like this:
CREATE TABLE testtable (
id SERIAL PRIMARY KEY,
name TEXT UNIQUE ...
- 1,555
44
votes
3
answers
185k
views
Using PostgreSQL 8.4, how to convert bytea to text value in postgres?
In my application I insert data in database using C code, since the strings I receive from an untrusted source I have escaped them using PQescapeByteaConn of libpq library. Which is working perfectly ...
- 591
22
votes
3
answers
10k
views
How can I generate a random bytea
I would like to be able to generate random bytea fields of arbitrary length (<1Gb) for populating test data.
What is the best way of doing this?
- 40.2k
3
votes
1
answer
7k
views
How to copy bytea from Postgres to Oracle
I have a db on Postgres 9.1. The db collected some binary data that are stored in bytea file.
I need to copy the data to Oracle 10g. How to do it?
The details
I have a table plines in Postgres9.1. ...
- 31