[SQL] Re: [GENERAL] How to Insert and retrieve multilingual (Hindi "an Indian language") into PostgreSQL

[Thom Brown]

On 22 June 2010 11:48, venkat  wrote:
> Dear All,
>
>   I want to insert and retrieve multilingual (Hindi) into
> database.is PostgreSQL supports that ?if it is ... please guide me how to
> enable multilingual in the table.
>
>     I am waiting for your great response.
> Thanks and Regards,
> Venkat

Do you just mean storing Devanagari text or storing multiple languages
alongside one another?  If the former, I think you'll just need to set
the database encoding to UTF8.

If it's the latter, this may be useful to you:

http://rwec.co.uk/blog/2009/11/atomic-translations-part-1/
http://rwec.co.uk/blog/2009/12/atomic-translations-part-2/

Regards

Thom

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[SQL] Re: How to Insert and retrieve multilingual (Hindi "an Indian language") into PostgreSQL

[Stephane Bortzmeyer]

On Tue, Jun 22, 2010 at 04:18:48PM +0530,
 venkat  wrote 
 a message of 39 lines which said:

> I want to insert and retrieve multilingual (Hindi) into database.is
> PostgreSQL supports that ?

[Currently, I'm storing arabic texts in a PostgreSQL database.
]

Actually, PostgreSQL does not know about languages (except for
services like full-text search): it stores text, period. For a long
time, PostgreSQL was able to store texts in various encodings. The
recommended one is UTF-8, which allows you to store the whole Unicode
character set.

% createdb --encoding=UTF-8 venkat

% psql venkat
venkat=> CREATE TABLE Misc (id SERIAL, content TEXT);
NOTICE:  CREATE TABLE will create implicit sequence "misc_id_seq" for serial 
column "misc.id"
CREATE TABLE
venkat=> INSERT INTO Misc (content) VALUES ('Texte en français');
INSERT 0 1


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] COPY command and required file permissions

[bruno.scovoli]

I have an script with many "\copy" commands. For example:

\copy privilegios from '/work/eclipse-workspace/Canoan
Server/database/load/privilegios.dat'
\copy privilegios_de_usuarios from '/work/eclipse-workspace/Canoan
Server/database/load/privilegios_de_usuarios.dat'
\copy classificacoes from '/work/eclipse-workspace/Canoan
Server/database/load/classificacoes.dat'
\copy tipo_de_produto from '/work/eclipse-workspace/Canoan
Server/database/load/tipos.dat'

This lines are OK. But when I remove the preceding backslash from them I get
the error that you mentioned.


Harold A. Giménez Ch. wrote:
> 
> Hi all,
> In migrating an application from sql server to Postgres, I created a ruby
> script that extracts csv files from sql server (from a windows box), then
> SCPs them into a directory (/home/ruby_process) on the server running
> Postgres (a Fedora core 8) and finally runs the Postgres COPY command for
> each of the csv files.
> 
> When the script runs the COPY commnand, I get the following error (for the
> genders table):
> 
> ERRORC42501  M could not open file "/home/ruby_process/genders.csv"
> for
> reading: Permission denied Fcopy.c L1694   RCopyFrom (RuntimeError)
> 
> My question is, what are the set of permissions required to perform a
> postgres copy?
> 
> I've set the permissions on the directory so that the postgres group owns
> the directory and has read and execute permissions, as follows:
> drwxrwxr-x 3 ruby_process postgres
> 
> I have also tried moving the files to /tmp and performing the copy from
> there, unsuccesfuly.
> 
> Any ideas would be appreciated. Thanks,
> -Harold
> 
> 

-- 
View this message in context: 
http://old.nabble.com/COPY-command-and-required-file-permissions-tp17800484p28976565.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[SQL] Average of Array?

[Lee Hachadoorian]

Is there a function that returns the average of the elements of an
array? I'm thinking of something that would work like the avg()
aggregate function where it returns the average of all non-NULL
values. Can't find anything like it in the docs, and I'd like to make
sure I'm not missing something.

Thanks,

-- 
Lee Hachadoorian
PhD Student, Geography
Program in Earth & Environmental Sciences
CUNY Graduate Center

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Average of Array?

[Tom Lane]

Lee Hachadoorian  writes:
> Is there a function that returns the average of the elements of an
> array? I'm thinking of something that would work like the avg()
> aggregate function where it returns the average of all non-NULL
> values. Can't find anything like it in the docs, and I'd like to make
> sure I'm not missing something.

You could unnest() the array and then apply the aggregate to the result.
unnest() is only built in in 8.4 and later, but it's not terribly hard
to write your own in previous versions.

regards, tom lane

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Average of Array?

[Pavel Stehule]

Hello

2010/6/25 Lee Hachadoorian :
> Is there a function that returns the average of the elements of an
> array? I'm thinking of something that would work like the avg()
> aggregate function where it returns the average of all non-NULL
> values. Can't find anything like it in the docs, and I'd like to make
> sure I'm not missing something.

it doesn't exists, but it is simple to develop it

CREATE OR REPLACE FUNCTION array_avg(double precision[])
RETURNS double precision AS $$
SELECT avg(v) FROM unnest($1) g(v)
$$ LANGUAGE sql;

Regards

Pavel Stehule

>
> Thanks,
>
> --
> Lee Hachadoorian
> PhD Student, Geography
> Program in Earth & Environmental Sciences
> CUNY Graduate Center
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Average of Array?

[Lee Hachadoorian]

Thanks Tom & Pavel, these are very helpful.

On Fri, Jun 25, 2010 at 12:29 PM, Pavel Stehule  wrote:
> Hello
>
> 2010/6/25 Lee Hachadoorian :
>> Is there a function that returns the average of the elements of an
>> array? I'm thinking of something that would work like the avg()
>> aggregate function where it returns the average of all non-NULL
>> values. Can't find anything like it in the docs, and I'd like to make
>> sure I'm not missing something.
>
> it doesn't exists, but it is simple to develop it
>
> CREATE OR REPLACE FUNCTION array_avg(double precision[])
> RETURNS double precision AS $$
> SELECT avg(v) FROM unnest($1) g(v)
> $$ LANGUAGE sql;
>
> Regards
>
> Pavel Stehule
>
>>
>> Thanks,
>>
>> --
>> Lee Hachadoorian
>> PhD Student, Geography
>> Program in Earth & Environmental Sciences
>> CUNY Graduate Center
>>
>> --
>> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-sql
>>
>



-- 
Lee Hachadoorian
PhD Student, Geography
Program in Earth & Environmental Sciences
CUNY Graduate Center

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[SQL] enforcing constraints across multiple tables

[Andrew Geery]

I have a question about checking a constraint that is spread across multiple
(in the example below, two) tables.  In the example below, every food (food
table) is in a food group (food_group table).  For every person (person
table), I want to enforce the constraint that there can only be one food in
a given food group (person_food link table) [think of it as every person may
have a favorite food in a given food group].

The problem seems to be that the link is in the person_food table, but the
information that is needed to verify the constraint is also in the food
table (i.e., what food group is the food in?).

There are two problems here:
(1) don't allow a food to be associated with a person if there is already a
food in the same food group associated with the person; and
(2) don't allow the food group for a food to be changed if this would
violate (1)

To enforce (1), I created a function to check whether a given food can be
associated with a given person (is there already a food in the same food
group associated with the person?) and added a check constraint to the
person_food table.
To enforce (2), I wasn't able to use a check constraint because the
constraint was being checked with the existing data, not with the new data.
 I had to add an after trigger that called a function to do the check.

My questions are:
(A) Is there a way to check (2) above using a constraint and not a trigger?
(B) Is there an easier way to solve this problem?  Does the complicated
nature of the solution make the design poor?
(C) Should I not worry about this constraint at the DB level and just
enforce it at the application level?

Below are the tables, functions and triggers I was using.

Thanks!
Andrew

===

create table person (
   id serial primary key,
   name varchar not null
);

create table food_group (
   id serial primary key,
   name varchar not null
);

create table food (
   id serial primary key,
   food_group_id int not null references food_group,
   name varchar not null
);

create table person_food (
   person_id int not null references person,
   food_id int not null references food,
   primary key (person_id, food_id),
   check (is_person_food_unique(person_id, food_id))
);

create or replace function is_food_person_unique(_food_id int) returns
boolean as $$
declare cnt int;
begin
   raise notice 'food_id: %', _food_id;
   select count(*) into cnt from
  (select
  pf.person_id
   from
  person_food pf
  join food f on pf.food_id = f.id
  join (select fg.* from food_group fg left join food f on fg.id =
f.food_group_id where f.id = _food_id) food_food_group
 on f.food_group_id = food_food_group.id
   group by
  pf.person_id
  having
 count(*) > 1) s;
   return cnt = 0;
end;
$$ language plpgsql;

create or replace function is_person_food_unique(_person_id int, _food_id
int) returns boolean as $$
declare cnt int;
begin
   select
 count(*) into cnt
   from
 person_food pf
join food f on pf.food_id = f.id
join (select distinct fg.* from food_group fg left join food f
on fg.id = f.food_group_id where f.id = _food_id) food_food_group
   on f.food_group_id = food_food_group.id
where
pf.person_id = _person_id;
   return cnt = 0;
end;
$$ language plpgsql;

create or replace function check_food_trigger() RETURNS TRIGGER AS $$
begin
   if not is_food_person_unique(NEW.id) then
  raise exception 'error changing the food group to %',
NEW.food_group_id;
   end if;
   return new;
end;
$$ language plpgsql;

create trigger after_food after update on food
   for each row execute procedure check_food_trigger();

alter table person_food add constraint check_person_food_unique
check(is_person_food_unique(person_id, food_id));

insert into person values (1, 'Me');
insert into food_group values (1, 'Fruit');
insert into food_group values (2, 'Meat');
insert into food values (1, 1, 'Apple');
insert into food values (2, 1, 'Orange');
insert into food values (3, 2, 'Chicken');
insert into person_food values (1, 1);
insert into person_food  values (1, 3);
insert into person_food values (1, 2); -- correctly fails

update food set food_group_id = 1 where id = 3; -- correctly fails

Re: [SQL] COPY command and required file permissions

[Adrian Klaver]

On 06/23/2010 02:01 PM, bruno.scovoli wrote:


I have an script with many "\copy" commands. For example:

\copy privilegios from '/work/eclipse-workspace/Canoan
Server/database/load/privilegios.dat'
\copy privilegios_de_usuarios from '/work/eclipse-workspace/Canoan
Server/database/load/privilegios_de_usuarios.dat'
\copy classificacoes from '/work/eclipse-workspace/Canoan
Server/database/load/classificacoes.dat'
\copy tipo_de_produto from '/work/eclipse-workspace/Canoan
Server/database/load/tipos.dat'

This lines are OK. But when I remove the preceding backslash from them I get
the error that you mentioned.


Harold A. Giménez Ch. wrote:


Hi all,
In migrating an application from sql server to Postgres, I created a ruby
script that extracts csv files from sql server (from a windows box), then
SCPs them into a directory (/home/ruby_process) on the server running
Postgres (a Fedora core 8) and finally runs the Postgres COPY command for
each of the csv files.

When the script runs the COPY commnand, I get the following error (for the
genders table):

ERRORC42501  M could not open file "/home/ruby_process/genders.csv"
for
reading: Permission denied Fcopy.c L1694   RCopyFrom (RuntimeError)

My question is, what are the set of permissions required to perform a
postgres copy?

I've set the permissions on the directory so that the postgres group owns
the directory and has read and execute permissions, as follows:
drwxrwxr-x 3 ruby_process postgres

I have also tried moving the files to /tmp and performing the copy from
there, unsuccesfuly.

Any ideas would be appreciated. Thanks,
-Harold






Are you connecting to the database as a superuser to run the COPY command?

--
Adrian Klaver
adrian.kla...@gmail.com

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] enforcing constraints across multiple tables

[Tim Landscheidt]

Andrew Geery  wrote:

> [...]
> My questions are:
> (A) Is there a way to check (2) above using a constraint and not a trigger?
> (B) Is there an easier way to solve this problem?  Does the complicated
> nature of the solution make the design poor?
> (C) Should I not worry about this constraint at the DB level and just
> enforce it at the application level?
> [...]

I don't know about (A) and (B), but (C) is the solution of
choice in most complex cases. It's usually *much* easier to
enforce than with constraints and triggers (you have to do
it at application level anyway as well as you probably don't
want to pass PostgreSQL's errors directly to the user) and
your mind doesn't become twisted too much. I would schedule
regular tests on the data set though to ensure that you no-
tice problems early on.

Tim


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Average of Array?

[Ireneusz Pluta]

Lee Hachadoorian pisze:

Is there a function that returns the average of the elements of an
array? I'm thinking of something that would work like the avg()
aggregate function where it returns the average of all non-NULL
values. Can't find anything like it in the docs, and I'd like to make
sure I'm not missing something.

Thanks,

  
Use unnest() -> 
http://www.postgresql.org/docs/8.4/interactive/functions-array.html


# select avg(unnest) from (select unnest(ARRAY[1,2,null,3])) a;
   avg


2.
(1 row)

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[SQL] How do I remove selected words from text field?

[Frank Bax]

I'm not quite sure how to ask for the query I want, so let's start with 
data:


create table t1 (i int, val varchar);
insert into t1 values(1,'A B C D');
insert into t1 values(2,'B D E F');
insert into t1 values(3,'G H I J');
create table t2 (q varchar, z varchar);
insert into t2 values('A','vowel');
insert into t2 values('B','consonant');
insert into t2 values('E','vowel');
insert into t2 values('K','consonant');

t1.val will contain "words" separated by blanks.  It might be better if 
each "word" were a separate row in another table; but that's not how the 
legacy database was built.  I understand this can be simulated by:


select i,a[s] from (select i, generate_subscripts(string_to_array(val,' 
'),1) as s, string_to_array(val,' ') as a from t1) foo;


In my "real life" situation, the "words" are not single letters.

I'd like to write a function that removes selected "words" from t1.val 
based on select on t2.

In the above example; let's exclude all vowels, so I end up with:
1 'B C D'
2 'B D F'
3 'G H I J'

For some "words" in val; there may not be a row when joining to t2.q; 
these words must be included in final result.  In the above example; 
there is no row in t2 where q="I"; so it is included in result.


How do I write such a function?  Can it be done with SQL only?

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[SQL] Round integer division

[Lee Hachadoorian]

Is it documented anywhere that floating-point numbers round
"scientifically", that is 0.5 rounds to the nearest even number? Compare:

SELECT round(2.5::real), round(2.5::numeric), round(3.5::real),
round(3.5::numeric);

generates

2 | 3 | 4 | 4

I stumbled across this when I was trying to use round(a::real/b::real)
to generate a rounded result to dividing integers, and noticed sometimes
0.5 was truncated and sometimes it was rounded up. Couldn't find
anything about this in the archives or the data type documentation. Is
there something obvious that I'm I missing?

Thanks,

-- 

Lee Hachadoorian
PhD Student, Geography
Program in Earth & Environmental Sciences
CUNY Graduate Center


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Round integer division

[Scott Marlowe]

On Fri, Jun 25, 2010 at 6:53 PM, Lee Hachadoorian
 wrote:
> Is it documented anywhere that floating-point numbers round
> "scientifically", that is 0.5 rounds to the nearest even number? Compare:
>
> SELECT round(2.5::real), round(2.5::numeric), round(3.5::real),
> round(3.5::numeric);
>
> generates
>
> 2 | 3 | 4 | 4
>
> I stumbled across this when I was trying to use round(a::real/b::real)
> to generate a rounded result to dividing integers, and noticed sometimes
> 0.5 was truncated and sometimes it was rounded up. Couldn't find
> anything about this in the archives or the data type documentation. Is
> there something obvious that I'm I missing?

That all floating point representations are approximate?

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Round integer division

[Lee Hachadoorian]

On 06/25/2010 07:00 PM, Scott Marlowe wrote:
> That all floating point representations are approximate?
>
But if it's error due to approximation, shouldn't the result be random?
I tried this for a handful of larger numbers, and it appears to
consistently round to the even number. Wouldn't that have to be
intentionally programmed that way?

--Lee

-- 
Lee Hachadoorian
PhD Student, Geography
Program in Earth & Environmental Sciences
CUNY Graduate Center


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Round integer division

[Adrian Klaver]

On Friday 25 June 2010 3:53:01 pm Lee Hachadoorian wrote:
> Is it documented anywhere that floating-point numbers round
> "scientifically", that is 0.5 rounds to the nearest even number? Compare:
>
> SELECT round(2.5::real), round(2.5::numeric), round(3.5::real),
> round(3.5::numeric);
>
> generates
>
> 2 | 3 | 4 | 4
>
> I stumbled across this when I was trying to use round(a::real/b::real)
> to generate a rounded result to dividing integers, and noticed sometimes
> 0.5 was truncated and sometimes it was rounded up. Couldn't find
> anything about this in the archives or the data type documentation. Is
> there something obvious that I'm I missing?
>
> Thanks,
>
> --
>
> Lee Hachadoorian
> PhD Student, Geography
> Program in Earth & Environmental Sciences
> CUNY Graduate Center

See here for explanation:
http://archives.postgresql.org/pgsql-general/2010-03/msg00969.php

-- 
Adrian Klaver
adrian.kla...@gmail.com

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] How do I remove selected words from text field?

[Osvaldo Kussama]

2010/6/25 Frank Bax :
> I'm not quite sure how to ask for the query I want, so let's start with
> data:
>
> create table t1 (i int, val varchar);
> insert into t1 values(1,'A B C D');
> insert into t1 values(2,'B D E F');
> insert into t1 values(3,'G H I J');
> create table t2 (q varchar, z varchar);
> insert into t2 values('A','vowel');
> insert into t2 values('B','consonant');
> insert into t2 values('E','vowel');
> insert into t2 values('K','consonant');
>
> t1.val will contain "words" separated by blanks.  It might be better if each
> "word" were a separate row in another table; but that's not how the legacy
> database was built.  I understand this can be simulated by:
>
> select i,a[s] from (select i, generate_subscripts(string_to_array(val,'
> '),1) as s, string_to_array(val,' ') as a from t1) foo;
>
> In my "real life" situation, the "words" are not single letters.
>
> I'd like to write a function that removes selected "words" from t1.val based
> on select on t2.
> In the above example; let's exclude all vowels, so I end up with:
> 1 'B C D'
> 2 'B D F'
> 3 'G H I J'
>
> For some "words" in val; there may not be a row when joining to t2.q; these
> words must be included in final result.  In the above example; there is no
> row in t2 where q="I"; so it is included in result.
>
> How do I write such a function?  Can it be done with SQL only?
>


Try:

SELECT i, array_to_string(array_agg(foo), ' ') "Val"
FROM (SELECT * FROM (SELECT i, regexp_split_to_table(val, E'\\s+') AS
foo FROM t1) bar
LEFT OUTER JOIN t2 ON (bar.foo = t2.q) WHERE z IS DISTINCT FROM 'vowel') foobar
GROUP BY i;
 i |   Val
---+-
 1 | B C D
 3 | G H I J
 2 | B D F
(3 linhas)

Osvaldo

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Round integer division

[Lee Hachadoorian]

Thanks. That was a fairly recent post, too, but I couldn't come up with
the right keyword search to find it.

On 06/25/2010 07:25 PM, Adrian Klaver wrote:
> On Friday 25 June 2010 3:53:01 pm Lee Hachadoorian wrote:
>   
>> Is it documented anywhere that floating-point numbers round
>> "scientifically", that is 0.5 rounds to the nearest even number? 
>> 
> See here for explanation:
> http://archives.postgresql.org/pgsql-general/2010-03/msg00969.php
>
>   

-- 
Lee Hachadoorian
PhD Student, Geography
Program in Earth & Environmental Sciences
CUNY Graduate Center


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] enforcing constraints across multiple tables

[Ben Morrow]

Quoth andrew.ge...@gmail.com (Andrew Geery):
> 
> I have a question about checking a constraint that is spread across multiple
> (in the example below, two) tables.  In the example below, every food (food
> table) is in a food group (food_group table).  For every person (person
> table), I want to enforce the constraint that there can only be one food in
> a given food group (person_food link table) [think of it as every person may
> have a favorite food in a given food group].
> 
> The problem seems to be that the link is in the person_food table, but the
> information that is needed to verify the constraint is also in the food
> table (i.e., what food group is the food in?).
> 
> There are two problems here:
> (1) don't allow a food to be associated with a person if there is already a
> food in the same food group associated with the person; and
> (2) don't allow the food group for a food to be changed if this would
> violate (1)
> 
> To enforce (1), I created a function to check whether a given food can be
> associated with a given person (is there already a food in the same food
> group associated with the person?) and added a check constraint to the
> person_food table.
> To enforce (2), I wasn't able to use a check constraint because the
> constraint was being checked with the existing data, not with the new data.
>  I had to add an after trigger that called a function to do the check.
> 
> My questions are:
> (A) Is there a way to check (2) above using a constraint and not a trigger?
> (B) Is there an easier way to solve this problem?  Does the complicated
> nature of the solution make the design poor?
> (C) Should I not worry about this constraint at the DB level and just
> enforce it at the application level?
> 
> Below are the tables, functions and triggers I was using.
> 
> Thanks!
> Andrew
> 
> ===
> 
> create table person (
>id serial primary key,
>name varchar not null
> );
> 
> create table food_group (
>id serial primary key,
>name varchar not null
> );
> 
> create table food (
>id serial primary key,
>food_group_id int not null references food_group,
>name varchar not null
> );
> 
> create table person_food (
>person_id int not null references person,
>food_id int not null references food,
>primary key (person_id, food_id),
>check (is_person_food_unique(person_id, food_id))
> );

Instead of this, try

create table person_food (
person_id int not null references person,
food_id int not null,
food_group_id int not null,

foreign key (food_id, food_group_id)
references food (id, food_group_id),
unique (person_id, food_group_id)
);

If you wish to move foods between groups, the foreign key above will
need to be ON UPDATE CASCADE.

Ben


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] COPY command and required file permissions

[Adrian Klaver]

On Friday 25 June 2010 6:07:35 pm Bruno Scovoli Santos wrote:
> * Are you connecting to the database as a superuser to run the COPY
> command? *
>
> Yes. This \copy command is in the database build script (create table
> blablabla). So it almost (I think) must have to be a superuser.
>
> But hey, my intent was just to guide the guy (Harold A. Giménez Ch.) to
> follow my practice (to use a preceding backslash). I dont bother to have to
> add that preceding backslash in my script. ;-)
>
>

Actually my question was directed to Harold, I put my reply in the wrong place.
In any case COPY and \copy are different commands. COPY is done from the 
server's perspective, \copy from the clients. Hence the permission issues will 
be different. See below for more detail.

http://www.postgresql.org/docs/8.4/interactive/sql-copy.html
http://www.postgresql.org/docs/8.4/interactive/app-psql.html


-- 
Adrian Klaver
adrian.kla...@gmail.com

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql