Renaming columns not working when new name is longer than 63 bytes (postgres column name length)

[drmnk]

Environment

• PostgreSQL version: 14.3
• PostgREST version: 9
• Operating system: Ubuntu 20.04 (via WSL2)

Description of issue

Hello! I found strange behavior in Postgrest: If we have a column name longer than 63 bytes (longest table/column name in bytes that PostgreSQL allow) - renaming of column will not work.

How to reproduce - create column with name, for example:

columnnamelongerthansixtythreesymbolscolumnnamelongerthansixtythreesymbols

And add some data to it. Of course, it will be truncated, but we can easily query it via postgrest.

After we quering table we will get

	{
		"columnnamelongerthansixtythreesymbolscolumnnamelongerthansixtyt": "123"
	}

Ok, but we for data crunching we need a full name, so we try to rename it in response with ":"

http://localhost:3033/tablename?select=columnnamelongerthansixtythreesymbolscolumnnamelongerthansixtythreesymbols:columnnamelongerthansixtythreesymbolscolumnnamelongerthansixtythreesymbols

But result will be the same. If we rename column in any name less than 63 symbols, abc for example, it will work.

P.S. Why do I need names longer than 63 symbols - it's because I use non-ASCII symbols in table names and column names, cyrillic, by the way. I do it because it sometimes can be very hard to trancslate specifing termins in my domain (accounting). And because of PostgreSQL limitations it truncates to 63 bytes - every non-ASCII symbol uses 2 bytesm so only 31 symbols left for my column names. Yes, it works :)

[drmnk]

SQL DB dump for example:

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

--
-- TOC entry 3 (class 2615 OID 2200)
-- Name: public; Type: SCHEMA; Schema: -; Owner: postgres
--

CREATE SCHEMA public;


ALTER SCHEMA public OWNER TO postgres;

--
-- TOC entry 3280 (class 0 OID 0)
-- Dependencies: 3
-- Name: SCHEMA public; Type: COMMENT; Schema: -; Owner: postgres
--

COMMENT ON SCHEMA public IS 'standard public schema';


SET default_tablespace = '';

SET default_table_access_method = heap;

--
-- TOC entry 209 (class 1259 OID 73735)
-- Name: tablenamelongerthansixtythreesymbolstablenamelongerthansixtythr; Type: TABLE; Schema: public; Owner: postgrestcolumn
--

CREATE TABLE public.tablenamelongerthansixtythreesymbolstablenamelongerthansixtythr (
    columnnamelongerthansixtythreesymbolscolumnnamelongerthansixtyt character varying
);


ALTER TABLE public.tablenamelongerthansixtythreesymbolstablenamelongerthansixtythr OWNER TO postgrestcolumn;

--
-- TOC entry 3274 (class 0 OID 73735)
-- Dependencies: 209
-- Data for Name: tablenamelongerthansixtythreesymbolstablenamelongerthansixtythr; Type: TABLE DATA; Schema: public; Owner: postgrestcolumn
--

COPY public.tablenamelongerthansixtythreesymbolstablenamelongerthansixtythr (columnnamelongerthansixtythreesymbolscolumnnamelongerthansixtyt) FROM stdin;
123
\.

[steve-chavez]

@drmnk Hm, this isn't the same as https://github.com/PostgREST/postgrest/issues/1937?

So NAMEDATALEN is max 63 chars?

Related https://github.com/PostgREST/postgrest-docs/issues/535

[steve-chavez]

Not even RDS supports more than 63 chars: https://stackoverflow.com/questions/55141062/how-to-increase-column-name-width-limit-in-aws-rds-postgres

If you host your own postgres, you might be able to compile it with a bigger value though.

[drmnk]

Sure, I understand that Postgres has limit of only 63 chars. It's not a question about NAMEDATALE.

But renaming of column is also relies on Postgres?

I don't know the inner mechanism on renaming. Thought that it can be some in-memory operation maybe.

[steve-chavez]

I don't know the inner mechanism on renaming. Thought that it can be some in-memory operation maybe.

@drmnk We use standard postgresql aliasing for this. It has the same limitation:

select name as columnnamelongerthansixtythreesymbolscolumnnamelongerthansixtythreesymbols from test.projects;

NOTICE:  identifier "columnnamelongerthansixtythreesymbolscolumnnamelongerthansixtythreesymbols" will be truncated to 
"columnnamelongerthansixtythreesymbolscolumnnamelongerthansixtyt"

 columnnamelongerthansixtythreesymbolscolumnnamelongerthansixtyt
-----------------------------------------------------------------
 Windows 7
 Windows 10
 IOS
 OSX
 Orphan

[wolfgangwalther]

Closing as duplicate.