[SQL] How to have a unique primary key on two tables
Hi everybody, I need to have a primary key which has to be unique on two tables. E.g.: CREATE TABLE first ( id serial NOT NULL, testo text, ) CREATE TABLE second ( id serial NOT NULL, testo text, ) When I insert some text on "first", I would like first.id = second.id + 1, and vice versa. A sort of primary key in common for both tables. Any hints? Thank you very much -- Daniel "bodom_lx" Graziotin - http://daniel.graziotin.net - http://daniel.graziotin.net/bodom_lx.asc - GPG public key ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] How to have a unique primary key on two tables
When you use serial a kind of macro is performed: in fact an integer field is
created, a sequence is created with a name based on the table's name and the
nextval of that sequence is used as the default value for the field. Now you
have to do these steps "manually".
CREATE SEQUENCE "public"."tbl_all_ID_seq" INCREMENT 1 MINVALUE 1 START 1 CACHE
1;
CREATE TABLE tbl_first (
id INTEGER DEFAULT nextval('public."tbl_all_ID_seq"'::text) NOT NULL,
testo text
);
CREATE TABLE tbl_second (
id INTEGER DEFAULT nextval('public."tbl_all_ID_seq"'::text) NOT NULL,
testo text
);
>>> "Daniel "bodom_lx" Graziotin" <[EMAIL PROTECTED]> 2007-11-22 12:01 >>>
Hi everybody,
I need to have a primary key which has to be unique on two tables.
E.g.:
CREATE TABLE first
(
id serial NOT NULL,
testo text,
)
CREATE TABLE second
(
id serial NOT NULL,
testo text,
)
When I insert some text on "first", I would like first.id = second.id
+ 1, and vice versa.
A sort of primary key in common for both tables.
Any hints?
Thank you very much
--
Daniel "bodom_lx" Graziotin
- http://daniel.graziotin.net ( http://daniel.graziotin.net/ )
- http://daniel.graziotin.net/bodom_lx.asc - GPG public key
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [SQL] How to have a unique primary key on two tables
am Thu, dem 22.11.2007, um 12:01:59 +0100 mailte Daniel bodom_lx Graziotin folgendes: > Hi everybody, > I need to have a primary key which has to be unique on two tables. > E.g.: > > CREATE TABLE first > ( > id serial NOT NULL, > testo text, > ) > > CREATE TABLE second > ( > id serial NOT NULL, > testo text, > ) > > When I insert some text on "first", I would like first.id = second.id > + 1, and vice versa. > A sort of primary key in common for both tables. > > Any hints? Yes. Create a SEQUENCE and use nextval(sequence) as primary key on both tables. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(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] Loading 8.2 data into 8.1
Am Dienstag, 20. November 2007 schrieb Andreas Joseph Krogh: > Is it considered "safe" to use 8.1's pg_dump to dump an 8.2-db and load it > into 8.1? No, pg_dump will complain if you try that. It could work, with manual fixups perhaps, but it is far from "safe". -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Bad Schema Design or Useful Trick?
Richard Broersma Jr wrote: Below I've included sample table definitions for a vertically partitioned disjunctive table hierarchy. I wanted to point out the use of the composite primary key declaration that is applied to two columns that are clearly not a candidate key. However, using the badly defined primary key allows for referential integrity to nicely handle the de-normalization between the main table and sub tables that is inherent with this type of data model. Would using a primary key in this manner be a decision that I will regret in the long run? If so, can any explain why? The parent table is parts with the child table pumps and hardware. CREATE TABLE Parts ( part_nbr varchar( 100 ) UNIQUE NOT NULL, part_type varchar( 20 ) NOT NULL, ... PRIMARY KEY( part_nbr, part_type ), ... So - what you're saying is that because part_type depends on part_nbr it shouldn't be part of the key, but because you want to search by part-type in the referencing tables it makes life easier. Will you regret this? Probably - I always seem to end regretting making short-cuts, although in this case I can't see any direct harm that could occur. I'd probably make (part_nbr) the pkey and have a separate unique constraint on (part_nbr,part_type) that I reference. That "feels" better , although I'm not sure it actually gains you anything. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] How to have a unique primary key on two tables
On Thu, 22 Nov 2007 12:11:20 +0100 "Bart Degryse" <[EMAIL PROTECTED]> wrote: > When you use serial a kind of macro is performed: in fact an integer field is > created, a sequence is created with a name based on the table's name and the > nextval of that sequence is used as the default value for the field. Now you > have to do these steps "manually". The second part, if you really mean the ID to be like a primary key is to put a constraint on the tables to assure that an ID in one does not exist in the other. That may sound like overkill if the situation can "never" occur but it doesn't hurt to program defensively. -- D'Arcy J.M. Cain <[EMAIL PROTECTED]> | Democracy is three wolves http://www.druid.net/darcy/| and a sheep voting on +1 416 425 1212 (DoD#0082)(eNTP) | what's for dinner. ---(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] dynmic column names inside trigger?
On Wed, Nov 21, 2007 at 09:14:14AM +0100, Bart Degryse wrote: > I would do something like this (not tested, but conceptually working): Hello, > BEGIN > if old.story is not null and new.story != old.story then > new.story = sanitize_text(new.story); > end if; > --checks on other field can be included here, eg > if old.otherfield is not null and new.otherfield != old.otherfield then > new.otherfield = sanitize_text(new.otherfield); > end if; But if I test a non-existent column for not being null I will have an exception, no? Otherwise this is a nice way of doing it. Thanks, ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] dynmic column names inside trigger?
On Tue, Nov 20, 2007 at 11:56:02AM -0500, Tom Lane wrote: > Louis-David Mitterrand <[EMAIL PROTECTED]> writes: > > I'd like to use it on other tables an columns but how can the column > > name be dynamic inside the procedure. > > It can't --- plpgsql has no support for that. You could probably make > it work in some of the other PL languages, such as plperl or pltcl, > which are less strongly typed. Hi Tom, What the performance penality of using plperl vs. plpgsql ? Thanks, ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] dynmic column names inside trigger?
I don't really see how you could test a non-existing column. Here > if old.story is not null and new.story != old.story then > new.story = sanitize_text(new.story); you would always use fields from OLD and NEW otherwise you can't even create the trigger. If a table has 3 fields (field1, field2 and field3) then why would you create a trigger in which you do something with field4. I haven't tried (because it seems so absurd) but I expect PG to refuse to create such a trigger. >>> Louis-David Mitterrand <[EMAIL PROTECTED]> 2007-11-22 14:54 >>> On Wed, Nov 21, 2007 at 09:14:14AM +0100, Bart Degryse wrote: > I would do something like this (not tested, but conceptually working): Hello, > BEGIN > if old.story is not null and new.story != old.story then > new.story = sanitize_text(new.story); > end if; > --checks on other field can be included here, eg > if old.otherfield is not null and new.otherfield != old.otherfield then > new.otherfield = sanitize_text(new.otherfield); > end if; But if I test a non-existent column for not being null I will have an exception, no? Otherwise this is a nice way of doing it. Thanks, ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] SQL state: 22P02
Hi,
I got the message "SQL state: 22P02"
to produce:
create table test (num varchar(20));
insert into test (num) values (null);
select * from test where cast(num as int8) = 0; --ok, no error
insert into test (num) values ('123123');
select * from test where cast(num as int8) = 123123; --ok, no error
insert into test (num) values (' ');
select * from test where cast(num as int8) = 123123; -- error
select * from test where case when trim(num) <> '' then cast(num
as int8) = 123123 else false end; -- ok, changed query and no error
insert into test (num) values ('a');
select * from test where cast(num as int8) = 123123; -- error
select * from test where case when trim(num) <> '' then cast(num
as int8) = 123123 else false end; -- error
I need get all tuples was the table have converting the characters to
number. If one conversion fail there not displayed.
Ex:
num return
--
0 0
null false
12 12
a false
12ab false
it´s possible get these results ?
other question:
Why this message ("SQL
state: 22P02") is not
displayed with the SQL command on log in data/pg_log ?
only appears
2007-11-22 12:55:13 BRT ERROR: invalid input syntax for integer: "a"
thanks,
Franklin Haut
Re: [SQL] SQL state: 22P02
On Nov 22, 2007 11:24 AM, Franklin Haut <[EMAIL PROTECTED]> wrote:
>num return
> --
>0 0
>null false
>1212
>a false
>12ab false
>
> it´s possible get these results ?
Try:
SELECT
NUM
, CASE
WHEN TRIM(NUM) ~ '^[0-9]+(.[0-9]+){0,1}$'
THEN NUM
ELSE 'false'
END AS RETURN
FROM TEST;
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] PL argument max size, and doubt
Tom Lane escribió: > Martin Marques <[EMAIL PROTECTED]> writes: > >> I have always heard that modification queries should be EXECUTED in PL. >> AFAICR. > > Run far away from whatever source gave you that advice... Sorry, it was with DDL commands. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] PL argument max size, and doubt
Martin Marques <[EMAIL PROTECTED]> writes: > Tom Lane escribió: >> Martin Marques <[EMAIL PROTECTED]> writes: >>> I have always heard that modification queries should be EXECUTED in PL. >>> AFAICR. >> >> Run far away from whatever source gave you that advice... > Sorry, it was with DDL commands. That's not much better ;-). DDL commands don't have plans, so there's not anything that could be invalidated. I don't see any reason to use an EXECUTE unless there's an actual textual change in the command you need to execute. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
