Re: [SQL] Is this a bug? Deleting a column deletes the constraint.
On Thu, 2006-10-12 at 01:25 -0400, Tom Lane wrote: > It does seem like this is wrong, in view of SQL92's statement about > ALTER TABLE DROP COLUMN: > > 4) If RESTRICT is specified, then C shall not be referenced in > the of any view descriptor or in the condition> of any constraint descriptor other than a table con- > straint descriptor that contains references to no other column > and that is included in the table descriptor of T. > > IOW we should only allow unique constraints to be auto-dropped if > they reference just the one single column. Ick. I didn't realize before that you can also drop all columns, leaving a table without *any* columns. Is that a SQL92 "feature"? Joe ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Is this a bug? Deleting a column deletes the constraint.
Joe <[EMAIL PROTECTED]> writes: > I didn't realize before that you can also drop all columns, leaving a > table without *any* columns. Is that a SQL92 "feature"? See the ALTER TABLE reference page: ALTER TABLE DROP COLUMN can be used to drop the only column of a table, leaving a zero-column table. This is an extension of SQL, which disallows zero-column tables. The CREATE TABLE reference page further amplifies: PostgreSQL allows a table of no columns to be created (for example, CREATE TABLE foo();). This is an extension from the SQL standard, which does not allow zero-column tables. Zero-column tables are not in themselves very useful, but disallowing them creates odd special cases for ALTER TABLE DROP COLUMN, so it seems cleaner to ignore this spec restriction. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Is this a bug? Deleting a column deletes the constraint.
[EMAIL PROTECTED] (Joe) writes: > On Thu, 2006-10-12 at 01:25 -0400, Tom Lane wrote: >> It does seem like this is wrong, in view of SQL92's statement about >> ALTER TABLE DROP COLUMN: >> >> 4) If RESTRICT is specified, then C shall not be referenced in >> the of any view descriptor or in the > condition> of any constraint descriptor other than a table con- >> straint descriptor that contains references to no other column >> and that is included in the table descriptor of T. >> >> IOW we should only allow unique constraints to be auto-dropped if >> they reference just the one single column. Ick. > > I didn't realize before that you can also drop all columns, leaving a > table without *any* columns. Is that a SQL92 "feature"? You can create a table without any columns. tutorial=# create table empty_table (); CREATE TABLE tutorial=# \d empty_table Table "public.empty_table" Column | Type | Modifiers +--+--- tutorial=# select * from empty_table ; -- (0 rows) A table without columns is of pretty limited usefulness, until you add some columns, but having a form of "zero" seems not insensible... -- let name="cbbrowne" and tld="acm.org" in String.concat "@" [name;tld];; http://www3.sympatico.ca/cbbrowne/lisp.html They are called computers simply because computation is the only significant job that has so far been given to them. -- Louis Ridenour ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Is this a bug? Deleting a column deletes the constraint.
* Tom Lane: > The CREATE TABLE reference page further amplifies: > > PostgreSQL allows a table of no columns to be created (for example, > CREATE TABLE foo();). This is an extension from the SQL standard, which > does not allow zero-column tables. Zero-column tables are not in > themselves very useful, but disallowing them creates odd special cases > for ALTER TABLE DROP COLUMN, so it seems cleaner to ignore this spec > restriction. And you need the syntax for table partitioning. -- Florian Weimer<[EMAIL PROTECTED]> BFK edv-consulting GmbH http://www.bfk.de/ Durlacher Allee 47tel: +49-721-96201-1 D-76131 Karlsruhe fax: +49-721-96201-99 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[SQL] spliting a row to make several rows
Hi all: What a want to do is something like this: suppose i have this record aa--bb--cc I guess if im able to do some sql/plsql procedure to get something like it aa bb cc (3 records, rigth?) Thanks a lot Gerardo ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] spliting a row to make several rows
Gerardo Herzig wrote:
Hi all: What a want to do is something like this:
suppose i have this record
aa--bb--cc
I guess if im able to do some sql/plsql procedure to get something like it
aa
bb
cc
(3 records, rigth?)
Thanks a lot
Gerardo
dev=#select split_to_rows('aa--bb--cc','--');
split_to_rows
---
aa
bb
cc
(3 rows)
This function was written by David Fetter,
http://archives.postgresql.org/pgsql-general/2005-12/msg00080.php
CREATE OR REPLACE FUNCTION split_to_rows(TEXT,TEXT) RETURNS SETOF TEXT
AS $$
SELECT (string_to_array($1, $2))[s.i]
FROM generate_series(
1,
array_upper(string_to_array($1, $2), 1)
) AS s(i);
$$ language sql strict;
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [SQL] spliting a row to make several rows
Cool!! Thanks a lot! I will try it as soon as possible
Gerardo
Gerardo Herzig wrote:
Hi all: What a want to do is something like this:
suppose i have this record
aa--bb--cc
I guess if im able to do some sql/plsql procedure to get something
like it
aa
bb
cc
(3 records, rigth?)
Thanks a lot
Gerardo
dev=#select split_to_rows('aa--bb--cc','--');
split_to_rows
---
aa
bb
cc
(3 rows)
This function was written by David Fetter,
http://archives.postgresql.org/pgsql-general/2005-12/msg00080.php
CREATE OR REPLACE FUNCTION split_to_rows(TEXT,TEXT) RETURNS SETOF TEXT
AS $$
SELECT (string_to_array($1, $2))[s.i]
FROM generate_series(
1,
array_upper(string_to_array($1, $2), 1)
) AS s(i);
$$ language sql strict;
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
