Error input of anonymous composite types is not implemented

In the expression link != '{}' the alias link refers to the table, not a single column (because of tblData AS link).

Postgres allows to refer to the complete row of a table by using the table name (or table alias). The error is telling you that you can’t compare a complete row with a single array value.

You need something like this:

select distinct cast(xpath('/Data/Link', xmldata) AS TEXT) as linkdata
from data 
where cast(xpath('/Data/Link', xmldata) AS TEXT) <> '{}';

Unfortunately it’s not possible to use a column alias in the where clause. If you want to avoid repeating the expression, use a derived table:

select distinct linkdata 
from (
  select cast(xpath('/Data/Link', xmldata) AS TEXT) as linkdata
  from data 
) t 
where linkdata <> '{}';

But as you are only expecting a single link anyway, the following might better:

select distinct linkdata 
from (
  select (xpath('/Data/Link', xmldata))[1] as linkdata
  from data 
) t 
where linkdata is not null;

xpath() returns an array, the expression (xpath(..))[1] gets the first element of that array, if there is no such element, it returns null. Maybe you event want xpath('/Data/Link/UUID/text()') to only get the actual UUID, rather than the complete XML node — but that’s not clear from the example you provided.

Hey guys, I am using this image to fiddle with GitLab CI/CD implementation. My .gitlab-ci.yml file looks like this:

test:
  image: python:3.7.5
  services:
    - name: mdillon/postgis:11
      alias: postgres
    - name: redis:latest
  script:
    - apt-get update
    - pip install -U pip setuptools
    - pip install -r requirements_dev.txt
    - pytest -x --strict
  variables:
    POSTGRES_DB: testdb
    POSTGRES_USER: xxx
    POSTGRES_PASSWORD: xxx
    PGPASSWORD: xxx
    REDIS_HOST: redis
    REDIS_PORT: 6379

However, once tests start running, I’m getting following error which only happens inside CI/CD pipeline with this image:

statement = 'SELECT users.password AS users_password, users.created_at AS users_created_at, users.updated_at AS users_updated_at, ...ND users.id = user_followers.follows_id AND user_followers.state = %(state_1)s ORDER BY user_followers.updated_at DESC'
parameters = {'param_1': 3, 'state_1': 'yes'}
context = <sqlalchemy.dialects.postgresql.psycopg2.PGExecutionContext_psycopg2 object at 0x7f8a2dce4490>

    def do_execute(self, cursor, statement, parameters, context=None):
>       cursor.execute(statement, parameters)
E       sqlalchemy.exc.NotSupportedError: (psycopg2.errors.FeatureNotSupported) input of anonymous composite types is not implemented
E       LINE 3: ...r_followers.follows_id AND user_followers.state = 'yes' ORDE...
E                                                                    ^

Does anyone have an idea about whats going on? It seems to have problems with ENUM PostgreSQL types, but I am not sure on how to debug or fix this. I am not able to reproduce this error outside of the container.

В выражении link != ‘{}’ псевдоним link относится к Таблица, а не к отдельному столбцу (из-за tblData AS link).

Postgres позволяет ссылаться на всю строку таблицы, используя имя таблицы (или псевдоним таблицы). Ошибка сообщает вам, что вы не можете сравнить полную строку с одним значением массива.

Вам понадобится что-то вроде этого:

select distinct cast(xpath('/Data/Link', xmldata) AS TEXT) as linkdata
from data 
where cast(xpath('/Data/Link', xmldata) AS TEXT) <> '{}';

К сожалению, в предложении where невозможно использовать псевдоним столбца. Если вы хотите избежать повторения выражения, используйте производную таблицу:

select distinct linkdata 
from (
  select cast(xpath('/Data/Link', xmldata) AS TEXT) as linkdata
  from data 
) t 
where linkdata <> '{}';

Но поскольку вы в любом случае ожидаете только ссылку Один, лучше может быть следующее:

select distinct linkdata 
from (
  select (xpath('/Data/Link', xmldata))[1] as linkdata
  from data 
) t 
where linkdata is not null;

Xpath() возвращает массив, выражение (xpath(..))[1] получает первый элемент этого массива, если такого элемента нет, оно возвращает ноль. Возможно, вы хотите, чтобы xpath(‘/Data/Link/UUID/text()’) получал только фактический UUID, а не полный узел XML, но это не ясно из приведенного вами примера.

I have a table with an XML column. The XML can contain an optional link element.

<Link ReasonCode="MultiLabel">
  <UUID>1d177dee-1da4-4451-b175-396666afc370</UUID>
</Link>

I want to fetch all records from the table that do not have link element. From those records that have the link element, I want only the DISTINCT records fetched. If two records have the same link element, skip the second occurrence. The query that I tried,

SELECT DISTINCT cast((xpath('/Data/Link', xmldata)) AS TEXT) 
FROM tblData AS link 
WHERE link != '{}'

ERROR: input of anonymous composite types is not implemented

1. What is it that I am doing wrong here that causes the above mentioned error? The query works fine without the WHERE clause.
2. In addition to this I want to use an order by clause before the DISTINCT method starts processing the results. Again, I messed up with the syntax of using order by and distinct together, and I got an error.

How do I achieve ordering of the results based on the value of a column in the table, for this query?

Answer

In the expression link != '{}' the alias link refers to the table, not a single column (because of tblData AS link).

Postgres allows to refer to the complete row of a table by using the table name (or table alias). The error is telling you that you can’t compare a complete row with a single array value.

You need something like this:

select distinct cast(xpath('/Data/Link', xmldata) AS TEXT) as linkdata
from data 
where cast(xpath('/Data/Link', xmldata) AS TEXT) <> '{}';

Unfortunately it’s not possible to use a column alias in the where clause. If you want to avoid repeating the expression, use a derived table:

select distinct linkdata 
from (
  select cast(xpath('/Data/Link', xmldata) AS TEXT) as linkdata
  from data 
) t 
where linkdata <> '{}';

But as you are only expecting a single link anyway, the following might better:

select distinct linkdata 
from (
  select (xpath('/Data/Link', xmldata))[1] as linkdata
  from data 
) t 
where linkdata is not null;

xpath() returns an array, the expression (xpath(..))[1] gets the first element of that array, if there is no such element, it returns null. Maybe you event want xpath('/Data/Link/UUID/text()') to only get the actual UUID, rather than the complete XML node – but that’s not clear from the example you provided.

Would it be possible to know what is the meaning of this error "error: SQL error: input of anonymous composite types is not implemented"

I am getting the above error("error: SQL error: input of anonymous composite types is not implemented") when I perform the below query,

select *
from dev d
where not exists (select *
                  from ( 
                    select * 
                    from jsonb_array_elements_text(d.json_id -> 'sourcedepartmentId')
                    where jsonb_typeof(d.json_id -> 'sourcedepartmentId') = 'array'
                    and d.json_id -> 'sourcedepartmentId' is not null
                    union all
                    select d.json_id ->> 'sourcedepartmentId'
                    where jsonb_typeof(d.json_id -> 'sourcedepartmentId') = 'string'
                    and and d.json_id -> 'sourcedepartmentId' is not null
                  )  as jdata
                  where jdata in (....)
                 )

Any help would be much appreciated. Thanks. The above statment

I have a table with an XML column. The XML can contain an optional link element.

<Link ReasonCode="MultiLabel">
  <UUID>1d177dee-1da4-4451-b175-396666afc370</UUID>
</Link>

I want to fetch all records from the table that do not have link element. From those records that have the link element, I want only the DISTINCT records fetched. If two records have the same link element, skip the second occurrence. The query that I tried,

SELECT DISTINCT cast((xpath('/Data/Link', xmldata)) AS TEXT) 
FROM tblData AS link 
WHERE link != '{}'

ERROR: input of anonymous composite types is not implemented

1. What is it that I am doing wrong here that causes the above mentioned error? The query works fine without the WHERE clause.
2. In addition to this I want to use an order by clause before the DISTINCT method starts processing the results. Again, I messed up with the syntax of using order by and distinct together, and I got an error.

How do I achieve ordering of the results based on the value of a column in the table, for this query?

Andrew Kiellor <akiellor(at)gmail(dot)com> writes:
> Sorry I omitted the output. It is as follows:

> psql:test.sql:14: ERROR: input of anonymous composite types is not implemented
> LINE 1: SELECT * FROM table1 WHERE column1 = ‘(0)’;
> ^

I think this is operating as designed. I agree it’d be slightly more
convenient if the parser would infer that the RHS must be of the same
type as the LHS, but shoehorning that into the existing system design
seems problematic. The record_eq operator doesn’t actually require
that its inputs be of identical composite types, only compatible ones.
To continue your example:

regression=# CREATE TYPE type2 AS (xyz int);
CREATE TYPE
regression=# SELECT * FROM table1 WHERE column1 = ‘(0)’::type2;
column1
———
(0)
(1 row)

regression=# CREATE TYPE type3 AS (x float);
CREATE TYPE
regression=# SELECT * FROM table1 WHERE column1 = ‘(0)’::type3;
ERROR: cannot compare dissimilar column types integer and double precision at record column 1

So if the parser assumed that the inputs must be of the same composite
type, it’d be exceeding its authority, and would likely cause queries
that work today to start failing.

The back story here is that type «record» isn’t really a polymorphic
type, though it behaves similarly to those types in some ways. If we
were designing in a green field it’d make sense to treat «record»
according to the polymorphism rules. But we’re not; «record» is way
older than the polymorphics so it has various unique idiosyncrasies.
The one that’s relevant here is that an input argument that’s declared
to be «record» isn’t required to be the same composite type as another
argument also declared as «record».

regards, tom lane