Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit7048c8c

Browse files
committed
Add mention of CREATE TABLE and atttypmod problems.
1 parent23ea2b1 commit7048c8c

File tree

1 file changed

+278
-0
lines changed

1 file changed

+278
-0
lines changed

‎doc/TODO.detail/atttypmod

Lines changed: 278 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,278 @@
1+
From tgl@sss.pgh.pa.us Wed Nov 21 22:51:02 2001
2+
Return-path: <tgl@sss.pgh.pa.us>
3+
Received: from sss.pgh.pa.us (root@[192.204.191.242])
4+
by candle.pha.pa.us (8.11.6/8.10.1) with ESMTP id fAM3p2v12831
5+
for <pgman@candle.pha.pa.us>; Wed, 21 Nov 2001 22:51:02 -0500 (EST)
6+
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
7+
by sss.pgh.pa.us (8.11.4/8.11.4) with ESMTP id fAM3p4c27978;
8+
Wed, 21 Nov 2001 22:51:04 -0500 (EST)
9+
To: Bruce Momjian <pgman@candle.pha.pa.us>
10+
cc: Peter Eisentraut <peter_e@gmx.net>,
11+
PostgreSQL Development <pgsql-hackers@postgresql.org>,
12+
stiening@cannon.astro.umass.edu, pgsql-bugs@postgresql.org
13+
Subject: Re: [BUGS] Bug #513: union all changes char(3) column definition
14+
In-Reply-To: <200111220310.fAM3A2V08766@candle.pha.pa.us>
15+
References: <200111220310.fAM3A2V08766@candle.pha.pa.us>
16+
Comments: In-reply-to Bruce Momjian <pgman@candle.pha.pa.us>
17+
message dated "Wed, 21 Nov 2001 22:10:02 -0500"
18+
Date: Wed, 21 Nov 2001 22:51:04 -0500
19+
Message-ID: <27975.1006401064@sss.pgh.pa.us>
20+
From: Tom Lane <tgl@sss.pgh.pa.us>
21+
Status: ORr
22+
23+
Bruce Momjian <pgman@candle.pha.pa.us> writes:
24+
> Added to TODO:
25+
> * CREATE TABLE AS can not determine column lengths from expressions
26+
> Seems it should be documented. Do we throw an error in these cases?
27+
28+
No. What we do right now is to generate non-length-constrained column
29+
types for the created table.
30+
31+
Your TODO item is too pessimistic: we *do* determine the column length
32+
in simple cases. For example:
33+
34+
regression=# create table foo (f1 char(3));
35+
CREATE
36+
regression=# create table bar as select * from foo;
37+
SELECT
38+
regression=# \d bar
39+
Table "bar"
40+
Column | Type | Modifiers
41+
--------+--------------+-----------
42+
f1 | character(3) |
43+
44+
However, in more complex cases we don't know the column length:
45+
46+
regression=# create table baz as select f1 || 'z' as f1 from foo;
47+
SELECT
48+
regression=# \d baz
49+
Table "baz"
50+
Column | Type | Modifiers
51+
--------+--------+-----------
52+
f1 | bpchar |
53+
54+
The argument here is about how much intelligence it's reasonable to
55+
expect the system to have. It's very clearly not feasible to derive
56+
a length limit automagically in every case. How hard should we try?
57+
58+
regards, tom lane
59+
60+
From pgsql-bugs-owner+M2695=candle.pha.pa.us=pgman@postgresql.org Wed Nov 21 23:16:19 2001
61+
Return-path: <pgsql-bugs-owner+M2695=candle.pha.pa.us=pgman@postgresql.org>
62+
Received: from rs.postgresql.org (server1.pgsql.org [64.39.15.238] (may be forged))
63+
by candle.pha.pa.us (8.11.6/8.10.1) with ESMTP id fAM4GJv15505
64+
for <pgman@candle.pha.pa.us>; Wed, 21 Nov 2001 23:16:19 -0500 (EST)
65+
Received: from postgresql.org (postgresql.org [64.49.215.8])
66+
by rs.postgresql.org (8.11.6/8.11.6) with ESMTP id fAM4CxN38340
67+
for <pgman@candle.pha.pa.us>; Wed, 21 Nov 2001 22:12:59 -0600 (CST)
68+
(envelope-from pgsql-bugs-owner+M2695=candle.pha.pa.us=pgman@postgresql.org)
69+
Received: from sss.pgh.pa.us ([192.204.191.242])
70+
by postgresql.org (8.11.3/8.11.4) with ESMTP id fAM48em84313;
71+
Wed, 21 Nov 2001 23:08:40 -0500 (EST)
72+
(envelope-from tgl@sss.pgh.pa.us)
73+
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
74+
by sss.pgh.pa.us (8.11.4/8.11.4) with ESMTP id fAM48bc28082;
75+
Wed, 21 Nov 2001 23:08:37 -0500 (EST)
76+
To: Bruce Momjian <pgman@candle.pha.pa.us>
77+
cc: Peter Eisentraut <peter_e@gmx.net>,
78+
PostgreSQL Development <pgsql-hackers@postgresql.org>,
79+
stiening@cannon.astro.umass.edu, pgsql-bugs@postgresql.org
80+
Subject: Re: [BUGS] Bug #513: union all changes char(3) column definition
81+
In-Reply-To: <200111220353.fAM3rRg12994@candle.pha.pa.us>
82+
References: <200111220353.fAM3rRg12994@candle.pha.pa.us>
83+
Comments: In-reply-to Bruce Momjian <pgman@candle.pha.pa.us>
84+
message dated "Wed, 21 Nov 2001 22:53:27 -0500"
85+
Date: Wed, 21 Nov 2001 23:08:37 -0500
86+
Message-ID: <28079.1006402117@sss.pgh.pa.us>
87+
From: Tom Lane <tgl@sss.pgh.pa.us>
88+
Precedence: bulk
89+
Sender: pgsql-bugs-owner@postgresql.org
90+
Status: OR
91+
92+
Bruce Momjian <pgman@candle.pha.pa.us> writes:
93+
> However, I don't think creating a bpchar
94+
> with no length is a proper solution. Should we just punt to text in
95+
> these cases?
96+
97+
How many special cases like that do you want to put into the allegedly
98+
datatype-independent CREATE TABLE code?
99+
100+
If I thought this were the only case then I'd not object ... but it
101+
looks like a slippery slope from here.
102+
103+
And --- it's not like replacing "bpchar" with "text" actually buys us
104+
any useful new functionality. AFAICS it's just a cosmetic thing.
105+
106+
regards, tom lane
107+
108+
PS: On the other hand, we might consider attacking the problem from
109+
the reverse direction, ie *removing* code. For example, if there
110+
weren't redundant || operators for char and varchar, then every ||
111+
operation would yield text, and the example we're looking at would
112+
work the way you want for free. I've thought for awhile that we
113+
could use a pass through pg_proc and pg_operator to remove some
114+
entries we don't really need.
115+
116+
---------------------------(end of broadcast)---------------------------
117+
TIP 5: Have you checked our extensive FAQ?
118+
119+
http://www.postgresql.org/users-lounge/docs/faq.html
120+
121+
From tgl@sss.pgh.pa.us Wed Nov 21 23:08:36 2001
122+
Return-path: <tgl@sss.pgh.pa.us>
123+
Received: from sss.pgh.pa.us (root@[192.204.191.242])
124+
by candle.pha.pa.us (8.11.6/8.10.1) with ESMTP id fAM48av14412
125+
for <pgman@candle.pha.pa.us>; Wed, 21 Nov 2001 23:08:36 -0500 (EST)
126+
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
127+
by sss.pgh.pa.us (8.11.4/8.11.4) with ESMTP id fAM48bc28082;
128+
Wed, 21 Nov 2001 23:08:37 -0500 (EST)
129+
To: Bruce Momjian <pgman@candle.pha.pa.us>
130+
cc: Peter Eisentraut <peter_e@gmx.net>,
131+
PostgreSQL Development <pgsql-hackers@postgresql.org>,
132+
stiening@cannon.astro.umass.edu, pgsql-bugs@postgresql.org
133+
Subject: Re: [BUGS] Bug #513: union all changes char(3) column definition
134+
In-Reply-To: <200111220353.fAM3rRg12994@candle.pha.pa.us>
135+
References: <200111220353.fAM3rRg12994@candle.pha.pa.us>
136+
Comments: In-reply-to Bruce Momjian <pgman@candle.pha.pa.us>
137+
message dated "Wed, 21 Nov 2001 22:53:27 -0500"
138+
Date: Wed, 21 Nov 2001 23:08:37 -0500
139+
Message-ID: <28079.1006402117@sss.pgh.pa.us>
140+
From: Tom Lane <tgl@sss.pgh.pa.us>
141+
Status: ORr
142+
143+
Bruce Momjian <pgman@candle.pha.pa.us> writes:
144+
> However, I don't think creating a bpchar
145+
> with no length is a proper solution. Should we just punt to text in
146+
> these cases?
147+
148+
How many special cases like that do you want to put into the allegedly
149+
datatype-independent CREATE TABLE code?
150+
151+
If I thought this were the only case then I'd not object ... but it
152+
looks like a slippery slope from here.
153+
154+
And --- it's not like replacing "bpchar" with "text" actually buys us
155+
any useful new functionality. AFAICS it's just a cosmetic thing.
156+
157+
regards, tom lane
158+
159+
PS: On the other hand, we might consider attacking the problem from
160+
the reverse direction, ie *removing* code. For example, if there
161+
weren't redundant || operators for char and varchar, then every ||
162+
operation would yield text, and the example we're looking at would
163+
work the way you want for free. I've thought for awhile that we
164+
could use a pass through pg_proc and pg_operator to remove some
165+
entries we don't really need.
166+
167+
From pgsql-bugs-owner+M2696=candle.pha.pa.us=pgman@postgresql.org Wed Nov 21 23:26:07 2001
168+
Return-path: <pgsql-bugs-owner+M2696=candle.pha.pa.us=pgman@postgresql.org>
169+
Received: from rs.postgresql.org (server1.pgsql.org [64.39.15.238] (may be forged))
170+
by candle.pha.pa.us (8.11.6/8.10.1) with ESMTP id fAM4Q6v16612
171+
for <pgman@candle.pha.pa.us>; Wed, 21 Nov 2001 23:26:06 -0500 (EST)
172+
Received: from postgresql.org (postgresql.org [64.49.215.8])
173+
by rs.postgresql.org (8.11.6/8.11.6) with ESMTP id fAM4MwN38618
174+
for <pgman@candle.pha.pa.us>; Wed, 21 Nov 2001 22:22:58 -0600 (CST)
175+
(envelope-from pgsql-bugs-owner+M2696=candle.pha.pa.us=pgman@postgresql.org)
176+
Received: from candle.pha.pa.us (candle.navpoint.com [162.33.245.46])
177+
by postgresql.org (8.11.3/8.11.4) with ESMTP id fAM4DUm84443;
178+
Wed, 21 Nov 2001 23:13:30 -0500 (EST)
179+
(envelope-from pgman@candle.pha.pa.us)
180+
Received: (from pgman@localhost)
181+
by candle.pha.pa.us (8.11.6/8.10.1) id fAM4DSH15042;
182+
Wed, 21 Nov 2001 23:13:28 -0500 (EST)
183+
From: Bruce Momjian <pgman@candle.pha.pa.us>
184+
Message-ID: <200111220413.fAM4DSH15042@candle.pha.pa.us>
185+
Subject: Re: [BUGS] Bug #513: union all changes char(3) column definition
186+
In-Reply-To: <28079.1006402117@sss.pgh.pa.us> "from Tom Lane at Nov 21, 2001
187+
11:08:37 pm"
188+
To: Tom Lane <tgl@sss.pgh.pa.us>
189+
Date: Wed, 21 Nov 2001 23:13:28 -0500 (EST)
190+
cc: Peter Eisentraut <peter_e@gmx.net>,
191+
PostgreSQL Development <pgsql-hackers@postgresql.org>,
192+
stiening@cannon.astro.umass.edu, pgsql-bugs@postgresql.org
193+
X-Mailer: ELM [version 2.4ME+ PL90 (25)]
194+
MIME-Version: 1.0
195+
Content-Transfer-Encoding: 7bit
196+
Content-Type: text/plain; charset=US-ASCII
197+
Precedence: bulk
198+
Sender: pgsql-bugs-owner@postgresql.org
199+
Status: OR
200+
201+
> How many special cases like that do you want to put into the allegedly
202+
> datatype-independent CREATE TABLE code?
203+
>
204+
> If I thought this were the only case then I'd not object ... but it
205+
> looks like a slippery slope from here.
206+
>
207+
> And --- it's not like replacing "bpchar" with "text" actually buys us
208+
> any useful new functionality. AFAICS it's just a cosmetic thing.
209+
>
210+
> regards, tom lane
211+
>
212+
> PS: On the other hand, we might consider attacking the problem from
213+
> the reverse direction, ie *removing* code. For example, if there
214+
> weren't redundant || operators for char and varchar, then every ||
215+
> operation would yield text, and the example we're looking at would
216+
> work the way you want for free. I've thought for awhile that we
217+
> could use a pass through pg_proc and pg_operator to remove some
218+
> entries we don't really need.
219+
220+
Can we convert bpchar to text in create table if no typmod is supplied?
221+
222+
--
223+
Bruce Momjian | http://candle.pha.pa.us
224+
pgman@candle.pha.pa.us | (610) 853-3000
225+
+ If your life is a hard drive, | 830 Blythe Avenue
226+
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
227+
228+
---------------------------(end of broadcast)---------------------------
229+
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
230+
231+
From peter_e@gmx.net Thu Nov 22 12:14:01 2001
232+
Return-path: <peter_e@gmx.net>
233+
Received: from mout02.kundenserver.de (mout02.kundenserver.de [195.20.224.133])
234+
by candle.pha.pa.us (8.11.6/8.10.1) with ESMTP id fAMHE0v13505
235+
for <pgman@candle.pha.pa.us>; Thu, 22 Nov 2001 12:14:00 -0500 (EST)
236+
Received: from [195.20.224.204] (helo=mrvdom00.schlund.de)
237+
by mout02.kundenserver.de with esmtp (Exim 2.12 #2)
238+
id 166xQB-0005p4-00; Thu, 22 Nov 2001 18:13:55 +0100
239+
Received: from p3e9e70dc.dip0.t-ipconnect.de ([62.158.112.220])
240+
by mrvdom00.schlund.de with esmtp (Exim 2.12 #2)
241+
id 166xQ9-00065m-00; Thu, 22 Nov 2001 18:13:53 +0100
242+
Date: Thu, 22 Nov 2001 18:21:17 +0100 (CET)
243+
From: Peter Eisentraut <peter_e@gmx.net>
244+
X-Sender: <peter@peter.localdomain>
245+
To: Tom Lane <tgl@sss.pgh.pa.us>
246+
cc: Bruce Momjian <pgman@candle.pha.pa.us>,
247+
PostgreSQL Development <pgsql-hackers@postgresql.org>
248+
Subject: Re: [BUGS] Bug #513: union all changes char(3) column definition
249+
In-Reply-To: <27975.1006401064@sss.pgh.pa.us>
250+
Message-ID: <Pine.LNX.4.30.0111221803230.766-100000@peter.localdomain>
251+
MIME-Version: 1.0
252+
Content-Type: TEXT/PLAIN; charset=US-ASCII
253+
Status: OR
254+
255+
Tom Lane writes:
256+
257+
> The argument here is about how much intelligence it's reasonable to
258+
> expect the system to have. It's very clearly not feasible to derive
259+
> a length limit automagically in every case. How hard should we try?
260+
261+
I would like to know what Proprietary database #1 does with
262+
263+
CREATE TABLE one ( a bit(6) );
264+
INSERT INTO one VALUES ( b'101101' );
265+
CREATE TABLE two ( b bit(4) );
266+
INSERT INTO two VALUES ( b'0110' );
267+
CREATE TABLE three AS SELECT a FROM one UNION SELECT b FROM two;
268+
269+
According to SQL92, clause 9.3, the result type of the union is bit(6).
270+
However, it's not possible to store a bit(4) value into a bit(6) field.
271+
Our current solution, "bit(<nothing>)" is even worse because it has no
272+
real semantics at all (but you can store bit(<anything>) in it,
273+
interestingly).
274+
275+
--
276+
Peter Eisentraut peter_e@gmx.net
277+
278+

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp