Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Select truncated string from Postgres

I have some large varchar values in Postgres that I want to SELECT and move somewhere else. The place they are going to uses VARCHAR(4095) so I only need at most 4095 bytes (I think that's bytes) and some of these varchars are quite big, so a performance optimization would be to SELECT a truncated version of them.

How can I do that?
Something like:

SELECT TRUNCATED(my_val, 4095) ...

I don't think it's a character length though, it needs to be a byte length?

like image 585
Some Guy Avatar asked Oct 08 '15 21:10

Some Guy


People also ask

How do I extract a string from a word Postgres?

SUBSTRING() function The PostgreSQL substring function is used to extract a string containing a specific number of characters from a particular position of a given string. The main string from where the character to be extracted. Optional. The position of the string from where the extracting will be starting.

How do I cut text in PostgreSQL?

PostgreSQL provides you with LTRIM, RTRIM() and BTRIM functions that are the shorter version of the TRIM() function. The LTRIM() function removes all characters, spaces by default, from the beginning of a string. The RTRIM() function removes all characters, spaces by default, from the end of a string.

How do you check if a string contains a substring PostgreSQL?

Use the substring() Function to SELECT if String Contains a Substring Match in PostgreSQL. The substring() returns the strings similar to abc in our case or contains abc . We then match the returned results to the str using the ~~ operator, short for like , and if they match, we select the results from the table.

Can we rollback truncate in PostgreSQL?

It depends on the database supporting DDL to be transactional. For example IBM DB2, Ingres and PostgreSQL support this feature, but Oracle on the other hand does not (it supports something different though) - you cannot rollback a truncate statement in Oracle.


1 Answers

The n in varchar(n) is the number of characters, not bytes. The manual:

SQL defines two primary character types: character varying(n) and character(n), where n is a positive integer. Both of these types can store strings up to n characters (not bytes) in length.

Bold emphasis mine.

The simplest way to "truncate" a string would be with left():

SELECT left(my_val, 4095)

Or just cast:

SELECT my_val::varchar(4095)

The manual once more:

If one explicitly casts a value to character varying(n) or character(n), then an over-length value will be truncated to n characters without raising an error. (This too is required by the SQL standard.)

like image 175
Erwin Brandstetter Avatar answered Sep 25 '22 01:09

Erwin Brandstetter