Re: [SQL] Correct implementation of 1:n relationship with n>0?

2013/4/30 Anton Gavazuk > 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 relationsh

Re: [SQL] Correct implementation of 1:n relationship with n>0?

2013/4/30 Wolfgang Keller > It hit me today that a 1:n relationship can't be implemented just by a > single foreign key constraint if n>0. 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 de

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.or

Re: [SQL] Using Ltree For Hierarchical Structures

2013/2/26 Thomas Kellerer > 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.ne

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 ch

Re: [SQL] Summing & Grouping in a Hierarchical Structure

le 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 wrote: > > Hi Don, > > Yes, its better to use it in category ta

Re: [SQL] Summing & Grouping in a Hierarchical Structure

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 tra

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 hiera

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

[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 > an

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,

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 > Jan Bakuwel writes: > > What I need is the ability to name the column in the vie

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

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

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]

Re: [SQL] ignore unique violation OR check row exists

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 wrote: > If exists is better, though insert

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 fr

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 tra

Re: [SQL] using a generated series in function

re) 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 i

Re: [SQL] using a generated series in function

s. > 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

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 Fabia

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

Re: [SQL] exclusion constraint for ranges of IP

Hi Herouth, I think you are right about exclusion... If you are getting 'string' I think then command would be: INSERT INTO customer_ip_range(cutomer_id, ip4r) VALUES('customeridstring', ip4r('iprangestring')) Kind Regards, Misa 2011/8/22 Herouth Maoz > > On 22/08/2011, at 01:19, Harald Fuc