[HELP] Attribute has an unknown type/is repeated

From: "Itzinger, Oskar" <oitzinger(at)opec(dot)org>
To: pgsql-general(at)postgresql(dot)org
Cc: "Itzinger, Oskar" <oitzinger(at)opec(dot)org>
Subject: [HELP] Attribute has an unknown type/is repeated
Date: 2001-06-26 08:05:03
Message-ID: FD2A5FEE4C3AD511A77B00508BB8538A02D291@OPECEXCH
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Oliver Vecernik 2001-06-26 08:40:50 Re: Stored procedures?
Previous Message Sterling 2001-06-26 07:27:10 SELECT something NOT EQUAL to????