Questions tagged [bytea]

Bytea is a binary string type for PostgreSQL.

Filter by
Sorted by
Tagged with
0
votes
0answers
61 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
vote
1answer
62 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 ...
0
votes
1answer
33 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
votes
1answer
429 views

LIKE search in bytea datatype (PostgreSQL)

Table: \d blob Column | Type | Collation | Default ---------+--------------+-----------+------------------------------------------ id | integer ...
0
votes
1answer
178 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 ...
0
votes
1answer
509 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 ...
1
vote
1answer
851 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
1answer
402 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
vote
1answer
116 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 ...
5
votes
6answers
2k 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 ...
6
votes
2answers
8k 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 ...
0
votes
1answer
2k 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 ...
1
vote
1answer
1k 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 ...
1
vote
2answers
665 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, ...
9
votes
1answer
624 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 ...
4
votes
1answer
2k 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?
0
votes
1answer
170 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 ...
4
votes
2answers
25k 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 ...
6
votes
1answer
1k 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 ...
0
votes
1answer
5k 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'] = $...
10
votes
1answer
34k 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, ...
5
votes
1answer
535 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 ...
13
votes
3answers
35k 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 ...
17
votes
2answers
4k 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?
3
votes
1answer
4k 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. ...