[SQL] SQL - update table problem...
Hi,
first i'm sorry if this message appears twice. first time didn't get through
because i wasn't registered.
i'm trying to do update on multiple tables but don't know how.
is something like this posible with postgresql?
update table1
join table2 on (table1.id=table2.t1)
join table3 on (table2.id=table3.t2)
set table1.name='test', table2.sum=table1.x+table2.y,
table3.cell='123456789'
where table1.id=6
i know that this syntax is not supported with postgres but i tried to
rewrite the code using this synopsis:
UPDATE [ ONLY ] table SET column = { expression | DEFAULT } [, ...]
[ FROM fromlist ]
[ WHERE condition ]
i failed again.
thanks for help
_
Express yourself instantly with MSN Messenger! Download today it's FREE!
http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
[SQL] Inserting data in composite types!
Hi, I have a question about how to insert data in composite types! Imagine the exemple: CREATE TYPE t_time AS ( a date, b date ); CREATE TABLE salary ( salary numeric(10,2), t_date t_time ); I know that if I want to insert data in the table SALARY I just have to do like: INSERT INTO salary VALUES (1000.00, ‘(2006/10/10, 2006/12/10)’); But if I have another table: CREATE TABLE employee ( employee_id int, name varchar(30), emp_salary salary ) How can I insert a single row in this table??? Thanks in advamce!
Re: [SQL] Inserting data in composite types!
Hi, Rodrigo Sakai wrote: How can I insert a single row in this table??? INSERT INTO employee (emp_salary) VALUES ((1000.00, '(2006/10/10, 2006/12/10)')); BTW: are you sure you don't want to use foreign keys instead? Something like: CREATE TABLE salaries ( id SERIAL PRIMARY KEY, salary numeric(10,2) NOT NULL, t_date t_time NOT NULL ); CREATE TABLE employee ( employee_id SERIAL PRIMARY KEY, name TEXT NOT NULL, salary INT NOT NULL REFERENCES salaries(id) ); Regards Markus ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Inserting data in composite types!
Rodrigo Sakai wrote: Hi, I have a question about how to insert data in composite types! Imagine the exemple: CREATE TYPE t_time AS ( a date, b date ); CREATE TABLE salary ( salary numeric(10,2), t_date t_time ); I know that if I want to insert data in the table SALARY I just have to do like: INSERT INTO salary VALUES (1000.00, '(2006/10/10, 2006/12/10)'); But if I have another table: CREATE TABLE employee ( employee_id int, name varchar(30), emp_salary salary ) I am thinking that with the salary type here you are thinking of your salary table defined above? If so and you want them in a separate table to record salary histories then you want to create a foreign key to link them. You would end up with - CREATE TABLE employee ( employee_id int PRIMARY KEY, name varchar(30) ); CREATE TABLE salary ( emp_id int REFERENCES employee(employee_id) ON DELETE CASCADE, salary numeric(10,2), t_date t_time ); then - INSERT INTO salary VALUES (1, 1000.00, '(2006/10/10, 2006/12/10)'); Otherwise you will want to change the CREATE TABLE salary... to CREATE TYPE salary... Probably as CREATE TYPE salary AS( salary numeric(10,2), a date, b date ); You can then INSERT INTO employee VALUES (1,'Hard Worker','(1000.00, 2006/10/10, 2006/12/10)'); -- Shane Ambler [EMAIL PROTECTED] Get Sheeky @ http://Sheeky.Biz ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
[SQL] hiding column values for specific rows
Hi, I don't know if this's possible but I'd like to hide column values for specific rows within a query. Imagine I've got a table with columns username and password: users(username,password). Now I'd like the user registered in the table to see her password, to see who is registered but not to see the other people passwords. For example, if the table contains: username | password --+- luca| myPaswd roberto | otherPaswd gianna| thirdPaswd I'd like to do a query like: "SELECT * FROM users where username=luca" obtaining something like: username | password --+- luca| myPaswd roberto | x gianna| X Is it possible to specify in the above query that all password columns that do not belong to selected row (username=luca) must be hidden? Anyone has an idea about how to implement this on the database side? Thanks, Luca ---(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] hiding column values for specific rows
am Mon, dem 13.11.2006, um 14:11:50 +0100 mailte Luca Ferrari folgendes: > Hi, > I don't know if this's possible but I'd like to hide column values for > specific rows within a query. Imagine I've got a table with columns username > and password: users(username,password). Now I'd like the user registered in > the table to see her password, to see who is registered but not to see the > other people passwords. For example, if the table contains: > username | password > --+- > luca| myPaswd > roberto | otherPaswd > gianna| thirdPaswd > > I'd like to do a query like: "SELECT * FROM users where username=luca" > obtaining something like: > username | password > --+- > luca| myPaswd > roberto | x > gianna| X > > Is it possible to specify in the above query that all password columns that > do You can do this with a VIEW and remoke all from normal users for the original table. Within the VIEW, you can use current_user for the username and/or a case when ... statement for the password-column. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47215, 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
[SQL] max (timestamp,timestamp)
I tried the following query but the query fails as "function max (timestamp w. timezone,timestamp w. timezone) does not exist" SELECT id, MAX(last_updated, (SELECT MAX (last_updated) FROM product_category_member WHERE product_category_member.id = product_category.id)) FROM product_category product_category.last_updated and product_category_member.last_updated are timestamps with timezone. Is there any other way I can produce this result -- Regards, Tarlika Elisabeth Schmitz ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] max (timestamp,timestamp)
am Mon, dem 13.11.2006, um 13:46:00 + mailte T E Schmitz folgendes: > I tried the following query but the query fails as > "function max (timestamp w. timezone,timestamp w. timezone) does not exist" > > SELECT id, > > MAX(last_updated, > (SELECT MAX (last_updated) FROM product_category_member WHERE > product_category_member.id = product_category.id)) > > FROM product_category > > > product_category.last_updated and product_category_member.last_updated > are timestamps with timezone. Really, there are no such function. Perhaps this can help you: SELECT id, MAX(product_category.last_updated), MAX(product_category_member.last_updated) from product_category, product_category_member WHERE product_category_member.id = product_category.id; **untested** Your fault is that there are no max(timestamp,timestamp) - funktion and i think, you should read more about JOINs. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47215, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Inserting data in composite types!
I am able to do this thing with the following query: insert into employee (a, name, s) values(1, 'emp name', ((1, '(01/01/2000, 01/01/2000)'))); --Imad www.EnterpriseDB.com On 11/13/06, Rodrigo Sakai <[EMAIL PROTECTED]> wrote: Hi, I have a question about how to insert data in composite types! Imagine the exemple: CREATE TYPE t_time AS ( a date, b date ); CREATE TABLE salary ( salary numeric(10,2), t_date t_time ); I know that if I want to insert data in the table SALARY I just have to do like: INSERT INTO salary VALUES (1000.00, '(2006/10/10, 2006/12/10)'); But if I have another table: CREATE TABLE employee ( employee_id int, name varchar(30), emp_salary salary ) How can I insert a single row in this table??? Thanks in advamce! ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] max (timestamp,timestamp)
max (timestamptz, timestamptz) does not exist already. You need to create a simple function in PLpgSQL something like if a > b return a; else return b; Even an sql function will do the job here using case statement. --Imad www.EntepriseDB.com On 11/13/06, A. Kretschmer <[EMAIL PROTECTED]> wrote: am Mon, dem 13.11.2006, um 13:46:00 + mailte T E Schmitz folgendes: > I tried the following query but the query fails as > "function max (timestamp w. timezone,timestamp w. timezone) does not exist" > > SELECT id, > > MAX(last_updated, > (SELECT MAX (last_updated) FROM product_category_member WHERE > product_category_member.id = product_category.id)) > > FROM product_category > > > product_category.last_updated and product_category_member.last_updated > are timestamps with timezone. Really, there are no such function. Perhaps this can help you: SELECT id, MAX(product_category.last_updated), MAX(product_category_member.last_updated) from product_category, product_category_member WHERE product_category_member.id = product_category.id; **untested** Your fault is that there are no max(timestamp,timestamp) - funktion and i think, you should read more about JOINs. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47215, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 6: explain analyze is your friend ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] max (timestamp,timestamp)
On Mon, Nov 13, 2006 at 07:29:09PM +0500, imad wrote: > max (timestamptz, timestamptz) does not exist already. You need to > create a simple function in PLpgSQL something like > > if a > b > return a; > else > return b; Since PostgreSQL 8.1 you can use GREATEST: test=> SELECT greatest(1, 2); greatest -- 2 (1 row) test=> SELECT greatest(2, 1); greatest -- 2 (1 row) test=> SELECT greatest(6, 3, 1, 10, 9, 5, 2, 7, 8, 4); greatest -- 10 (1 row) -- Michael Fuhr ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] COALESCE and GROUP BY and AGGREGATES
Ok, here's a sample table for the question I have: CREATE TABLE sales_table ( sale_type varchar default 'setup' not null, sale_amount numeric not null sale_date timestamp without timezone default now()); So, let's say there are 3 different sale_types: 'setup', 'layaway', 'the_hookup' and I want to get totals for each type in a given month: SELECT sale_type, SUM(sale_amount) FROM sales_table WHERE sale_date LIKE '2006-11%' GROUP BY sale_type; If there hasn't been a sale of a given type in that month there won't be a row in the result set for that type. I want a row for each type with a default of 0 if there haven't been any sales for that type yet that month. I've tried: SELECT sale_type, (COALESCE(SUM(sale_amount), 0) FROM sales_table WHERE sale_date LIKE '2006-11%' GROUP BY sale_type; but, no dice. Any ideas? I know I can break this out into separate queries for each type and the COALESCE will work, but in my real-world situation I have a lot more than three types and that'd be ugly. Thanks, -- erik jones <[EMAIL PROTECTED]> software development emma(r) ---(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] COALESCE and GROUP BY and AGGREGATES
On Nov 13 10:49, Erik Jones wrote: > Ok, here's a sample table for the question I have: > > CREATE TABLE sales_table ( > sale_type varchar default 'setup' not null, > sale_amount numeric not null > sale_date timestamp without timezone default now()); > > So, let's say there are 3 different sale_types: 'setup', 'layaway', > 'the_hookup' and I want to get totals for each type in a given month: > > SELECT sale_type, SUM(sale_amount) > FROM sales_table > WHERE sale_date LIKE '2006-11%' > GROUP BY sale_type; > > If there hasn't been a sale of a given type in that month there won't be > a row in the result set for that type. I want a row for each type with > a default of 0 if there haven't been any sales for that type yet that > month. What about such a schema design: CREATE TABLE sale_types ( id serial PRIMARY KEY, nametextNOT NULL DEFAULT 'setup' ); CREATE UNIQUE INDEX sales_types_typ_idx ON sales_types (typ); CREATE TABLE sales_table ( typ bigint REFERENCES sale_types (id), amount numeric NOT NULL, sdate timestamp without time zone DEFAULT CURRENT_TIMESTAMP ); SELECT TYP.name, COALESCE(SUM(TBL.amount), 0) FROM sale_types AS TYP LEFT OUTER JOIN sales_table AS TBL ON (TYP.id = TBL.typ) WHERE TBL.sale_date LIKE '2006-11%' GROUP BY TYP.name; I didn't try the above SQL queries, but I hope you understand what I meant. Regards. ---(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
RES: [SQL] Inserting data in composite types!
Thanks, It works! I have tried: insert into employee (c, nome, sal) values(1, 'emp name', '(1, (01/01/2000, 01/01/2000))' ); And it doesn't work! -Mensagem original- De: imad [mailto:[EMAIL PROTECTED] Enviada em: segunda-feira, 13 de novembro de 2006 11:10 Para: [EMAIL PROTECTED] Cc: pgsql-sql@postgresql.org Assunto: Re: [SQL] Inserting data in composite types! I am able to do this thing with the following query: insert into employee (a, name, s) values(1, 'emp name', ((1, '(01/01/2000, 01/01/2000)'))); --Imad www.EnterpriseDB.com On 11/13/06, Rodrigo Sakai <[EMAIL PROTECTED]> wrote: > > > > > Hi, I have a question about how to insert data in composite types! > > > > Imagine the exemple: > > > > CREATE TYPE t_time AS ( > > a date, > > b date > > ); > > > > CREATE TABLE salary ( > >salary numeric(10,2), > >t_date t_time > > ); > > > > I know that if I want to insert data in the table SALARY I just have to do > like: > > > > INSERT INTO salary VALUES (1000.00, '(2006/10/10, 2006/12/10)'); > > > > But if I have another table: > > > > CREATE TABLE employee ( > > employee_id int, > > name varchar(30), > > emp_salary salary > > ) > > > > How can I insert a single row in this table??? > > > > Thanks in advamce! > > ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] drop a check
Dear all... I have created a check constraint without giving it a name.. now I have a check named "$25" in my table that I need to drop or modify!!! How can I do??? with names is simple alter table tablename drop constraint constraintname; but without name?? :-) many thanks... Ivan -- Ivan Marchesini Department of Civil and Environmental Engineering University of Perugia Via G. Duranti 93/a 06125 Perugia (Italy) e-mail: [EMAIL PROTECTED] [EMAIL PROTECTED] tel: +39(0)755853760 fax: +39(0)755853756 jabber: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: explain analyze is your friend
[SQL] Another question about composite types
Hi, I need to create a table like: CREATE TYPE t_salary AS ( Value numeric(10,2), Validity date ); CREATE TABLE employee ( id int, name varchar(30), salary t_salary[] ); That is, I need an array of composite type and searching on google I found that is impossible to do it! Is it really impossible? Anyone have do it ? Thanks!
Re: RES: [SQL] Inserting data in composite types!
> Thanks, It works! I have tried: > > insert into employee (c, nome, sal) values(1, 'emp name', '(1, (01/01/2000, > 01/01/2000))' ); > > And it doesn't work! What is the error message? Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] drop a check
On Nov 13 05:32, ivan marchesini wrote: > I have created a check constraint without giving it a name.. > now I have a check named "$25" in my table that I need to drop or > modify!!! > How can I do??? > with names is simple > > alter table tablename drop constraint constraintname; > > but without name?? :-) CREATE TABLE cons_test ( u integer CHECK (u > 10) ); SELECT constraint_name FROM information_schema.constraint_column_usage WHERE table_name = 'cons_test' AND column_name = 'u'; See infoschema-constraint-column-usage.html in the manual for further assistance. Regards. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Another question about composite types
> CREATE TYPE t_salary AS (Value numeric(10,2), Validity date); > CREATE TABLE employee (id int, name varchar(30), salary t_salary[]); > > That is, I need an array of composite type and searching on google I found > that is impossible to do it! Is it really impossible? Anyone have do it ? Why not use a conventional table to hold this information that references your employee table? Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
RES: [SQL] Another question about composite types
I know that if I create a table salary that references my employee table
works! But I'm trying to work with the composite type concept!
Thinking in Object-Oriented Programming (OOP) I have a class employee that
is composed by the class salary among other things! Like:
Class salary {
private float value;
private date validity;
}
Class employee {
private int ID;
private String name;
private salary[] sal;
}
So I'd like to map these classes to PostgreSQL. I think nested tables are
one of the advantages of object-relational databases. And it works if I
think in just one salary for each employee. But I want to keep all salary
history for each employee. Is there any way?
-Mensagem original-
De: Richard Broersma Jr [mailto:[EMAIL PROTECTED]
Enviada em: segunda-feira, 13 de novembro de 2006 14:46
Para: [EMAIL PROTECTED]; pgsql-sql@postgresql.org
Assunto: Re: [SQL] Another question about composite types
> CREATE TYPE t_salary AS (Value numeric(10,2), Validity date);
> CREATE TABLE employee (id int, name varchar(30), salary t_salary[]);
>
> That is, I need an array of composite type and searching on google I found
> that is impossible to do it! Is it really impossible? Anyone have do it ?
Why not use a conventional table to hold this information that references
your employee table?
Regards,
Richard Broersma Jr.
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [SQL] COALESCE and GROUP BY and AGGREGATES
Volkan YAZICI wrote: On Nov 13 10:49, Erik Jones wrote: Ok, here's a sample table for the question I have: CREATE TABLE sales_table ( sale_type varchar default 'setup' not null, sale_amount numeric not null sale_date timestamp without timezone default now()); So, let's say there are 3 different sale_types: 'setup', 'layaway', 'the_hookup' and I want to get totals for each type in a given month: SELECT sale_type, SUM(sale_amount) FROM sales_table WHERE sale_date LIKE '2006-11%' GROUP BY sale_type; If there hasn't been a sale of a given type in that month there won't be a row in the result set for that type. I want a row for each type with a default of 0 if there haven't been any sales for that type yet that month. What about such a schema design: CREATE TABLE sale_types ( id serial PRIMARY KEY, nametextNOT NULL DEFAULT 'setup' ); CREATE UNIQUE INDEX sales_types_typ_idx ON sales_types (typ); CREATE TABLE sales_table ( typ bigint REFERENCES sale_types (id), amount numeric NOT NULL, sdate timestamp without time zone DEFAULT CURRENT_TIMESTAMP ); SELECT TYP.name, COALESCE(SUM(TBL.amount), 0) FROM sale_types AS TYP LEFT OUTER JOIN sales_table AS TBL ON (TYP.id = TBL.typ) WHERE TBL.sale_date LIKE '2006-11%' GROUP BY TYP.name; I didn't try the above SQL queries, but I hope you understand what I meant. Awesome. I didn't (and couldn't) change the schema, but doing a self-outer join on the table did the trick. Thanks! -- erik jones <[EMAIL PROTECTED]> software development emma(r) ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] drop a check
ivan marchesini <[EMAIL PROTECTED]> schrieb: > Dear all... > I have created a check constraint without giving it a name.. > now I have a check named "$25" in my table that I need to drop or > modify!!! > How can I do??? Can you see the name with \d within psql? An example: test=# create table bla (id int check (id between 1 and 4)); CREATE TABLE test=# \d bla Table "public.bla" Column | Type | Modifiers +-+--- id | integer | Check constraints: "bla_id_check" CHECK (id >= 1 AND id <= 4) test=# alter table bla drop CONSTRAINT bla_id_check; ALTER TABLE test=# \d bla Table "public.bla" Column | Type | Modifiers +-+--- id | integer | HTH, Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I was god, I would recompile penguin with --enable-fly."(unknow) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] drop a check
Dear Volkan, thank you for your answer... I have tried to verify the table information_schema.constraint_column_usage, but, and I was really surprised, I have found nothing inside it.. it seems there are not checks!!! I have also seen the manual page you suggested but I can't understand why I don't have checks in this table... but I'm sure I have checks in my table.. because they works! does this problem can be related to the fact that I have created the checks only after that I have created the table... I have used this syntacs: alter table tablename add check (..) however it sounds strange! thanks!!! Ivan On lun, 2006-11-13 at 19:41 +0200, Volkan YAZICI wrote: > On Nov 13 05:32, ivan marchesini wrote: > > I have created a check constraint without giving it a name.. > > now I have a check named "$25" in my table that I need to drop or > > modify!!! > > How can I do??? > > with names is simple > > > > alter table tablename drop constraint constraintname; > > > > but without name?? :-) > > CREATE TABLE cons_test ( > u integer CHECK (u > 10) > ); > > SELECT constraint_name > FROM information_schema.constraint_column_usage > WHERE table_name = 'cons_test' AND >column_name = 'u'; > > See infoschema-constraint-column-usage.html in the manual for further > assistance. > > > Regards. > -- Ivan Marchesini Department of Civil and Environmental Engineering University of Perugia Via G. Duranti 93/a 06125 Perugia (Italy) e-mail: [EMAIL PROTECTED] [EMAIL PROTECTED] tel: +39(0)755853760 fax: +39(0)755853756 jabber: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] drop a check
. or it depend on the fact I'm using postgres 7.4.13 many thanks... Ivan On lun, 2006-11-13 at 19:41 +0200, Volkan YAZICI wrote: > On Nov 13 05:32, ivan marchesini wrote: > > I have created a check constraint without giving it a name.. > > now I have a check named "$25" in my table that I need to drop or > > modify!!! > > How can I do??? > > with names is simple > > > > alter table tablename drop constraint constraintname; > > > > but without name?? :-) > > CREATE TABLE cons_test ( > u integer CHECK (u > 10) > ); > > SELECT constraint_name > FROM information_schema.constraint_column_usage > WHERE table_name = 'cons_test' AND >column_name = 'u'; > > See infoschema-constraint-column-usage.html in the manual for further > assistance. > > > Regards. > -- Ivan Marchesini Department of Civil and Environmental Engineering University of Perugia Via G. Duranti 93/a 06125 Perugia (Italy) e-mail: [EMAIL PROTECTED] [EMAIL PROTECTED] tel: +39(0)755853760 fax: +39(0)755853756 jabber: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] drop a check
On Nov 13 06:38, ivan marchesini wrote: > thank you for your answer... > I have tried to verify the table > information_schema.constraint_column_usage, but, and I was really > surprised, I have found nothing inside it.. > it seems there are not checks!!! > I have also seen the manual page you suggested but I can't understand > why I don't have checks in this table... > > but I'm sure I have checks in my table.. because they works! > does this problem can be related to the fact that I have created the > checks only after that I have created the table... > I have used this syntacs: > alter table tablename add check (..) I tried to produce same strange behaviour with no luck: test=# CREATE TABLE cons_test (u int); CREATE TABLE test=# ALTER TABLE cons_test test-# ADD CONSTRAINT forget_my_name CHECK (u > 10); ALTER TABLE test=# SELECT constraint_name test-# FROM information_schema.constraint_column_usage test-# WHERE table_name = 'cons_test' AND test-# column_name = 'u'; constraint_name - forget_my_name (1 row) Also, you cannot see that constraint listed in the \d table_name output, ain't? Maybe you should try a hardcoded search over consrc column of pg_catalog.pg_constraint table. Regards. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Another question about composite types
There is always an _typname created for arrays like we have _int4 for
_int4, _varchar for varchar and _timestamp for timestamp etc.
May be you have to write some _typname for your type to use it in
arrays. We always use array_in and array_out for _typenames BTW.
This may be helpful to you.
--Imad
www.EnterpriseDB.com
On 11/14/06, Rodrigo Sakai <[EMAIL PROTECTED]> wrote:
I know that if I create a table salary that references my employee table
works! But I'm trying to work with the composite type concept!
Thinking in Object-Oriented Programming (OOP) I have a class employee that
is composed by the class salary among other things! Like:
Class salary {
private float value;
private date validity;
}
Class employee {
private int ID;
private String name;
private salary[] sal;
}
So I'd like to map these classes to PostgreSQL. I think nested tables are
one of the advantages of object-relational databases. And it works if I
think in just one salary for each employee. But I want to keep all salary
history for each employee. Is there any way?
-Mensagem original-
De: Richard Broersma Jr [mailto:[EMAIL PROTECTED]
Enviada em: segunda-feira, 13 de novembro de 2006 14:46
Para: [EMAIL PROTECTED]; pgsql-sql@postgresql.org
Assunto: Re: [SQL] Another question about composite types
> CREATE TYPE t_salary AS (Value numeric(10,2), Validity date);
> CREATE TABLE employee (id int, name varchar(30), salary t_salary[]);
>
> That is, I need an array of composite type and searching on google I found
> that is impossible to do it! Is it really impossible? Anyone have do it ?
Why not use a conventional table to hold this information that references
your employee table?
Regards,
Richard Broersma Jr.
---(end of broadcast)---
TIP 6: explain analyze is your friend
---(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] drop a check
ivan marchesini <[EMAIL PROTECTED]> schrieb: > . > or it depend on the fact I'm using postgres 7.4.13 > many thanks... Maybe, the information-schema can be different in different versions. Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I was god, I would recompile penguin with --enable-fly."(unknow) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
