Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [bytea]

Bytea is a binary string type for PostgreSQL.

A bytea type can be stored in-line or out-of-line (TOASTed), but will not be compressed. There are no native casts to or from bytea.

You can read more about bytea in the PostgreSQL docs.

38 questions
45
votes
3 answers

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 fine i.e. results in Octet format String. See…
Amit
  • 591
  • 2
  • 5
  • 10
22
votes
3 answers

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?
Jack Douglas
  • 40,517
  • 16
  • 106
  • 178
12
votes
1 answer

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, SATA III) So, we have to load into DB aprox.…
Andremoniy
  • 557
  • 1
  • 8
  • 17
12
votes
2 answers

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, encode(metadata::bytea, 'escape') FROM…
ryanzidago
  • 395
  • 1
  • 3
  • 11
9
votes
8 answers

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 mytable LIMIT 1; Output:…
R. Martin
  • 123
  • 1
  • 5
9
votes
1 answer

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 a few seconds each. Each epoch's record holds…
beldaz
  • 1,740
  • 3
  • 16
  • 26
7
votes
1 answer

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 year. For my implementation, the hash never needs…
user32234
6
votes
2 answers

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 symptoms from client - - "huvudvärke" - - where "ä"…
James Brown
  • 455
  • 1
  • 5
  • 12
6
votes
2 answers

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 limit 1) TO '/tmp/imagetest.hext'; In operating…
Melih
  • 284
  • 2
  • 6
  • 19
5
votes
1 answer

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?
tzippy
  • 151
  • 1
  • 2
5
votes
3 answers

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 NOT NULL, -- This is just an example: this could…
Bruno
  • 1,557
  • 3
  • 17
  • 31
4
votes
2 answers

LIKE search in bytea datatype (PostgreSQL)

Table: \d blob Column | Type | Collation | Default ---------+--------------+-----------+------------------------------------------ id | integer | |…
guettli
  • 1,591
  • 5
  • 25
  • 51
4
votes
1 answer

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 SELECTing the whole byte string into the memory?
Tokuchi Toa
  • 41
  • 1
  • 3
3
votes
1 answer

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. plines contains a bytea field shape. The number…
ysa
  • 31
  • 1
  • 2
3
votes
1 answer

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 against loid 1001 (3 page nos) which is fine. But,…
RBB
  • 815
  • 2
  • 15
  • 37
1
2 3