Why the weak key is created as unique
Example:
mydb=> create table AAA (a serial primary key);
NOTICE: CREATE TABLE will create implicit sequence 'aaa_a_seq' for SERIAL column 'aaa.a'
NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'aaa_pkey' for table 'aaa'
CREATE
mydb=> create table BBB (a serial references AAA, b integer, primary key(a,b));
NOTICE: CREATE TABLE will create implicit sequence 'bbb_a_seq' for SERIAL column 'bbb.a'
NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'bbb_pkey' for table 'bbb'
NOTICE: CREATE TABLE/UNIQUE will create implicit index 'bbb_a_key' for table 'bbb'
NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
CREATE
mydb=> insert into AAA values (1);
INSERT 20369 1
mydb=> insert into BBB values (1,1);
INSERT 20370 1
mydb=> insert into BBB values (1,2);
ERROR: Cannot insert a duplicate key into unique index bbb_a_key
I would like that the pair keys (a,b) was unique.
Certainly, I can remove unique index 'bbb_a_key'...
But how more correctly?
--
Marat Khairullin mailto:xmm@rambler.ru
Marat.Khairullin@f92.n5049.z2.fidonet.org
----
О©╫О©╫О©╫О©╫О©╫О©╫О©╫О©╫О©╫О©╫ О©╫О©╫О©╫О©╫О©╫ http://mail.Rambler.ru/
О©╫О©╫О©╫О©╫О©╫О©╫О©╫-О©╫О©╫О©╫О©╫О©╫О©╫О©╫ http://ad.rambler.ru/ban.clk?pg=1691&bn=9346
You probably do not want a serial in BBB since you want
to be setting the values. Use "a int references AAA" instead
I think.
Hi,
I looked through PL/pgSQL tutorial, but I can't get quotes and spaces to
work in queries executed from Pl/pgSQl. Here is an example:
create procedure get_name(varchar)
...
BEGIN
query := ''SELECT first_name || '''' '''' || last_name FROM user'';
EXECUTE query;
...
END;
...
Basically I want to get full name, i.e. first name separated with space from
the last name. If I follow the PL/pgSQL manual as shown above, I get parse
error.
What am I doing wrong?
thanks,
Oleg
Oleg,
I'm assuming that this is just a hypothetical example, as the below is
far from the fastest way to get something as simple as a name.
BEGIN
query := ''SELECT first_name || '''' '''' || last_name FROM
user'';
EXECUTE query;
...
END;
...
I'm pretty sure your quotes are correct. However, I believe "query" is
a reserved word. Try using a different variable name.
-Josh
______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology josh@agliodbs.com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco
On Fri, 5 Oct 2001, Oleg Lebedev wrote:
Hi,
I looked through PL/pgSQL tutorial, but I can't get quotes and spaces to
work in queries executed from Pl/pgSQl. Here is an example:create procedure get_name(varchar)
...
BEGIN
query := ''SELECT first_name || '''' '''' || last_name FROM user'';
EXECUTE query;
...
END;
...Basically I want to get full name, i.e. first name separated with space from
the last name. If I follow the PL/pgSQL manual as shown above, I get parse
error.
What am I doing wrong?
Odd, a script like the following works for me on both 7.1 and 7.2devel:
drop table aa;
drop function fgn(varchar);
create table aa(a varchar);
insert into aa values ('d');
create function fgn(varchar) returns text as '
DECLARE
query text;
rec record;
BEGIN
query := ''SELECT a || '''' '''' || a as bar from aa;'';
RAISE NOTICE ''%'', query;
FOR rec in EXECUTE query LOOP
return rec.bar;
END LOOP;
END;'
language 'plpgsql';
select fgn('f');
I just upgraded to 7.1 and the query works for me now.
thanks,
Oleg
Stephan Szabo wrote:
Show quoted text
On Fri, 5 Oct 2001, Oleg Lebedev wrote:
Hi,
I looked through PL/pgSQL tutorial, but I can't get quotes and spaces to
work in queries executed from Pl/pgSQl. Here is an example:create procedure get_name(varchar)
...
BEGIN
query := ''SELECT first_name || '''' '''' || last_name FROM user'';
EXECUTE query;
...
END;
...Basically I want to get full name, i.e. first name separated with space from
the last name. If I follow the PL/pgSQL manual as shown above, I get parse
error.
What am I doing wrong?Odd, a script like the following works for me on both 7.1 and 7.2devel:
drop table aa;
drop function fgn(varchar);
create table aa(a varchar);
insert into aa values ('d');
create function fgn(varchar) returns text as '
DECLARE
query text;
rec record;
BEGIN
query := ''SELECT a || '''' '''' || a as bar from aa;'';
RAISE NOTICE ''%'', query;
FOR rec in EXECUTE query LOOP
return rec.bar;
END LOOP;
END;'
language 'plpgsql';
select fgn('f');