[SQL] unique constraint on views
Hi All, Is it possible to add unique constraint on updateable views in postgres? Thanks, Jyoti Seth -- 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] unique constraint on views
In response to Jyoti Seth : > Hi All, > > Is it possible to add unique constraint on updateable views in postgres? Add the constraint to the base-table. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net -- 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] inconsistent automatic casting between psql and function
Tom Lane wrote: > Richard Huxton <[EMAIL PROTECTED]> writes: > > That's because a quoted literal isn't necessarily a timestamp. Without > > context it could be anything, and in the context of comparing to a date > > the planner probably tries to make it a date. > > I think the real point here is this: > > regression=# select '2008-12-09 02:00:00'::date; > date > > 2008-12-09 > (1 row) > > ie, when it does decide that a literal should be a date, it will happily > throw away any additional time-of-day fields that might be in there. > Had it raised an error, Stefano might have figured out his mistake > sooner. > > ISTM we deliberately chose this behavior awhile back, but I wonder > whether it does more harm than good. Well, it seems fine to me because it works just like the cast of a float to an integer: test=> select 1.23432::integer; int4 -- 1 (1 row) -- Bruce Momjian <[EMAIL PROTECTED]>http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Collapsing (select) row values into single text field.
Hi all, I would like to concatenate the field values of several rows in a table that meet some similarity criteria based on a the values of some other field (more like a group by). Then I would also like to also include the lowest value of another associated field along. I have a table that contains 3 fields of interest. create table temp (id INTEGER NOT NULL ,location TEXT NOT NULL --this will hold the zip code ,lowest_temp NUMERIC(5,2) NOT NULL --The lowest temperature at some given night ,location_bit_data VARBIT NOT NULL ,PRIMARY KEY(id) ); There will be usually more than one record for a location (location+lowest_temp is not unique either). Now I would like to collapse the data in this table (an populate another table) as follows. Lets assume this table has the structure below. create table temp_major (id INTEGER NOT NULL ,location TEXT NOT NULL --this will hold the zip code ,lowest_overall_temp NUMERIC(5,2) NOT NULL --The lowest temperature at some given night ,overall_location_bit_data VARBIT NOT NULL ,PRIMARY KEY(id) ,UNIQUE(location) ); The new table (temp_major) is population as follows: the "location_bit_data" values for a given location are "grouped" into one entry (to create a concatenation effect), the lowest_temp reading across all the records of the given location is noted and the location is also noted, this data is used in populating the table. The solution I have so far involves using a stored procedure and cursors (on Select .. order by location) to continuously "grow" the data for a given location's "overall_location_bit_data" field. Allan. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Is there a bug in PostgreSQL ?
Hello, I'm writing a query with a left join to a view, and the server is giving me a wrong result. SELECT emp_id,institution from sip_carriere where emp_id = 342 and institution = 1; emp_id | institution +- 342 | 1 (1 row) SELECT * from sip_demissionaire where emp_id = 342; emp_id | demission_date + (0 rows) IF I COMBINE THE TWO QUERIES WITH A LEFT JOIN QUERY AND PUT ONLY ONE CONDITION, IT WOKS JUST FINE : SELECT distinct c.emp_id,c.institution,d.* from sip_carriere c left join sip_demissionaire d on d.emp_id = c.emp_id where c.emp_id = 342 ; emp_id | institution | emp_id | demission_date +-++ 342 | 1 || 342 | 63 || 342 | 85 || (3 rows) SELECT distinct c.emp_id,c.institution,d.* from sip_carriere c left join sip_demissionaire d on d.emp_id = c.emp_id where c.institution = 1; emp_id | institution | emp_id | demission_date +-++ 342 | 1 || ... |... | ...|... BUT IF I PUT BOTH CONDITIONS SELECT distinct c.emp_id,c.institution,d.* from sip_carriere c left join sip_demissionaire d on d.emp_id = c.emp_id where c.emp_id = 342 and c.institution = 1; emp_id | institution | emp_id | demission_date +-++ (0 rows) What's the problem ? I'm sure that the problem is with the view "sip_demissionaire" cause when I copied its content to a temp table, the query returned a result. SELECT * into temp foo from sip_demissionaire ; SELECT distinct c.emp_id,c.institution,d.* from sip_carriere c left join foo d on d.emp_id = c.emp_id where c.emp_id = 342 and c.institution = 1; emp_id | institution | emp_id | demission_date +-++ 342 | 1 || (1 row) Here's the description of the view "sip_demissionaire" in case you need it CREATE VIEW sip_demissionaire AS ( SELECT t1.* from ( SELECT emp_id,max(demission_date) as demission_date from sip_carriere_dates where demission_date is not null group by emp_id ) as t1 left join ( select emp_id from sip_carriere_dates where demission_date is null ) as t2 on t1.emp_id = t2.emp_id where t2.emp_id is null ); I know it's a long mail, but I'd appreciate any help Thx in advance Pascal
Re: [SQL] Collapsing (select) row values into single text field.
2008/12/10 Allan Kamau <[EMAIL PROTECTED]> > Hi all, > I would like to concatenate the field values of several rows in a table > that meet some similarity criteria based on a the values of some other > field (more like a group by). Then I would also like to also include the > lowest value of another associated field along. > > I have a table that contains 3 fields of interest. > create table temp > (id INTEGER NOT NULL > ,location TEXT NOT NULL --this will hold the zip code > ,lowest_temp NUMERIC(5,2) NOT NULL --The lowest temperature at some given > night > ,location_bit_data VARBIT NOT NULL > ,PRIMARY KEY(id) > ); > > There will be usually more than one record for a location > (location+lowest_temp is not unique either). > Now I would like to collapse the data in this table (an populate another > table) as follows. > Lets assume this table has the structure below. > > create table temp_major > (id INTEGER NOT NULL > ,location TEXT NOT NULL --this will hold the zip code > ,lowest_overall_temp NUMERIC(5,2) NOT NULL --The lowest temperature at some > given night > ,overall_location_bit_data VARBIT NOT NULL > ,PRIMARY KEY(id) > ,UNIQUE(location) > ); > > The new table (temp_major) is population as follows: the > "location_bit_data" values for a given location are "grouped" into one entry > (to create a concatenation effect), the lowest_temp reading across all the > records of the given location is noted and the location is also noted, this > data is used in populating the table. > > The solution I have so far involves using a stored procedure and cursors > (on Select .. order by location) to continuously "grow" the data for a given > location's "overall_location_bit_data" field. > > Allan. > sounds like you need a custom aggregate function. http://www.postgresql.org/docs/current/static/xaggr.html however it's not clear how you want to aggregate; what does your actual grouping function do? general pattern is: CREATE FUNCTION varbit_concat(varbit,varbit) returns varbit as 'whatever you need' language 'of your choice' immutable; CREATE AGGREGATE agg_varbit_concat ( varbit ) ( SFUNC = varbit_concat, STYPE = varbit -- check CREATE AGGREGATE syntax, maybe you need something fancy here ); -- and finally: SELECT location, min(lowest_temp) as lowest_overall_temp, agg_varbit_concat(location_bit_data) as overall_location_bit_data FROM temp; -- Filip Rembiałkowski
Re: [SQL] Is there a bug in PostgreSQL ?
"Pascal Tufenkji" <[EMAIL PROTECTED]> writes: > I'm writing a query with a left join to a view, and the server is giving me > a wrong result. What PG version? 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] Is there a bug in PostgreSQL ?
Pascal Tufenkji wrote: > > SELECT distinct c.emp_id,c.institution,d.* from sip_carriere c left join > sip_demissionaire d on d.emp_id = c.emp_id where c.emp_id = 342 ; [snip - rows] > > SELECT distinct c.emp_id,c.institution,d.* from sip_carriere c left join > sip_demissionaire d on d.emp_id = c.emp_id where c.institution = 1; [snip - rows] > > BUT IF I PUT BOTH CONDITIONS > > > > SELECT distinct c.emp_id,c.institution,d.* from sip_carriere c left join > sip_demissionaire d on d.emp_id = c.emp_id where c.emp_id = 342 and > c.institution = 1; [snip - no rows] > What's the problem ? > > I'm sure that the problem is with the view "sip_demissionaire" cause when I > copied its content to a temp table, the query returned a result. > SELECT * into temp foo from sip_demissionaire ; > > SELECT distinct c.emp_id,c.institution,d.* from sip_carriere c left join foo > d on d.emp_id = c.emp_id where c.emp_id = 342 and c.institution = 1; Good testing. It looks to me like you have a corrupted index. If you run EXPLAIN ANALYSE SELECT ... for each of your queries, you'll probably see that the one that returns no rows is using a particular index that the other queries aren't. Have you had any crashes / power failures / disk errors recently? Oh - and what version of PostgreSQL is this? -- Richard Huxton Archonet Ltd -- 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] Best way to "and" from a one-to-many joined table?
Milan Oparnica wrote: > This is how I do it, and it runs fast: > select p.* > from test_people p inner join test_attributes a on p.people_id = > a.people_id > where a."attribute" = @firstAttr or a."attribute" = @secondAttr But that does an "or" search, not "and", returning Satan in addition to Obama: select * from test_people p inner join test_attributes a on p.people_id = a.people_id lyell5-> where a."attribute" = 'Dark Hair' or a."attribute" = 'USA President'; +---+-+---+---+ | people_id | person_name | people_id | attribute | +---+-+---+---+ | 8 | Obamba | 8 | USA President | | 8 | Obamba | 8 | Dark Hair | | 8 | Obamba | 8 | Dark Hair | |10 | Satan |10 | Dark Hair | +---+-+---+---+ How can I get an AND search (people with Dark Hair AND who are President)? -- 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] Best way to "and" from a one-to-many joined table?
*How can I get an AND search (people with Dark Hair AND who are President)?* The two joins didn't work? Or were they too slow ? Best, Oliveiros 2008/12/10 Bryce Nesbitt <[EMAIL PROTECTED]> > > > Milan Oparnica wrote: > > This is how I do it, and it runs fast: > > select p.* > > from test_people p inner join test_attributes a on p.people_id = > > a.people_id > > where a."attribute" = @firstAttr or a."attribute" = @secondAttr > But that does an "or" search, not "and", returning Satan in addition to > Obama: > > select * from test_people p inner join test_attributes a on p.people_id > = a.people_id > lyell5-> where a."attribute" = 'Dark Hair' or a."attribute" = 'USA > President'; > +---+-+---+---+ > | people_id | person_name | people_id | attribute | > +---+-+---+---+ > | 8 | Obamba | 8 | USA President | > | 8 | Obamba | 8 | Dark Hair | > | 8 | Obamba | 8 | Dark Hair | > |10 | Satan |10 | Dark Hair | > +---+-+---+---+ > > How can I get an AND search (people with Dark Hair AND who are President)? > > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql >
[SQL] Is it possible to only allow deletes from a table via referential integrity cascades?
I have two tables joined by a foreign key constraint: CREATE TABLE test_master( id SERIAL PRIMARY KEY, foo TEXT ); CREATE TABLE test_detail( id SERIAL PRIMARY KEY, master BIGINT NOT NULL REFERENCES test_master(id) ON DELETE CASCADE ON UPDATE CASCADE, bar TEXT ); Is there a way to block deletes on the "test_detail" table that will only allow rows to be deleted if it is the result of deleting the corresponding "test_master" record? In other words, I'd like to disallow direct DELETE commands like this: DELETE FROM test_detail WHERE id = 1; while allowing a command like DELETE FROM test_master WHERE id = 1; to subsequently delete via CASCADE all "test_detail" rows that reference test_master # 1. I've tried using rules and revoking privileges, but both of these approaches fail when trying to delete from "test_master". Thanks in advance, Chris -- 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] Collapsing (select) row values into single text field.
Thanks Filip for the User Defined Aggregates information I will look into it, and compare its performance with the another probable solution (explained next). I do recall making use of arrays (then array_to_string()) to do this kind of collapsing as a subquery in the select clause of a group by query, but I can not recall the actual syntax. Allan. On Wed, Dec 10, 2008 at 4:45 PM, Filip Rembiałkowski <[EMAIL PROTECTED]> wrote: > > > 2008/12/10 Allan Kamau <[EMAIL PROTECTED]> >> >> Hi all, >> I would like to concatenate the field values of several rows in a table >> that meet some similarity criteria based on a the values of some other >> field (more like a group by). Then I would also like to also include the >> lowest value of another associated field along. >> >> I have a table that contains 3 fields of interest. >> create table temp >> (id INTEGER NOT NULL >> ,location TEXT NOT NULL --this will hold the zip code >> ,lowest_temp NUMERIC(5,2) NOT NULL --The lowest temperature at some given >> night >> ,location_bit_data VARBIT NOT NULL >> ,PRIMARY KEY(id) >> ); >> >> There will be usually more than one record for a location >> (location+lowest_temp is not unique either). >> Now I would like to collapse the data in this table (an populate another >> table) as follows. >> Lets assume this table has the structure below. >> >> create table temp_major >> (id INTEGER NOT NULL >> ,location TEXT NOT NULL --this will hold the zip code >> ,lowest_overall_temp NUMERIC(5,2) NOT NULL --The lowest temperature at >> some given night >> ,overall_location_bit_data VARBIT NOT NULL >> ,PRIMARY KEY(id) >> ,UNIQUE(location) >> ); >> >> The new table (temp_major) is population as follows: the >> "location_bit_data" values for a given location are "grouped" into one entry >> (to create a concatenation effect), the lowest_temp reading across all the >> records of the given location is noted and the location is also noted, this >> data is used in populating the table. >> >> The solution I have so far involves using a stored procedure and cursors >> (on Select .. order by location) to continuously "grow" the data for a given >> location's "overall_location_bit_data" field. >> >> Allan. > > > sounds like you need a custom aggregate function. > http://www.postgresql.org/docs/current/static/xaggr.html > > however it's not clear how you want to aggregate; what does your actual > grouping function do? > > general pattern is: > > CREATE FUNCTION varbit_concat(varbit,varbit) > returns varbit > as 'whatever you need' language 'of your choice' immutable; > > CREATE AGGREGATE agg_varbit_concat ( varbit ) ( > SFUNC = varbit_concat, > STYPE = varbit > -- check CREATE AGGREGATE syntax, maybe you need something fancy here > ); > > > -- and finally: > > SELECT > location, > min(lowest_temp) as lowest_overall_temp, > agg_varbit_concat(location_bit_data) as overall_location_bit_data > FROM temp; > > > -- > Filip Rembiałkowski > -- 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] unique constraint on views
I want to put unique constraint on columns of more than one table. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of A. Kretschmer Sent: Wednesday, December 10, 2008 6:04 PM To: pgsql-sql@postgresql.org Subject: Re: [SQL] unique constraint on views In response to Jyoti Seth : > Hi All, > > Is it possible to add unique constraint on updateable views in postgres? Add the constraint to the base-table. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net -- 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
