[HELP] Attribute has an unknown type/is repeated

"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 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