Re: [SQL] CREATE TABLE with foreign key and primary key
"Paulo Roberto Siqueira" wrote: >ufgvirtual=# create table matricula ( >ufgvirtual(# id_aluno char(15) references pessoa, >ufgvirtual(# id_curso int4 references curso_polo, >ufgvirtual(# id_polo int2 references curso_polo, >ufgvirtual(# local_prova varchar(50) not null, >ufgvirtual(# autorizado bool default 'f' not null, >ufgvirtual(# id_plano_pgto int2 references plano_pgto not null, >ufgvirtual(# data_matricula date default CURRENT_DATE not null, >ufgvirtual(# primary key(id_aluno,id_curso,id_polo)); ... >I have tables pessoa, curso, polo, curso_polo and matricula. Primary key in >curso_polo are id_curso (references curso) and id_polo (references polo). In >table matricula I want as primary key id_pessoa (references pessoa), >id_curso (references curso_polo) and id_polo (references curso_polo). You can't use REFERENCES on a column if the target primary key is made up of more than one column; you have to use a FOREIGN KEY table constraint: create table matricula ( id_aluno char(15) references pessoa, id_curso int4 not null, -- I assume you want id_polo int2 not null, -- not null here local_prova varchar(50) not null, autorizado bool default 'f' not null, id_plano_pgto int2 references plano_pgto not null, data_matricula date default CURRENT_DATE not null, primary key(id_aluno,id_curso,id_polo), FOREIGN KEY (id_curso, id_polo REFERENCES curso_polo (id_curso,id_polo)); -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight http://www.lfix.co.uk/oliver PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47 GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C "Delight thyself also in the LORD; and he shall give thee the desires of thine heart." Psalms 37:4
[SQL] Type conversion
Hello I have one big select that computes some value. The value is in format numeric (40,4). Then is this value send to console and when someone on console agree with the number then is the number send to database where i need to do something like this select TheNumberFromConsole/(select max(division_point) from table1) Division_point is in format numeric (40,4). But the number is sometimes small and sometimes big, when the number is big, then is all OK, but when the number is small i got error message that / for float8 (!) and numeric is not possible and i must use explicit typecast, but i dont know how! I have look in manual, but there are informations about float8 only and at the bottom is written that most of functions for float8 works for numeric so i try: select numeric (TheNumberFromConsole)/(select max(division_point) from table1) But this don't work too... Please help how should i written the statement correct. Thanks for help and sorry for my bad english. Best regards Ice Planet e-mail: [EMAIL PROTECTED] ICQ#: 67765483
[SQL] Corruption... please help
Hi... I am unable to select every row from a table. Every time, the backend disconnect. When I do a : The_DB=> vacuum; I get : ERROR: Invalid XID in t_cmin (2) What does it meens and how can I recover from it? Frédéric Boucher [EMAIL PROTECTED]
Re: [SQL] Corruption... please help
[ Charset ISO-8859-1 unsupported, converting... ] > Hi... > > I am unable to select every row from a table. Every time, the backend > disconnect. When I do a : > > The_DB=> vacuum; > > I get : > > ERROR: Invalid XID in t_cmin (2) > > What does it meens and how can I recover from it? > Are you the one moving the table files around? You need pg_log. See pg_upgrade. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
Re: [SQL] Type conversion
> select TheNumberFromConsole/(select max(division_point) from table1) Try select '1234.5678'/(select max(division_point) from table1); The quotes around the apparent floating point number keeps Postgres from assuming that it *is* a floating point number, and it later decides that it must have been a numeric() type. - Thomas
[SQL] Subselects with IN and EXISTS
Hello, Here is my query. SELECT id, title, type, sub_type, order_number, version, date, referred_to, referred_in FROM sop WHERE (type||sub_type||order_number, version) IN ^^^ (SELECT type||sub_type||order_number, max(version) FROM sop GROUP BY type||sub_type||order_number) ORDER BY type, sub_type, order_number It looks like is not as fast as I would like so I thought of rewriting it as: SELECT id, title, type, sub_type, order_number, version, date, referred_to, referred_in FROM sop WHERE EXISTS (SELECT type||sub_type||order_number, max(version) FROM sop GROUP BY type||sub_type||order_number) ORDER BY type, sub_type, order_number The results that I get are not the same. Could anyone point what am I doing wrong? tia Dorin
[SQL] Supported Encoding
Hello people, I need answers to a simple question that I couldn't find the definite answer for: Does Postgresql support only EUC? Basically, I am trying to save international fonts to Postgresql, so I'm trying to find out what exactly I need to do. It seems like it works fine when I am using PHP to insert/retrieve data without any encoding conversions. I think my Postgresql was not specified any special encoding at the time of make. Do I need to convert everything EUC before I store data, and do why would I need to do that? Thanks in advance!! Tony
RE: [SQL] How to get a self-conflicting row level lock?
> Is it true that SELECT ... FOR UPDATE only acquires a ROW > SHARE MODE lock, and that it isn't self-conflicting? SELECT FOR UPDATE acquires ROW SHARE LOCK on *table* level. But rows returned by SELECT FOR UPDATE is locked *exclusively* - so any other transaction which tries to mark the same row for update (or delete/update it) will be blocked... and will return *updated* row version after 1st transaction committed. Vadim
[SQL] SERIAL type does not generate new ID ?
Hi all,
I don't really understand what happens, so I put context, then problem:
1/ Context
--
I have this table:
CREATE TABLE film (
film_id SERIAL PRIMARY KEY,
film_country_id CHAR(2),
film_country_id2 CHAR(2),
film_country_id3 CHAR(2),
film_country_id4 CHAR(2),
film_ec_certif BOOL DEFAULT 'false',
film_ce_certif BOOL DEFAULT 'false',
film_prod_year INTEGER,
film_eur_support BOOL DEFAULT 'false',
film_media_support BOOL DEFAULT 'false',
film_budgetnat DECIMAL,
film_budgetnat_rate_id CHAR(3),
film_budget DECIMAL,
film_provisoire BOOL DEFAULT 'false',
film_production_id INTEGER,
film_production_id2 INTEGER,
film_production_id3 INTEGER,
film_production_id4 INTEGER
);
CREATE UNIQUE INDEX film_uidx ON film ( film_id );
CREATE INDEX film_production_1 ON film (film_production_id);
CREATE INDEX film_production_2 ON film (film_production_id2);
CREATE INDEX film_production_3 ON film (film_production_id3);
CREATE INDEX film_production_4 ON film (film_production_id4);
Now if I look at it with postgresadmin, I see:
-- postgresAdmin PostgreSQL-Dump
--
-- Serveur: localhost:5432 Base de données: admissions12
-
--
-- Structure de la table 'film'
--
DROP SEQUENCE film_film_id_seq;
CREATE TABLE film (
film_id serial,
film_country_id bpchar,
film_country_id2 bpchar,
film_country_id3 bpchar,
film_country_id4 bpchar,
film_ec_certif bool,
film_ce_certif bool,
film_prod_year int4,
film_eur_support bool,
film_media_support bool,
film_budgetnat numeric,
film_budgetnat_rate_id bpchar,
film_budget numeric,
film_provisoire bool,
film_production_id int4,
film_production_id2 int4,
film_production_id3 int4,
film_production_id4 int4
)
;
SELECT setval ('film_film_id_seq', 6);
2/ Problem:
I have this query in PHP:
insert into film
(film_country_id,film_country_id2,film_country_id3,film_country_id4,film_prod_year,film_ec_certif,film_ce_certif,film_eur_support,film_media_support,film_provisoire)
values ('FR','','','','2000','f','f','f','f','f')
And it gives the following error:
ERROR: Cannot insert a duplicate key into a unique index
3/ Question:
Shouldn't it automagically create an appropriate film_id ?
Right now, I can do it with
$sql_query_film="insert into film
(film_id,film_country_id,film_country_id2,film_country_id3,film_country_id4,film_prod_year,film_ec_certif,film_ce_certif,film_eur_support,film_media_support,film_provisoire)
values
(MAX(film_id)+1,'$film_country_id','$film_country_id2','$film_country_id3','$film_country_id4','$film_prod_year','$film_ec_certif','$film_ce_certif','$film_eur_support','$film_media_support','$film_provisoire')";
and it works fine as long as there are no concurrent accesses, but
there must be a better way :-(
This is very puzzling because it seems that this is the only table which
gives such errors.
Pointers on relevant online documentation accepted, too :-)
Jean-Marc Libs
--
Jean-Marc Libs, ingénieur INTERNET/INTRANET
Actimage 1 rue St Leon F-67000 STRASBOURGhttp://www.actimage.net
Professionnel : [EMAIL PROTECTED]
[SQL] problem with date
How can I insert/update a null value in a date field. If a try a sql statement like the following: update customer set birth_date = '' where cust_id like 'xyz', I get a error message saying: Bad date representation. Thanks in advance, Shalini
Re: [SQL] SERIAL type does not generate new ID ?
Jean-Marc Libs wrote:
>
> I have this table:
>
> CREATE TABLE film (
>film_id serial,
> ...
>
> SELECT setval ('film_film_id_seq', 6);
>
> I have this query in PHP:
> insert into film
>
>(film_country_id,film_country_id2,film_country_id3,film_country_id4,film_prod_year,film_ec_certif,film_ce_certif,film_eur_support,film_media_support,film_provisoire)
> values ('FR','','','','2000','f','f','f','f','f')
>
> And it gives the following error:
> ERROR: Cannot insert a duplicate key into a unique index
You should not have to do anything special with the serial or the
sequence (including setting it to 6). Possible sources for for your
error: 1) you are resetting the sequence value to 6 when you already
have a row with that value for film_id in the table, or 2) could be the
message is coming from a triggered insert "downstream" from your initial
insert (see your server log). BTW, 7.0+ tells you *which* index caused
the problem.
Regards,
Ed Loher
Re: [SQL] problem with date
Try NULL rather than ''. '' is not a null in postgres. - Original Message - From: Shalini shikha <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Monday, July 10, 2000 3:15 PM Subject: [SQL] problem with date > How can I insert/update a null value in a date field. If a try a sql > statement like the following: > > update customer set birth_date = '' where cust_id like 'xyz', I get a > error message saying: Bad date representation. > > Thanks in advance, > Shalini
[SQL] Timestamp problem
Hi, I have Postgres 7.0, and I created a table table - ... borrow timestamp return timestamp Now, I thought timestamp is in sec eg; 35434434, but in PostgreSQL, it shows up as datetime datatype eg; 2000-06-07 17:00:05-07. I was wondering is this format a correct one for timestamp, or is it a bug? Abd if I want to show the time, each retrieval I have to chop of the '-07' timezone at the end. Is there a way to avoid this being show up in Postgres? Thanks. - Bernie begin:vcard n:Huang;Bernie tel;fax:(604)664-9195 tel;work:(604)664-9172 x-mozilla-html:TRUE org:Environment Canada;Standards and Technology Services adr:;;700-1200 West 73 Ave.;Vancouver;BC;V6P 6H9;Canada version:2.1 email;internet:[EMAIL PROTECTED] title:Programmer x-mozilla-cpt:;0 fn:Bernie Huang end:vcard
Re: [SQL] Timestamp problem
Bernie Huang wrote: > > table > - > ... > borrow timestamp > return timestamp > > Now, I thought timestamp is in sec eg; 35434434, but in PostgreSQL, it > shows up as datetime datatype eg; 2000-06-07 17:00:05-07. > > I was wondering is this format a correct one for timestamp, or is it a > bug? Abd if I want to show the time, each retrieval I have to chop of > the '-07' timezone at the end. Is there a way to avoid this being show > up in Postgres? You might want to check out the date/time and formatting functions at http://www.postgresql.org/docs/postgres/index.html Regards, Ed Loehr
[SQL] Re: Matching and Scoring with multiple fields
I have a problem. Ok I'll rephrase that, a challenge. I have a table like this: a,b,c,d,e,f,g,h --- 2,5,3,4,4,5,2,2 1,1,1,1,1,1,1,1 5,5,5,5,5,5,5,5 3,3,2,4,5,1,1,3 1,1,5,5,5,5,1,4 1,5,5,5,4,4,2,1 5,5,5,5,1,1,1,1 1,1,1,1,5,5,5,5 (rows 8) a to h are of type int. I want to take input values which relate to this table say: how do you feel about a: how do you feel about b: how do you feel about c: ... and the answers will be 1 to 5. Now I want to take those answers for my incoming a to h and scan down the table pulling out the closest matches from best to worst. There will be about 2000 rows in the final table and I will LIMIT the rows in blocks of 10 or so. I can do the limiting stuff, but not the matching. My first thought was to sum each row and match by that until I came out of my mental coma and noticed that the last two lines have the same sum and are complete opposites. So, where to from here? I thought I could go through line by line selecting with a tolerance on each value say +-1 to begin with, then again with +-2 but that will take hours and I'm not entirely sure it'll work or how I'll do it. I know general netequitte says that I shouldn't just dump my problem here, but I am truly stumped by this one - if anybody can give me a pointer in the right direction I'd greatly appreciate it. Thanks, Tim Johnson --- http://www.theinkfactory.co.uk
Re: [SQL] Re: Matching and Scoring with multiple fields
I'm not sure, but it seems you could calculate a column like: SELECT a,b,c,..., abs(-a)+abs(-b)+abs(-c)+... AS weight FROM t ORDER BY weight This way the closest matches would come first. On Mon, Jul 10, 2000 at 07:56:08PM +0100, Tim Johnson wrote: > I have a problem. Ok I'll rephrase that, a challenge. > > I have a table like this: > > a,b,c,d,e,f,g,h > --- > 2,5,3,4,4,5,2,2 > 1,1,1,1,1,1,1,1 > 5,5,5,5,5,5,5,5 > 3,3,2,4,5,1,1,3 > 1,1,5,5,5,5,1,4 > 1,5,5,5,4,4,2,1 > 5,5,5,5,1,1,1,1 > 1,1,1,1,5,5,5,5 > (rows 8) > > a to h are of type int. > > > I want to take input values which relate to this table say: > how do you feel about a: > how do you feel about b: > how do you feel about c: > ... > > and the answers will be 1 to 5. > > Now I want to take those answers for my incoming a to h and scan down the > table pulling out the closest matches from best to worst. There will be > about 2000 rows in the final table and I will LIMIT the rows in blocks of 10 > or so.
Re: [SQL] Re: Matching and Scoring with multiple fields
Tim Johnson wrote: > > I have a table like this: > > a,b,c,d,e,f,g,h > --- > 2,5,3,4,4,5,2,2 > 1,1,1,1,1,1,1,1 > > a to h are of type int. > > I want to take input values which relate to this table say: > how do you feel about a: > how do you feel about b: > how do you feel about c: > ... > > and the answers will be 1 to 5. > > Now I want to take those answers for my incoming a to h and scan down the > table pulling out the closest matches from best to worst. I wonder if you don't really just want to find the vector(s) closest in N-space to the input vector. You might dig up an old 3-variable calculus book, find the formula, and write a PL/pgSQL function to compute the distance between two N-dimensional vectors... Regards, Ed Loehr
[SQL] Finding entries not in table..differnce?
I need to write a quick function that tells me all of the entriles in table that are not in table2. The tables are copies of each other, but 1 has been updated. I know this is easy, but I am running on little sleep :) I want to due something like select prodlang.prodlsku from prodland,prodlang2 WHERE prodlang.prodlsku != prodlang2.prodlsku But of course I would get every record, several times since at some point the sku does not equal another sku. I tried !!= (NOT IN) but it did not like that at all, since sku is a char field. I am running an older version of postgress on this server, I do not know if that is important. -- Zot O'Connor http://www.ZotConsulting.com http://www.WhiteKnightHackers.com
Re: [SQL] Finding entries not in table..differnce?
Assuming that you don't have nulls in prodlang.prodlsku, this should probably work, although I haven't tried it for real. SELECT prodlang.prodlsku FROM prodlang WHERE NOT EXISTS (SELECT * FROM prodlang2 WHERE prodlang2.prodlsku=prodlang.prodlsku); If you do have nulls, the inner select probably needs to be something like: (SELECT * FROM prodlang2 WHERE prodlang2.prodlsku=prodlang.prodlsku or (prodlang2.prodlsku is null and prodlang.prodlsku is null)) - Original Message - From: "Zot O'Connor" <[EMAIL PROTECTED]> To: "postgres sql" <[EMAIL PROTECTED]> Sent: Monday, July 10, 2000 5:29 PM Subject: [SQL] Finding entries not in table..differnce? > I need to write a quick function that tells me all of the entriles in > table that are not in table2. > > The tables are copies of each other, but 1 has been updated. I know > this is easy, but I am running on little sleep :) > > I want to due something like > > select prodlang.prodlsku from prodland,prodlang2 WHERE prodlang.prodlsku > != prodlang2.prodlsku > > But of course I would get every record, several times since at some > point the sku does not equal another sku. > > I tried !!= (NOT IN) but it did not like that at all, since sku is a > char field. > > I am running an older version of postgress on this server, I do not know > if that is important.
Re: [SQL] Finding entries not in table..differnce?
If I understand you exactly, you may use except: select distinct * from prodlang except select distinct * from prodlang2 gives you the records which exist in prodlang and do not exist in prodlang2. So you get all the records in prodlang which are newly inserted or updated. regards erol Zot O'Connor wrote: > > I need to write a quick function that tells me all of the entriles in > table that are not in table2. > > The tables are copies of each other, but 1 has been updated. I know > this is easy, but I am running on little sleep :) > > I want to due something like > > select prodlang.prodlsku from prodland,prodlang2 WHERE prodlang.prodlsku > != prodlang2.prodlsku > > But of course I would get every record, several times since at some > point the sku does not equal another sku. > > I tried !!= (NOT IN) but it did not like that at all, since sku is a > char field. > > I am running an older version of postgress on this server, I do not know > if that is important. > > -- > Zot O'Connor > > http://www.ZotConsulting.com > http://www.WhiteKnightHackers.com
[SQL] Error : Unknown address family (0)
Each time I try to do an pg_dump or pg_dumpall I receive this message : Error : Unknown address family (0) What can I do to resolve this? Or at least what does it means? Thanks a lot! Frédéric Boucher [EMAIL PROTECTED]
[SQL] Creating timestamps in queries?
Hi all, I would like to say, "select * from blah where stamp >= 7 days ago" ...where the "days ago" is calculated at query time; meaning that its not hardcoded into the query as a date string. Is this possible? TIA! =) - Rob Slifka
