Re: [SQL] DEFAULT Constraint based on table type?
On Mon, 2005-11-28 at 14:22 -0600, Announce wrote: > Lets say I have the following tables. > > CREATE TABLE animals(id primary key, name varchar, type varchar); > CREATE TABLE dogs (breed varchar)INHERITS (animals); > CREATE TABLE birds (bool hasFeathers) INHERITS (animals); > > Is there a way I can specify a default on the child table that will populate > the 'type' column? For example, if I am inserting a row in table DOGS, I > would always want the default value for column TYPE to be 'DOG'. If I am > inserting into BIRDS type 'BIRD'. > > I know that I could add individual triggers on each table that set the TYPE > field to a default value on insert but I wanted a more simple solution like > setting a DEFAULT table-constraint. > > Also, In java, this could be done on a parent object by overriding a > constructor or method, using the Class object or instanceof. Is there > anyway for a table to "know" it's "class" in this scenario? SELECT tableoid::regproc, * from animals; -- Oliver Elphick olly@lfix.co.uk Isle of Wight http://www.lfix.co.uk/oliver GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA Do you want to know God? http://www.lfix.co.uk/knowing_god.html ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] DEFAULT Constraint based on table type?
On Tue, 2005-11-29 at 10:31 +, Oliver Elphick wrote: > SELECT tableoid::regproc, * from animals; regproc should be regclass; sorry. -- Oliver Elphick olly@lfix.co.uk Isle of Wight http://www.lfix.co.uk/oliver GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA Do you want to know God? http://www.lfix.co.uk/knowing_god.html ---(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] How to change database owner in PostgreSQL 7.4?
Quoting Andreas Kretschmer <[EMAIL PROTECTED]>: > frank church <[EMAIL PROTECTED]> schrieb: > > > Hi guys, > > > > What is the command change database owner in PostgreSQL 7.4? > > ALTER DATABASE foo OWNER TO blob; I tried that and it didn't work. That command appears to be available only in 8.0. Is there a way of updating the system tables to change it? > In general: start psql and type '\h alter database', and, more general, > '\h' and '\?' > > > HTH, Andreas > -- > Really, I'm not out to destroy Microsoft. That will just be a completely > unintentional side effect. (Linus Torvalds) > Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° > > ---(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 > This message was sent using IMP, the Internet Messaging Program. ---(end of broadcast)--- TIP 6: explain analyze is your friend
[SQL] Strange bug
I just noticed that I accidentally got a duplicate id. My definitions are here: CREATE TABLE citations ( citation_id INTEGER PRIMARY KEY, source_fk INTEGER REFERENCES sources (source_id) ); CREATE TABLE relation_citations ( relation_fk INTEGER REFERENCES relations (relation_id) ) INHERITS (citations); ALTER TABLE relation_citations ADD CONSTRAINT source_relation_citation UNIQUE (source_fk, relation_fk); CREATE INDEX cit_relation_key ON relation_citations (relation_fk); CREATE TABLE event_citations ( event_fkINTEGER REFERENCES events (event_id) ) INHERITS (citations); ALTER TABLE event_citations ADD CONSTRAINT source_event_citation UNIQUE (source_fk, event_fk); CREATE INDEX cit_event_key ON event_citations (event_fk); And here is the accident: pgslekt=> insert into relation_citations values (64062,4578,20017); INSERT 1478990 1 pgslekt=> insert into relation_citations values (64062,4578,20018); INSERT 1478991 1 I got an error when I transferred the data to my Web database running MySQL: ERROR 1062 at line 19839 in file: 'ss_relation_citations.sql': Duplicate entry '64062' for key 1 How can this happen? -- Leif Biberg Kristensen | Registered Linux User #338009 http://solumslekt.org/ | Cruising with Gentoo/KDE ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Strange bug
"Leif B. Kristensen" <[EMAIL PROTECTED]> writes: > I just noticed that I accidentally got a duplicate id. My > definitions are here: > CREATE TABLE citations ( > citation_id INTEGER PRIMARY KEY, > source_fk INTEGER REFERENCES sources (source_id) > ); > CREATE TABLE relation_citations ( > relation_fk INTEGER REFERENCES relations (relation_id) > ) INHERITS (citations); relation_citations doesn't have a primary key. See http://www.postgresql.org/docs/8.1/static/ddl-inherit.html particularly the "caveats" section. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Strange bug
am 29.11.2005, um 15:31:30 +0100 mailte Leif B. Kristensen folgendes: > CREATE TABLE citations ( > citation_id INTEGER PRIMARY KEY, > source_fk INTEGER REFERENCES sources (source_id) > ); > > CREATE TABLE relation_citations ( > relation_fk INTEGER REFERENCES relations (relation_id) > ) INHERITS (citations); You are using inheritation in PostgreSQL. > > ALTER TABLE relation_citations > ADD CONSTRAINT source_relation_citation UNIQUE > (source_fk, relation_fk); > CREATE INDEX cit_relation_key ON relation_citations (relation_fk); > > And here is the accident: > > pgslekt=> insert into relation_citations values (64062,4578,20017); > INSERT 1478990 1 > pgslekt=> insert into relation_citations values (64062,4578,20018); > INSERT 1478991 1 Right, the uniq contraints are on (source_fk, relation_fk). No problem. > > I got an error when I transferred the data to my Web database running > MySQL: MySQL is a other RDBMS. You can't expect that all features from PostgreSQL are working with MySQL. > > ERROR 1062 at line 19839 in file: 'ss_relation_citations.sql': Duplicate > entry '64062' for key 1 My guess: MySQL can't handle inheritance. HTH, Andreas -- Andreas Kretschmer(Kontakt: siehe Header) Heynitz: 035242/47212, D1: 0160/7141639 GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net ===Schollglas Unternehmensgruppe=== ---(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] Strange bug
On Tuesday 29 November 2005 15:37, Tom Lane wrote: >relation_citations doesn't have a primary key. See >http://www.postgresql.org/docs/8.1/static/ddl-inherit.html >particularly the "caveats" section. Uh-oh. That's my first 'gotcha' in PostgreSQL. I added the following constraints: ALTER TABLE relation_citations ADD CONSTRAINT unique_relation_citation_id UNIQUE (citation_id); ALTER TABLE event_citations ADD CONSTRAINT unique_event_citation_id UNIQUE (citation_id); And I hope that will be sufficient. -- Leif Biberg Kristensen | Registered Linux User #338009 http://solumslekt.org/ | Cruising with Gentoo/KDE ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Strange bug
On Tuesday 29 November 2005 15:43, A. Kretschmer wrote: >> I got an error when I transferred the data to my Web database >> running MySQL: > >MySQL is a other RDBMS. You can't expect that all features from >PostgreSQL are working with MySQL. > >> ERROR 1062 at line 19839 in file: 'ss_relation_citations.sql': >> Duplicate entry '64062' for key 1 > >My guess: MySQL can't handle inheritance. No, it doesn't. Therefore, I have a quite different table structure in the MySQL database, and it caught the error because of the very fact that the citation_id is a genuine primary key in the receiving MySQL table. -- Leif Biberg Kristensen | Registered Linux User #338009 http://solumslekt.org/ | Cruising with Gentoo/KDE ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Strange bug
On Tuesday 29 November 2005 15:52, Leif B. Kristensen wrote: >Uh-oh. That's my first 'gotcha' in PostgreSQL. > >I added the following constraints: I probably should drop both the inheritance and the citation_id altogether, and operate with two separate tables: CREATE TABLE relation_citations ( relation_fk INTEGER REFERENCES relations (relation_id), source_fk INTEGER REFERENCES sources (source_id), CONSTRAINT PRIMARY KEY (relation_fk, source_fk) ); CREATE TABLE event_citations ( event_fk INTEGER REFERENCES events (event_id), source_fk INTEGER REFERENCES sources (source_id), CONSTRAINT PRIMARY KEY (event_fk, source_fk) ); Is there an easy and non-disruptive way to do this? -- Leif Biberg Kristensen | Registered Linux User #338009 http://solumslekt.org/ | Cruising with Gentoo/KDE ---(end of broadcast)--- TIP 6: explain analyze is your friend
[SQL] 'AS' in 'DELETE/UPDATE'
Hi.
Maybe I miss something but I can't use 'AS' with 'DELETE' (7.4.3)
Example:
db=# SELECT * FROM temp1 ;
host_id | user_id | raw | uniq
-+-+-+--
2 | 1 | 125 | 85
2 | 2 | 100 | 50
(2 rows)
And there is temp2 just like temp1.
db=# DELETE FROM temp1 AS t1 WHERE EXISTS (SELECT 1 FROM temp2 AS t2
WHERE t2.host_id = t1.host_id AND t2.user_id = t1.user_id);
ERROR: syntax error at or near "AS" at character 19
but
db=# DELETE FROM temp1 WHERE EXISTS (SELECT 1 FROM temp2 AS t2 WHERE
t2.host_id = temp1.host_id AND t2.user_id = temp1.user_id);
DELETE 1
db=# SELECT * FROM temp1 ;
host_id | user_id | raw | uniq
-+-+-+--
2 | 1 | 125 | 85
(1 row)
It make me supply full name of table...
Another example with UPDATE
db=# UPDATE referer_total AS ref SET ref.raw = ref.raw + u.raw,
ref.uniq = ref.uniq + u.uniq FROM temp1 AS u
WHERE u.user_id = ref.user_id AND ref.referer = u.referer;
ERROR: syntax error at or near "AS" at character 22
db=# UPDATE referer_total SET
db-#referer_total.raw = referer_total.raw + u.raw,
db-#referer_total.uniq = referer_total.uniq + u.uniq
db-# FROM temp1 AS u WHERE u.user_id = referer_total.user_id
db-# AND referer_total.referer = u.referer;
ERROR: syntax error at or near "." at character 46
So it make me rename temp1's "raw" to something else ("r"), "uniq"
too; and 'AS' not possible too. And finally, working version:
UPDATE referer_total SET raw = raw + r, uniq = uniq + u
FROM temp1 AS u WHERE
u.user_id = referer_total.user_id AND
referer_total.referer = u.referer;
It looks strange, are there any limitations or something else that
make it not possible to use 'AS' in 'DELETE' and 'UPDATE'? Of course
with described workarounds I can eliminate that problems, but I want
to know is it so in 8.x? Or why, if it right behaviour?
--
engineer
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [SQL] 'AS' in 'DELETE/UPDATE'
TODO has:
o Allow an alias to be provided for the target table in
UPDATE/DELETE
This is not SQL-spec but many DBMSs allow it.
so we want to add this capability some day.
---
[EMAIL PROTECTED] wrote:
> Hi.
>
> Maybe I miss something but I can't use 'AS' with 'DELETE' (7.4.3)
> Example:
>
> db=# SELECT * FROM temp1 ;
> host_id | user_id | raw | uniq
> -+-+-+--
>2 | 1 | 125 | 85
>2 | 2 | 100 | 50
> (2 rows)
>
> And there is temp2 just like temp1.
>
> db=# DELETE FROM temp1 AS t1 WHERE EXISTS (SELECT 1 FROM temp2 AS t2
> WHERE t2.host_id = t1.host_id AND t2.user_id = t1.user_id);
> ERROR: syntax error at or near "AS" at character 19
>
> but
>
> db=# DELETE FROM temp1 WHERE EXISTS (SELECT 1 FROM temp2 AS t2 WHERE
> t2.host_id = temp1.host_id AND t2.user_id = temp1.user_id);
> DELETE 1
> db=# SELECT * FROM temp1 ;
> host_id | user_id | raw | uniq
> -+-+-+--
>2 | 1 | 125 | 85
> (1 row)
>
> It make me supply full name of table...
>
>
> Another example with UPDATE
>
> db=# UPDATE referer_total AS ref SET ref.raw = ref.raw + u.raw,
> ref.uniq = ref.uniq + u.uniq FROM temp1 AS u
> WHERE u.user_id = ref.user_id AND ref.referer = u.referer;
> ERROR: syntax error at or near "AS" at character 22
>
> db=# UPDATE referer_total SET
> db-#referer_total.raw = referer_total.raw + u.raw,
> db-#referer_total.uniq = referer_total.uniq + u.uniq
> db-# FROM temp1 AS u WHERE u.user_id = referer_total.user_id
> db-# AND referer_total.referer = u.referer;
> ERROR: syntax error at or near "." at character 46
>
> So it make me rename temp1's "raw" to something else ("r"), "uniq"
> too; and 'AS' not possible too. And finally, working version:
>
> UPDATE referer_total SET raw = raw + r, uniq = uniq + u
> FROM temp1 AS u WHERE
> u.user_id = referer_total.user_id AND
> referer_total.referer = u.referer;
>
>
> It looks strange, are there any limitations or something else that
> make it not possible to use 'AS' in 'DELETE' and 'UPDATE'? Of course
> with described workarounds I can eliminate that problems, but I want
> to know is it so in 8.x? Or why, if it right behaviour?
>
> --
> engineer
>
>
> ---(end of broadcast)---
> TIP 6: explain analyze is your friend
>
--
Bruce Momjian| http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup.| Newtown Square, Pennsylvania 19073
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [SQL] 'AS' in 'DELETE/UPDATE'
> It looks strange, are there any limitations or something else that > make it not possible to use 'AS' in 'DELETE' and 'UPDATE'? Of course > with described workarounds I can eliminate that problems, but I want > to know is it so in 8.x? Or why, if it right behaviour? > > -- > engineer > --- extracted from TODO --- o Allow an alias to be provided for the target table in UPDATE/DELETE This is not SQL-spec but many DBMSs allow it. --- end extracting --- -- regards, Jaime Casanova (DBA: DataBase Aniquilator ;) ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Strange bug
On Tuesday 29 November 2005 17:01, Leif B. Kristensen wrote: >Is there an easy and non-disruptive way to do this? For the record, I just did the following: pgslekt=> create table rel_cits ( pgslekt(> relation_fk integer references relations (relation_id), pgslekt(> source_fk integer references sources (source_id), pgslekt(> PRIMARY KEY (relation_fk, source_fk) pgslekt(> ); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "rel_cits_pkey" for table "rel_cits" CREATE TABLE pgslekt=> insert into rel_cits (select relation_fk, source_fk from relation_citations); INSERT 0 19837 pgslekt=> create table event_cits ( pgslekt(> event_fk integer references events (event_id), pgslekt(> source_fk integer references sources (source_id), pgslekt(> PRIMARY KEY (event_fk, source_fk) pgslekt(> ); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "event_cits_pkey" for table "event_cits" CREATE TABLE pgslekt=> insert into event_cits (select event_fk, source_fk from event_citations); INSERT 0 29139 pgslekt=> drop table event_citations cascade; NOTICE: drop cascades to rule _RETURN on view event_notes NOTICE: drop cascades to view event_notes DROP TABLE pgslekt=> drop table relation_citations cascade; NOTICE: drop cascades to rule _RETURN on view relation_notes NOTICE: drop cascades to view relation_notes DROP TABLE pgslekt=> drop table citations; DROP TABLE pgslekt=> alter table rel_cits rename to relation_citations; ALTER TABLE pgslekt=> alter table event_cits rename to event_citations; ALTER TABLE pgslekt=> \i views_and_functions.sql Seems simple enough :-) -- Leif Biberg Kristensen | Registered Linux User #338009 http://solumslekt.org/ | Cruising with Gentoo/KDE ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Triggers
On 11/23/2005 3:44 AM, Achilleus Mantzios wrote: O Neil Saunders έγραψε στις Nov 22, 2005 : And change AFER INSERT to BEFORE INSERT 1) it doesnt make any difference since we are updating a different table than the trigger's one In this particular case it doesn't. In general, another BEFORE trigger can still modify the new row, so your trigger might not see the final values in NEW. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 6: explain analyze is your friend
