Re: [SQL] Correct implementation of 1:n relationship with n0?
2013/4/30 Wolfgang Keller felip...@gmx.net It hit me today that a 1:n relationship can't be implemented just by a single foreign key constraint if n0. I must have been sleeping very deeply not to notice this. E.g. if there is a table list and another table list_item and the relationship can be described as every list has at least one list_item (and every list_item can only be part of one list, but this is trivial). A correct solution would require (at least?): 1. A foreign key pointing from each list_item to its list 2. Another foreign key pointing from each list to one of its list_item. But this must be a list_item that itself points to the same list, so just a simple foreign key constraint doesn't do it. 3. When a list has more than one list_item, and you want to delete the list_item that its list points to, you have to re-point the foreign key constraint on the list first. Do I need to use stored proceures then for all insert, update, delete actions? (4. Anything else that I've not seen?) Is there a straight (and tested) solution for this in PostgreSQL, that someone has already implemented and that can be re-used? No, I definitely don't want to get into programming PL/PgSQL myself. especially if the solution has to warrant data integrity under all circumstances. Such as concurrent update, insert, delete etc. TIA, Sincerely, Wolfgang I don't think there is the way to achieve that without programming (less important in which language...) Your rules say: 1) End user - can't be able to create new list at all... (just new List) (If he can create new list - it will brake the your rule 2) He always creates list_item - but in one case - should pick existing list in another he must enter info about new list_item together with info about new list Technically - create new list_item calls one or another function 2) End User - just can delete list_item (function will make additional check - if there is no more list_items in my list - delete the list as well - the same check will be run after repoint) Everything else - will be assured with existing FK integrity
Re: [SQL] Correct implementation of 1:n relationship with n0?
2013/4/30 Anton Gavazuk antongava...@gmail.com Hi, Can you explain what you are trying to achieve because it's not clear... There are 2 types of relationships which might be used in your case: 1) unidirectional relationship from list_item to list through foreign key on list 2) bidirectional relationship implemented through join table which contains references between both tables These are pretty standard generic techniques applied many times and don't require any programming Thanks, Anton On Apr 30, 2013, at 16:39, Wolfgang Keller felip...@gmx.net wrote: It hit me today that a 1:n relationship can't be implemented just by a single foreign key constraint if n0. I must have been sleeping very deeply not to notice this. E.g. if there is a table list and another table list_item and the relationship can be described as every list has at least one list_item (and every list_item can only be part of one list, but this is trivial). A correct solution would require (at least?): 1. A foreign key pointing from each list_item to its list 2. Another foreign key pointing from each list to one of its list_item. But this must be a list_item that itself points to the same list, so just a simple foreign key constraint doesn't do it. 3. When a list has more than one list_item, and you want to delete the list_item that its list points to, you have to re-point the foreign key constraint on the list first. Do I need to use stored proceures then for all insert, update, delete actions? (4. Anything else that I've not seen?) Is there a straight (and tested) solution for this in PostgreSQL, that someone has already implemented and that can be re-used? No, I definitely don't want to get into programming PL/PgSQL myself. especially if the solution has to warrant data integrity under all circumstances. Such as concurrent update, insert, delete etc. TIA, Sincerely, Wolfgang -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql 2) bidirectional relationship implemented through join table which contains references between both tables What is an example of that?
Re: [SQL] Efficiency Problem
Hi,
1) Is function marked as immutable?
2) if immutable doesnt help... It should be possible execute it first, and
use it in other dynamics things in where...
Cheers,
Misa
Sent from my Windows Phone
--
From: Surfing
Sent: 17/03/2013 12:16
To: pgsql-sql@postgresql.org
Subject: [SQL] Efficiency Problem
Hi all,
I'm composing a query from a web application of type:
*SELECT * FROM table WHERE a_text_field LIKE replace_something ('%**
a_given_string**%');*
The function replace_something( ... ) is a stored procedure that replaces
some particular characters with others.
The problem is that I noticed that this query is inefficient... and I think
that the replace_something ( ... ) function is called for each row of the
table.
This observation is motivated by the fact that it takes around 30 seconds
to execute on the table (of about 25,000 rows), whereas if I execute:
*SELECT * FROM table WHERE a_text_field LIKE '**pre_processed_string
';*
where* pre_processed_string** *is the result of the application of
replace_something ('%*a_given_string*%') it just takes 164ms.
The execution of
*SELECT replace_something ('%**a_given_string**%')*
takes only 14ms.
Summarizing,
- Replace function: 14ms
- SELECT query without replace function: 164ms
- SELECT query with replace function: 30.000ms
Morever, I cannot create a stored procedure that precalculate the
*pre_processed_string
*and executes the query, since I dinamically
compose other conditions in the WHERE clause.
Any suggestion?
Thank you.
**
Re: [SQL] Using Ltree For Hierarchical Structures
Hi Igor, I agree it is all in the eyes of beholder. Would be good if you can show how to achieve the goal (Summing on Top Levels categories in hierarchy) with CTE? For example show all categories in level 2 (x), and sum amounts for each... (Sum takes all amounts from all transactions of its child categories in any bellow levels). I have tested both scenarios - and indexed ltree has given better result - though there is a possibility I haven't pick best approach to solve the problem with CTE... I am just interested in performance - implementation detail is less important... Data: Total number of categories: 1000 (in all levels) No of Categories in top level: 5 No of categories in level 2: 20 Total number of levels: can vary - max in my testing was 8... Transaction rows with amounts: 1 000 000 (though I am not sure what u meant by: 2-table design using ltree, and with CTE there are 2 tables... Categories and Transactions: just in categories instead of ltree datatype, is integer datatype: parent_id) Many thanks, Misa 2013/2/26 Igor Neyman iney...@perceptron.com From: Don Parris [mailto:parri...@gmail.com] Sent: Sunday, February 24, 2013 5:21 PM To: pgsql-sql@postgresql.org Subject: Using Ltree For Hierarchical Structures Hi all, With many thanks to Misa and others who helped out with my question about working with hierarchical data, I have now written a blog post on how I implemented the ltree module to solve my problem. http://dcparris.net/2013/02/24/using-ltree-hierarchical-postgresql/ Frankly, if you work with hierarchical data, I'm not sure I could recommend it strongly enough. I should think that even experienced, advanced SQL gurus would appreciate the simplicity ltree offers, when compared to the ugly table designs and recursive queries in order to work with hierarchical structures. I really hope this blog post will help others in the same boat. Regards, Don It's all in the eyes of beholder. IMHO, recursive CTEs are perfect for hierarchical structures, and much cleaner than 2-table design using ltree, that you show in the blog. Regards, Igor Neyman -- 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 Ltree For Hierarchical Structures
2013/2/26 Thomas Kellerer spam_ea...@gmx.net Don Parris wrote on 24.02.2013 23:20: With many thanks to Misa and others who helped out with my question about working with hierarchical data, I have now written a blog post on how I implemented the ltree module to solve my problem. http://dcparris.net/2013/02/**24/using-ltree-hierarchical-**postgresql/http://dcparris.net/2013/02/24/using-ltree-hierarchical-postgresql/ Frankly, if you work with hierarchical data, I'm not sure I could recommend it strongly enough. I should think that even experienced, advanced SQL gurus would appreciate the simplicity ltree offers, when compared to the ugly table designs and recursive queries in order to work with hierarchical structures. I really hope this blog post will help others in the same boat. How do you ensure referential integrity with this approach? (i.e. make sure that all elements from the path column actually point to an existing category) Thomas Hi Thomas, Yes we met that problem and it further makes deeper problems... i.e. what if some category in up level - change his parent (updated path field) - path must be changed for all childs... Of several solutions - we have picked to use the best from both worlds... So we still use - parent_id column... and ltree is used just as materialized path - to improve performance... I think Materialized Views what comming in 9.3 - (I still havent seen how it works) - will help in that way - we will see...
Re: [SQL] Summing Grouping in a Hierarchical Structure
Hi Don, To be honest with you - i dont know - but think it is not... We use it to solve the problem with hierarchy relations - but it is nowhere visible to users in the app... Our internal rule is to use ids in ltree structure to solve many others problems, actually to easy get, actual category info... From any point... So if needed, it is easy from 1.2.3.4, get: TOP.Groceries.Food.Herbs Spices if needed... Each of them are actually category names in the table... Kind regards, Misa On Saturday, February 23, 2013, Don Parris wrote: Misa, Is it possible to use spaces in the ltree path, like so: TOP.Groceries.Food.Herbs Spices Or do the elements of the path have to use underscores and dashes? On Sat, Feb 23, 2013 at 7:19 AM, Misa Simic misa.si...@gmail.com wrote: Hi Don, Yes, its better to use it in category table... Bryan, how many levels there will be - we dont know... With one table - and ltree is solved all cases... To add new subcategory user just picks the parent category... So it is easy to add chain ring to gear... As category... In another transaction table is category_id, amount... Don already posted query for sum... In these case just category and transaction table should be joined sum amount, group by functions on lpath(depending what is the goal...) Kind Regards, Misa On Saturday, February 23, 2013, Bryan L Nuse wrote: This works fine: test_ltree= SELECT path, trans_amt FROM testcat; path | trans_amt -+--- TOP.Transportation.Auto.Fuel| 50.00 TOP.Transportation.Auto.Maintenance | 30.00 TOP.Transportation.Auto.Fuel| 25.00 TOP.Transportation.Bicycle.Gear | 40.00 TOP.Transportation.Bicycle.Gear | 20.00 TOP.Transportation.Fares.Bus| 10.00 TOP.Transportation.Fares.Train | 5.00 TOP.Groceries.Food.Beverages| 30.00 TOP.Groceries.Food.Fruit_Veggies| 40.00 TOP.Groceries.Food.Meat_Fish| 80.00 TOP.Groceries.Food.Grains_Cereals | 30.00 TOP.Groceries.Beverages.Alcohol.Beer| 25.00 TOP.Groceries.Beverages.Alcohol.Spirits | 10.00 TOP.Groceries.Beverages.Alcohol.Wine| 50.00 TOP.Groceries.Beverages.Juice | 45.00 TOP.Groceries.Beverages.Other | 15.00 (16 rows) So if I want to see: TOP.Groceries| 240.00 TOP.Transportation | 180.00 Hello Don, Perhaps I am missing something about what your constraints are, or what you're trying to achieve, but is there any reason you could not use a series of joined tables indicating parent-child relationships? The following example follows that in your previous posts. Note that this approach (as given) will not work if branches stemming from the same node are different lengths. That is, if you have costs associated with Transportation.Bicycle.Gear, you could not also have a category Transportation.Bicycle.Gear.Chain_ring. (To add the latter category, you'd have to put costs from the former under something like Transportation.Bicycle.Gear.General -- or modify the approach.) However, lengthening the Alcohol branches, e.g., by tacking on a level5 table would be easy. Notice that level3 and level4 are not true look-up tables, since they may contain duplicate cat values. If I'm off base, by all means specify just how. Regards, Bryan -- CREATE TABLE level1 ( cat text PRIMARY KEY ); CREATE TABLE level2 ( cat text PRIMARY KEY, parent text REFERENCES level1(cat) ); -- D.C. Parris, FMP, Linux+, ESL Certificate Minister, Security/FM Coordinator, Free Software Advocate http://dcparris.net/ https://www.xing.com/profile/Don_Parrishttp://www.linkedin.com/in/dcparris GPG Key ID: F5E179BE
Re: [SQL] Summing Grouping in a Hierarchical Structure
Hi, Have you considered maybe ltree datatype? http://www.postgresql.org/docs/9.1/static/ltree.html I think it solves a lot of problems in topic Kind regards, Misa On Friday, February 15, 2013, Don Parris wrote: Hi all, I posted to this list some time ago about working with a hierarchical category structure. I had great difficulty with my problem and gave up for a time. I recently returned to it and resolved a big part of it. I have one step left to go, but at least I have solved this part. Here is the original thread (or one of them): http://www.postgresql.org/message-id/CAJ-7yonw4_qDCp-ZNYwEkR2jdLKeL8nfGc+-TLLSW=rmo1v...@mail.gmail.com Here is my recent blog post about how I managed to show my expenses summed and grouped by a mid-level category: http://dcparris.net/2013/02/13/hierarchical-categories-rdbms/ Specifically, I wanted to sum and group expenses according to categories, not just at the bottom tier, but at higher tiers, so as to show more summarized information. A CEO primarily wants to know the sum total for all the business units, yet have the ability to drill down to more detailed levels if something is unusually high or low. In my case, I could see the details, but not the summary. Well now I can summarize by what I refer to as the 2nd-level categories. Anyway, I hope this helps someone, as I have come to appreciate - and I mean really appreciate - the challenge of working with hierarchical structures in a 2-dimensional RDBMS. If anyone sees something I should explain better or in more depth, please let me know. Regards, Don -- D.C. Parris, FMP, Linux+, ESL Certificate Minister, Security/FM Coordinator, Free Software Advocate http://dcparris.net/ https://www.xing.com/profile/Don_Parrishttp://www.linkedin.com/in/dcparris GPG Key ID: F5E179BE
Re: [SQL] returning the number of rows output by a copy command from a function
Hi
Maybe:
1.
strSQL := 'copy (select MyColumns from MyExportTable) to MyFile.csv with
CSV HEADER;';
Execute strSQL
strSQL := 'Select count(*) from (select MyColumns from MyExportTable) t';
Execute strSQL into export_count;
Return export_count;
Kind Regards,
Misa
On Wednesday, January 16, 2013, James Sharrett wrote:
I have a function that generates a table of records and then a SQL
statement that does a COPY into a text file. I want to return the number
of records output into the text file from my function. The number of rows
in the table is not necessarily the number of rows in the file due to
summarization of data in the table on the way out. Here is a very
shortened version of what I'm doing:
CREATE OR REPLACE FUNCTION export_data(list of parameters)
RETURNS integer AS
$BODY$
declare
My variables
Begin
{ A lot of SQL to build and populate the table of records to export}
strSQL := 'copy (select MyColumns from MyExportTable) to MyFile.csv with
CSV HEADER;';
Execute strSQL;
Return 0;
end
$BODY$
LANGUAGE plpgsql VOLATILE
strSQL gets dynamically generated so it's not a static statement.
This all works exactly as I want. But when I try to get the row count
back out I cannot get it. I've tried the following:
1.
strSQL := 'copy (select MyColumns from MyExportTable) to MyFile.csv with
CSV HEADER;';
Execute strSQL into export_count;
Return export_count;
This give me an error saying that I've tried to use the INTO statement
with a command that doesn't return data.
2.
strSQL := 'copy (select MyColumns from MyExportTable) to MyFile.csv with
CSV HEADER;';
Execute strSQL;
Get diagnostics export_count = row_count;
This always returns zero.
3.
strSQL := 'copy (select MyColumns from MyExportTable) to MyFile.csv with
CSV HEADER;';
Execute strSQL;
Return row_count;
This returns a null.
Any way to do this?
Thanks in advance,
James
[SQL] returning the number of rows output by a copy command from a function
I meant the count from the same query as for copy command what actually go
to file... Not count rows from table...
But i agree could be slow...
Cheers,
Misa
On Wednesday, January 16, 2013, James Sharrett wrote:
The # rows in the table # rows in the file because the table is grouped
and aggregated so simple table row count wouldn't be accurate. The table
can run in the 75M - 100M range so I was trying to avoid running all the
aggregations once to output the file and then run the same code again just
to get a count.
On 1/16/13 11:36 AM, Rob Sargent robjsarg...@gmail.com wrote:
On 01/16/2013 09:30 AM, James Sharrett wrote:
I have a function that generates a table of records and then a SQL
statement that does a COPY into a text file. I want to return the
number of records output into the text file from my function. The
number of rows in the table is not necessarily the number of rows in the
file due to summarization of data in the table on the way out. Here is
a very shortened version of what I'm doing:
CREATE OR REPLACE FUNCTION export_data(list of parameters)
RETURNS integer AS
$BODY$
declare
My variables
Begin
{ A lot of SQL to build and populate the table of records to export}
strSQL := 'copy (select MyColumns from MyExportTable) to MyFile.csv with
CSV HEADER;';
Execute strSQL;
Return 0;
end
$BODY$
LANGUAGE plpgsql VOLATILE
strSQL gets dynamically generated so it's not a static statement.
This all works exactly as I want. But when I try to get the row count
back out I cannot get it. I've tried the following:
1.
strSQL := 'copy (select MyColumns from MyExportTable) to MyFile.csv with
CSV HEADER;';
Execute strSQL into export_count;
Return export_count;
This give me an error saying that I've tried to use the INTO statement
with a command that doesn't return data.
2.
strSQL := 'copy (select MyColumns from MyExportTable) to MyFile.csv with
CSV HEADER;';
Execute strSQL;
Get diagnostics export_count = row_count;
This always returns zero.
3.
strSQL := 'copy (select MyColumns from MyExportTable) to MyFile.csv with
CSV HEADER;';
Execute strSQL;
Return row_count;
This returns a null.
Any way to do this?
Thanks in advance,
James
declare export_count int;
select count(*) from export_table into export_count();
raise notice 'Exported % rows', export_count;
--
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] returning the number of rows output by a copy command from a function
The other option would be to create temp table...
Execute dynamic sql to fil temp table
Copy from temp table - copy will return number of rowsx (not inside
execute...)
Drop temp
Kind regards,
Misa
On Wednesday, January 16, 2013, Misa Simic wrote:
I meant the count from the same query as for copy command what actually go
to file... Not count rows from table...
But i agree could be slow...
Cheers,
Misa
On Wednesday, January 16, 2013, James Sharrett wrote:
The # rows in the table # rows in the file because the table is grouped
and aggregated so simple table row count wouldn't be accurate. The table
can run in the 75M - 100M range so I was trying to avoid running all the
aggregations once to output the file and then run the same code again just
to get a count.
On 1/16/13 11:36 AM, Rob Sargent robjsarg...@gmail.com wrote:
On 01/16/2013 09:30 AM, James Sharrett wrote:
I have a function that generates a table of records and then a SQL
statement that does a COPY into a text file. I want to return the
number of records output into the text file from my function. The
number of rows in the table is not necessarily the number of rows in
the
file due to summarization of data in the table on the way out. Here is
a very shortened version of what I'm doing:
CREATE OR REPLACE FUNCTION export_data(list of parameters)
RETURNS integer AS
$BODY$
declare
My variables
Begin
{ A lot of SQL to build and populate the table of records to export}
strSQL := 'copy (select MyColumns from MyExportTable) to MyFile.csv
with
CSV HEADER;';
Execute strSQL;
Return 0;
end
$BODY$
LANGUAGE plpgsql VOLATILE
strSQL gets dynamically generated so it's not a static statement.
This all works exactly as I want. But when I try to get the row count
back out I cannot get it. I've tried the following:
1.
strSQL := 'copy (select MyColumns from MyExportTable) to MyFile.csv
with
CSV HEADER;';
Execute strSQL into export_count;
Return export_count;
This give me an error saying that I've tried to use the INTO statement
with a command that doesn't return data.
2.
strSQL := 'copy (select MyColumns from MyExportTable) to MyFile.csv
with
CSV HEADER;';
Execute strSQL;
Get diagnostics export_count = row_count;
This always returns zero.
3.
strSQL := 'copy (select MyColumns from MyExportTable) to MyFile.csv
with
CSV HEADER;';
Execute strSQL;
Return row_count;
This returns a null.
Any way to do this?
Thanks in advance,
James
declare export_count int;
select count(*) from export_table into export_count();
raise notice 'Exported % rows', export_count;
--
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] SELECT 1st field
When you select from function I think column name is the same as function name. So if function name is func query would be: SELECT func AS id FROM func(5); Sent from my Windows Phone From: Jan Bakuwel Sent: 15/05/2012 08:02 To: pgsql-sql@postgresql.org Subject: [SQL] SELECT 1st field Hi, I've spend some time checking the documentation but haven't been able to find what I'm looking for. I've got a function that returns a set of integers and a view that selects from the function. What I need is the ability to name the column in the view, ie. create function func(i int) returns setof integer as $$ ... ...code ... $$ language plpythonu volatile; create view v as select 1 as id from func(5); In other words I'd like to refer to the first (and only) field returned and give that an alias, in this case id. In some SQL dialects you can use select 1 to select the first field, select 2 to select the 2nd field and so on. Any suggestions? regards, Jan -- 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 1st field
Both works fine: SELECT generate_series AS id FROM generate_series(1,5); and SELECT id FROM generate_series(1,5) AS foo(id); Technically dont know is there any differenece... Thanks, Misa 2012/5/15 Tom Lane t...@sss.pgh.pa.us Jan Bakuwel jan.baku...@greenpeace.org writes: What I need is the ability to name the column in the view, ie. create view v as select 1 as id from func(5); I think what you're looking for is the ability to re-alias a column name, for example select id from func(5) as foo(id); regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Call function with dynamic schema name
You could take a look on EXECUTE command in plpgsql... Depends on concrete problem but should be very careful with dynamic SQL because of SQL injection potential risk... Kind Regards, Misa Sent from my Windows Phone From: IlGenna Sent: 15/01/2012 18:29 To: pgsql-sql@postgresql.org Subject: [SQL] Call function with dynamic schema name Hi to everyone, I would like to use in my function (plpgsql or sql) dynamic schema name to execute query or to call other functions. For exemple in oracle is possible to excute query in this manner: SELECT * FROM SCHEMA_NAME..TABLE_NAME; Where I think SCHEMA_NAME. is a sessione variable. I found tath I can use dynamic SQL like this: execute 'select * from ' || schema_name || '.table_name'; However, I would like to know if exist any other system to use dynamic schema name more similiar to Oracle. Another pl language is also ok. Thank you very much. Alessio -- View this message in context: http://postgresql.1045698.n5.nabble.com/Call-function-with-dynamic-schema-name-tp5146721p5146721.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 -- 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] sql query problem
It seems question is not clear... I could not determine what should be in column Attended, and based on what should define passed/failed But quick tip would be SELECT name, CASE status WHEN 1 THEN 'Passed' ELSE 'Failed' END FROM UserTable INNER JOIN result ON UserTable.id = result.user_id Sent from my Windows Phone From: Alok Thakur Sent: 15/01/2012 22:08 To: pgsql-sql@postgresql.org Subject: [SQL] sql query problem Dear All, I have two tables one contains details of user and other contains result. The details are: 1. UserTable - id, name, phone 2. result - id, question_id, user_id, status (0 or 1) I want the list like this: User Id Name Attended Failed Passed but i could not find the way to do this. Please help -- 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] ignore unique violation OR check row exists
Well, idea is to make process faster as possible... And usualy staging table does not have any constrains so can't violates... When we want to import banch of data... Process when we taking row by row from source, validate it, if valid insert to some table could be very slow... Much faster is when we work with sets.. •import all records to some table without constrains (staging table). And best would be if we can use COPY command instead of insert... •inert into liveTable select all valid records from stagingTable Of course it is just in case when we want to import what is ok... In case all or nothing - import direct to liveTable works fine... Sent from my Windows Phone From: Jasen Betts Sent: 04/01/2012 10:02 To: pgsql-sql@postgresql.org Subject: Re: [SQL] ignore unique violation OR check row exists On 2012-01-03, Misa Simic misa.si...@gmail.com wrote: If exists is better, though insert line by line and check if exists may be very slow... The best would be if you can use copy command from csv to staging table (without constraints) and then Insert to live from stage where stage constraint column not exist in live... Its a good idea to check that the staging table doesn't conflict with itself, before tryign to insert it. -- ⚂⚃ 100% natural -- 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] ignore unique violation OR check row exists
If exists is better, though insert line by line and check if exists may be very slow... The best would be if you can use copy command from csv to staging table (without constraints) and then Insert to live from stage where stage constraint column not exist in live... Kind Regards, Misa Sent from my Windows Phone From: rverghese Sent: 03/01/2012 21:55 To: pgsql-sql@postgresql.org Subject: [SQL] ignore unique violation OR check row exists I want to insert a bunch of records and not do anything if the record already exists. So the 2 options I considered are 1) check if row exists or insert and 2) ignore the unique violation on insert if row exists. Any opinions on whether it is faster to INSERT and then catch the UNIQUE VIOLATION exception and ignore it in plpgsql versus check if row exists and INSERT if it doesn't. I can't seem to ignore the UNIQUE VIOLATION exception via php, since it is a plpgsql command, so if I have to do the check and insert, alternatively i have a function that tries to insert and then ignores the violation. I was wondering if one way was better than the other. Thanks -- View this message in context: http://postgresql.1045698.n5.nabble.com/ignore-unique-violation-OR-check-row-exists-tp5117916p5117916.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 -- 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
Quite a few other RDBMS will give you the error but will also allow you to continue on your merry way (and not loose everything you've done up to that point). Why-o-why have the PostgreSQL developers decided to do it this way...? I don't know these other rdbms, but it sounds strange to have transaction and not rollback if something is wrong... Option in db i don't think is good generic solution because that business rule is dynamic... In some case user wants to import everything what is ok.. And then manually fix errors, but in some not simply they want all or nothing... so manually entering and import are two different processes... Our solution for partial import case is to import all data to staging table without constraint... Validate data... Import valid... Not valid show to user so they can fix what is wrong etc... Kind Regards, Misa Sent from my Windows Phone From: Jan Bakuwel Sent: 30/12/2011 23:52 To: pgsql-sql@postgresql.org Subject: Re: [SQL] Current transaction is aborted, commands ignored until end of transaction block Hi Leif, On 30/12/11 22:44, Leif Biberg Kristensen wrote: Fredag 30. desember 2011 09.43.38 skrev Jan Bakuwel : Would be nice to have an option in PostgreSQL something along the lines of: 'abort-transaction-on-constraint-violation = false' That option is called MySQL with MyISAM tables. Seriously, if the user encounters a constraint violation, that is IMO a symptom of bad design. Such conditions should be checked and caught _before_ the transaction begins. Really? One of my detail tables here is a list of codes. The design currently is so that you are not allowed to add two identical codes in that table for a particular related master record, ie. if you try it raises a constraint violation (duplicate key). Users try anyway (you know those pesky users doing things they're not supposed to do). Why would that a bad design? I simply want to tell the user: sorry you can't do this because it violates a constraint (duplicate key). Sometimes they try to delete something that has other records referring to it and the database design is so that it won't cascade delete (for various reasons). In that case I want to tell them: sorry you can't do this because there are related records. In a well designed system, you'd have those constraints at the database level not the application level and use exception handling to deal with these, not write tests to find out the possible error conditions beforehand. Of course it's possible to write code around all of this (and I'm starting to realise that is what I might have to do) but I consider that bad design. I don't claim to know all other RDBMS but I think PostgreSQL might be one of the few (or only one) that considers a constraint violation something really really really serious... so serious that the transaction will have to be aborted. Quite a few other RDBMS will give you the error but will also allow you to continue on your merry way (and not loose everything you've done up to that point). Why-o-why have the PostgreSQL developers decided to do it this way...? regards, Jan -- 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
It is not totally clear to me what are u trying to do... But in second
query it seems there is missing from
It is as
SELECT week-date::date AS week-date WHERE week-date in (subquery which
have from)
So week-date column in main query does not exist..
Sent from my Windows Phone From: John Fabiani
Sent: 16 December 2011 05:16
To: pgsql-sql@postgresql.org
Subject: [SQL] using a generated series in function
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
--
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] using a generated series in function
Hi John,
Well, maybe the best would be to say on english what you want to achieve...
From SQL code in your mail - it is not clear ( at least to me...)
but: SELECT now() - it will just execute function ant there is not possible
to say WHERE in that...
and like you said:
*select function_name(integer, date); -- function returns a numeric*
*
*
it works - there is no place for WHERE...
If the query have WHERE - then it also at leasy must have FROM clausule...
Kind Regards,
Misa
2011/12/16 John Fabiani jo...@jfcomputer.com
Actually what would the from be - this could be a newbie issue here?
Neither statement requires a from because neither of the statements uses
a
table - I think! I'll try to add one but the first part is a function
like a
any other function. What is the from when you do:
select now() - really I don't know!
The second part is tricky because I don't really understand it. Howerver,
I
have used it several times (got it off the web somewhere) but only in a
for
loop. If I just run it by it's self it generates a table of dates.
Therefore, I have always thought of it as a function. Again, like select
now()
So I know this must sound like I'm sort of idiot - just never considered
the
second half (the part that provides the dates) anything other than a
postgres
function.
Johnf
On Friday, December 16, 2011 01:30:53 AM Misa Simic wrote:
It is not totally clear to me what are u trying to do... But in second
query it seems there is missing from
It is as
SELECT week-date::date AS week-date WHERE week-date in (subquery which
have from)
So week-date column in main query does not exist..
Sent from my Windows Phone From: John Fabiani
Sent: 16 December 2011 05:16
To: pgsql-sql@postgresql.org
Subject: [SQL] using a generated series in function
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
--
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
That is good - that you solved it...
Well - in from it does not need to be just from table...
it needs to be some set of rows... is it Table or function (SELECT i FROM
generate_series(0, 84, 7) i - is actually from function...) or from View...
SELECT * FROM (SELECT * FROM Table1) as InlineView
Also works becouse of subquery also returns some set of rows...
Kind Regrads,
Misa
2011/12/16 John Fabiani jo...@jfcomputer.com
I have solved my problem. But this still does not explain the idea of
from
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!
Johnf
On Friday, December 16, 2011 02:46:18 AM John Fabiani wrote:
Actually what would the from be - this could be a newbie issue here?
Neither statement requires a from because neither of the statements
uses a
table - I think! I'll try to add one but the first part is a function
like
a any other function. What is the from when you do:
select now() - really I don't know!
The second part is tricky because I don't really understand it.
Howerver, I
have used it several times (got it off the web somewhere) but only in a
for loop. If I just run it by it's self it generates a table of dates.
Therefore, I have always thought of it as a function. Again, like
select
now()
So I know this must sound like I'm sort of idiot - just never considered
the
second half (the part that provides the dates) anything other than a
postgres function.
Johnf
On Friday, December 16, 2011 01:30:53 AM Misa Simic wrote:
It is not totally clear to me what are u trying to do... But in second
query it seems there is missing from
It is as
SELECT week-date::date AS week-date WHERE week-date in (subquery which
have from)
So week-date column in main query does not exist..
Sent from my Windows Phone From: John Fabiani
Sent: 16 December 2011 05:16
To: pgsql-sql@postgresql.org
Subject: [SQL] using a generated series in function
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
--
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] Subselects to Joins? Or: how to design phone calls database
I think its definitely better to split phone number in calls table on 2
or even 3 parts... (Country prefix, carrier/area prefix, number)
Though maybe better design would be 3th table with full number as pk:
PhoneNumbers (number, country prefix, optionally carrier/area prefix,
rest of number)
Then you can join calls to phonenumbers on full number string then join
countries on country prefix...
Kind Regards,
Misa
Sent from my Windows Phone From: Mario Splivalo
Sent: 10 December 2011 23:27
To: pgsql-sql@postgresql.org
Subject: [SQL] Subselects to Joins? Or: how to design phone calls
database
I have a table called 'calls' which holds 'call detail records'. Let's
assume the table looks like this:
CREATE TABLE cdr (
call_id serial,
phone_number text
);
And I have a table with country call prefixes, that looks like this:
CREATE TABLE prefixes (
prefix text,
country text
);
And now some test data:
INSERT INTO prefixes VALUES ('1', 'USA');
INSERT INTO prefixes VALUES ('44', 'UK');
INSERT INTO prefixes VALUES ('385', 'Croatia');
INSERT INTO prefixes VALUES ('387', 'Bosnia');
INSERT INTO prefixes VALUES ('64', 'New Zeland');
INSERT INTO prefixes VALUES ('642', 'New Zeland Mobile');
INSERT INTO calls VALUES (1, '11952134451');
INSERT INTO calls VALUES (2, '448789921342');
INSERT INTO calls VALUES (3, '385914242232');
INSERT INTO calls VALUES (4, '385914242232');
INSERT INTO calls VALUES (5, '645122231241');
INSERT INTO calls VALUES (6, '444122523421');
INSERT INTO calls VALUES (7, '64212125452');
INSERT INTO calls VALUES (8, '1837371211');
INSERT INTO calls VALUES (9, '11952134451');
INSERT INTO calls VALUES (10, '448789921342');
INSERT INTO calls VALUES (11, '385914242232');
INSERT INTO calls VALUES (12, '385914242232');
INSERT INTO calls VALUES (13, '645122231241');
INSERT INTO calls VALUES (14, '4441232523421');
INSERT INTO calls VALUES (15, '64112125452');
INSERT INTO calls VALUES (16, '1837371211');
Now, if I want to have a 'join' between those two tables, here is what I
am doing right now:
SELECT
call_id,
phone_number,
(SELECT
country
FROM
prefixes
WHERE
calls.phone_number LIKE prefix || '%'
ORDER BY
length(prefix) DESC LIMIT 1
) AS country
FROM calls;
Is there a way I could use join here? I can do something like:
SELECT ... FROM calls JOIN prefixes ON calls.phone_number LIKE prefix || '%'
but I'd get duplicate rows there (for instance, for New Zeland calls,
from my test data).
Or should I add 'prefix' field to the calls table, and then do a inner
join with prefixes table?
Mario
--
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
