HiveBrain v1.2.0
Get Started
← Back to all entries
snippetsqlMinor

How to differentiate text[] and text[][] when both have the same internal type _text?

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
samethedifferentiatehowtextbothinternaltype_textwhen

Problem

According to https://dba.stackexchange.com/a/307373/156984, both text[] and text[][] have the same internal type _text.
If the internal type of both is the same, how can I then actually differentiate them?

SELECT * FROM pg_type; just returns one row, representing both types. I would expect it to return two, one for text[] and one for text[][].

EDIT:
The reason I need this information is that i kind of want to reverse engineer text[] or text[][] from _text to then send the type information as a String to a client.

Solution

According to Postgres array documentation, Postgres does not differentiate between one- and multidimensional arrays in its type.

The current implementation does not enforce the declared number of
dimensions either. Arrays of a particular element type are all
considered to be of the same type, regardless of size or number of
dimensions. So, declaring the array size or number of dimensions in
CREATE TABLE is simply documentation; it does not affect run-time
behavior.

For example, both int[] and int[][] have the same type _int.

It is possible to get the dimensions of an array from pg_attribute.attndims.

Sources:

  • https://www.postgresql.org/docs/current/arrays.html



  • https://github.com/jOOQ/jOOQ/issues/252#issuecomment-1240674582

Context

StackExchange Database Administrators Q#325347, answer score: 2

Revisions (0)

No revisions yet.