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

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 it through a select so Pg knows what to cast them to. When you do the select with the additional '' as colx Pg doesn't really care 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 select query, Pg has to determine the table's schema based on what data type each field of the SELECT query is. In this case the '' 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(at)postgresql(dot)org)
>