Questions tagged [bytea]

Bytea is a binary string type for PostgreSQL.

Filter by
Sorted by
Tagged with
0
votes
0answers
11 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 ...
24
votes
3answers
69k 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 ...
0
votes
2answers
1k 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, ...
1
vote
1answer
393 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 ...
0
votes
1answer
31 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: ...
20
votes
3answers
5k 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?
0
votes
2answers
48 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 ...
7
votes
6answers
3k 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 ...
0
votes
0answers
219 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 ...
6
votes
2answers
13k 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 ...
1
vote
1answer
191 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
46 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
2k views

LIKE search in bytea datatype (PostgreSQL)

Table: \d blob Column | Type | Collation | Default ---------+--------------+-----------+------------------------------------------ id | integer ...
0
votes
1answer
637 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
874 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 ...
12
votes
1answer
38k 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
591 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
vote
1answer
1k 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
754 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 ...
0
votes
1answer
6k 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'] = $...
6
votes
2answers
34k 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 ...
1
vote
2answers
851 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, ...
7
votes
1answer
2k 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 ...
1
vote
1answer
172 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 ...
0
votes
1answer
270 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 ...
1
vote
1answer
2k 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 ...
3
votes
1answer
5k 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. ...
9
votes
1answer
850 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
vote
1answer
3k 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 ...
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?