Re: [SQL] Calculate the ratio
SELECT cols, SUM( CASE WHEN cf = 1 THEN 1 ELSE 0 END ) AS sum_1, SUM ( CASE WHEN cf = 0 THEN 1 ELSE 0 END ) AS sum_0 FROM table WHERE cf IN (0, 1) GROUP BY cols From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org] On Behalf Of Andreas Forø Tollefsen Sent: Monday, August 15, 2011 8:22 AM To: pgsql-sql@postgresql.org Subject: [SQL] Calculate the ratio Hi all, This is probably quite simple. I want to calculate the ratio of each country (gwcode) which experiences a 1 in one column (cf). Structure: gid; gridyear; gwcode; area; cf I want to select the SUM(area) WHERE cf = 1 into one column, and SUM(area) WHERE cf = 0 into another column and group by gridyear and gwcode. Then i can see the ratio of the area of each country affected by cf. Any suggestions? Thanks. A.
Re: [SQL] Calculate the ratio
Replace the 1 in the case with area like the msi77 said From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org] On Behalf Of David Johnston Sent: Monday, August 15, 2011 9:08 AM To: 'Andreas Forø Tollefsen'; pgsql-sql@postgresql.org Subject: Re: [SQL] Calculate the ratio SELECT cols, SUM( CASE WHEN cf = 1 THEN 1 ELSE 0 END ) AS sum_1, SUM ( CASE WHEN cf = 0 THEN 1 ELSE 0 END ) AS sum_0 FROM table WHERE cf IN (0, 1) GROUP BY cols From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org] On Behalf Of Andreas Forø Tollefsen Sent: Monday, August 15, 2011 8:22 AM To: pgsql-sql@postgresql.org Subject: [SQL] Calculate the ratio Hi all, This is probably quite simple. I want to calculate the ratio of each country (gwcode) which experiences a 1 in one column (cf). Structure: gid; gridyear; gwcode; area; cf I want to select the SUM(area) WHERE cf = 1 into one column, and SUM(area) WHERE cf = 0 into another column and group by gridyear and gwcode. Then i can see the ratio of the area of each country affected by cf. Any suggestions? Thanks. A.
Re: [SQL] which is better: using OR clauses or UNION?
-Original Message-
From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org]
On Behalf Of adam_pgsql
Sent: Tuesday, August 16, 2011 7:39 AM
To: pgsql-sql
Subject: [SQL] which is better: using OR clauses or UNION?
Hi,
I have a query hitting a table of 25 million rows. The table has a text
field ('identifier') which i need to query for matching rows. The question
is if i have multiple strings to match against this field I can use multiple
OR sub-statements or multiple statements in a UNION. The UNION seems to run
quicker is this to be expected? or is there anything else I can do
improve the speed of this query? Some query details:
You can also try the following form:
... WHERE column = ANY( string_to_array ( lower( 'Bug1,Bug2,Bug3' ) , ',' )
);
The main advantage of this is that you can parameterize the input string and
so it will not logically matter how many values you are checking for.
Also, you can always just place the values you want to search for into a
table (temp or otherwise) and perform an Inner Join.
No idea which one is "faster" but the "string_to_array" above requires no
Dynamic SQL which all of the other forms (OR, UNION, IN) need. The table
form also does not require dynamic SQL but you need additional INSERTS for
each search value. I doubt it would be any better than the (OR/UNION/JOIN)
form but you could also create a VALUES virtual table - which also requires
Dynamic SQL.
SELECT *
FROM target_table
NATURAL JOIN ( VALUES ('Bug1'),('Bug2'),('Bug3') ) searchtarget (
join_column_name );
David J.
--
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] Add one column to another
I have to deal with a table which contains: first_name surname email1 email2 ... and I would like to create a view which combines both email columns thus: first_name surname email It looks simple but I can't think of an obvious query. --- SELECT first_name, surname, ARRAY[email1, email2] AS email FROM [...] David J. -- 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] function based index problem
From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org] On Behalf Of Viktor Bojovic Sent: Wednesday, August 31, 2011 5:27 PM To: pgsql-sql@postgresql.org; pgsql-ad...@postgresql.org Subject: [SQL] function based index problem Hi, on table entry (17M records) there is one index: CREATE INDEX ndxlen ON uniprot_frekvencije.entry USING btree (length(sequence::text)); When using ">=" in search which returns only two records, query runs much (hundred times) slower. i don't know why it doesn't use index scan. I just wanted to ask how can i modify the query to use that index? Explain plans are pasted below. bioinf=> explain select * from entry where length(sequence)=36805; QUERY PLAN Bitmap Heap Scan on entry (cost=1523.54..294886.26 rows=81226 width=1382) Recheck Cond: (length((sequence)::text) = 36805) -> Bitmap Index Scan on ndxlen (cost=0.00..1503.23 rows=81226 width=0) Index Cond: (length((sequence)::text) = 36805) (4 rows) bioinf=> explain select * from entry where length(sequence)>=36805; QUERY PLAN Seq Scan on entry (cost=0.00..5400995.21 rows=5415049 width=1382) Filter: (length((sequence)::text) >= 36805) (2 rows) Thanx in advance -- --- Viktor Bojović --- Wherever I go, Murphy goes with me Some observations/suggestions: Please do not Cross-Post You have not provided your PostgreSQL version You state the “>=” query only returns 2 rows but the plan expects to return 5.4 MILLION – with that many results Sequential Scan is going to be faster than an Index Either you have not run “ANALYZE” or you have more data than you think matching your criteria. Try “EXPLAIN ANALYZE” to actually run the query and see what you get. It is likely that a simple ANALYZE on the table will solve your problem (ALWAYS RUN ANALYZE BEFORE POSTING QUESTIONS LIKE THIS); in the unlikely event it does not please post the “EXPLAIN ANALYZE” results so we can see exactly how many records each query returned. David J.
Re: [SQL] Schema partitioning
On Sep 1, 2011, at 14:13, Charlie wrote: > Could I get feedback from the community on schema partitioning? > > I'm doing maintenance on my ddl and I'm noticing that my tables are all in 1 > schema, but they have prefixes on their names like table_app1_sometable, > table_app1_secondtable, table_app2_anothertable, > table_priviledged_restrictedtable1, etc. The table_app1 tables seem to want > to go in their own schema "app1", etc, and drop the prefixes. Except they'll > still be there, as in app1.sometable. > > Is this just style? Or are there concrete benefits to partitioning? > > Mostly style but some ease-of-use when it comes to permissions as well. It's really no different than why you'd use sub-directories in your OS instead of putting everything in C/root. It does give you namespaces features as well (I.e., duplicate names but in different contexts). David J. -- 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] Sorting of data from two tables
On Sep 17, 2011, at 9:32, "R. Smith" wrote: > > What I want to do is do a query joining table A with B and sorting > firstly on a field in Table A then on several fields in Table B. > > > SELECT a.gdn_gdn, a.gdn_custref, a.gdn_date, a.gdn_address_name, > a.gdn_method, b.gdn_stockref, b.gdn_row, b.gdn_bay, b.gdn_shelf > FROM a > LEFT JOIN b > ON a.gdn_gdn = b.gdn_gdn > ORDER BY a.gdn_method,b.gdn_row, b.gdn_bay, b.gdn_shelf > It would help if you gave an example comparing the above query results with your desired results. It would also help if you actually provided a detailed description of you goal instead of the generic one quoted above. Given your stated need the query does exactly what you requested. David J.
Re: [SQL] Combining several CTEs with a recursive CTE
On Sep 20, 2011, at 5:58, Thomas Kellerer wrote: > > I'm just wondering if this is intended behavioury, simply not (yet) > implemented or even invalid according to the standard? I didn't find any > reference that it's not allowed in the manual. > > Regards > Thomas > > Try sticking the recursive keyword after the "with" if any of the following CTEs are recursive. WITH RECURSIVE normal1 AS () ,recursine1 AS () ,normal2 AS () ,recursine2 AS () SELECT ... David J.
Re: [SQL] ambiguous local variable name in 9.0 proc
From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org] On Behalf Of Samuel Gendler Sent: Wednesday, September 21, 2011 7:35 PM To: pgsql-sql@postgresql.org Subject: [SQL] ambiguous local variable name in 9.0 proc I've got a stored proc (which worked fine in 8.3 and 8.4) that is declared as such: CREATE OR REPLACE FUNCTION reporting_mgmt.aggregate_timescales_impl ( div_start TIMESTAMP WITHOUT TIME ZONE, tbl_schema VARCHAR, tbl_root VARCHAR, fine_timescale VARCHAR, coarse_timescale VARCHAR, coarser_timescale VARCHAR, fact_fields VARCHAR, dim_fields VARCHAR, sum_fields VARCHAR) RETURNS INTEGER AS $$ Within that proc, I've got the following line: IF EXISTS ( SELECT table_name FROM information_schema.tables WHERE table_schema = tbl_schema AND table_name = tbl_fine_part_old ) THEN IF EXISTS ( SELECT status FROM reporting_mgmt.etl_status AS e WHERE tbl_schema = e.tbl_schema AND tbl_root = e.tbl_root AND div_start_old = e.fine_time AND coarse_timescale = e.coarse_scale AND status = 0 ) THEN And in 9.0, it is generating the following error: ERROR: column reference "tbl_schema" is ambiguous LINE 2: WHERE tbl_schema = e.tbl_schema ^ DETAIL: It could refer to either a PL/pgSQL variable or a table column. QUERY: SELECT EXISTS ( SELECT status FROM reporting_mgmt.etl_status AS e WHERE tbl_schema = e.tbl_schema AND tbl_root = e.tbl_root AND div_start_old = e.fine_time AND coarse_timescale = e.coarse_scale AND status = 0 ) CONTEXT: PL/pgSQL function "aggregate_timescales_impl" line 52 at IF PL/pgSQL function "aggregate_timescales" line 23 at RETURN Of course, it is true that tbl_schema could refer to the column in table etl_status, except older versions of postgres seemed to correctly figure out that comparing a column to itself isn't terribly useful, so it must be referring to the pl/pgsql variable rather than the table column. I'm happy to modify the proc definition, except that I am unsure how to do so other than to rename the variable, which is my least favourite way to do that. I'd far rather qualify the name somehow, so that it knows that I am refering to a local variable, if at all possible. Suggestions? Not tested but I think all local variables are implicitly scoped to the function name so you should be able to do the following: WHERE reporting_mgmt.aggregate_timescales_impl.tbl_schema = e.tbl_schema You are going to have the same problem with other fields as well (e.g., tbl_root). I believe there is some way to define the function so that it does not throw that particular error; it would be up to you make sure that the ambiguity is being resolved correctly (which it should in this particular case). Maybe look for "SET" variables. You could also copy the tbl_schema variable value into a different variable. DECLARE tbl_schema_copy VARCHAR; tbl_schema_copy := tbl_schema; . WHERE tbl_schema_copy = e.tbl_schema . David J.
Re: [SQL] using the aggregate function max()
On Sep 22, 2011, at 22:49, John Fabiani wrote: > Hi, > I need a little help understanding how to attack this problem. > > I need to find the max(date) of a field but I need that value later in my > query. > > If I > select max(x.date_field) as special_date from (select date_field) from table > where ...)x > > I get one row and column. > > But now I want to use that field in the rest of the query > > select y.*, max(x.date_field) as special_date from (select date_field) from > table where ...)x > from aTable y where y.somefield = special_date. > > The above only returns one row and one column the "special_date." > > How can I use the aggregate field "special_date" in the rest of the query? > Or > is there some other way? > > Johnf > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql Your query above is syntactically invalid. Try this. WITH max_date AS ( select max(datefield) AS specialdate from ...) SELECT * FROM table JOIN max_date ON table.somefield = max_date.specialdate; You can use a online query instead of the WITH if desired, same effect. You could also drop the join and use the max_date CTE in a WHERE clause: ... FROM table WHERE table.somefield = (SELECT specialdate FROM max_date) David J -- 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] Sorting of data from two tables
On Sep 25, 2011, at 12:37, "R. Smith" wrote: > On Sat, Sep 17, 2011 at 2:56 PM, David Johnston wrote: >> On Sep 17, 2011, at 9:32, "R. Smith" wrote: >> >> >> What I want to do is do a query joining table A with B and sorting >> firstly on a field in Table A then on several fields in Table B. >> >> >> SELECT a.gdn_gdn, a.gdn_custref, a.gdn_date, a.gdn_address_name, >> a.gdn_method, b.gdn_stockref, b.gdn_row, b.gdn_bay, b.gdn_shelf >> FROM a >> LEFT JOIN b >> ON a.gdn_gdn = b.gdn_gdn >> ORDER BY a.gdn_method,b.gdn_row, b.gdn_bay, b.gdn_shelf >> >> >> It would help if you gave an example comparing the above query results with >> your desired results. It would also help if you actually provided a >> detailed description of you goal instead of the generic one quoted above. >> Given your stated need the query does exactly what you requested. >> David J. > > Ok, here is some more specific information. The data stored is > inventory pick data. > We have a table which stores all the header information, addresses etc > (Table A) and > then each order line (Table B) which stores item to be picked and location. > > There isn't any useable way to output a variable number of columns. So, you need to decide how the data is going to be used and specify the order by accordingly. You can use window functions to capture some of the order information from discontiguous lines; including ARRAY_AGG(). You should also ask yourself if you are trying to do too much with a single query/report. If you are stuck with CSV export your non-SQL options are limited but maybe your reporting environment can assist. If you are stuck with CSV you could try using array_agg and then convert the resultant array to a formatted string for output. CSV would surround the entire formatted output in quotes but maybe you could post-process the result to remove the quotes so that, say Excel, would see the string as simply being additional columns in the file. David J. -- 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] plpgsql function executed multiple times for each return value
> So my questions are: 1) How do we cause the paymentcalc function to be > executed only once? and 2) How do we call a table returning function with > inputs from a table? > > Thank you very much! > > Steve > WITH func AS ( SELECT FUNC(...) AS func_result FROM ... ) SELECT (func.func_result).* FROM func David J.
Re: [SQL] advice on how to store variable attributes
On Oct 22, 2011, at 6:41, Linos wrote: > Hi all, >i need a little of advice on what could be the best way to store this > information. > > We need to calculate the difference in costs for our operations, we are > already > storing our vendor invoices in the database so calculate the monetary change > it > is a no-brainer but we need to store special attributes for any of the > invoices > that we need to compare too, for example: >-electric provider: total Kw. >-water provider: total m3. >-car maintenance: kilometers of the car. >-mobile phones provider: international call minutes, national minutes, > number > of sms, etc.. > > And much more types/variables, the number of variables can change, not every > day > but still can change, i would like that they can be defined/changed from our > application, so alter table to add columns don't seem the best way (still an > option though). We will have "generic" reports that will show us changes in > costs and specific reports for the types with "extended attributes" that we > want > to compare. > > To compare values from this "extended attributes" i think we have two ways: >1- have them in columns and use standard SQL. >2- create the columns with a function that reads this attrs and create the > columns. > > So far i thin we have this options: >1- a bunch of columns that would be null except when the type of the > invoice > uses them. >2- a table related with the vendor invoices table for every type of invoice > with his specifics columns. >3- a key/value in a separate table related with the vendor invoices table > where > i store the extended attrs of every invoice that needs them. >4- use a hstore column in the vendor invoces table to store this attrs. > > The first two have the problem of probably changes to the number of attributes > of every type and give a more closed solution, apart from that 1- seems to be > a > bit awkward and 2- would need the application that creates the query to know > with what table should join for every type (other point we will need to change > if we want to create new invoices types). > > The last two have his own problems too, with 3 i will need to create a > function > that return rows as columns to compare them, with 4- given that i will store > the > attrs of every type in the database anyway i can use the operator -> (with a > CASE using operator ? returning 0 if the searched attr it is not in the > hstore) > but still don't seem a clean solution for me. > > For me it seems i am missing something, probably any of you have a much more > elegant (or correct) way to handle this situation, what would be your advice? > Thanks. > > Create a table with a single numeric column and multiple category columns. ( amount_value, amount_unit, amount_category, vendor_id ) If necessary each "amount_value" data type should have it's own table since the processing logic will vary (I.e., you cannot subtract text or Boolean values). You are , in effect, creating multiple tables but combining them into one and using the category column to distinguish between them. David J. -- 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] advice on how to store variable attributes
On Oct 22, 2011, at 11:39, Linos wrote: > El 22/10/11 14:53, David Johnston escribió: >> On Oct 22, 2011, at 6:41, Linos wrote: >> >>> Hi all, >>> i need a little of advice on what could be the best way to store this >>> information. >>> >>> We need to calculate the difference in costs for our operations, we are >>> already >>> storing our vendor invoices in the database so calculate the monetary >>> change it >>> is a no-brainer but we need to store special attributes for any of the >>> invoices >>> that we need to compare too, for example: >>> -electric provider: total Kw. >>> -water provider: total m3. >>> -car maintenance: kilometers of the car. >>> -mobile phones provider: international call minutes, national minutes, >>> number >>> of sms, etc.. >>> >>> And much more types/variables, the number of variables can change, not >>> every day >>> but still can change, i would like that they can be defined/changed from our >>> application, so alter table to add columns don't seem the best way (still an >>> option though). We will have "generic" reports that will show us changes in >>> costs and specific reports for the types with "extended attributes" that we >>> want >>> to compare. >>> >>> To compare values from this "extended attributes" i think we have two ways: >>> 1- have them in columns and use standard SQL. >>> 2- create the columns with a function that reads this attrs and create >>> the columns. >>> >>> So far i thin we have this options: >>> 1- a bunch of columns that would be null except when the type of the >>> invoice >>> uses them. >>> 2- a table related with the vendor invoices table for every type of >>> invoice >>> with his specifics columns. >>> 3- a key/value in a separate table related with the vendor invoices table >>> where >>> i store the extended attrs of every invoice that needs them. >>> 4- use a hstore column in the vendor invoces table to store this attrs. >>> >>> The first two have the problem of probably changes to the number of >>> attributes >>> of every type and give a more closed solution, apart from that 1- seems to >>> be a >>> bit awkward and 2- would need the application that creates the query to know >>> with what table should join for every type (other point we will need to >>> change >>> if we want to create new invoices types). >>> >>> The last two have his own problems too, with 3 i will need to create a >>> function >>> that return rows as columns to compare them, with 4- given that i will >>> store the >>> attrs of every type in the database anyway i can use the operator -> (with a >>> CASE using operator ? returning 0 if the searched attr it is not in the >>> hstore) >>> but still don't seem a clean solution for me. >>> >>> For me it seems i am missing something, probably any of you have a much more >>> elegant (or correct) way to handle this situation, what would be your >>> advice? >>> Thanks. >>> >>> >> >> Create a table with a single numeric column and multiple category columns. >> >> ( amount_value, amount_unit, amount_category, vendor_id ) >> >> If necessary each "amount_value" data type should have it's own table since >> the processing logic will vary (I.e., you cannot subtract text or Boolean >> values). >> >> You are , in effect, creating multiple tables but combining them into one >> and using the category column to distinguish between them. >> >> David J. > > Yeah, thanks for the advice David, if i understand you. this is (much better > explained) my option 3, i have used this format any times for configuration > tables with great success. > > I am not speaking about much data, maybe 200~300 invoices every month so this > should not be a problem in a long long time, the problem i see with this way > of > doing things it is how should i in the report queries get this values as > columns > to compare change in time? something like this? > > SELECT inv.invoice_id, > inv.total, > (SELECT amount_value > FROM vendor_invoices_attrs > WHERE invoice_id = inv.invoice_id > AND amount_category = 'international call minu
Re: [SQL] Handling mutliple clients access with views
On Oct 24, 2011, at 22:54, Craig Ringer wrote: > On 25/10/11 03:23, Brice André wrote: >> Hello everyone, >> >> I am developping a web service where some tables are handling the data >> of different clients. Depending on configured rights, one client can >> have no access, or read access, or read and write access to other >> clients data. >> >> In order to handle that, I am using views and, to ensure that a client >> cannot access data outside the view, all clients info is stored in a >> table where its postgresql user name is also stored. So, in order to >> limit access of view, I am using the postgresql special function >> "current_user()" and I am retrieving the id of my client like this. > > That sounds ... roundabout. > > Why not use roles and role inheritance? You can use SET ROLE to > temporarily change roles, log in as different roles, have one role be a > member of other roles, have role access permissions on tables/views at > the column or table level, etc. > >> - My method requests that each user has its own postgresql user. But, >> in this case, my web server needs to establish a postgresql connection >> for each user, which will maybe cause problems. > > Connect as a single user, then SET ROLE to the user you want in order to > control access. > > Instead of using current_user() and programmatic security checking, use > GRANT and REVOKE for declarative access checking where possible. > >> So, I was guessing if I was not completely wrong by doing like that. >> Maybe is there a simpler way of doing what I try to do ? Or maybe am I a >> little bit too paranoïde, and maybe should I handle all my clients with >> a single postgresql user, handling all safety aspect in my php script ? > > Nope, I heartily approve of doing security in-database, especially if > you can do it declaratively. > > -- > Craig Ringer > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql Except database roles cannot strictly enforce row-level security (i.e., multiple-tenant) which is the goal of this setup. Views are not fool-proof in providing row-level security, for that you need functions. While a view itself will not provide the protected data a function can be used to process data (via RAISE NOTICE) that would otherwise be filtered out in the end result. This is because views are simply re-write rules. David J. -- 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] advice on how to store variable attributes
On Oct 22, 2011, at 10:07, Pavel Stehule wrote: > 2011/10/22 David Johnston : >> On Oct 22, 2011, at 6:41, Linos wrote: >> >>> Hi all, >>>i need a little of advice on what could be the best way to store this >>> information. >>> >>> We need to calculate the difference in costs for our operations, we are >>> already >>> storing our vendor invoices in the database so calculate the monetary >>> change it >>> is a no-brainer but we need to store special attributes for any of the >>> invoices >>> that we need to compare too, for example: >>>-electric provider: total Kw. >>>-water provider: total m3. >>>-car maintenance: kilometers of the car. >>>-mobile phones provider: international call minutes, national minutes, >>> number >>> of sms, etc.. >>> >>> And much more types/variables, the number of variables can change, not >>> every day >>> but still can change, i would like that they can be defined/changed from our >>> application, so alter table to add columns don't seem the best way (still an >>> option though). We will have "generic" reports that will show us changes in >>> costs and specific reports for the types with "extended attributes" that we >>> want >>> to compare. >>> >>> To compare values from this "extended attributes" i think we have two ways: >>>1- have them in columns and use standard SQL. >>>2- create the columns with a function that reads this attrs and create >>> the columns. >>> >>> So far i thin we have this options: >>>1- a bunch of columns that would be null except when the type of the >>> invoice >>> uses them. >>>2- a table related with the vendor invoices table for every type of >>> invoice >>> with his specifics columns. >>>3- a key/value in a separate table related with the vendor invoices >>> table where >>> i store the extended attrs of every invoice that needs them. >>>4- use a hstore column in the vendor invoces table to store this attrs. >>> >>> The first two have the problem of probably changes to the number of >>> attributes >>> of every type and give a more closed solution, apart from that 1- seems to >>> be a >>> bit awkward and 2- would need the application that creates the query to know >>> with what table should join for every type (other point we will need to >>> change >>> if we want to create new invoices types). >>> >>> The last two have his own problems too, with 3 i will need to create a >>> function >>> that return rows as columns to compare them, with 4- given that i will >>> store the >>> attrs of every type in the database anyway i can use the operator -> (with a >>> CASE using operator ? returning 0 if the searched attr it is not in the >>> hstore) >>> but still don't seem a clean solution for me. >>> >>> For me it seems i am missing something, probably any of you have a much more >>> elegant (or correct) way to handle this situation, what would be your >>> advice? >>> Thanks. >>> >>> >> >> Create a table with a single numeric column and multiple category columns. >> >> ( amount_value, amount_unit, amount_category, vendor_id ) > > > This is EAV model - is good for smaller datasets, for larger datasets > is problematic. There is second possibility - using a "hstore" contrib > module - that emulates HASH table - It has better for larger datasets. > > Regards > > Pavel Stehule Store was mentioned by the OP. Any suggestions on where the line between small and large is drawn? Partitions could help in the larger cases. My personal first choice is to use separate tables. If going EAV route plan on eventually moving to the separate table route and at least try to make migration relatively easy. Since both models capture the same data the decision at least partially rests upon the immediate use-cases for the data. Lacking use-cases and size parameters recommendations are difficult to make. I have not used hstore so my ability to recommend it is limited. My main "concern" is the fact that the data is less visible and always stored as text. I see hstore as being useful for situations where the attributes are variable but this use case seems to have fairly well-defined attributes. David J. > >> >> If necessary each "amount_
Re: [SQL] Create Type with typmod_in
On Nov 4, 2011, at 10:01, Russell Keane wrote: > I’m trying to create a custom type (PostgreSQL 9.0) which will essentially > auto truncate a string to a certain length. > > > > > > Can this be done purely in plpgsql? > > If so, how? > > > An explicit cast of a value to varchar(n) causes the value to be truncated to n characters. E.g., '1234567'::varchar(5) -> '12345' How to integrate that knowledge into a type I do not know. David J. >
Re: [SQL] the use of $$string$$
On Nov 4, 2011, at 11:26, John Fabiani wrote: > On Friday, November 04, 2011 07:38:29 am John Fabiani wrote: >> Hi, >> I just discovered that I can use $$string$$ to account for the problem of >> single quotes in the string (or other strange char's). However, I noticed >> that the table field contained E'string'. I actually tried to find info on >> this but I did not find anything. >> >> Could someone explain what it means or better provide a web link for me to >> discover the info. >> >> Thanks in advance, >> Johnf > > > Thank you both for the quick replies and the links. > > What I still do not understand (I'm a little slow) is the fact that pgadmin3 > showed the data as E'string'. So I'm wondering if the data is now different > in the sense that I need use the 'E' in the field. > > For example I have the last name of O'Reilly > the field lname now appears to contain E'O'Reilly' > > So will the normal select statements work; > > Select * from table where lname = 'O'' Reilly' > Select * from table where lname like 'O%' > > or will I need to know the data contains the "E" and add the 'E' to the sql > statements? > > Thanks > Johnf > > Try it and see. If, indeed, the E is part of the data you should fix your process. Done correctly there should be no difference in the end result regardless of how you choose to identify your strings. Don't use pgadmin3 myself so I don't know if what you are looking at would include the E. If it is outputting DDL (I.e., INSERT statements) it might but simple SELECT results should not. David J. -- 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] Issue with a variable in a function
-Original Message- From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org] On Behalf Of tlund79 Sent: Tuesday, November 08, 2011 8:17 AM To: pgsql-sql@postgresql.org Subject: [SQL] Issue with a variable in a function The issue relates to the variable "prosjektkode" ($4). CREATE OR REPLACE FUNCTION ppr_data(aarstall int, frauke int, tiluke int, prosjektkode int) RETURNS int AS $$ DECLARE antall bigint; ---/Original Message-- Read about "ARRAY"s Change your function signature to something like: CREATE OR REPLACE FUNCTION ppr_data(aarstall int, frauke int, tiluke int, prosjektkode int[] ) -- Note the change to int[] from int for prosjektkode David J. -- 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] Updatable view should truncate table fields
From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org] On Behalf Of Russell Keane Sent: Tuesday, November 08, 2011 4:34 PM To: pgsql-sql@postgresql.org Subject: [SQL] Updatable view should truncate table fields Using PostgreSQL 9.0. We have a table which is not accessible by client code. We also have views with rules and triggers to intercept any insert or update statements and write that data in a slightly different format back to the table. A particular field in the table is currently 5 chars but recently we have had update / insert statements containing more than 5. This obviously (and correctly) throws an error. We can extend the table to accept more than 5 characters but the view must return 5 characters. If we try to extend the table to accept, say, 10 characters the view will display 10. If I also cast the view field to 5 characters then any insert with more than 5 characters still fails. - Haven't used updatable VIEWs yet but couldn't you either define the VIEW as: CREATE VIEW AS ( SELECT field1, field2::varchar(5) AS field2 FROM table ); Or, alternatively, define the INSERT/UPDATE functions to perform the truncation upon inserting into the table? Does the INSERT itself throw the error or is one of your functions raising the error when it goes to insert the supplied value into the table? It is generally bad form to modify user data for storage so either you truly have a length limitation that you need to restrict upon data entry (in which case everything is working as expected) or you should allow any length of data to be input and, in cases where the output medium has length restrictions, you can ad-hoc limit the display length of whatever data was provided. David J. -- 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] clarification about ARRAY constructor implementation
On Nov 11, 2011, at 8:38, the6campbells wrote: > consider the following > > create table TARRBINT ( RNUM integer not null , CARRBINT bigint array[5] ) ; > > Can someone clarify why Postgres does not like examples 2, 6 and 7 An array must have a base type; an empty array has nothing with which to infer a type so you must cast it yourself. Null is typeless and so you need an explicit cast if all values are null. > > 1.insert into TARRBINT(RNUM, CARRBINT) values ( 0, null); > 2.insert into TARRBINT(RNUM, CARRBINT) values ( 0, ARRAY[]); > 3.insert into TARRBINT(RNUM, CARRBINT) values ( 0, ARRAY[]::integer[]); > 4.insert into TARRBINT(RNUM, CARRBINT) values ( 0, ARRAY[null,1]); > 5.insert into TARRBINT(RNUM, CARRBINT) values ( 0, ARRAY[1, null]); > 6.insert into TARRBINT(RNUM, CARRBINT) values ( 0, ARRAY[null]); > 7.insert into TARRBINT(RNUM, CARRBINT) values ( 0, ARRAY[null,null]); > > Similarly, why does it seem to force casting expressions with other instances > of literal values used in the array constructor. Is this due to Postgres > treating some literal values to be a 'lob' type and thus concluding that it > cannot be used in the context of array constructor? > > For example, this will work > > create table TARRCHAR ( RNUM integer not null , CARRCHAR char(72) array[5] > ) ; > insert into TARRCHAR(RNUM, CARRCHAR) values ( 1, ARRAY ['']) > > But scenarios like this will not > > create table TXML ( RNUM integer not null , CXML xml ) ; > insert into TXML(RNUM, CXML) values ( 1, ''); > > create table TARRXML ( RNUM integer not null , CARRXML xml array[5] ) ; > insert into TARRXML(RNUM, CARRXML) values ( 1, ARRAY ['']); > > ERROR: column "carrxml" is of type xml[] but expression is of type text[] > LINE 1: insert into TARRXML(RNUM, CARRXML) values ( 1, ARRAY ['^ > HINT: You will need to rewrite or cast the expression. Going by observation/inference here... An array can never take on the "unknown" type whereas a scalar can. The unknown type can be passed to the Insert where the target's column type can then be used for casting. The array, needing immediate casting, chooses the most liberal type, in this case text, before it gets sent to the Insert. David J. -- 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] Change in 9.1?
-Original Message- From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org] On Behalf Of Rob Sargent Sent: Tuesday, November 22, 2011 10:30 AM To: pgsql-sql@postgresql.org Subject: Re: [SQL] Change in 9.1? On 11/22/2011 12:39 AM, Jasmin Dizdarevic wrote: > Hi, > > we have a reporting tool, that sometimes uses this kind of condition. > ...WHERE a.field = a.field > > To explain this: a.field can be filtered by the user. the user can > choose some values. if he does, this condition will be build: > ...WHERE a.field IN (1,2,3) > > If the user doesn't choose any values the * = * condition is used. > > Since 9.1 we're experiencing problems with this construction. Have > there been any changes to the planner regarding this? > > Ty > Regards, Jasmin If it's a commercial product please name that reporting tool: clearly it's to be avoided. If it's an in-house tool clearly it's broken. While probably not optimal Dynamic SQL isn't the easiest thing to construct and so having the occasional sub-optimal construct shouldn't be taken as being wrong - it should at least result in a reasonably optimal, and correct, query plan. The bigger problem is that the OP has utterly failed to describe what kinds of "problems" are being experienced AND has not provided enough data to properly evaluate the situation. Providing a self-contained test case would at least allow people to run the query and see what is happening...and at a minimum a query plan (with analyze ideally) - from both versions - would at least allow some degree of analysis even without a full test-case. Since the data matters proving all of: a test case, AND EXPLAIN ANALYZE results, AND a description of what is taken as being a problem, is necessary to provide suggestions. David J. -- 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] prepared statements
-Original Message- From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org] On Behalf Of Pavel Stehule Sent: Thursday, December 08, 2011 2:40 AM To: Vad N Cc: pgsql-sql@postgresql.org Subject: Re: [SQL] prepared statements Hello 2011/12/8 Vad N : > > Hi. > > How can i pass a set of values to prepared statement? > > example > I have a prepared query: > select * from users in ( $1 ) > > i would like to pass: 1,2,3,4 and get: > > select * from users in ( 1,2,3,4 ) > > Any ideas? use a array parameter, please regards Pavel Stehule -- Or, in lieu of readily passing arrays from your client API you can always pass a string and modify the query to parse the string into an array. ... users = ANY ( regexp_split_to_array( $1, ',' )::integer[] ) David J. -- 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] conditional FROM
On Dec 10, 2011, at 11:03, Richard Klingler wrote: > Good day... > > I'm trying to build a query for PGSQL 9.1 where a table has two > references with only one being used depending of the type of entry.. > > For example, the table has following simplified structure: > >portidprimary key >port2nodeindex to table node >port2cardindex to table card > > So how can I do a conditional FROM clause in the query depending on the > column port2node and port2card? > If port2card is Null or 0 I don't want it in the FROM clause as the > query will return unneccessary duplicate > row...the same goes for port2node being Null or 0... > > > thanx in advance > richard > > Two options (one of which may not work for you). 1. Write two queries, one for each table, and union the results. 2. Use LEFT JOINs (somehow...) David J. -- 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] using a generated series in function
On Dec 15, 2011, at 23:16, John Fabiani wrote:
> Hi,
>
> I am attempting (without success) use the generated series of dates that come
> from:
> select (cast(date_trunc('week', '2011-11-20'::date ) as date) + (i+6)) as
> week_date from generate_series(0,84,7) i
>
> in a function.
> select function_name(integer, date); -- function returns a numeric
>
> This does NOT work:
> select (function_name(303, week_date::date)) as week_date where week_date in
> (select (cast(date_trunc('week', '2011-11-20'::date ) as date) + (i+6)) as
> week_date from generate_series(0,84,7) i )
>
> The error is:
> ERROR: column "week_date" does not exist
> LINE 1: select (xchromasun._chromasun_getqtyordered(303, week_date::...
>
> I hope I can do this? What am I doing wrong?
> Johnf
>
>
"select ... as week_date where week_date in ..."
You are missing a FROM clause for the main select; the only FROM you have is
part of the IN target subquery.
David J.
--
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] using a generated series in function
>From is not required if you use literals or function results (with literal
>input parameters). If you reference something that is not one of these it has
>to come from somewhere and that location is the from/join part of the query.
In your query foo.work_date is not a literal or function and so must be sourced
from somewhere. 2011-11-20 is a literal being fed into a function and so does
not require a From clause in order to be evaluated.
David J.
On Dec 17, 2011, at 11:50, John Fabiani wrote:
> As always I respect your insights - Adrian. I do understand what I did wrong
> in my first attempt at getting my statement to work. But it is either over
> my
> head or there is something missing. Where is the "from" in
> select now()?
>
> I have been using similar SQL statements for years. I never questioned why
> there was not a 'from' until this list noted that I was missing a 'From'. I
> then went to the postgres site to read. That's how I determined what I had
> done incorrectly.
>
> I hope this is not one of those things like javascript where all white space
> is ignored unless it's not! I hate that language! It appears that
> everything
> needs a 'From' in SQL (reading the doc's) and the above statement is missing
> a
> 'From'!
>
> As always everyone - thanks for your help!
>
> Johnf
>
>
> On Friday, December 16, 2011 07:31:40 AM Adrian Klaver wrote:
>> FROM Clause
>> "select
>>
>>A sub-SELECT can appear in the FROM clause. This acts as though its
>> output were created as a temporary table for the duration of this single
>> SELECT command. Note that the sub-SELECT must be surrounded by parentheses,
>> and an alias must be provided for it. A VALUES command can also be used
>> here. "
>>
>>>
>>>
>>> select foo.week_date, xchromasun._chromasun_getqtyordered(303,
>>> foo.week_date) as week_qty from
>>>
>>> (select ((date_trunc('week', '2011-11-20'::date )::date) + (i+6)) as
>>>
>>> week_date from generate_series(0,84,7)
>>>
>>> i ) as foo
>>>
>>>
>>> The above works!
>
> --
> 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] Current transaction is aborted, commands ignored until end of transaction block
On Dec 29, 2011, at 23:25, Jan Bakuwel wrote: > Hi, > > Maybe there is a simple solution for PostgreSQL behaviour that is > annoying me... > > I've got users making updates to a master table and a number of detail > tables. All changes to the master record and related detail records are > encapsulated in a transaction so everything can be rolled back if > necessary and also to lock those master and related records for the user > making the changes. > > When they do something that violates a constraint (for example adding a > duplicate detail record where that is not allowed), PostgreSQL aborts > the transaction. What I would much rather have is that PostgreSQL > returns an error but does not cancel the transaction as it's perfectly > OK (from a user's point of view) to try to do something that violates a > constraint. > > What annoys me is that I don't think that a constraint violation made by > a user should result in an aborted transaction. There is probably a very > good reason to do that however the logic escapes me... > > Of course I can start testing existing values in the database before > accepting them in the user interface but that's putting the horse behind > the cart. I much rather use the constraints at the database level to > tell me a particular update can't be done and do that without loosing > everything else I happened to have done in that transaction until that > point. > > Any suggestions? > > Jan > > Start a "savepoint" before each sub-update and rollback to the savepoint if the update fails, and then try again with different data. If it succeeds you then release the savepoint anad move on. David J. -- 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] Nested custom types: array - unable to insert
On Dec 30, 2011, at 21:53, John Poole wrote:
> I am trying to create a custom data type for phone numbers where
> I have a primary phone number and then an array of additional
> phone numbers qualified by certain types.
>
> Below is a set of SQL commands I used to set up my custom
> types. I am unable to insert into the array field and wonder
> if I have found a bug, or I'm just missing some basic technique.
>
> Here is the error I receive when trying to insert into the array field:
>
> ERROR: cannot cast type record[] to phonenumber_type
> LINE 2: ...2,'office'), (333,'cell'),(444,'eve')]::phonenum...
>
> Can someone provide me an example of how to insert one or more
> records into the secondary array component of the type or provide
> some light on creating a custom type that would hold two fields:
> 1) a single type
> 2) an array of types (this field may be null)
>
>
> Thank you.
>
> John Poole
>
> =
> Here is the SQL to recreate my attempt:
> --
> -- Creating a custom data type and inserting/updating example
> --
> -- create database demo_typestudy1;
>
> create type phoneattribute_type as ENUM
> ('home','office','day','eve','mobile','fax');
>
>
> create type phonenumber_type as
> (numbers int,
> phone_type phoneattribute_type
> );
>
> create type contactphone_type as
> (primarynumber phonenumber_type,
> othernumbers phonenumber_type ARRAY
> );
>
> create table people
> (id integer PRIMARY KEY,
> username text NOT NULL UNIQUE,
> phone contactphone_type
> );
>
> --
> -- create a record w/o phone
> --
> insert into people
> (id, username)
> VALUES
> (1,'bob');
> --
> -- add the custom data type: contactphone, just the primary and no other
> -- we'll try adding array values later
> --
> update people
> set phone = ((1234567899,'home'),NULL)
> where id = 1;
> --
> -- also more qualified
> --
> update people
> set phone = ROW(ROW(1234567899,'home'),NULL)
> where id = 1;
> --
> -- most qualified (with casting)
> --
> update people
> set phone = CAST(ROW(CAST(ROW(1234567899,'home') as phonenumber_type),NULL)
> as contactphone_type)
> where id = 1;
> --
> -- view it
> --
> select phone
> from people where id = 1;
> --
> -- try adding to the array field
> -- replace the primary and add additional phone numbers
> -- This is where things go awry.
> --
> update people
> set phone = CAST(ROW(CAST(ROW(111,'home') as
> phonenumber_type),ARRAY[(222,'office'),
> (333,'cell'),(444,'eve')]::phonenumber_type)
> as contactphone_type)
> where id = 1;
>
>
... ,ARRAY[ ... ]::phonenumber_type[]
You need to cast to an array of the type which is done by adding the trailing
brackets to the type.
Also, your CREATE TYPE syntax (the "othernumbers phonenumber_type ARRAY" part)
is something I have not yet seen. Where did you see this? I would have
expected it to read "othernumbers phonenumber_type[]"; without the word ARRAY.
David J.
--
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] Unable To Alter Data Type
-Original Message- From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org] On Behalf Of Carlos Mennens Sent: Wednesday, January 11, 2012 6:53 PM To: PostgreSQL (SQL) Subject: [SQL] Unable To Alter Data Type Now I'm attempting to ALTER the field 'cust_zip' TYPE from character to integer however I'm getting the following error: I think there is a rule in PostgreSQL that says that any field with "zip" in the name MUST BE of a character type :) You seriously do not want to turn this into an integer (big or otherwise) - ZIP (POSTAL) CODES ARE NOT NUMERIC; THEY ARE TEXT! If someone is telling you to make this change then tell them they are wrong and whatever requirement are in place on the other end need to be modified - not the table you indicate. I do confirm, however, that a direct cast from character to bigint works and thus you SHOULD be able to perform the TYPE alteration without adding the USING (expression) clause. So either this is a bug OR, more likely, character-to-bigint casting is not implicit (or an assignment cast, whatever that is) and so you must resort to the USING clause - which does work. [ ... ALTER COLUMN zip_code TYPE bigint USING (zip_code::bigint) ] http://www.postgresql.org/docs/9.0/interactive/sql-altertable.html This is on 9.0 for me. However, I will say again, you DO NOT WANT TO ACTUALLY DO THIS! The specific issue is that some US Postal Code begin with a zero ( 0 ) and so whenever you want to the zip_code value you need to pad leading zeros if the length is less than 5. Now consider that a full zip_code can be in 5+4 format with an embedded hyphen and you no longer can even store it as numeric. If you deal with Canada (and maybe Mexico) at all then spaces and letters become acceptable characters within the zip_code. David J. -- 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] Unable To Alter Data Type
On Jan 11, 2012, at 19:30, Carlos Mennens wrote: > On Wed, Jan 11, 2012 at 7:13 PM, David Johnston wrote: >> However, I will say again, you DO NOT WANT TO ACTUALLY DO THIS! >> >> The specific issue is that some US Postal Code begin with a zero ( 0 ) and >> so whenever you want to the zip_code value you need to pad leading zeros if >> the length is less than 5. Now consider that a full zip_code can be in 5+4 >> format with an embedded hyphen and you no longer can even store it as >> numeric. If you deal with Canada (and maybe Mexico) at all then spaces and >> letters become acceptable characters within the zip_code. > > David - Thank you for that great info / explanation. Very informative > and helpful. I was not required to make this change but rather just > goofing off attempting to learn SQL as I'm rather terrible at it. Can > you tell me if there's an organized cheat sheet or something > documented in regards to data types commonly used for commonly used > field association? I think that's great for people who can't look at > the documentation and clearly understand specific definitions or > assumed categorization based on the type definition. If you can perform reasonable arithmetic on the field value you encode it as a number otherwise you should use text; even if the only possibly valid values are numbers. David J. -- 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] Unable To Modify Table
-Original Message- From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org] On Behalf Of Carlos Mennens Sent: Thursday, January 12, 2012 11:43 AM To: PostgreSQL (SQL) Subject: [SQL] Unable To Modify Table I seem to have an issue where I can't modify a table due to another tables foreign key association: [CODE]trinity=# \d developers Table "public.developers" Column| Type | Modifiers --++--- id | character(10) | not null name| character(50) | not null address | character(50) | city| character(50) | state | character(2) | zip | character(10) | country | character(50) | phone | character(50) | email | character(255) | Indexes: "developers_pkey" PRIMARY KEY, btree (id) Referenced by: TABLE "orders" CONSTRAINT "fk_orders_developers" FOREIGN KEY (id) REFERENCES developers(id) [/CODE] Now I want to change the formatting of field data in 'id' in table 'developers': [CODE]trinity=# SELECT id FROM developers; id 11 12 13 14 15 16 (109 rows) [/CODE] Now when I try and change the values before I alter the field TYPE, I get an error that another table (orders) with a foreign key associated with public.developers 'id' field still has old values therefor can't change / modify the 'developers' table. [CODE]trinity=# UPDATE developers SET id = '1000' WHERE id = '11'; ERROR: update or delete on table "developers" violates foreign key constraint "fk_orders_developers" on table "orders" DETAIL: Key (id)=(11) is still referenced from table "orders". [/CODE] How does one accomplish my goal? Is this difficult to change or once that foreign key is created, are you stuck with that particular constraint? --- There are two possible actions you can take with respect to an existing Primary Key; you can UPDATE it or you can DELETE it. When you define a FOREIGN KEY you can specify what you want to happen if the corresponding PRIMARY KEY is UPDATEd or DELETEd. Read the documentation on FOREIGN KEY in detail to understand why you are seeing that error and what modifications you can make to the FOREIGN KEY on "orders" to obtain different behavior. Keep in mind, also, that the TYPE of the PRIMARY KEY and FOREIGN KEY must match. Contrary to my earlier advice assigning a sequential ID (thus using a numeric TYPE) is one of the exceptions where you can use a number even though you cannot meaningfully perform arithmetic on the values. The reason you would use a numeric value instead of a character is that the value itself is arbitrary and the space required to store a number is less than the space required to store a string of the same length. There are many points-of-view regarding whether to use "serial" PRIMARY KEYs but regardless of whether you add one or not you should try and define a UNIQUE constraint on the table by using meaningful values. However, for things like Orders this is generally not possible and so you would want to generate a sequential identifier for every record. David J. -- 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] Unable To Modify Table
-Original Message- From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org] On Behalf Of Adrian Klaver Sent: Thursday, January 12, 2012 11:55 AM To: pgsql-sql@postgresql.org Cc: Carlos Mennens Subject: Re: [SQL] Unable To Modify Table > > How does one accomplish my goal? Is this difficult to change or once > that foreign key is created, are you stuck with that particular > constraint? You are pushing in the wrong direction. You need to make the change in the table 'orders'. This assumes the FK in 'orders' has ON UPDATE CASCADE enabled. Adrian, you are not helping...if ON UPDATE CASCADE was enabled on "orders" the error in question would never have appeared and the UPDATE would have succeeded. Carlos' goal is to change the value of a Primary Key that has already been used in a FOREIGN KEY constraint and he needs to learn to use the documentation to solve some of these basic questions instead of asking the list. His approach is correct, execute UPDATE against the "developers" table. Deferrable constraints and transactions work as well but are more complicated to setup and execute compared to the more direct ON UPDATE CASCADE modifier to the FOREIGN KEY. But learning both methods is good. David J. -- 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] Query Problem... Left OuterJoin / Tagging Issue
On Jan 12, 2012, at 23:31, John Tuliao wrote: > Hi, > > I've been working on this for quite awhile now and don't seem to get the > proper query. > > I have basically 4 tables. > > 1. Table john_test contains the numbers of the calls. > 2. Table john_country contains the country with prefix. > 3. Table john_clients contains the clients and their sub_id's > (because a client usually has a subclient, [ex. highway-2, highway-3]) > and their prefix. > 4. Table john_test contains the phone numbers. > > > > select * from john_client_rate limit 3; > >name |country | cali | cana | callrate | dir_id | trans_id | > svc_id | base | incr | client_id > --++--+--+--++--++--+--+--- > highway | Afghanistan| | | 0.6212 | 0 | 0| >|6 |6 | 4 > highway | Albania| | | 0.3945 | 0 | 1| >|6 |6 | 4 > highway | Bahamas| | | 0.0513 | 0 | 1| >|6 |6 | 4 > (3 rows) > > select * from john_country limit 3; > > country| state | prefix | area_code > ---+---+--+--- > Afghanistan | | 93 | > Aland Islands | | 35818120 | > Albania | | 355 | > (3 rows) > > > select * from john_clients limit 3; > > id | client_id | sub_id | name | prefix | type > +---++--+-+ > 80 |80 | 0 | highway | 71081 | client > 80 |80 | 0 | highway | 7107011 | client > 80 |80 | 0 | highway | 71091 | client > (3 rows) > > select * from john_test limit 3; > > client_id | name | phonenum | calledphonenum | > phonenumtranslat | direction | duration > --+-+--+-+--+---+-- > 2 | highway | 83863011351927330133 | 20100147011351927330133 | > | outbound | 363 > 2 | highway | 83863011441179218126 | 1943011441179218126 | > | outbound | 83 > 2 | highway | 83863011441179218126 | 20100147011441179218126 | > | outbound | 32 > (3 rows) > > > > What I want to do is to remove the prefix, and retain the number using the > following query: > > select > john_test.name, > john_test.gwrxdcdn, > john_test.duration as dur, > john_client_rate.name as name2, > john_client_rate.country, > john_country.prefix, > substring(john_test.gwrxdcdn from length(john_country.prefix)+1) as strip, > get_duration(john_test.duration::int,john_client_rate.base,john_client_rate.incr) > as realdur > from john_test > left outer join john_client_rate > on (prefix in >( > select prefix from john_client_rate > where john_test.phonenum ~ ( '^' || john_country.prefix) > order by length(john_country.prefix) desc limit '1' >) >) > limit 20; If you have a select within the ON clause of a join it isn't really a join. ON clauses should be simple expressions (almost always equality) between fields on the two tables with AND/OR logic. > > > > I have achieved this already, now I want to identify which country it's > supposed to be for. > Problem is sometimes the "stripped" number that is retained shows: 8661234567 > or 8889876543 > This would indicate that the call is already toll free without me being able > to identify the country. > How can I get over this? > > Further, I am planning to use multiple joins since I have several tables and > so as to identify missing countries. On this questions which query is better? > > Query 1: > > Select table1.column,table2.column,table3.column from table1 left outer join > table 2 on (table1.column=table2.column) left outer join table3 on > (table2.column=table3.column) ; > > or Query 2: > > Select table1.column,table2.column,table3.column from table1,table2,table3 > where [conditions] ; > Query 1 is an outer join, query 2 is an inner join; totally different semantics so the question is more "which one will work" versus "which one is better". Do you at least understand the difference? > Ultimately, I want to run one query that will satisfy these things and help > me insert into a table that will have it "TAGGED" properly with the right > Country, Client(name), prefix, and Rate for computation with Duration. > > Hope you can reply to me asap.
Re: [SQL] sql query problem
On Jan 17, 2012, at 8:35, Andreas Kretschmer wrote: > Alok Thakur wrote: > >> Dear, >> >> I am trying to provide you as much details as possible. >> >> answer` ( >> `id` int(10) NOT NULL AUTO_INCREMENT, >> `question_id` int(10) NOT NULL, >> `user_id` int(10) NOT NULL, >> `answer` int(10) NOT NULL, -> >> `status` tinyint(1) NOT NULL, --> Status will be 0 or 1 means wrong >> or right answer >> `date` datetime NOT NULL, >> PRIMARY KEY (`quiz_result_id`) >> ) >> >> user` ( >> `user_id` int(11) NOT NULL AUTO_INCREMENT, >> `user` varchar(255) NOT NULL, >> `username` varchar(255) NOT NULL, >> `user_email` varchar(255) NOT NULL, >> `user_gender` varchar(255) NOT NULL, >> `refrence` varchar(255) NOT NULL, >> `join_date` varchar(255) NOT NULL, >> `status` tinyint(1) NOT NULL, >> `banned` tinyint(1) NOT NULL, >> PRIMARY KEY (`user_id`) >> ) > > That's MySQL (i guess), please join a mysql-list. > > DB aside the query you are looking for is very simple SQL. The only real trick is using SUM(case when status = 1/0 then 1 else 0 end to obtain the proper counts. Any reference materials covering table joining and group by will give you the syntax and examples needed to write your query. The lack of response is because most people are not going to bother answering very simple queries that beginner reference materials cover adequately. Plus, you didn't display any effort in attempting to solve the question yourself; you can do this by showing and and asking what you did wrong as opposed to simply asking for an answer. David J. -- 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] Update Mass Data in Field?
-Original Message- From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org] On Behalf Of Carlos Mennens Sent: Thursday, January 26, 2012 6:59 PM To: PostgreSQL (SQL) Subject: [SQL] Update Mass Data in Field? I'm new to SQL so I'm looking for a way to change several email addresses with one command. For example everyone has a 'holyghost.org' domain and I need to change a few 100 email addresses in the field 'emp_email'. I need to UPDATE employees table which has a COLUMN 'emp_email' and change %holyghost.org to %ghostsoftware.com. I tried: UPDATE employees SET emp_email = '%ghostsoftware.com' WHERE emp_email LIKE '%holyghost.org'; It didn't update anything when I ran the command. Does anyone know how I need to correct my SQL statement above to change everyone's email address? Like Steve said, use the "regexp_replace" function. However, I am concerned that nothing updated when you executed the above. What you should have seen happen is that EVERY email address ending with "holyghost.org" became changed to the literal value "%ghostsoftware.com" - which obviously is not an e-mail address - and the original "holyghost.org" email address would have been gone beyond easy recovery. Takeaway: test update queries on sample data (or at least within a transaction block) and confirm your results before executing against live data (or committing the transaction). David J. -- 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] must appear in the GROUP BY clause or be used in an aggregate function problem
-Original Message- From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org] On Behalf Of Edward W. Rouse Sent: Tuesday, January 31, 2012 3:27 PM To: pgsql-sql@postgresql.org Subject: [SQL] must appear in the GROUP BY clause or be used in an aggregate function problem Attempting to get a list of items that we want to be grouped by id and date, with a detail column. Table a is a typical user table; id, first and last name are all that I am using Table b is a tracking table, combining data from 8+ tables into one for ease of use for reporting purposes; id, userid, company and invoice_number are all that I am using Table c is an exception table; it has relations with the other 2 by both the tracking and user ids but all I need from it is the exception date So the data required is the users first and last name, the company, the invoice number and the exception date. And the expected structure for the report is: User Name: Company: Date: Invoice Number For those invoices that are in the exception table. The sql I can get to work is: SELECT MAX(a.name_first) AS name_first, MAX(a.name_last) AS name_last, b.company, MAX(c.report_date) AS rDate, b.invoicenum FROM resources a JOIN tracking b ON (a.id=b.resource_id) JOIN except_detail c ON (b. id = b.tracking_id) WHERE b.region = NE' AND b.state = 1 GROUP BY a.id, b. company, b.invoicenum ORDER BY name_last, name_first, b.role_name, rDate And in most cases this works fine. The problem arises when invoices get added to the exception table due to their not being an invoice number. Even though we join on the tracking id, the group by on invoicenum lumps the different blank invoices into a single line, if the same user has more than 1. What we want is for each of the individual blank invoicenum entries to have a separate line in the result. If I remove b.invoicenum from the group by then I get the error in the subject line. If I try to use an aggregate function (like I used MAX on the names) it's even worse. MAX works on the names because they are all the same. MAX on the date doesn't seem to effect the results that I can see other than if an invoice went into exception more than once, and in that case we only want the latest one anyway. Any hints as to how to get this to not lump all of the empty invoicenums for a user into a single line? And I have mentioned putting in a dummy value like the date for an invoicenum, but that works as well as I expected it would (not at all). Edward W. Rouse --- Edward, I would suggest first processing all exceptions EXCEPT "missing invoice number" in one query, then constructing a second query that deals only with missing invoice numbers, and finally UNION-ing the two results. Also, I dislike the "MAX(varchar)" semantics. You should be able to write the query without it but without more details I cannot readily specify how. Generally you'd simply operate off of "id" and then join in the corresponding first/last names at the outer-most layer of the query. Since you are grouping on "a.id" anyway I would suggest you try it. David J. -- 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] Multiple tables query on Mapserver
On Feb 2, 2012, at 0:16, tiplip wrote: > Hi all, > > I have a couple of tables with same structure but standing for different > layers(1,2,3...) respectivle for Mapserver, > > table1 >gid| id| name | address| post code | layer > | geom > ---+--+--+-+---++ >1 1 'name11' 'address11' 102356 1 > > geom11 >2 2 'name12' 'address12' 102356 1 > > geom12 >- -'name1-' 'address1-' 102356 > 1 geom1- > > table2 >gid| id| name | address| post code | layer > | geom > ---+--+--+-+---++ >1 1 'name21' 'address21' 102356 2 > > geom21 >2 2 'name22' 'address22' 102356 2 > > geom22 >- -'name2-' 'address2-' 102356 2 > > geom2- > > > table3 >gid| id| name | address| post code | layer > | geom > ---+--+--+-+---++ >1 1 'name31' 'address31' 102356 3 > > geom31 >2 2 'name32' 'address32' 102356 3 > > geom32 >- -'name3-' 'address3-' 102356 > 3 geom3- > > > I want to get query results from table1, 2, 3... if key word matches, say > name like 'name' as follows: > > input 'name', results will be like > > table2 >gid| id| name | address| post code | layer > | geom > ---+--+--+-+---+-+ >1 1 'name11' 'address11' 102356 1 > > geom11 >2 2 'name12' 'address12' 102356 1 > > geom12 >3 3 'name21' 'address21' 102356 2 > > geom21 >4 4 'name22' 'address22' 102356 2 > > geom22 >5 5 'name31' 'address31' 102356 3 > > geom31 >6 6 'name32' 'address32' 102356 3 > > geom32 >- - - - --- - > > - > > > can I achieve that results with a single query sentence? and how? > any good ideas will be appricated? > > thanks > > The general method is to use "UNION" but not sure about the changing of the gid and id values... David J. -- 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] Multiple tables query on Mapserver
On Feb 2, 2012, at 0:32, tiplip wrote: > I just need gid or id in increasing order start from 0 (or 1), fetching their > original gid (or id) value is not necessary:) > can I do that? > > > David Johnston wrote >> >> >> The general method is to use "UNION" but not sure about the changing of >> the gid and id values... >> >> David J. >> -- >> Window function Row_number() over () David J. -- 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] update column with multiple values
-Original Message-
From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org]
On Behalf Of ssylla
Sent: Wednesday, February 08, 2012 9:31 PM
To: pgsql-sql@postgresql.org
Subject: [SQL] update column with multiple values
Dear list,
sorry, I already posted this, but it did not seem to have been accepted by
the mailing list. So here's my second try:
I need to update all rows of a column ('parent') based on a subquery that
returns me the first four digits of another column ('id'):
UPDATE admin SET parent=(SELECT SUBSTR(id,1,4) FROM admin);
After executing, I get the following error-message:
"more than one row returned by a subquery used as an expressionmore than one
row returned by a subquery used as an expression"
I am not quite sure about that, but maybe I need to construct a
function/loop to fulfill this task?
thanks for help.
Stefan
---
No sub-select required, just use the function:
UPDATE admin SET parent = substr(id,1,4);
This will, for each record, set the value of parent to the first four
characters of its ID.
When you use the sub-select there is not inherent linkage between the
"UPDATE" table and the "FROM" table. You can make a correlated sub-query
but in this case the is necessary.
David J.
--
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] need help with import
-Original Message- From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org] On Behalf Of Andreas Sent: Wednesday, February 15, 2012 8:03 PM To: pgsql-sql@postgresql.org Subject: [SQL] need help with import Hi I get CSV files to import. Th structure is like this. main part, sub part Could be like this A, a1 A, a2 A, a3 B, b1 B, b2 The database has a table for main_part and one for sub_part. The relation needs to be n:m so there is a relation table that holds ( main_id, sub_id ). The 2 primary keys main_part.id and sub_part.id are both serials. Is there a way to do an import with SQL? I can read the CSV into a temporary table and I can do a INSERT INTO main_part ( ... ) SELECT DISTINCT main columns FROM import; as well as a INSERT INTO sub_part ( ... ) SELECT sub columns FROM import; But how would I know what main_id and sub_id to insert into the n:m relation? At first when I do the import the relation is actually 1:n. You will need to use the temporary table and perform multiple insert+select. I do not understand where you are confused. It would help to provide more meaningful sample data and/or the final result you are trying to achieve. Keep in mind any n:m setup requires three tables with the joining table usually having some descriptive meaning. Is time one of your components that you are not showing us? Dave -- 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] need help with import
On Feb 15, 2012, at 21:05, Andreas wrote: > Am 16.02.2012 02:13, schrieb David Johnston: >> -Original Message- >> From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org] >> On Behalf Of Andreas >> Sent: Wednesday, February 15, 2012 8:03 PM >> To: pgsql-sql@postgresql.org >> Subject: [SQL] need help with import >> >> Hi >> I get CSV files to import. >> Th structure is like this. >> main part, sub part >> Could be like this >> >> A, a1 >> A, a2 >> A, a3 >> B, b1 >> B, b2 >> >> The database has a table for main_part and one for sub_part. >> The relation needs to be n:m so there is a relation table that holds ( >> main_id, sub_id ). >> The 2 primary keys main_part.id and sub_part.id are both serials. >> >> Is there a way to do an import with SQL? >> >> I can read the CSV into a temporary table and I can do a INSERT INTO >> main_part ( ... ) SELECT DISTINCT main columns FROM import; as well as a >> INSERT INTO sub_part ( ... ) SELECT sub columns FROM import; >> >> But how would I know what main_id and sub_id to insert into the n:m relation? >> >> At first when I do the import the relation is actually 1:n. >> >> >> >> You will need to use the temporary table and perform multiple insert+select. >> >> I do not understand where you are confused. It would help to provide more >> meaningful sample data and/or the final result you are trying to achieve. >> Keep in mind any n:m setup requires three tables with the joining table >> usually having some descriptive meaning. Is time one of your components >> that you are not showing us? >> >> > As you say there are 3 tables > main_part ( id serial primary key, ... ) > sub_part ( id serial primary key, ... ) > main_to_sub ( main_id, sub_id ) > > I would read the csv into a temporary table "import" and insert the main > columns into main_part (). > Then there are new tuples in main_part() > 42, A > 43, B > > Now I insert the sub columns into sub_part() > I'll get e.g. > 1000, a1 > 1001, a2 > 1002, a3 > 1003, b1 > 1004, b2 > > To fill up main_to_sub ( ... ) I needed to know which sub_id belongs to which > main_id. > ( 42, 1000 ) > ( 42, 1001 ) > ( 42, 1002 ) > ( 43, 1003 ) > ( 43, 1004 ) > > I could compare every main-column in "import" to every related data-column in > main_part to get the newly created main_id and do the same with every > sub-data-column but this seems to be a wee bit tedious. > > Is there a more elegant way hat I don't see, yet? > Ditch the whole idea of using a sequence and take your primary keys from the source data. Otherwise yes, you will need to perform the join between the import and live tables to determine the newly created identifier. The question to answer is if you see the same values in subsequent import files do you create a new sequence value or reuse the existing value? Why? David J. -- 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] Window function frame clause
On Feb 16, 2012, at 20:01, vpapavas wrote: > Hello all, > > I am trying to use this query in a toy database with customers and orders in > order to understand the capabilities of partitioning. In plain english what > I want to do is to select the orders of each customer and return only 3 of > those orders. > > The query I am using is this: > select c_custkey, o_orderkey, o_orderpriority, id from ( > select c_custkey, o_orderkey, o_orderpriority, o_totalprice, row_number() > over(PARTITION BY c_custkey ROWS between UNBOUNDED PRECEDING and 3 > FOLLOWING) as id > from customers left outer join orders on c_custkey = o_custkey) as temp > > Although I am using the frame clause ROWS between UNBOUNDED PRECEDING and 3 > FOLLOWING which in my understanding should return the first row of the > partition and the three following, this query returns all rows in the > partition. Am I doing something wrong? Or have I understood wrong the > semantics of the frame clause? I am using Postgresql v9.1 > > I rewrote the query like this in order to make it work: > select c_custkey, o_orderkey, o_orderpriority, id from ( > select c_custkey, o_orderkey, o_orderpriority, row_number() > over(PARTITION BY c_custkey) as id > from customers left outer join orders on c_custkey = o_custkey ) as temp > where id <= 3 > > but the problem is that I would like to not have to compute the entire join > since I am interested in only 3 orders for each customer. > > Thank you, > Vicky > > Put the window function on the order table, perform the where-limit, then join customer to the result. Also, you are numbering rows but not imposing any kind of order before doing so. Row_number doesn't make sense with a frame clause...frame is more useful for stuff like calculating rolling sums/averages and the like - where you evaluate fields in the surrounding frame as part of the aggregate. Window functions do not affect the number of rows returned. David J. -- 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] Invalid syntax for integer
From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org] On Behalf Of Rehan Saleem Sent: Tuesday, March 13, 2012 2:26 PM To: pgsql-sql@postgresql.org Subject: [SQL] Invalid syntax for integer hi , what is wrong with this if statement in this function if distance ='' THEN distance := 1; end if; here distance variable is of type integer , this function has been created successfully but when i execute this function it gives me this error , ERROR: invalid input syntax for integer: "" LINE 1: SELECT distance ='' ^ QUERY: SELECT distance ='' CONTEXT: PL/pgSQL function "getsitesbytfid" line 6 at IF ** Error ** ERROR: invalid input syntax for integer: "" SQL state: 22P02 Context: PL/pgSQL function "getsitesbytfid" line 6 at IF The empty-string is not a valid syntax for an integer. You probably want: IF (distance IS NULL) . David J. -- 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] help on a function with exception
On Mar 13, 2012, at 14:29, "M. D." wrote: > Hi, > > I want to do a check on a column if other columns meet certain conditions. > The program I'm working with allows to create additional columns on every > 'object' - called extra data, but I have no control over the program. I want > to enforce the values on this one extra data to be of type date. > > My idea was to do a Trigger function and cast to a date and if there's an > exception, raise an error. Below is what I've tried, but it just keeps on > Raising Exception. > > Could someone please help me? The date I enter is: 2012-10-10 which works > fine if I do a: > select '2012-10-10'::date > > Thanks > > --Postgres 9.0 > > CREATE OR REPLACE FUNCTION fnc_check_PO_extra_date() > RETURNS trigger AS > $BODY$ > DECLARE >tmp_date date; > BEGIN > IF (TG_OP = 'INSERT' OR TG_OP = 'UPDATE') THEN >IF (NEW.data_value IS NOT NULL or new.data_value = '') and NEW.extra_id = >(select extra_id from extra_data where data_type = 9 >and (data_name = 'ETA' or data_name = 'Adjusted ETA')) >THEN >tmp_date := new.data_value::date; >END IF; > END IF; > EXCEPTION >WHEN others THEN >RAISE EXCEPTION 'Invalid date on Extra Data!'; > return NEW; > END; > $BODY$ > LANGUAGE plpgsql VOLATILE > > CREATE TRIGGER trg_check_PO_extra_date > BEFORE INSERT OR UPDATE > ON extra_values > FOR EACH ROW > EXECUTE PROCEDURE fnc_check_PO_extra_date(); > > You are suppressing the original exception so figuring out what is wrong is very difficult. Your IF allows new.data_value to be the empty string which, iirc, cannot be cast to date David J. -- 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] Postgresql function which compares values from both tables
You would need to install the "HSTORE" extension to convert the record into a key->value pair then perform the comparison on that. Dave From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org] On Behalf Of Rehan Saleem Sent: Monday, March 19, 2012 3:40 PM To: pgsql-sql@postgresql.org Subject: [SQL] Postgresql function which compares values from both tables hi , how to write a function which should read data from 2 tables having same number of columns and should show the common values from those tables. thanks
Re: [SQL] Postgresql function which compares values from both tables
> -Original Message- > From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql- > ow...@postgresql.org] On Behalf Of Richard Huxton > Sent: Monday, March 19, 2012 4:59 PM > To: David Johnston > Cc: 'Rehan Saleem'; pgsql-sql@postgresql.org > Subject: Re: [SQL] Postgresql function which compares values from both > tables > > On 19/03/12 20:04, David Johnston wrote: > > You would need to install the "HSTORE" extension to convert the record > > into a key->value pair then perform the comparison on that. > > No, you can do this just fine with a simple join. > > BEGIN; > > CREATE TABLE ta (id int PRIMARY KEY, d date, t text); CREATE TABLE tb (id int > PRIMARY KEY, d date, t text); > > INSERT INTO ta > SELECT 100+n, '2001-01-01'::date + n, 'entry ' || n FROM > generate_series(1,19) n; > > INSERT INTO tb > SELECT 200+n, '2001-01-01'::date + n, 'entry ' || n FROM > generate_series(1,19) n; > > SELECT ta.id AS id_a, tb.id AS id_b, ta.d, ta.t FROM ta JOIN tb USING (d,t); > > ROLLBACK; > > If the fields were named differently you'd need something like: >FROM ta JOIN tb ON (ta.d1, ta.t1) = (tb.d2, tb.t2) > I think I read into the question too much. If you want to find matching rows then ( ROW(alias_1.*) = ROW(alias_2.*) ) will work (and you do not even have to specify the columns explicitly). Read this for details. Note that if any of the columns can be NULL you need to be especially careful. http://www.postgresql.org/docs/9.0/interactive/functions-comparisons.html I was thinking that you wanted to know which specific columns matched even if the row as a whole did not. Dave J. -- 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] Wrong output from union
Documented behavior. Please read the section on UNION for the why and the
proper alternative syntax:
http://www.postgresql.org/docs/9.0/interactive/sql-select.html
On Mar 28, 2012, at 7:01, Gary Stainburn wrote:
> Hi folks.
>
> I have two selects which in themselves report what they should. However, when
> I run a union to combine their outputs (to then feed a select/count) lines
> disappear. Below are the two seperate selects, followed by the untion in
> which duplicate rows are lost. I'm using Postgresql 8.3.3-2 RPMs on a Fedora
> 9 system.
>
> Can anyone tell me why it is happening and how to fix it.
>
> Ta.
>
> users=# select r_u_id || ':' ||r_deadline as key from requests where r_u_id
> is
> not NULL and r_deadline>='2012-03-26' and r_deadline <= ('2012-03-26'::date
> + '7 days'::interval);
>
> key
> ---
> 25:2012-03-28
> 25:2012-03-28
> 25:2012-03-28
> 25:2012-03-30
> 25:2012-03-29
> 25:2012-03-27
> (6 rows)
>
> users=# select u_id || ':' || rm_timestamp::date as key from
> request_reminders
> where u_id is not null and rm_timestamp>='2012-03-26' and rm_timestamp <=
> ('2012-03-26'::date + '7 days'::interval);
> key
> -
> (0 rows)
>
> users=# select r_u_id || ':' ||r_deadline as key from requests where r_u_id
> is
> not NULL and r_deadline>='2012-03-26' and r_deadline <= ('2012-03-26'::date
> + '7 days'::interval)
>
> union
>
> select u_id || ':' || rm_timestamp::date as key from request_reminders where
> u_id is not null and rm_timestamp>='2012-03-26' and rm_timestamp <=
> ('2012-03-26'::date + '7 days'::interval);
> key
> ---
> 25:2012-03-27
> 25:2012-03-28
> 25:2012-03-29
> 25:2012-03-30
> (4 rows)
>
> users=#
> --
> Gary Stainburn
> Group I.T. Manager
> Ringways Garages
> http://www.ringways.co.uk
>
> --
> 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] generic crosstab ?
From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org] On Behalf Of Andreas Sent: Tuesday, April 24, 2012 5:35 PM To: Samuel Gendler Cc: pgsql-sql@postgresql.org Subject: Re: [SQL] generic crosstab ? Am 24.04.2012 22:08, schrieb Samuel Gendler: On Tue, Apr 24, 2012 at 1:01 PM, Andreas wrote: Hi, is there a generic solution to dump the result of a query as a crosstab, when I can't know how many columns I will need? E.g. I get something like this: id, x 1, a 1, b 1, c 2, l 2, m Yes. You can provide a query which returns the columns to the version of the crosstab function which looks like this: crosstab(text source_sql, text category_sql) It does exactly what you are looking for. The second query returns the set of values that act as columns in the final result (the pivot for each row in the result returned by the first query). This allows the function to correctly insert a null for any column for which there is no row in the first query results. I got stuck with an error that translates to "Materialisation mode is needed but is not allowed in this context." I couldn't figure out what this materialisation mode is, yet. Could you please have a look at my query sample? Both queries work for themselves but crosstab() fails. :( I checked and there are never more than 20 child_ids per parent_id so there should be enough columns. select crosstab ( $$ select parent_idas row_name, 'x' || row_number() over ( partition by parent_id order by child_id ) as category, child_id as value from children order by 1 $$, $$ select 'x' || generate_series(1, 20) as cat order by 1 $$ ); You must specify the output record structure: SELECT crosstab(text, text) AS ( col1_name col1_type [, colN_name colN_type]* ) See: http://www.postgresql.org/docs/9.0/interactive/tablefunc.html for official usage and examples Whether this relates to the "materialization node" message you are receiving I have no idea. Dave
Re: [SQL] Finding Max Value in a Row
> -Original Message- > From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql- > ow...@postgresql.org] On Behalf Of Carlos Mennens > Sent: Friday, May 11, 2012 3:04 PM > To: PostgreSQL (SQL) > Subject: [SQL] Finding Max Value in a Row > > I have a problem in SQL I don't know how to solve and while I'm sure there > are 100+ ways to do this in ANSI SQL, I'm trying to find the most cleanest / > efficient way. I have a table called 'users' and the field 'users_id' is listed as > the PRIMARY KEY. I know I can use the COUNT function, then I know exactly > how many records are listed but I don't know what the maximum or highest > numeric value is so that I can use the next available # for a newly inserted > record. Sadly the architect of this table didn't feel the need to create a > sequence and I don't know how to find the highest value. > > Thank you for any assistance! > Finding the MAXimium of a given set of data is an aggregate operation and so you should look in the functions section of the documentation under "Aggregate" http://www.postgresql.org/docs/9.0/interactive/functions-aggregate.html Assuming that the users_id field is an integer: SELECT MAX(users_id) FROM users; --NO GROUP BY needed since no other fields are being output... That said, you really should create and attach a sequence so that you can avoid race/concurrency issues. David J. -- 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] Lowest 2 items per
On Jun 1, 2012, at 10:34, "Relyea, Mike" wrote: > I need a little help putting together a query. I have the tables listed > below and I need to return the lowest two consumables (ranked by cost > divided by yield) per printer, per color of consumable, per type of > consumable. > > CREATE TABLE printers > ( > printerid serial NOT NULL, > make text NOT NULL, > model text NOT NULL, > CONSTRAINT printers_pkey PRIMARY KEY (make , model ), > CONSTRAINT printers_printerid_key UNIQUE (printerid ), > ) > > CREATE TABLE consumables > ( > consumableid serial NOT NULL, > brand text NOT NULL, > partnumber text NOT NULL, > color text NOT NULL, > type text NOT NULL, > yield integer, > cost double precision, > CONSTRAINT consumables_pkey PRIMARY KEY (brand , partnumber ), > CONSTRAINT consumables_consumableid_key UNIQUE (consumableid ) > ) > > CREATE TABLE printersandconsumables > ( > printerid integer NOT NULL, > consumableid integer NOT NULL, > CONSTRAINT printersandconsumables_pkey PRIMARY KEY (printerid , > consumableid ), > CONSTRAINT printersandconsumables_consumableid_fkey FOREIGN KEY > (consumableid) > REFERENCES consumables (consumableid) MATCH SIMPLE > ON UPDATE CASCADE ON DELETE CASCADE, > CONSTRAINT printersandconsumables_printerid_fkey FOREIGN KEY > (printerid) > REFERENCES printers (printerid) MATCH SIMPLE > ON UPDATE CASCADE ON DELETE CASCADE > ) > > I've pulled together this query which gives me the lowest consumable per > printer per color per type, but I need the lowest two not just the first > lowest. > > SELECT printers.make, printers.model, consumables.color, > consumables.type, min(cost/yield) AS cpp > FROM printers > JOIN printersandconsumables ON printers.printerid = > printersandconsumables.printerid > JOIN consumables ON consumables.consumableid = > printersandconsumables.consumableid > WHERE consumables.cost Is Not Null > AND consumables.yield Is Not Null > GROUP BY printers.make, printers.model, consumables.color, > consumables.type > ORDER BY make, model; > > > After doing a google search I didn't come up with anything that I was > able to use so I'm asking you fine folks! > > Mike > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql I would recommend using the "RANK" window function with an appropriate partition clause in a sub-query then in the outer query you simply WHERE rank <= 2 You will need to decide how to deal with ties. David J. -- 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] defaults in a function
On Jun 6, 2012, at 11:08, John Fabiani wrote: > Hi, > In python when I create a method/function is set a default value for a passed > value if one is not provided. > > def foo(self, event = None): > > In the above function if the second value is not passed a value of None is > used as the default. > > Is this possible with plpgsql??? > > Johnf > http://www.postgresql.org/docs/9.0/interactive/sql-createfunction.html ... param_n type DEFAULT value OR ... param_n type = value It is SQL DDL syntax and not specific to the language the function is written in. David J. -- 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] using ordinal_position
> -Original Message- > From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql- > ow...@postgresql.org] On Behalf Of John Fabiani > Sent: Thursday, June 07, 2012 7:18 PM > To: pgsql-sql@postgresql.org > Subject: [SQL] using ordinal_position > > I'm attempting to retrieve data using a select statement without knowing the > column names. I know the ordinal position but not the name of the column > (happens to be a date::text and I have 13 fields). > > Below provides the name of the column in position 3: > > select column_name from (select column_name::text, ordinal_position from > information_schema.columns where > table_name='wk_test') as foo where ordinal_position = 3; > > But how can I use the above as a column name in a normal select statement. > > Unlike other databases I just can't use ordinal position in the select > statement - RIGHT??? > > Johnf > This seems like a seriously messed up requirement but I guess the easiest way would be as follows: SELECT tbl.col3 FROM (SELECT * FROM table) tbl (col1, col2, col3) Basically you select ALL columns (thus not caring about their names and always getting the defined order) from the table and give explicit aliases to columns 1 though N where N is the desired column position you want to return. All subsequent columns will retain their original names. If the parent query you can then simply select the column alias you assigned to the desired column position. If you query the catalog for the true column name you would have to use pl/pgSQL and EXECUTE to run the query against a manually built (stringified) query; SQL proper does not allow for table and column names to be variable. That said you may find it worthwhile to publish the WHY behind your inquiry to see if some other less cumbersome and error-prone solution can be found. While column order is fairly static it is not absolute and if the column order were to change you would have no way of knowing. At least when using actual column names the query would fail with an unknown column name exception instead of possibly silently returning bad data. The extra layer of indirection just seems dirty to me - but aside from the possibility of column order changes I don't see any major downsides. Since you are just dealing with column aliases it should not meaningfully impact query plan generation and thus it should be no slower than a more direct query. David J. -- 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] Need help building this query
> -Original Message- > From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql- > ow...@postgresql.org] On Behalf Of Rihad > Sent: Thursday, June 21, 2012 1:49 PM > To: pgsql-sql@postgresql.org > Subject: [SQL] Need help building this query > > Hi, folks. I currently need to join two tables that lack primary keys, and > columns used to distinguish each record can be duplicated. I need to build > statistics over the data in those tables. Consider this: > > > TableA: > row 1: foo: 123, bar: 456, baz: 789, amount: 10.99, date_of_op: date row 2: > foo: 123, bar: 456, baz: 789, amount: 10.99, date_of_op: date row 3: foo: 123, > bar: 456, baz: 789, amount: 10.99, date_of_op: date > > TableB: > row 1: foo: 123, bar: 456, baz: 789, amount: 10.99, date_of_op: date > > > Columns foo + bar + baz are used to distinguish a performed "operation": > TableA.date_of_op isn't, because it can lag behind TableB. > > Not all different "operations" are in table B. > Table B is just there so we know which "operations" are complete, so to > speak (happening under external means and not under any of my control). > > Now, for each operation (foo+bar+baz) in table A, only *one* row should be > matched in table B, because it only has one matching row there. > The other two in TableA should be considered unmatched. > > Now the query should be able to get count(*) and sum(amount) every day > for that day, considering that matched and unmatched operations should be > counted separately. The report would look something like this: > > TableA.date_of_op TableB.date_of_op > 2012-06-21[empty] [count(*) and sum(amount) > of all data in TableA for this day unmatched in TableB] > 2012-06-212012-06-20[count(*) and sum(amount) of > all data in TableA matched in TableB for the 20-th] > 2012-06-212012-06-19[count(*) and sum(amount) of > all data in TableA matched in TableB for the 19-th] > > > Can this awkward thing be done in pure SQL, or I'd be better off using > programming for this? > > Thanks, I hope I could explain this. > You seem to be describing a straight reconciliation between two tables. My current means of doing this are programmatically but for the simple case pure SQL should be doable. The main thing is that you have to distinguish between "duplicate" records first and then match them up: TableA Keys: AA AA AA AB AB AC TableB Keys: AA AA AB First you use "ROW_NUMBER() OVER (PARTITION BY key)" to assign an integer "sub-id" to every set of possible keys in both tables: TableA-Sub: AA-1 AA-2 AA-3 AB-1 AB-2 AC-1 TableB-Sub: AA-1 AA-2 AB-1 Now, with these newly constructed key+sub-key values in place, you can perform a simple LEFT (or possibly FULL) JOIN between tables A & B. This makes no allowances for any of kind of desired date restriction on the matching nor does it consider the eventual report that you wish to generate. What this gives you is a listing of ALL rows in both tables with matched records joined together into a single (NULL-less) row while unmatched records will have one of the two resultant columns NULLed SELECT tableA.subid_a, tableB.subid_b FROM tableA FULL OUTER JOIN tableB ON (tableA.subid_a = tableB.subid_b) Requires at least version 8.4 David J. -- 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 to solve the old bool attributes vs pivoting issue?
On Jun 27, 2012, at 21:07, Andreas wrote: > Hi > > I do keep a table of objects ... let's say companies. > > I need to collect flags that express yes / no / don't know. > > TRUE / FALSE / NULL would do. > > > Solution 1: > I have a boolean column for every flag within the companies-table. > Whenever I need an additional flag I'll add another column. > This is simple to implement. > On the other hand I'll have lots of attributes that are NULL. > > Solution 2: > I create a table that holds the flag's names and another one that has 2 > foreign keys ... let's call it "company_flags". > company_flags references a company and an id in the flags table. > This is a wee bit more effort to implement but I gain the flexibility to add > any number of flags without having to change the table layout. > > There are drawbacks > 1) 2 integers as keys would probaply need more space as a boolean column. > On the other hand lots of boolean-NULL-columns would waste space, too. > 2)Probaply I'll need a report of companies with all their flags. > How would I build a view for this that shows all flags for any company? > When I create this view I'would not know how many flags exist at > execution time. > > > This must be a common issue. > > Is there a common solution, too? > > You should look and see whether the hstore contrib module will meet your needs. http://www.postgresql.org/docs/9.1/interactive/hstore.html David J. -- 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] Prevent double entries ... no simple unique index
On Jul 12, 2012, at 4:44, Andreas wrote: > Am 12.07.2012 07:14, schrieb Andreas Kretschmer: >> Marc Mamin wrote: >> >>> A partial index would do the same, but requires less space: >>> >>> create unique index on log(state) WHERE state IN (0,1); >> > > > OK, nice :) > > What if I have those states in a 3rd table? > So I can see a state-history of when a state got set by whom. > > > objects ( id serial PK, ... ) > events ( id serial PK, object_id integer FK on objects.id, ... ) > > event_states ( id serial PK, event_id integer FK on events.id, state > integer ) > > There still should only be one event per object that has state 0 or 1. > Though here I don't have the object-id within the event_states-table. > > Is it still possible to have a unique index that needs to span over a join of > events and event_states? > No, all index columns must come from the same table. You would need to use a trigger-based system to enforce your constraint. You can either have the triggers simply perform validation or you can create a materialized view and create the partial index on that. You could also consider creating an updatable view and avoid directly interacting with the three individual tables. You could also just turn event states into a history table and leave the current state on the event table. David J. -- 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] query two tables using same lookup table
On Jul 22, 2012, at 23:04, ssylla wrote: > Dear list, > > assuming I have two tables as follows > > t1: > id_project|id_auth > 1|1 > 2|2 > > t2: > id_project|id_auth > 1|2 > 2|1 > > > and a lookup-table: > > t3 > id_auth|name_auth > 1|name1 > 2|name2 > > Now I want to query t1 an t2 using the 'name_auth' column of lookup-table > t3, so that I get the following output: > id_project|name_auth_t1|name_auth_t2 > 1|name1|name2 > 2|name2|name1 > > Any ideas? > > Thanks- > Stefan > > Not tested, may need minor syntax cleanup but the theory is sound. With pj as ( Select id_project, id_name1, id_name2 From (select id_project, id_auth as id_auth1 from t1) s1 Natural Full outer join (select id_project, id_auth as id_auth2 from t2) s2 ) Select pj.id_project, n1.name_auth, n2.name_auth From pj Left join t3 as n1 on (id_auth1 = id_auth) Left join t3 as n2 on (id_auth2 = id_auth) ; Full join the two project tables and give aliases to the duplicate id_auth field. Then left join against t3 twice (once for eachid_auth) using yet a another set of aliases to distinguish them. David J. -- 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] join against a function-result fails
On Jul 27, 2012, at 21:57, Andreas wrote > Hi, > I have a table with user ids and names. > Another table describes some rights of those users and still another one > describes who inherits rights from who. > > A function all_rights ( user_id ) calculates all rights of a user recursively > and gives back a table with all userright_ids this user directly has or > inherits of other users as ( user_id, userright_id ). > > Now I'd like to find all users who have the right 42. > > > select user_id, user_name > fromusers >join all_rights ( user_id ) using ( user_id ) > where userright_id = 42; > > won't work because the parameter user_id for the function all_rights() is > unknown when the function gets called. > > Is there a way to do this? > Suggest you write a recursive query that does what you want. If you really want to do it this way you can: With cte as (Select user_id, user_name, all_rights(user_id) as rightstbl) Select * from cte where (rightstbl).userright_id = 42; This is going to be very inefficient since you enumerate every right for every user before applying the filter. With a recursive CTE you can start at the bottom of the trees and only evaluate the needed branches. David J. -- 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] [GENERAL] Indexing question
> -Original Message-
> From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-
> ow...@postgresql.org] On Behalf Of amit sehas
> Sent: Tuesday, August 14, 2012 12:55 PM
> To: pgsql-sql@postgresql.org; pgsql-gene...@postgresql.org
> Subject: [GENERAL] Indexing question
>
> In SQL, given a table T, with two fields f1, f2,
>
> is it possible to create an index such that the same record is indexed in
the
> index, once with field f1 and once with field f2. (I am not looking for a
> compound index in which the key would look like , instead there
> should be two entries in the index and ).
>
> we have a few use cases for the above, perhaps we need to alter the
> schema somehow to accommodate the above,
>
> any advice is greatly appreciated ..
>
> thanks
>
In short: No, you cannot create an index on T in the way you describe. You
need to create a new table: TF, with columns {T(id), f}, and having rows 1
and 2 with the same T(id) value; An index over "f" on table TF will then
contain both values.
Slightly longer:
This seems like a classic case of column duplication. I am assuming that
the columns in question are, say, phone1 and phone2 an you want to be able
to search by phone number without having to specify the two fields
separately. The correct way to do this is to create a "phone" table and add
a single line for each phone number you want to store (along with the
corresponding FK value of the original table) - with possibly a "phone_type"
column.
If this is not what you are after then you should be more explicit in your
requirements. Why is creating two separate indexes (on f1 and f2) not
acceptable?
If indeed you are dealing with variations of the above example you really
want to consider modifying your schema to use two tables with a one-to-many
relationship because the current scenario begs the question(s): "why only f1
and f2? Why isn't there an f3?". The idea is that there are generally 3
separate cardinalities {0, 1, >1}. Zero you ignore, 1 you generally put on
the same table - though not always, and more-than-one you create a separate
table and store multiple values as separate rows instead of as columns.
David J.
--
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] Error: Template Id should be teh identifier of a template - help
From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org] On Behalf Of Alex Sent: Friday, August 17, 2012 11:58 AM To: pgsql-sql@postgresql.org Subject: [SQL] Error: Template Id should be teh identifier of a template - help Hi, All! When I execute this: UPDATE HTMLPAGE SET PAGE_URL = REPLACE(PAGE_URL, '.dot', '.html') WHERE PAGE_URL LIKE '%.dot'; I get the following error from psql. Could you tell me what is wrong or how to fix it? Thanks, Alex Failed to execute SQL : SQL UPDATE HTMLPAGE SET PAGE_URL = REPLACE(PAGE_URL, '.dot', '.html') WHERE PAGE_URL LIKE '%.dot'; failed : ERROR: Template Id should be the identifier of a template == This seems to be an application error and not something PostgreSQL is issuing on its own. The htmlpage table seems to have an update trigger on it that checks for the validity of a template id. Since it is a trigger and not a constraint it is possible/likely the trigger was added without validating existing data. Your update must hit one or more records that do not meet the conditions that the triggers sets forth so the trigger performs a "RAISE ERROR 'Template Id should be the identifier of a template'". You will need to speak with someone responsible for maintaining the database. David J. -- 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] prepared statement in crosstab query
On Aug 31, 2012, at 21:53, Samuel Gendler wrote: > I have the following crosstab query, which needs to be parameterized in the 2 > inner queries: > > SELECT * FROM crosstab( > $$ > SELECT t.local_key, >s.sensor_pk, >CASE WHEN t.local_day_abbreviation IN (?,?,?,?,?,?,?) THEN > q.dpoint_value >ELSE NULL >END as dpoint_value > FROM dimensions.sensor s > INNER JOIN dimensions.time_ny t > ON s.building_id = ? > AND s.sensor_pk IN > (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) > AND t.local_key BETWEEN ? AND ? > LEFT OUTER JOIN ( > SELECT f.time_fk, f.sensor_fk, >cast(avg(f.dpoint_value) as numeric(10,2)) as dpoint_value > FROM facts.bldg_4_thermal_fact f > WHERE f.time_fk BETWEEN ? AND ? > AND f.sensor_fk IN > (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) > GROUP BY 1,2) q > ON q.time_fk = t.local_key > AND q.sensor_fk = s.sensor_pk > ORDER BY 1,2 > $$, > $$ > SELECT s.sensor_pk > FROM dimensions.sensor s > WHERE s.building_id = ? > AND s.sensor_pk IN > (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) > ORDER BY 1 > $$ > ) q(time_key bigint, a4052 real,a4053 real,a4054 real,a4055 real,a4056 > real,a4057 real,a4058 real,a4059 real,a4060 real,a4061 real,a4062 real,a4063 > real,a4064 real,a4065 real,a4066 real,a4067 real,a4068 real,a4069 real,a4070 > real,a4071 real,a4072 real,a4073 real,a4074 real,a4075 real,a4076 real,a4077 > real,a4078 real,a4079 real) > > > > > However, when I attempt to create a prepared statement in java (or groovy, or > as a hibernate sqlQuery object) with the following set of parameters (the > counts do match), I always get an exception telling me the following > > > > > [Mon, Tue, Wed, Thu, Fri, Sat, Sun, 4, 4052, 4053, 4054, 4055, 4056, 4057, > 4058, 4059, 4060, 4061, 4062, 4063, 4064, 4065, 4066, 4067, 4068, 4069, 4070, > 4071, 4072, 4073, 4074, 4075, 4076, 4077, 4078, 4079, 20120402, > 20120404, 20120402, 20120404, 4052, 4053, 4054, 4055, 4056, 4057, > 4058, 4059, 4060, 4061, 4062, 4063, 4064, 4065, 4066, 4067, 4068, 4069, 4070, > 4071, 4072, 4073, 4074, 4075, 4076, 4077, 4078, 4079, 4, 4052, 4053, 4054, > 4055, 4056, 4057, 4058, 4059, 4060, 4061, 4062, 4063, 4064, 4065, 4066, 4067, > 4068, 4069, 4070, 4071, 4072, 4073, 4074, 4075, 4076, 4077, 4078, 4079] > > Caused by: org.postgresql.util.PSQLException: The column index is out of > range: 1, number of columns: 0. > at > org.postgresql.core.v3.SimpleParameterList.bind(SimpleParameterList.java:53) > at > org.postgresql.core.v3.SimpleParameterList.setStringParameter(SimpleParameterList.java:118) > at > org.postgresql.jdbc2.AbstractJdbc2Statement.bindString(AbstractJdbc2Statement.java:2184) > at > org.postgresql.jdbc2.AbstractJdbc2Statement.setString(AbstractJdbc2Statement.java:1303) > at > org.postgresql.jdbc2.AbstractJdbc2Statement.setString(AbstractJdbc2Statement.java:1289) > at > org.postgresql.jdbc2.AbstractJdbc2Statement.setObject(AbstractJdbc2Statement.java:1763) > at > org.postgresql.jdbc3g.AbstractJdbc3gStatement.setObject(AbstractJdbc3gStatement.java:37) > at > org.postgresql.jdbc4.AbstractJdbc4Statement.setObject(AbstractJdbc4Statement.java:46) > at > org.apache.commons.dbcp.DelegatingPreparedStatement.setObject(DelegatingPreparedStatement.java:169) > at > org.apache.commons.dbcp.DelegatingPreparedStatement.setObject(DelegatingPreparedStatement.java:169) > > > > > I've tried a number of different escaping mechanisms but I can't get anything > to work. I'm starting to think that postgresql won't allow me to use do > parameter replacement in the inner queries. Is this true? The query runs > just fine if I manually construct the string, but some of those params are > user input so I really don't want to just construct a string if I can avoid > it. > > Any suggestions? > > Or can I create a prepared statement and then pass it in as a param to > another prepared statement? > > Something like: > > SELECT * FROM crosstab(?, ?) q(time_key bigint, a4052 real,a4053 real,a4054 > real,a4055 real,a4056 real,a4057 real,a4058 real,a4059 real,a4060 real,a4061 > real,a4062 real,a4063 real,a4064 real,a4065 real,a4066 real,a4067 real,a4068 > real,a4069 real,a4070 real,a4071 real,a4072 real,a4073 real,a4074 real,a4075 > real,a4076 real,a4077 real,a4078 real,a4079 real) > > With each '?' being passed a prepared statement? That'd be a really cool way > to handle it, but it seems unlikely to work. > > Doing the whole thing in a stored proc isn't really easily done - at least > with my limited knowledge of creating stored procs, since all of the lists > are of varying lengths, as are the number of returned columns (which always > matches the length of the last 3 lists plus 1. > > Ques
Re: [SQL] Need to Iterate the record in plpgsql
Yelai, The etiquette on this list is to place all replies either in-line (but following the content being quoted) or at the end of the posting. My reply is at the end. =By: Sergey Konoplev If you do not need information about column types you can use hstore for this purpose. [local]:5432 grayhemp@grayhemp=# select * from r limit 1; a | b | c ---+---+--- 1 | 2 | 3 (1 row) [local]:5432 grayhemp@grayhemp=# select * from each((select hstore(r) from r limit 1)); key | value -+--- a | 1 b | 2 c | 3 (3 rows) The key and value columns here of the text type. == > > Hi All, > > > > I am facing a issue in Iterating the RECORD. > > > > The problem is, I would like to iterate the RECORD without using sql > > query, but as per the syntax I have to use query as shown below. > > > > FOR target IN query LOOP > > statements > > END LOOP [ label ]; > > > > In my procedure, I have stored one of the procedure output as record, > > which I am later using in another iteration. Below is the example > > > > > > CREATE OR REPLACE FUNCTION test2() > > > > Rec1 RECORD; > > Rec2 RECORD; > > Rec3 RECORD; > > > > SELECT * INTO REC1 FROM test(); > > > > FOR REC2 IN ( select * from test3()) > > LOOP > > FOR REC3 IN REC2 --- this syntax does not allowed by Postgresql > > LOOP > > > > END LOOP > > END LOOP > > > > As per the example, How can I iterate pre stored record. > > > > Please let me know if you have any suggestions. > > > > Thanks & Regards, > > Ramkumar > > This makes no sense to me. Since REC2 is a single record from "test3()" there are no "sub-records" to iterate over. Re-reading the thread what you want to do is now iterate over the columns of the record that is currently in play. The following is theoretical: A starting point for doing what you want would be to create a temporary table from the results of the call to "test3()". CREATE TEMP TABLE test3_table AS ON COMMIT DROP SELECT * FROM test3() Now using hstore you can iterate over the columns and retrieve the name and textual value for each. Save the column name and lookup the corresponding column on "test3_table" to determine the data type associated with the value. I do not know the specific syntax to do this but the information is available in the database. It helps to provide the why behind what you are trying to accomplish and just ask whether some behavior can be accomplished or emulated. David J. -- 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] returning values to variables from dynamic SQL
>>
From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org]
On Behalf Of James Sharrett
Sent: Saturday, September 08, 2012 6:24 PM
To: pgsql-sql@postgresql.org
Subject: [SQL] returning values to variables from dynamic SQL
I have a PG function ( using plpgsql) that calls a number of sub functions
also in plpgsql. I have two main problems that seem to be variations on the
same theme of running dynamic SQL from a variable with the EXECUTE statement
and returning the results back to a variable defined in the calling
function.
>>
Please read:
http://www.postgresql.org/docs/9.0/interactive/plpgsql-statements.html#PLPGS
QL-STATEMENTS-EXECUTING-DYN
Basically when you use "EXECUTE" you do not embed the "INTO" as part of the
SQL query but rather INTO becomes a modifier of the EXECUTE itself:
EXECUTE 'some query' INTO {variables}
David J.
--
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] Query with LIMIT clause
From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org] On Behalf Of JORGE MALDONADO Sent: Sunday, September 09, 2012 1:26 PM To: pgsql-sql@postgresql.org Subject: [SQL] Query with LIMIT clause I have the following records that I get from a query, the fields are date type in day/month/year format: - Initial Final Date Date - 27/08/2012 04/09/2012 29/08/2012 09/09/2012 28/08/2012 09/09/2012 30/08/2012 09/09/2012 30/08/2012 09/09/2012 27/08/2012 09/09/2012 31/08/2012 09/09/2012 28/08/2012 10/09/2012 05/09/2012 16/09/2012 As you can see, this result is ordered by Final Date. What I need is to get the last 5 records only. If I use the LIMIT 5 clause I get the first 5. I have tried an ORDEY BY DESC but the result is the same. I will very much appreciate your comments. . First, you should really order by both columns, not just "final date". The ties on 9/9/12 are unordered. Second, you will indeed need to reverse the sort order and then take the first 5 records; figuring out and limiting on the last 5 isn't worth the effort. SELECT initial_date, final_date FROM date_source ORDER BY final_date DESC, initial_date DESC LIMIT 5 You can put the above into a sub-query and re-order if the final result is needed in ascending order. If this doesn't seem to work you will want to provide the exact query/queries you are trying so that someone may spot what you are doing wrong. Dave -- 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] Query with LIMIT clause
Always reply to list. It is also preferred to bottom-post. Indexes are never simple answers and there isn't enough info to really give good advice here. You should try different versions and estimate performance (read and write). My guess is that a compound index (2 columns) would work well though if you are going to filter/sort on initial date frequently (by itself) then it likely wants its own index anyway and having two separate indexes would be better. David J. On Sep 9, 2012, at 20:56, JORGE MALDONADO wrote: > Firstly, who should I reply to, you or the list? > Your solution is working pretty fine, I appreciate your advice. Now, I am > sure that an index is a good idea in order to make the query fast. What would > be a good criteria to define an index? Will an index for final date and > another for initial date is the choice, or one index composed of both initial > and final date? > > Regards, > Jorge Maldonado > > On Sun, Sep 9, 2012 at 12:45 PM, David Johnston wrote: > From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org] > On Behalf Of JORGE MALDONADO > Sent: Sunday, September 09, 2012 1:26 PM > To: pgsql-sql@postgresql.org > Subject: [SQL] Query with LIMIT clause > > I have the following records that I get from a query, the fields are date > type in day/month/year format: > > - > Initial Final > DateDate > - > 27/08/2012 04/09/2012 > 29/08/2012 09/09/2012 > 28/08/2012 09/09/2012 > 30/08/2012 09/09/2012 > 30/08/2012 09/09/2012 > 27/08/2012 09/09/2012 > 31/08/2012 09/09/2012 > 28/08/2012 10/09/2012 > 05/09/2012 16/09/2012 > > As you can see, this result is ordered by Final Date. What I need is to get > the last 5 records only. If I use the LIMIT 5 clause I get the first 5. I > have tried an ORDEY BY DESC but the result is the same. I will very much > appreciate your comments. > > >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>. > > First, you should really order by both columns, not just "final date". The > ties on 9/9/12 are unordered. > > Second, you will indeed need to reverse the sort order and then take the > first 5 records; figuring out and limiting on the last 5 isn't worth the > effort. > > SELECT initial_date, final_date > FROM date_source > ORDER BY final_date DESC, initial_date DESC > LIMIT 5 > > You can put the above into a sub-query and re-order if the final result is > needed in ascending order. > > If this doesn't seem to work you will want to provide the exact > query/queries you are trying so that someone may spot what you are doing > wrong. > > Dave > > >
Re: [SQL] ERROR: missing FROM-clause entry for table "new"
On Sep 13, 2012, at 20:40, James Sharrett wrote: > I'm trying to define a trigger function that looks for changes in table A > (table the trigger for the function is on) and write a delta record into > table B. So if a record has a value of 100 in table A, and it is updated to > 50, the function should write –50 in table B. I can get the trigger to work > with static SQL statements but for the actual code, I need to use dynamic SQL > because I need to alter the insert statement to B depending on what column in > table A is altered. I can get the correct SQL generated but when I execute > the string inside the trigger function I get an error because it doesn't seem > to be able to see the NEW table when it's run with EXECUTE. > > So, this works in the trigger function: > > Insert into A (col1,col2,…colN) > Select new.col1,new.co2…new.colN) > > This doesn't: > > sql := 'Insert into A (col1,col2,…colN) '; > sql := sql || 'Select new.col1,new.co2…new.colN)'; > Execute sql; > > ERROR: missing FROM-clause entry for table "new" > > There is nothing wrong with the resulting code from sql because if I output > the string and put it in as static SQL in my trigger it works. > > How do I build the string within the trigger and execute it with a reference > to NEW? > > Thanks in advance for the help, > James > Please read all of: http://www.postgresql.org/docs/9.2/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN But especially 39.5.4 You want to make use of format and/or USING to pass in the values to a parameterized dynamic statement. Note I linked to 9.2 but any recent version should have the behavior, if different section numbers. In short the whole "NEW.name" is a variable and you need to build the statement the same way you would with any user-defined variable. David J.
Re: [SQL]
On Sep 22, 2012, at 20:15, JORGE MALDONADO wrote: > I have the following query: > > SELECT > sem_clave, > to_char(secc_esp_media.sem_fechareg,'TMMon-DD-') as sem_fechareg, > sem_seccion, > sem_titulo, > sem_enca, > tmd_nombre, > tmd_archivo, > tmd_origen, > gen_nombre, > smd_nombre, > prm_urlyoutube, > prm_prmyoutube, > prm_urlsoundcloud, > prm_prmsoundcloud > FROM secc_esp_media > INNER JOIN cat_tit_media ON tmd_clave = sem_titulo > INNER JOIN cat_secc_media ON smd_clave = sem_seccion > INNER JOIN cat_generos ON gen_clave = tmd_genero > INNER JOIN parametros ON 1 = 1 > WHERE > smd_nombre = 'SOMETHING' AND > sem_fipub <= 'SOME DATE' > ORDER BY sem_fipub DESC, sem_ffpub DESC > > I thought it was working fine until I noticed I needed to include a DISTINCT > clause as follows: > > SELECT DISTINCT ON (sem_clave) ..(the rest of the query is exactly the > same as above) > > But, when I run it, I get a message telling me that I need an ORDER BY the > field "sem_clave" which is the field in the DISTINCT clause. How can I solve > this issue without affecting the ORDER BY it already has ? > > Regards, > Jorge Maldonado Since you are forced to include the ON field(s) first in the ORDER BY if you want a different final sort order you will have to use either a sub-select or a CTE/WITH to execute the above query then in the outer/main query you can perform a second sort. David J.
Re: [SQL] Reuse temporary calculation results in an SQL update query
On Sep 29, 2012, at 6:49, Matthias Nagel wrote: > Hello, > > is there any way how one can store the result of a time-consuming calculation > if this result is needed more than once in an SQL update query? This solution > might be PostgreSQL specific and not standard SQL compliant. Here is an > example of what I want: > > UPDATE table1 SET > StartTime = 'time consuming calculation 1', > StopTime = 'time consuming calculation 2', > Duration = 'time consuming calculation 2' - 'time consuming calculation 1' > WHERE foo; > > It would be nice, if I could use the "new" start and stop time to calculate > the duration time. First of all it would make the SQL statement faster and > secondly much more cleaner and easily to understand. > > Best regards, Matthias > > You are allowed to use a FROM clause with UPDATE so if you can figure out how to write a SELECT query, including a CTE if needed, you can use that as your cache. An immutable function should also be optimized in theory though I've never tried it. David J. -- 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] Need help with a special JOIN
On Sep 29, 2012, at 12:02, Andreas wrote: > Hi, > > asume I've got 2 tables > > objects ( id int, name text ) > attributes ( object_id int, value int ) > > attributes has a default entry with object_id = 0 and some other where > another value should be used. > > e.g. > objects > ( 1, 'A' ), > ( 2, 'B' ), > ( 3, 'C' ) > > attributes > ( 0, 42 ), > ( 2, 99 ) > > The result of the join should look like this: > > object_id, name, value > 1, 'A', 42 > 2, 'B', 99 > 3, 'C', 42 > > > I could figure something out with 2 JOINs, UNION and some DISTINCT ON but > this would make my real query rather chunky. :( > > Is there an elegant way to get this? > General form (idea only, syntax not tested) Select objectid, name, coalesce(actuals.value, defaults.value) From objects cross join (select ... From attributes ...) as defaults Left join attributes as actuals on ... Build up a master relation with all defaults then left join that against the attributes taking the matches where present otherwise taking the default. David J. -- 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] Reuse temporary calculation results in an SQL update query [SOLVDED]
> > thank you. The "WITH" clause did the trick. I did not even know that such a > thing exists. But as it turns out it makes the statement more readable and > elegant but not faster. > > The reason for the latter is that both the CTE and the UPDATE statement > have the same "FROM ... WHERE ..." part, because the tempory calculation > needs some input values from the same table. Hence the table is looked up > twice instead once. This is unusual; the only WHERE clause you should require is some kind of key matching... Like: UPDATE tbl SET FROM ( WITH final_result AS ( SELECT pkid, FROM tbl WHERE ... ) -- /WITH SELECT pkid, FROM final_result ) src -- /FROM WHERE src.pkid = tbl.pkid ; If you provide an actual query better help may be provided. David J. -- 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] [noob] How to optimize this double pivot query?
From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org] On Behalf Of Robert Buck Sent: Monday, October 01, 2012 8:47 PM To: pgsql-sql@postgresql.org Subject: [SQL] [noob] How to optimize this double pivot query? I have two tables that contain key-value data that I want to combine in pivoted form into a single result set. They are related to two separate tables. The tables are: test_results, test_variables, metric_def, metadata_key. The latter two tables are enum-like tables, basic descriptors of data stored in other tables. The former two tables are basically key-value tables (with ids as well); these k-v tables are related to the latter two tables via foreign keys. The following SQL takes about 11 seconds to run on a high-end laptop. The largest table is about 54k records, pretty puny. Can someone provide a hint as to why this is so slow? Again, I am a noob to SQL, so the SQL is probably poorly written. Your query, while maybe not great, isn’t the cause of your problem. It is the table schema, specifically the “key-value” aspect, that is killing you. You may want to try: SELECT * FROM (SELECT id FROM …) id_master NATURAL LEFT JOIN (SELECT id, field_value AS … FROM … WHERE fieldtype = ‘’) f1 NATURAL LEFT JOIN (SELECT id, field_value AS … FROM … WHERE fieldtype = ‘’) f2 [repeat one left join for every field; though you will then need to decide if/how to deal with NULL – not that you are currently doing anything special anyway…] Mainly the above avoids the use of “max()” and instead uses direct joins between the relevant tables. I have no clue whether that will improve things but if you are going to lie in this bed you should at least try different positions. The better option is to educate yourself on better ways of constructing the tables so that you do not have to write this kind of god-awful query. In some cases key-value has merit but usually only when done in moderation. Not for the entire database. You likely should simply have a table that looks like the result of the query below. As a second (not necessarily mutually exclusive) alternative: install and use the hstore extension. David J. Thanks in advance, Bob select t.id_name, max(t.begin_time) as begin_time, max(t.end_time) as end_time, max(case when (m.id_name = 'package-version') then v.value end) as package_version, max(case when (m.id_name = 'database-vendor') then v.value end) as database_vendor, max(case when (m.id_name = 'bean-name') then v.value end) as bean_name, max(case when (m.id_name = 'request-distribution') then v.value end) as request_distribution, max(case when (m.id_name = 'ycsb-workload') then v.value end) as ycsb_workload, max(case when (m.id_name = 'record-count') then v.value end) as record_count, max(case when (m.id_name = 'transaction-engine-count') then v.value end) as transaction_engine_count, max(case when (m.id_name = 'transaction-engine-maxmem') then v.value end) as transaction_engine_maxmem, max(case when (m.id_name = 'storage-manager-count') then v.value end) as storage_manager_count, max(case when (m.id_name = 'test-instance-count') then v.value end) as test_instance_count, max(case when (m.id_name = 'operation-count') then v.value end) as operation_count, max(case when (m.id_name = 'update-percent') then v.value end) as update_percent, max(case when (m.id_name = 'thread-count') then v.value end) as thread_count, max(case when (d.id_name = 'tps') then r.value end) as tps, max(case when (d.id_name = 'Memory') then r.value end) as memory, max(case when (d.id_name = 'DiskWritten') then r.value end) as disk_written, max(case when (d.id_name = 'PercentUserTime') then r.value end) as percent_user, max(case when (d.id_name = 'PercentCpuTime') then r.value end) as percent_cpu, max(case when (d.id_name = 'UserMilliseconds') then r.value end) as user_milliseconds, max(case when (d.id_name = 'YcsbUpdateLatencyMicrosecs') then r.value end) as update_latency, max(case when (d.id_name = 'YcsbReadLatencyMicrosecs') then r.value end) as read_latency, max(case when (d.id_name = 'Updates') then r.value end) as updates, max(case when (d.id_name = 'Deletes') then r.value end) as deletes, max(case when (d.id_name = 'Inserts') then r.value end) as inserts, max(case when (d.id_name = 'Commits') then r.value end) as commits, max(case when (d.id_name = 'Rollbacks') then r.value end) as rollbacks, max(case when (d.id_name = 'Objects') then r.value end) as objects, max(case when (d.id_name = 'ObjectsCreated') then r.value end) as objects_created, max(case when (d.id_name = 'FlowStalls') then r.value end) as flow_stalls, max(case when (d.id_name = 'NodeApplyPingTime') then r.value end) as node_apply_ping_time, max(case when (d.id_name = 'NodePingTime') then r.value end) as node_ping_time,
Re: [SQL] [noob] How to optimize this double pivot query?
Two issues... 1. I do not know how you acquire the data or who controls how/what is generated 2. I do not know the primary means of using said data If you capture a new metric you generally have to change quite a few things to actually use it so you might as well just add a column as well. If you want to be able to at least capture unknown input and store it somewhere that is where the hstore extension comes in. In effect you should store unknown data there until you decide to update the schema to actually make use of it. In situations where you want to use it without altering the schema you normally simply list the unknowns and as such should output a row-like structure. The query you provide will need to be updated in the same way a physical table would be. So just use a table. Or do not provide a consolidated/wide query. If both, then deal with the performance hit one time per id and create a materialized view - basically insert the results of the query into a physical table and for live usage query that table. This is a cache and comes with all the benefits and downsides thereof. David J. On Oct 1, 2012, at 21:13, Robert Buck wrote: > So as you can probably glean, the tables store performance metric data. The > reason I chose to use k-v is simply to avoid having to create an additional > column every time a new metric type come along. So those were the two options > I thought of, straight k-v and column for every value type. > > Are there other better options worth considering that you could point me > towards that supports storing metrics viz. with an unbounded number of metric > types in my case? > > Bob > > On Mon, Oct 1, 2012 at 9:07 PM, David Johnston wrote: > From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org] > On Behalf Of Robert Buck > Sent: Monday, October 01, 2012 8:47 PM > To: pgsql-sql@postgresql.org > Subject: [SQL] [noob] How to optimize this double pivot query? > > > > I have two tables that contain key-value data that I want to combine in > pivoted form into a single result set. They are related to two separate > tables. > > The tables are: test_results, test_variables, metric_def, metadata_key. The > latter two tables are enum-like tables, basic descriptors of data stored in > other tables. The former two tables are basically key-value tables (with ids > as well); these k-v tables are related to the latter two tables via foreign > keys. > > The following SQL takes about 11 seconds to run on a high-end laptop. The > largest table is about 54k records, pretty puny. > > Can someone provide a hint as to why this is so slow? Again, I am a noob to > SQL, so the SQL is probably poorly written. > > > > > Your query, while maybe not great, isn’t the cause of your problem. It is > the table schema, specifically the “key-value” aspect, that is killing you. > > > > You may want to try: > > > > SELECT * > > FROM (SELECT id FROM …) id_master > > NATURAL LEFT JOIN (SELECT id, field_value AS … FROM … WHERE fieldtype = ‘’) f1 > > NATURAL LEFT JOIN (SELECT id, field_value AS … FROM … WHERE fieldtype = ‘’) f2 > > [repeat one left join for every field; though you will then need to decide > if/how to deal with NULL – not that you are currently doing anything special > anyway…] > > > > Mainly the above avoids the use of “max()” and instead uses direct joins > between the relevant tables. I have no clue whether that will improve things > but if you are going to lie in this bed you should at least try different > positions. > > > > The better option is to educate yourself on better ways of constructing the > tables so that you do not have to write this kind of god-awful query. In > some cases key-value has merit but usually only when done in moderation. Not > for the entire database. You likely should simply have a table that looks > like the result of the query below. > > > > As a second (not necessarily mutually exclusive) alternative: install and use > the hstore extension. > > > > David J. > > > > > Thanks in advance, > > Bob > > select > > t.id_name, > max(t.begin_time) as begin_time, > max(t.end_time) as end_time, > > max(case when (m.id_name = 'package-version') then v.value end) as > package_version, > max(case when (m.id_name = 'database-vendor') then v.value end) as > database_vendor, > max(case when (m.id_name = 'bean-name') then v.value end) as bean_name, > max(case when (m.id_name = 'request-distribution') then v.value end) as > request_distribution, > max(case wh
Re: [SQL] Help in accessing array
> -Original Message-
> From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-
> ow...@postgresql.org] On Behalf Of mephysto
> Sent: Thursday, September 27, 2012 6:12 AM
> To: pgsql-sql@postgresql.org
> Subject: [SQL] Help in accessing array
>
> Hi to everyone,
> I have a little problem to retrieve data from multidimensional array. For
> example, if I have this array
>
> foo[][] = {{5,3},{2,2}}
>
> how can I retrieve one of two internal array (for example I would to
retrieve
> {5,3}?
>
> I tried foo[1:1] but the result is {{5,3}}, not {5,3}.
>
> Is a method to achieve my goal?
>
> Thanks in advance.
>
> Mephysto
IIRC
You cannot de-dimension an array (aside from the special-case single
dimension, 1 value, case). You will need to unnest the array into
components, filter those components, then go from there. Each,
"unnest(array)" call explodes one dimension. Consider wraping things in a
function to make the code cleaner.
David J.
--
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] Calling the CTE for multiple inputs
> -Original Message- > From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql- > ow...@postgresql.org] On Behalf Of air > Sent: Thursday, October 04, 2012 3:32 PM > To: pgsql-sql@postgresql.org > Subject: [SQL] Calling the CTE for multiple inputs > > I have a CTE that takes top left and bottom right latitude/longitude values > along with a start and end date and it then calculates the amount of user > requests that came from those coordinates per hourly intervals between the > given start and end date. However, I want to execute this query for about > 2600 seperate 4-tuples of lat/lon corner values instead of typing them in one- > by-one. How would I do that? The code is as below: > > AND lat BETWEEN '40' AND '42' > AND lon BETWEEN '28' AND '30' I don't really follow but if I understand correctly you want to generate 2600 distinct rows containing values like (40, 42, 28, 30)? You could use "generate_series()" to generate each individual number along with a row_number and then join them all together: SELECT lat_low, lat_high, long_low, long_high FROM (SELECT ROW_NUMBER() OVER () AS index, generate_series(...) AS lat_low) lat_low_rel NATURAL JOIN (SELECT ROW_NUMBER() OVER () AS index, generate_series(...) AS lat_high) lat_high_rel NATURAL JOIN (SELECT ROW_NUMBER() OVER () AS index, generate_series(...) AS long_low) long_low_rel NATURAL JOIN (SELECT ROW_NUMBER() OVER () AS index, generate_series(...) AS long_high) long_high_rel You may (probably will) need to move the generate_series into a FROM clause in the sub-query but the concept holds. Then in the main query you'd simply... AND lat BETWEEN lat_low AND lat_high AND lon BETWEEN long_low AND long_high HTH David J. -- 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] String Search
> -Original Message- > From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql- > ow...@postgresql.org] On Behalf Of Fabio Ebner - Dna Solution > Sent: Thursday, October 04, 2012 3:41 PM > To: pgsql-sql@postgresql.org > Subject: [SQL] String Search > > Anyone know the best way to do one select in String field? > > tks You are going to need to phrase a better question. In the meantime please read the documentation on the various built-in string functions available. I've provided links to the function index page (look for "string functions") as well as PostgreSQL's full text search capability since both are "string" related. http://www.postgresql.org/docs/9.2/interactive/functions.html http://www.postgresql.org/docs/9.2/interactive/textsearch.html My best guess is you want to learn about substring functions and/or regular expressions. David J. -- 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 to make this CTE also print rows with 0 as count?
> -Original Message-
> From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-
> ow...@postgresql.org] On Behalf Of air
> Sent: Saturday, October 06, 2012 8:48 AM
> To: pgsql-sql@postgresql.org
> Subject: [SQL] How to make this CTE also print rows with 0 as count?
>
> I have a CTE based query, to which I pass about 2600 4-tuple
> latitude/longitude values using joins - these latitude longitude 4-tuples
have
> been ID tagged and held in a second table called coordinates. These top
left
> and bottom right latitude / longitude values are passed into the CTE in
order
> to display the amount of requests (hourly) made within those coordinates
> for given two timestamps).- I am able to get the total requests per day
within
> the timestamps given, that is, the total count of user requests on every
> specified day. (E.g. user opts to see every Wednesday or Wednesday AND
> Thursday etc. - between hours 11:55 and 22:04 between dates January 1 and
> 31, 2012 for every latitude/longitude 4-tuples I pass.) But I cannot view
the
> rows with count 0. My query is as below:
>
>
>
> WITH v AS (
>SELECT '2012-01-1 11:55:11'::timestamp AS _from
> ,'2012-01-31 22:02:21'::timestamp AS _to
>)
> , q AS (
>SELECT c.coordinates_id
> , date_trunc('hour', t.calltime) AS stamp
> , count(*) AS zcount
>FROM v
>JOIN mytable t ON t.calltime BETWEEN v._from AND v._to
>AND (t.calltime::time >= v._from::time AND
> t.calltime::time <= v._to::time) AND (extract(DOW
from
> t.calltime) = 3)
>JOIN coordinates c ON (t.lat, t.lon)
>BETWEEN (c.bottomrightlat, c.topleftlon)
>AND (c.topleftlat, c.bottomrightlon)
>GROUP BY c.coordinates_id, date_trunc('hour', t.calltime)
>)
> , cal AS (
>SELECT generate_series('2011-2-2 00:00:00'::timestamp
> , '2012-4-1 05:00:00'::timestamp
> , '1 hour'::interval) AS stamp
>FROM v
>)
> SELECT q.coordinates_id, cal.stamp::date, sum(q.zcount) AS zcount
> FROM v, cal
> LEFT JOIN q USING (stamp)
> WHERE extract(hour from cal.stamp) >= extract(hour from v._from)
> ANDextract(hour from cal.stamp) <= extract(hour from v._to)
> ANDextract(DOW from cal.stamp) = 3
> ANDcal.stamp >= v._from
> ANDcal.stamp <= v._to
> GROUP BY q.coordinates_id, cal.stamp::date ORDER BY q.coordinates_id,
> stamp;
>
>
>
>
> The output I get when I execute this query is basically like this
(normally I
> have about 10354 rows returned excluding the rows with 0 zcount, just
> providing two coordinates for sake of similarity):
>
> coordinates_id | stamp | zcount
> 1 ;"2012-01-04"; 2
> 1 ;"2012-01-11"; 3
> 1 ;"2012-01-18"; 2
> 2 ;"2012-01-04"; 2
> 2 ;"2012-01-11"; 3
> 2 ;"2012-01-18"; 2
>
>
>
>
> However, it should be like this where all rows with zcount 0 should also
be
> printed out along with rows that have nonzero zcounts -E.g. January 25
with
> zcount 0 for the two coordinates with ID 1 and 2 should also be printed in
this
> small portion of example-:
>
> coordinates_id | stamp | zcount
> 1 ;"2012-01-04"; 2
> 1 ;"2012-01-11"; 3
> 1 ;"2012-01-18"; 2
> 1 ;"2012-01-25"; 0
> 2 ;"2012-01-04"; 2
> 2 ;"2012-01-11"; 3
> 2 ;"2012-01-18"; 2
> 2 ;"2012-01-25"; 0
>
>
>
>
> How can I achieve this? Thanks in advance.
>
>
Food for thought, generally when you want "everything including the zeros"
you want to build the "master" set without any values, build out the "values
only" dataset, then LEFT JOIN them and use COALESCE to generate values for
the missing data.
So:
SELECT id, stamp, COALESCE(datavalues.zcount, 0) AS zcount
FROM (cal CROSS JOIN id_master) master
LEFT JOIN datavalues USING (id, stamp)
Also, the mixing of multiple FROM relations and JOINs is confusing. In
particular is the fact the JOIN takes precedence over the "," in FROM
"A JOIN clause combines two FROM items. Use parentheses if necessary to
determine the order of nesting. In the absence of parentheses, JOINs nest
left-to-right. In any case JOIN binds more tightly than the commas
separating FROM items."
http://www.postgresql.org/docs/9.2/interactive/sql-select.html
Your query is equivalent to:
SELECT ...
FROM v CROSS JOIN (cal LEFT JOIN q USING stamp)
WHERE ...
Anyway, the "create master, left join data, coalesce" methodology is one
that I find to be easy to understand and implement.
HTH,
David J.
--
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] Trigger triggered from a foreign key
> -Original Message- > From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql- > ow...@postgresql.org] On Behalf Of Victor Sterpu > Sent: Friday, October 19, 2012 2:15 PM > To: pgsql-sql@postgresql.org > Subject: [SQL] Trigger triggered from a foreign key > > I have this trigger that works fine. The trigger prevents the deletion of the > last record. > But I want skip this trigger execution when the delete is done from a external > key. > How can I do this? > I do not think this is possible; there is no "stack" context to examine to determine how a DELETE was issued. The trigger itself would seem to be possibly exhibit concurrency issues, meaning that in certain circumstances the last record could be deleted. You may want to add explicit locking to avoid that possibility. That or figure out a better way to accomplish whatever it is you are trying to do. David J. -- 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] Insert strings that contain colons into a table
> -Original Message-
> From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-
> ow...@postgresql.org] On Behalf Of lmagnell
> Sent: Friday, October 19, 2012 4:25 PM
> To: pgsql-sql@postgresql.org
> Subject: [SQL] Insert strings that contain colons into a table
>
> How can I insert multiple strings into a table where the strings contain
colons.
> This case fails:
>
> INSERT INTO wwn (wwn_start,wwn_end) VALUES
> ('50:06:0B:00:00:C2:86:80','50:06:0B:00:00:C2:86:83');
> ERROR: array value must start with "{" or dimension information at
character
> 71 LINE 1: ..._start,wwn_end) VALUES
('50:06:0B:00:00:C2:86:80','50:06:0B:...
>
> But this case passes:
>
> INSERT INTO wwn (wwn_start) VALUES ('50:06:0B:00:00:C2:86:80');
>
> Thank you,
> Lance
>
The "colon" has nothing to do with it.
It appears that "wwn_end" is defined as an array and not a simple text
value.
You need to provide the table definition for "wwn".
This is a section in the documentation regarding arrays but I am guessing
the use of an array in this situation is a mistake.
http://www.postgresql.org/docs/9.0/interactive/arrays.html
David J.
--
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] pull in most recent record in a view
On Oct 26, 2012, at 5:24, Gary Stainburn wrote: > This is my best effort so far is below. My concern is that it isn't very > efficient and will slow down as record numbers increase > > create view current_qualifications as > select q.*, (q.qu_qualified+q.qu_renewal)::date as qu_expires from > qualifications q > join (select st_id, sk_id, max(qu_qualified) as qu_qualified from > qualifications group by st_id, sk_id) s > on q.st_id=s.st_id and q.sk_id = s.sk_id and q.qu_qualified = s.qu_qualified; > > > select t.st_id, t.st_name, k.sk_id, k.sk_desc, q.qu_qualified, q.qu_renewal, > q.qu_expires > from current_qualifications q > join staff t on t.st_id = q.st_id > join skills k on k.sk_id = q.sk_id; > The best way to deal with recency problems is to maintain a table that contains only the most recent records using insert/update/delete triggers. A boolean flag along with a partial index can work instead of an actual table in some cases. If using a table only the pkid needs to be stored, along with any desired metadata. It probably isn't worth the effort until you actually do encounter performance problems. David J. -- 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] replace text occurrences loaded from table
> -Original Message-
> From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-
> ow...@postgresql.org] On Behalf Of jan zimmek
> Sent: Tuesday, October 30, 2012 7:45 AM
> To: pgsql-sql@postgresql.org
> Subject: [SQL] replace text occurrences loaded from table
>
> hello,
>
> i am actually trying to replace all occurences in a text column with some
> value, but the occurrences to replace are defined in a table. this is a
> simplified version of my schema:
>
> create temporary table tmp_vars as select var from
> (values('ABC'),('XYZ'),('VAR123')) entries (var); create temporary table
> tmp_messages as select message from (values('my ABC is XYZ'),('the XYZ is
> very VAR123')) messages (message);
>
> select * from tmp_messages;
>
> my ABC is XYZ -- row 1
> the XYZ is very VAR123 -- row 2
>
> now i need to somehow update the rows in tmp_messages, so that after the
> update i get the following:
>
> select * from tmp_messages;
>
> my XXX is XXX -- row 1
> the XXX is very XXX -- row 2
>
> i have implemented a solution in plpgsql by doing a nested for-loop over
> tmp_vars and tmp_messages, but i would like to know if there is a more
> efficient way to solve this problem ?
>
You may want to consider creating an alternating regular expression and
using "regexp_replace(...)" one time per message instead of "replace(...)"
three times
Not Tested: regexp_replace(message, 'ABC|XYZ|VAR123', 'XXX', 'g')
This should at least reduce the amount of overhead checking each expression
against each message would incur.
If you need even better performance you would need to find some way to
"index" the message contents so that for each expression the index can be
used to quickly identify the subset of messages that are going to be
altered. The full-text-search capabilities of PostgreSQL will probably help
here though I am not familiar with them personally.
Since you have not shared the true context of your request no alternatives
can be suggested. Also, your ability to implement certain algorithms is
influenced by the version of PostgreSQL that you are running and which you
have also not provided.
David J.
--
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] Using regexp_matches in the WHERE clause
On Nov 26, 2012, at 7:13, Thomas Kellerer wrote: > > So I tried: > > SELECT * > FROM some_table > WHERE regexp_matches(somecol, 'foobar') is not null; > > However that resulted in: ERROR: argument of WHERE must not return a set > > Hmm, even though an array is not a set I can partly see what the problem is > (although given the really cool array implementation in PostgreSQL I was a > bit surprised). > regex_matches returns a set because you can supply the "g" option to capture all matches and each separate match returns its own record. Even though only one record is ever returned without the "g" option the function itself is the same and still is defined to return a set. David J. -- 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] Joining several rows into only one
> -Original Message- > From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql- > ow...@postgresql.org] On Behalf Of Oliver d'Azevedo Cristina > Sent: Wednesday, November 28, 2012 1:42 PM > To: JORGE MALDONADO > Cc: pgsql-sql@postgresql.org > Subject: Re: [SQL] Joining several rows into only one > > You need to use the array_agg() and array_to_string() functions. > Or you can skip directly to the "string_agg(expression, delimiter)" function. See http://www.postgresql.org/docs/9.2/interactive/functions-aggregate.html for more information on aggregate-related functions. David J. -- 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] deciding on one of multiple results returned
From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org] On Behalf Of Wes James Sent: Friday, December 21, 2012 11:32 AM To: pgsql-sql@postgresql.org Subject: [SQL] deciding on one of multiple results returned If a query returns, say the following results: id value 0 a 0 b 0 c 1 a 1 b How do I just choose a preferred element say value 'a' over any other elements returned, that is the value returned is from a subquery to a larger query? Thanks. ORDER BY (with a LIMIT depending on circumstances) David J.
Re: [SQL] Query execution based on a condition
From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org] On Behalf Of JORGE MALDONADO Sent: Saturday, December 29, 2012 2:06 PM To: pgsql-sql@postgresql.org Subject: [SQL] Query execution based on a condition I have a query similar to the one shown below but, depending on the value of a field, only the first SELECT statement shoud execute and the other 3 should be ignored. Is there a way to achieve this situation? SELECT fields FROM tblTable WHERE condition UNION SELECT fields FROM tblTable WHERE condition UNION SELECT fields FROM tblTable WHERE condition UNION SELECT fields FROM tblTable WHERE condition Respectfully, Jorge Maldonado Not using pure SQL. pl/pgsql provides you access to conditionals and flow control so you should be able to create a function to do precisely what you need. David J.
Re: [SQL] pivot query with count
SELECT num_ads, sum(...), sum(...), FROM ( your query here ) GROUP BY num_ads; BTW, While "SELECT '1' "num_ads" is valid syntax I recommend you use the "AS" keyword. '1' AS "num_ads" David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/pivot-query-with-count-tp5752072p5752077.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
Re: [SQL] pivot query with count
My prior comment simply answers your question. You likely can rewrite your query so that a separate grouping layer is not needed (or rather the group by would exist in the main query and you minimize the case/sub-select column queries and use aggregates and case instead). David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/pivot-query-with-count-tp5752072p5752078.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
Re: [SQL] Select statement with except clause
JORGE MALDONADO wrote > How does the EXCEPT work? Do fields should be identical? > I need the difference to be on the first 3 fields. Except operates over the entire tuple so yes all fields are evaluated and, if they all match, the row from the "left/upper" query is excluded. If you need something different you can use some variation of: IN EXISTS NOT IN NOT EXISTS with a sub-query (correlated or uncorrelated as your need dictates). For example: SELECT col1, col2, col3, sum(col4) FROM tbl WHERE (col1, col2, col3) NOT IN (SELECT col1, col2, col3 FROM tbl2) -- not correlated GROUP BY col1, col2, col3 SELECT col1, col2, col3, sum(col4) FROM tbl WHERE NOT EXISTS ( SELECT 1 FROM tbl AS tbl2 WHERE --make sure to alias the sub-query table if it matches the outer reference (tbl.col1, tbl.col2, tbl.col3) = (tbl2.col1, tbl2.col2, tbl2.col3) ) -- correlated; reference "tbl" within the query inside the where clause GROUP BY col1, col2, col3 I do not follow your example enough to provide a more explicit example/solution but this should at least help point you in the right direction. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Select-statement-with-except-clause-tp5756658p5756661.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
Re: [SQL] Select statement with except clause
Reply-all is acceptable; but standard list protocol is to respond at the end of the message after performing "quote editing". JORGE MALDONADO wrote > Firstly, I want to thank you for responding. > Secondly, I wonder if I should only reply to the mailing list (I clicked > Reply All); if this is the case, I apologize for any inconvenience. Please > let me know so I reply correctly next time. > > I will describe my issue with more detail. I need to perform 2 very > similar > queries as follows: > > *** QUERY 1 *** > SELECT fldA, fldB, fldC, SUM(fldD) AS fldD > FROM tableA > WHERE condition1 > GROUP BY fldA, fldB, fldC > > *** QUERY 2 *** > SELECT fldA, fldB, fldC, SUM(fldD)*(-1) AS fldD > FROM tableA > WHERE condition2 > GROUP BY fldA, fldB, fldC > > As you can see, both reference the same table and the same fields. > > The differences between the queries are: > a) The last SELECTED field is multiplied by (-1) in the second query. > b) The WHERE conditions. > > What I finally need is to exclude records generated by QUERY1 from QUERY2 > when fldA, fldB and fldC are equal in both results. Example query layout; not promising it is the most efficient but it works. WITH q1 AS ( SELECT fldA, fldB, fldC, sum(fldD) AS sumD ... ) , q2 AS ( SELECT fldA, fldB, fldC, sum(fldD)*-1 AS sumD ... WHERE ... AND (fldA, fldB, fldC) NOT IN (SELECT (q1.fldA, q2.fldB, q3.fldC) FROM q1) ) SELECT fldA, fldB, fldC, sumD FROM q1 UNION ALL SELECT fldA, fldB, fldC, sumD FROM q2 ; If you actually explain the goal and not just ask a technical question you might find that people suggest alternatives that you are not even considering. SELECT fldA, fldB, fldC, sum_positive, sum_negative FROM (SELECT fldA, fldB, fldC, sum(fldD) AS sum_positive) q1 NATURAL FULL OUTER JOIN (SELECT fldA, fldB, fldC, sum(fldD)*-1 AS sum_negative) q2 WHERE <...> Food for thought. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Select-statement-with-except-clause-tp5756658p5756790.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
Re: [SQL] UNNEST result order vs Array data
gmb wrote >>> The best, which you won't >>> like, is to wait for 9.4 where unnest() will most likely have a WITH >>> ORDINALITY option and you can sort on that. > > The fact that this type of thing is on the 9.4 roadmap indicates (to me, > in any case) that there are problems with the UNNEST functionality in the > current version (I'm running 9.2). > > Thanks Vik, I'll take a look at the implementation you suggested. To recap: unnest() returns its output in the same order as the input. Since an array is ordered it will be returned in the same output order by unnest. However, since unnest() only returns a single column (though possibly of a composite type) it cannot provide the row number in the output thus in order to maintain the same order elsewhere in the query it is necessary to use "ROW_NUMBER() OVER (...)" on the output of the unnest() - and before joining it with any other unnest calls or tables - before supplying it to the rest of the query. The "WITH ORDINALITY" functionality proposed for 9.4 will cause the unnest() [and other] function to output this additional column along with the usual output. This is, I am pretty such, a usability enhancement that makes easier something that can be done today using CTE/WITH and/or sub-queries. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/UNNEST-result-order-vs-Array-data-tp5760087p5760126.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
Re: [SQL] Update a composite nested type variable
Luca Vernini wrote
> I'm writing a system with havy use of composite types.
> I have a doubt.
>
> I'm writing all in functions with language plpgsql.
> When I read a field from a composite type I must write something like
> this:
> status = ((in_customer.customer_data).customer_status).status_id
>
> And this works fine. I need to enclose the base type, but this is not a
> problem.
>
> When I need to assign a value I try to write something like:
> (in_customer.customer_data).field_a := NULL;
>
> But postgresql rise an error:
> ERROR: syntax error at or near "("
> SQL state: 42601
>
> If I dont use parentesis I rise a different error:
>
> ERROR: "in_customer.customer_data.field_a" is not a known variable
> SQL state: 42601
>
> 2 questions:
> Why is the behavior so different in read and in assign.
> How can I workaround this and update my values?
>
> Luca.
This may be a pl/pgsql limitation but you should probably provide a complete
self-contained example with your attempt so that user-error can be
eliminated.
David J.
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Update-a-composite-nested-type-variable-tp5763023p5763082.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
Re: [SQL] Update a composite nested type variable
Luca Vernini wrote > 2013/7/8 David Johnston < > polobo@ > > > >> >> This may be a pl/pgsql limitation but you should probably provide a >> complete >> self-contained example with your attempt so that user-error can be >> eliminated. >> >> David J. >> >> > All right. Here you are a complete example. Just tested it. > Sorry for the long email. This does appear to be a limitation. The documentation says pl/pgsql allows for "simple variables" in the target which 2-layer composite types do not qualify for. As a work-around I'd suggest creating local variables for each of the relevant fields - say by using the same names but with "_" instead of "."; You will then need to reconstruct each complex value from the basic values and return the reconstructed value. r_cus_id := retset.cus_data.id; r_cus_name := retset.cus_data.name; r_cus_email := retset.cus_data.email; r_superpower := retset.superpower: RETURN SELECT (r_cus_id, r_cus_name, r_cus_email)::type_customer, r_superpower)::type_supercustomer; Not tested but as I am writing this I am getting a Deja-Vu sensation which I think means I am correct and that this somewhat convoluted way is what you've got. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Update-a-composite-nested-type-variable-tp5763023p5763119.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
Re: [SQL] Criteria to define indexes
JORGE MALDONADO wrote > And so on. What I see is that it is not a good decision to set a key for > every possibility because it will have an impact on performance due to > index maintenance. What would be a good way to define indexes in a case > like this? For your specific case, and also more generally, you will define multiple indexes with a single column within each. PostgreSQL is able to fairly efficiently scan multiple indexes and then combine them to find records that exist on both (or other logical combinations). Multi-key indexes can be advantageous in, for instance, composite primary key definitions but in this kind of star-schema setup simply have each foreign key and whatever other searching fields you require maintain their own individual index. David J. P.S. Arguably, having a separate column for each kind of person is a poor design at face value - though not uncommon. Whether it is going to bite you in the future is unknown but depending on whether a single person can hold multiple roles or if you need to add new roles in the future maintenance and querying this table for summary information may become more difficult. At the same time basic data entry and modelling to some degree is easier since this model is simpler. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Criteria-to-define-indexes-tp5765334p5765336.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
Re: [SQL] Using regexp_matches in the WHERE clause
spulatkan wrote > so following is enough to get the rows that matches regular expression > This is bad form even if it works. If the only point of the expression is to filter rows it should appear in the WHERE clause. The fact that regexp_matches(...) behaves in this way at all is, IMO, a flaw of the implementation. > on pgadmin the column type is shown as text[] thus I also do not > understand why array_length on where condition does not work for this. > This works because the array_length formula is applied once to each "row" of the returned set. As mentioned before it makes absolutely no sense to evaluate a set-returning function within the WHERE clause and so attempting to do so causes a fatal exception. For my usage I've simply written a wrapper function that implements the same basic API as regexp_matches but that returns a scalar "text[]" instead of a "setof text[]". It makes coding these kinds of queries easier if you know/understand the fact that your matching will never cause more than 1 row to be returned. If zero rows are returned I return an empty array and the normal 1-row case returns the matching array. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Using-regexp-matches-in-the-WHERE-clause-tp5733684p5768926.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
Re: [SQL] removing duplicates and using sort
Note that you could always do something like: WITH original_query AS ( SELECT DISTINCT ... ) SELECT * FROM original_query ORDER BY lastname, firstname; OR SELECT * FROM ( SELECT DISTINCT ) sub_query ORDER BY lastname, firstname I am thinking you cannot alter the existing ORDER BY otherwise your use of "DISTINCT ON" begins to mal-function. I dislike DISTINCT ON generally but do not wish to ponder how you can avoid it, so I'd suggest just turning your query into a sub-query like I show above. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/removing-duplicates-and-using-sort-tp5770931p5771096.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
Re: [SQL] postgres subfunction return error
jonathansfl wrote > greetings. I'm trying to write a function that acts like a switchboard, > calling other functions depending on incoming parameters. > I'm getting error: query has no destination for result data > > > SELECT * FROM dev.pr_test_subfunction(SWV_Action); In pl/pgsql if you do not use an "INTO" clause on a select statement you must replace the "SELECT" with "PERFORM". Failing to do so results in the error you are seeing. In this case your calls to the sub-function do not magically populate the parent function variables. You must manually map the output of the sub-function call query onto the parent variables. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/postgres-subfunction-return-error-tp5772407p5772408.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
Re: [SQL] postgres subfunction return error
jonathansfl wrote > SELECT * INTO v_outvar1, v_outvar2, v_outvar3 FROM > custom.pr_test_subfunction(SWV_Action); > OPEN swv_refcur for SELECT v_outvar1; > OPEN swv_refcur2 for SELECT v_outvar2; > OPEN swv_refcur3 for SELECT v_outvar3; > RETURN; I've never used cursors in this way so my help is more theory but: The called-function already created the cursors. In the parent function you should simply be able to pass them through unaltered: SELECT * INTO v_outvar1, ...; swv_refcur := v_outvar1; ... ... RETURN; You can possible simply the above and toss the temporary variables but that should not impact the semantics. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/postgres-subfunction-return-error-tp5772407p5772627.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
Re: [SQL] Help needed with Window function
gmb wrote > item_code | _date| qty | max > - > ABC | 2013-04-05 | 10.00| 2013-04-05 > ABC | 2013-04-06 | 10.00| 2013-04-06 > ABC | 2013-04-06 | -2.00| 2013-04-06 > ABC | 2013-04-07 | 10.00| 2013-04-07 > ABC | 2013-04-08 | -2.00| 2013-04-07<< last date > where a positive qty was posted > ABC | 2013-04-09 | -1.00| 2013-04-07<< last date > where a positive qty was posted Brute force approach; tweak if performance dictates: WITH vals (id, amt, tag) AS ( VALUES (1, 10, '1'), (2, -2, '2'), (3, -3, '3'), (4, 5, '4'), (5, -1, '5'), (6, 6, '6') ) SELECT * , array_agg(CASE WHEN amt < 0 THEN NULL ELSE tag END) OVER (ORDER BY id) , array_last_nonnull(array_agg(CASE WHEN amt < 0 THEN NULL ELSE tag END) OVER (ORDER BY id)) FROM vals; CREATE OR REPLACE FUNCTION array_last_nonnull(in_array anyarray) RETURNS anyelement AS $$ SELECT unnest FROM ( SELECT unnest, row_number() OVER () AS array_index FROM ( SELECT unnest($1) ) explode ) filter WHERE unnest IS NOT NULL ORDER BY array_index DESC LIMIT 1; $$ LANGUAGE sql STRICT IMMUTABLE ; Basic idea: use ORDER BY in the window to auto-define a range-preceding frame. Create an array of all dates (tags in the example) that match with positive amounts. Negative amounts get their matching tag added to the array as NULL. The provided function looks into the generated array and returns the last (closest to the current row in the frame) non-null date/tag in the array which ends up being the date/tag matching the last positive amount in the frame. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Help-needed-with-Window-function-tp5773160p5773171.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
Re: [SQL] Advice on defining indexes
JORGE MALDONADO wrote > I have a table with fields that I guess would be a good idea to set as > indexes because users may query it to get results ordered by different > criteria. For example: > > -- > Artists Table > -- > 1. art_id > 2. art_name > 3. art_bday > 4. art_sex > 5. art_country (foreign key, there is a table of countries) > 6. art_type (foreign key, there is a table of types of artists) > 7. art_email > 8. art_comment > 9. art_ bio > > "art_id" is the primary key. > Users query the table to get results ordered by fields (2) to (6). Is it > wise to define such fields as indexes? > > I ask this question because our database has additional tables with the > same characteristics and maybe there would be many indexes. > > With respect, > Jorge Maldonado Some thoughts: Indexes for sorting are less useful than indexes for filtering. I probably would not create an index if it was only intended for sorting. Note that in many situations the number of ordered records will be fairly small so on-the-fly sorting is not going to be that expensive anyway. Indexes decrease insertion/update performance but generally improve selection performance. The relative volume of each is important. Index keys which contain a large number of rows are generally ignored in favor of a table scan. For this reason gender is seldom indexed. You have the option of a partial index if a single key contains a large number of records. Simply index everything but that key. Smaller indexes are better and any searches for the ignored key would end up skipping the index in many cases anyway. Consider create full-text search indexes on the comment/bio column and you can probably also add in the other fields into some form of functional index so that performing a search over that single field will in effect search all of the columns. I'd probably index country and type to make the foreign key lookups faster and then create a functional full-text index on the different text fields. I would then add an index on art_bday and call it done. You can then write a view/function that performs a full-text search against the functional index (or just create an actual column) for most text searches and have separate criteria filters for country/type/birthday. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Advice-on-defining-indexes-tp5773423p5773424.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
Re: [SQL] Unique index VS unique constraint
JORGE MALDONADO wrote > I have search for information about the difference between "unique index" > and "unique constraint" in PostgreSQL without getting to a specific > answer, > so I kindly ask for an explanation that helps me clarify such concept. A constraint says what valid data looks like. An index stores data in such a way as to enhance search performance. Uniqueness is a constraint. It happens to be implemented via the creation of a unique index since an index is quickly able to search all existing values in order to determine if a given value already exists. PostgreSQL has chosen to allow a user to create a unique index directly, instead of only via a constraint, but one should not do so. The uniqueness property is a constraint and so a "unique index" without a corresponding constraint is an improper model. If you look at the model without any indexes (which are non-model objects) you would not be aware of the fact that duplicates are not allowed yet in the implementation that is indeed the case. Logically the constraint layer sits on top of an index and performs its filtering of incoming data so that the index can focus on its roles of storing and retrieving. Extending this thought the underlying index should always be non-Unique and a unique filter/constraint would use that index for validation before passing the new value along. However, practicality leads to the current situation where the index takes on the added role of enforcing uniqueness. This is not the case for any other constraint but the UNIQUE constraints case is so integral to PRIMARY KEY usage that the special case behavior is understandable and much more performant. Conceptually the index is an implementation detail and uniqueness should be associated only with constraints. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Unique-index-VS-unique-constraint-tp5773386p5773427.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
Re: [SQL] Advice on defining indexes
JORGE MALDONADO wrote > If a table has a foreign key on 2 fields, should I also create an index > composed of such fields? Yes. If you want to truly/actually model a foreign key the system will require you to create a unique constraint/index on the "primary/one" side of the relationship. CREATE TABLE list ( lst_source, lst_date, FOREIGN KEY (lst_source, lst_date) REFERENCES source (src_id, src_date) ...; If a unique constraint (in this case I'd suggest primary key) does not exist for source(src_id, src_date) the create table with the foreign key will fail. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Advice-on-defining-indexes-tp5773423p5773428.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
Re: [SQL] Unique index VS unique constraint
Steve Grey-2 wrote > Unique indexes can be partial, i.e. defined with a where clause (that must > be included in a query so that PostgreSQL knows to use that index) whereas > unique constraints cannot. This implies there can be data in the table but not in the index and thus said index is not part of the model. This strikes me, though, as a shortcoming of the declarative constraint implementation since such behavior should not modeled via indexes even if that is how they are implemented. The where clause limitation on constraints is arbitrary though adding it just for this would not pass a cost-benefit analysis. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Unique-index-VS-unique-constraint-tp5773386p5773434.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
