[SQL] Cloning hierarchical data
Hi,
let's assume i have a self referencing hierarchical table like this one:
CREATE TABLE test
(name text,id serial primary key,parent_id integer
references test)
insert into test (name,id,parent_id) values
('root1',1,NULL),('root2',2,NULL),('root1sub1',3,1),('root1sub2',4,1),('root
2sub1',5,2),('root2sub2',6,2)
testdb=# select * from test;
name| id | parent_id
---++---
root1 | 1 |
root2 | 2 |
root1sub1 | 3 | 1
root1sub2 | 4 | 1
root2sub1 | 5 | 2
root2sub2 | 6 | 2
What i need now is a function that would take the id of a test record and
clone all attached records (including the given one). The desired result
would like this for example:
Select * from cloningfunction(2);
name| id | parent_id
---++---
root2 | 7 |
root2sub1 | 8 | 7
root2sub2 | 9 | 7
Any pointers?
thanks!
Maximilian Tyrtania
--
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 default values
On 23/lug/08, at 11:28, Pavel Stehule wrote: Hello 2008/7/23 Maximilian Tyrtania <[EMAIL PROTECTED]>: Hi there, just a quickie: Is there a way to select all default values of a given table? Something like "Select Default values from sometable" ? Unfortunately this syntax doesn't seem to be supported. I know i can select the default values for each column, but being able to select them in one go would be handy... it's not possible directly, you can find expressions used as default in system tables or postgres=# create table f(a integer default 1, b integer); CREATE TABLE postgres=# insert into f(a,b) values(default, default) returning *; It seems that you can’t use the same syntax with function calls: select function(default,default); gives a syntax error. Is it expected? -- Giorgio Valoti -- 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 default values
Giorgio Valoti wrote: postgres=# insert into f(a,b) values(default, default) returning *; It seems that you can’t use the same syntax with function calls: select function(default,default); gives a syntax error. Is it expected? Um - there is no default value for a function. -- Richard Huxton Archonet Ltd -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] postgres time zone settings
Hai all, when i was going through the Postgresql documents, i came across a section which tells about the TIME ZONE settings what i understood from the document is, based on the value given in this conf file postgres will add or subtract the displacement hours to the system time assuming that the system time is the GMT. so there should be a configuration settings which will tell the postgres that the displacement is zero. How I can set the displacement is zero? Thanks in advance Anoop
Re: [SQL] truncate vs. delete
Emi Lu wrote: Thank you. I am quite sure that I will not use "delete" now. Now I a question about how efficient between (1) truncate a big table (with 200, 000) vacuum it (optional?) drop primary key load new data load primary key vacuum it (2) drop table (this table has no trigger, no foreign key) re-create table (without primary key) load new data setup primary key vacuum it suggestions PLEASE? Thanks a lot! Shouldn't be a noticeable difference either way. A quick test - postgres=# \timing Timing is on. postgres=# create table test (id serial primary key,data integer); NOTICE: CREATE TABLE will create implicit sequence "test_id_seq" for serial column "test.id" NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "test_pkey" for table "test" CREATE TABLE Time: 26.779 ms postgres=# insert into test (data) values (generate_series(1,20)); INSERT 0 20 Time: 4604.307 ms postgres=# truncate table test; TRUNCATE TABLE Time: 31.278 ms postgres=# insert into test (data) values (generate_series(1,20)); INSERT 0 20 Time: 4545.386 ms postgres=# drop table test; DROP TABLE Time: 45.261 ms postgres=# shows a 10ms difference between truncate and drop. -- Shane Ambler pgSQL (at) Sheeky (dot) Biz Get Sheeky @ http://Sheeky.Biz -- 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] truncate vs. delete
A. Kretschmer wrote: am Thu, dem 24.07.2008, um 10:01:46 -0400 mailte Emi Lu folgendes: A. Kretschmer wrote: am Thu, dem 24.07.2008, um 9:47:48 -0400 mailte Emi Lu folgendes: I found a link for SQL Server, it applies to PostgreSQL 8.0.x as well? http://vadivel.blogspot.com/2004/06/delete-vs-truncate-statement.html Not realy, for instance, pg can rollback a truncate, and a sequence are not reset. Thank you. I am quite sure that I will not use "delete" now. Now I a question about how efficient between (1) truncate a big table (with 200, 000) vacuum it (optional?) not required drop primary key load new data load primary ke vacuum it analyse it, instead vacuum. It gets more and more clear to me know! I guess I need only do analyze(primary key column) after loading data. The new picture will be: . truncate table . drop primary key . load data . set primary key . analyze interesting columns -- 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 time zone settings
On Thu, Jul 24, 2008 at 7:56 AM, Anoop G <[EMAIL PROTECTED]> wrote: > Hai all, > > when i was going through the Postgresql documents, i came across a section > which tells about the TIME ZONE settings > > > what i understood from the document is, based on the value given in this > conf file postgres will add or subtract the displacement hours to the system > time assuming that the system time is the GMT. > so there should be a configuration settings which will tell the postgres > that the displacement is zero. > How I can set the displacement is zero? > set timezone = 'GMT' (or set the guc in postgresql.conf) -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Guayaquil - Ecuador Cel. (593) 87171157 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] truncate vs. delete
Good morning, If I remember correctly, "delete" does not release space, while truncate will. I have an option now (1) Use object creator(with create/drop permission which I do not need in my cronjob script) to truncate table1(>100,000 recs) records (2) Use user1(has r/w only) to delete from table1, then vacuum it May I know how inefficient "delete from" comparing to truncate please? Thanks a lot! -- 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] truncate vs. delete
am Thu, dem 24.07.2008, um 9:47:48 -0400 mailte Emi Lu folgendes: > I found a link for SQL Server, it applies to PostgreSQL 8.0.x as well? > http://vadivel.blogspot.com/2004/06/delete-vs-truncate-statement.html Not realy, for instance, pg can rollback a truncate, and a sequence are not reset. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] truncate vs. delete
The truncate is more fast to delete, the truncate command not scan the table http://www.postgresql.org/docs/8.3/static/sql-truncate.html --- On Thu, 7/24/08, A. Kretschmer <[EMAIL PROTECTED]> wrote: > From: A. Kretschmer <[EMAIL PROTECTED]> > Subject: Re: [SQL] truncate vs. delete > To: pgsql-sql@postgresql.org > Date: Thursday, July 24, 2008, 1:53 PM > am Thu, dem 24.07.2008, um 9:47:48 -0400 mailte Emi Lu > folgendes: > > I found a link for SQL Server, it applies to > PostgreSQL 8.0.x as well? > > > http://vadivel.blogspot.com/2004/06/delete-vs-truncate-statement.html > > Not realy, for instance, pg can rollback a truncate, and a > sequence are > not reset. > > > Andreas > -- > Andreas Kretschmer > Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: > -> Header) > GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA > http://wwwkeys.de.pgp.net > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] truncate vs. delete
I found a link for SQL Server, it applies to PostgreSQL 8.0.x as well? http://vadivel.blogspot.com/2004/06/delete-vs-truncate-statement.html Emi Lu wrote: Good morning, If I remember correctly, "delete" does not release space, while truncate will. I have an option now (1) Use object creator(with create/drop permission which I do not need in my cronjob script) to truncate table1(>100,000 recs) records (2) Use user1(has r/w only) to delete from table1, then vacuum it May I know how inefficient "delete from" comparing to truncate please? Thanks a lot! -- 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] truncate vs. delete
am Thu, dem 24.07.2008, um 10:01:46 -0400 mailte Emi Lu folgendes: > A. Kretschmer wrote: > >am Thu, dem 24.07.2008, um 9:47:48 -0400 mailte Emi Lu folgendes: > >>I found a link for SQL Server, it applies to PostgreSQL 8.0.x as well? > >>http://vadivel.blogspot.com/2004/06/delete-vs-truncate-statement.html > > > >Not realy, for instance, pg can rollback a truncate, and a sequence are > >not reset. > > > > > Thank you. I am quite sure that I will not use "delete" now. > Now I a question about how efficient between > > (1) truncate a big table (with 200, 000) > vacuum it (optional?) not required > drop primary key > load new data > load primary ke > vacuum it analyse it, instead vacuum. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] truncate vs. delete
A. Kretschmer wrote: am Thu, dem 24.07.2008, um 9:47:48 -0400 mailte Emi Lu folgendes: I found a link for SQL Server, it applies to PostgreSQL 8.0.x as well? http://vadivel.blogspot.com/2004/06/delete-vs-truncate-statement.html Not realy, for instance, pg can rollback a truncate, and a sequence are not reset. Thank you. I am quite sure that I will not use "delete" now. Now I a question about how efficient between (1) truncate a big table (with 200, 000) vacuum it (optional?) drop primary key load new data load primary ke vacuum it (2) drop table (this table has no trigger, no foreign key) re-create table (without primary key) load new data setup primary key vacuum it suggestions PLEASE? Thanks a lot! -- 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] truncate vs. delete
n Thu, Jul 24, 2008 at 7:53 AM, A. Kretschmer <[EMAIL PROTECTED]> wrote: > am Thu, dem 24.07.2008, um 9:47:48 -0400 mailte Emi Lu folgendes: >> I found a link for SQL Server, it applies to PostgreSQL 8.0.x as well? >> http://vadivel.blogspot.com/2004/06/delete-vs-truncate-statement.html > > Not realy, for instance, pg can rollback a truncate, and a sequence are > not reset. Also you can truncate a table with foreign key references (using cascade), something SQL Server apparently can't do either. -- 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 default values
On 24/lug/08, at 12:42, Richard Huxton wrote: Giorgio Valoti wrote: postgres=# insert into f(a,b) values(default, default) returning *; It seems that you can’t use the same syntax with function calls: select function(default,default); gives a syntax error. Is it expected? Um - there is no default value for a function. Yes, but you could define a domain with a default value and using it as an IN argument for a function. In that case it would handy to be able to use the default value, wouldn’t it? Without this "feature" you have to overload the function arguments. -- Giorgio Valoti -- 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 default values
On Thu, Jul 24, 2008 at 12:35 PM, Giorgio Valoti <[EMAIL PROTECTED]> wrote: >> Um - there is no default value for a function. > > Without this "feature" you have to overload > the function arguments. You could pass a casted null to the function. The would eliminate function overloading. Then internally you could handle the null by passing DEFAULTS to you INSERT or UPDATE statements. I don't know if this would work for you in this case. -- Regards, Richard Broersma Jr. Visit the Los Angles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
