Thread: [HELP] Attribute has an unknown type/is repeated

In PostgreSQL 7.1.2, I'm experimenting with the following two cases (all
referred to non-temporary tables exist in the current database):

=========

1.

CREATE TEMPORARY TABLE tmp (
  col1 TEXT,
  col2 TEXT,
  col3 TEXT,
  col4 TEXT,
  col5 TEXT,
  col6 TEXT
);

INSERT INTO tmp
SELECT
  o.number || ' ' || o.composer || ' --- ' || o.work,
  '      ' || class || ' --- ' || place || ' ' || trim(date),
  '      ' || conductor || ' --- ' || orchestra,
  '      ' || artists,
  '      ' || source || ' --- ' || mode || ' --- ' || duration,
  '      ' || note
FROM     operall o, work_class w, artists_cast c
WHERE    o.work = w.work
  AND    o.composer = w.composer
  AND    o.number = c.number
ORDER BY o.composer, o.work, date;

\o Z_master_cw_a
SELECT *, '' AS "colx" FROM tmp;


2.

CREATE TEMPORARY TABLE tmp2 AS
SELECT
  o.number || ' ' || o.composer || ' --- ' || o.work AS col1,
  '      ' || class || ' --- ' || place || ' ' || trim(date) AS col2,
  '      ' || conductor || ' --- ' || orchestra AS col3,
  '      ' || artists AS col4,
  '      ' || source || ' --- ' || mode || ' --- ' || duration AS col5,
  '      ' || note AS col6,
  '' AS "colx"
FROM     operall o, work_class w, artists_cast c
WHERE    o.work = w.work
  AND    o.composer = w.composer
  AND    o.number = c.number
ORDER BY o.composer, o.work, date;

\o Z_master_cw_a2
SELECT * FROM tmp2;

=========

While the written files are ok in both cases, for Case 2 I get the following

NOTICE:  Attribute 'colx' has an unknown type
        Relation created; continue

My ???:

1. What exactly is the type of colx in Case 1?
2. Why can't PostgreSQL infer that very same type for colx in Case 2?


Additionally, if in Case 2 I do not specify explicitely <AS col1,...,AS
col6>,
I receive

ERROR:  Attribute '?column?' is repeated

My ?:

3. Why can't PostgreSQL here automatically assign column labels as needed?


Thanks.

/oskar

"Itzinger, Oskar" <oitzinger@opec.org> writes:
> 1. What exactly is the type of colx in Case 1?
> 2. Why can't PostgreSQL infer that very same type for colx in Case 2?

The || operator is known to yield a result of type "text".  But when the
sum total of the known information is

    '' AS colx

there is no basis at all for assigning a specific type, and it remains
"unknown".

            regards, tom lane

On Tue, 26 Jun 2001, Itzinger, Oskar wrote:

> In PostgreSQL 7.1.2, I'm experimenting with the following two cases (all
> referred to non-temporary tables exist in the current database):
>
> =========
>
> 1.
>
> CREATE TEMPORARY TABLE tmp (
>   col1 TEXT,
>   col2 TEXT,
>   col3 TEXT,
>   col4 TEXT,
>   col5 TEXT,
>   col6 TEXT
> );
>
> INSERT INTO tmp
> SELECT
>   o.number || ' ' || o.composer || ' --- ' || o.work,
>   '      ' || class || ' --- ' || place || ' ' || trim(date),
>   '      ' || conductor || ' --- ' || orchestra,
>   '      ' || artists,
>   '      ' || source || ' --- ' || mode || ' --- ' || duration,
>   '      ' || note
> FROM     operall o, work_class w, artists_cast c
> WHERE    o.work = w.work
>   AND    o.composer = w.composer
>   AND    o.number = c.number
> ORDER BY o.composer, o.work, date;
>
> \o Z_master_cw_a
> SELECT *, '' AS "colx" FROM tmp;
>
>
> 2.
>
> CREATE TEMPORARY TABLE tmp2 AS
> SELECT
>   o.number || ' ' || o.composer || ' --- ' || o.work AS col1,
>   '      ' || class || ' --- ' || place || ' ' || trim(date) AS col2,
>   '      ' || conductor || ' --- ' || orchestra AS col3,
>   '      ' || artists AS col4,
>   '      ' || source || ' --- ' || mode || ' --- ' || duration AS col5,
>   '      ' || note AS col6,
>   '' AS "colx"
> FROM     operall o, work_class w, artists_cast c
> WHERE    o.work = w.work
>   AND    o.composer = w.composer
>   AND    o.number = c.number
> ORDER BY o.composer, o.work, date;
>
> \o Z_master_cw_a2
> SELECT * FROM tmp2;
>
> =========
>
> While the written files are ok in both cases, for Case 2 I get the following
>
> NOTICE:  Attribute 'colx' has an unknown type
>         Relation created; continue
>
> My ???:
>
> 1. What exactly is the type of colx in Case 1?
> 2. Why can't PostgreSQL infer that very same type for colx in Case 2?
>

Hmmm, I'll guess here so bare with me. In the first case you have defined the schema of the table and you insert into
itthrough a select so Pg knows what to cast them to. When you do the select with the additional '' as colx Pg doesn't
reallycare what its data type is. It picks the first cast rule i.e. it is cast to text. 

In the second case, you don't have a well defined schema for the temp table tmp2. So when you create it through the
selectquery, Pg has to determine the table's schema based on what data type each field of the SELECT query is. In this
casethe '' is ambiguous. It can be text or int or double or... 
It is ambiguous because:

test=# select '';
 ?column?
----------

(1 row)

test=# select ''::int;
 ?column?
----------
        0
(1 row)


The first select casts it to text and the second to integer which translates to 0.
If you had cast the '' it wouldn't complain. Check this:

test=# create temp table xoxo as select '' as a,''::int as b,''::text as c;
NOTICE:  Attribute 'a' has an unknown type
    Relation created; continue
SELECT

>
> Additionally, if in Case 2 I do not specify explicitely <AS col1,...,AS
> col6>,
> I receive
>
> ERROR:  Attribute '?column?' is repeated
>
> My ?:
>
> 3. Why can't PostgreSQL here automatically assign column labels as needed?

It does assign column labels automatically. The label is '?column?'

cheers,
thalis

>
>
> Thanks.
>
> /oskar
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>