PostgreSQL – RIGHT() Function Not Behaving as Expected in 9.1

postgresql-9.1

The following is with regard to PostgresQL 9.1 :

I am using the right() and left() string functions to fix a few instances where data was input into my database being “off by one” .. so I’m doing something along the lines of right(field1,1)||left(field2,-1) to get correct values and such. In the process, I encountered an unexpected result on a char(2) column.

If I do SELECT RIGHT('A ',1) it gives me the expected ' ' returned.

However, if I do SELECT RIGHT('A '::CHAR(2),1) it gives me 'A'.

Why is it doing this? Is it a bug? I tried SELECT RIGHT('A '::CHAR(3),1) and it also gives 'A' as a result.

Best Answer

The CHAR data type is awful. Avoid it if at all possible, its padding rules are insane. PostgreSQL wouldn't support it at all if the standard didn't require support for it.

It's not clear whether this is intended behaviour or a bug. Usually, leading white-space gets stripped from CHAR before processing, and I suspect that for RIGHT, Pg is stripping the whitespace from the right, as if this were RTL text.

I suggest raising this for discussion on pgsql-general. If nothing else, it clearly warrants a documentation note.

Demo:

Setup:

CREATE ROLE thegroup;
CREATE USER user1 IN ROLE thegroup;
CREATE USER user2 IN ROLE thegroup;
CREATE TABLE t1 ( x integer not null );
ALTER TABLE t1 OWNER TO user1;

Without further changes, here's what happens if user2 tries to add an index on t1.x:

regress=# SET ROLE user2;
SET
regress=> CREATE INDEX t1_x_idx ON t1(x);
ERROR:  must be owner of relation t1

The solution is to grant the table ownership to the shared role. As user1 or a superuser:

ALTER TABLE t1 OWNER TO thegroup;

now:

regress=# SET ROLE user2;
SET
regress=> CREATE INDEX t1_x_idx ON t1(x);
CREATE INDEX
regress=>

PostgreSQL 9.1 – Failed Insert Occupies Increasing Disk Space

Error on inserting data due to FK constraint violation will leave dead tuple on the target table. That's the nature of Postgres. This dead tuple of course will occupy disk space. Things to prevent this filling up disk space :

A well tuned autovacuum can handle this wasted space.
Do a manual FK constraint check (on application side perhaps) prior to inserting data to the target tables.

Best Answer

Related Solutions

Postgresql – Error creating index on PostgreSQL 9.1

Demo:

PostgreSQL 9.1 – Failed Insert Occupies Increasing Disk Space

Related Question