[SQL] How do I concatenate row-wise instead of column-wise?
I have a table like this: SELECT * FROM old_tab; id|descr --- 1|aaa 1|aaa 1|bbb 2|ccc 2|bbb 3|ddd 3|ddd 3|eee 3|fff 4|bbb etc... And I want a new table where the descr is concatenated row-wise like this: SELECT * FROM new_tab; id|descr -- 1|aaa;bbb 2|ccc;bbb 3|ddd;eee;fff 4|bbb etc... This is the closest I get: UPDATE new_tab SET descr = old_tab.descr||' ; '||old_tab.descr from old_tab where old_tab.id=new_tab.id; UPDATE 4 SELECT * FROM new_tab ; id | descr +--- 1 | aaa ; aaa 2 | ccc ; ccc 3 | ddd ; ddd 4 | bbb ; bbb etc... Thus, the concatenating operator never works on other rows than the present. How can I get around that and still stick to the postgresql syntax? Regards Marcus ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] [HACKERS] please help on query
- Original Message - From: "Masaru Sugawara" <[EMAIL PROTECTED]> To: "Luis Alberto Amigo Navarro" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Monday, July 15, 2002 6:15 PM Subject: Re: [SQL] [HACKERS] please help on query > > Sorry, I don't know the reason. > I need more info. Can you show me the outputs of EXPLAIN ANALYZE ? > Here it is: > > EXPLAIN ANALYZE > SELECT > orders.orderkey > FROM > lineitem LEFT OUTER JOIN > orders USING(orderkey) > WHERE > orders.orderkey IS NOT NULL > GROUP BY > orders.orderkey > HAVING > SUM(lineitem.quantity) > 300; > Aggregate (cost=1257368.92..1287375.04 rows=600122 width=12) (actual time=1236941.71..1454824.56 rows=62 loops=1) -> Group (cost=1257368.92..1272371.98 rows=6001225 width=12) (actual time=1233968.87..1385034.91 rows=6001225 loops=1) -> Sort (cost=1257368.92..1257368.92 rows=6001225 width=12) (actual time=1233968.82..1276147.37 rows=6001225 loops=1) -> Hash Join (cost=166395.00..520604.08 rows=6001225 width=12) (actual time=59061.21..773997.08 rows=6001225 loops=1) -> Seq Scan on lineitem (cost=0.00..195405.25 rows=6001225 width=8) (actual time=20.66..115511.34 rows=6001225 loops=1) -> Hash (cost=162645.00..162645.00 rows=150 width=4) (actual time=59032.16..59032.16 rows=0 loops=1) -> Seq Scan on orders (cost=0.00..162645.00 rows=150 width=4) (actual time=17.33..44420.10 rows=150 loops=1) Total runtime: 1454929.11 msec > > > EXPLAIN ANALYZE > SELECT > t2.* > FROM (SELECT > orders.orderkey > FROM >lineitem LEFT OUTER JOIN >orders USING(orderkey) > WHERE >orders.orderkey IS NOT NULL > GROUP BY >orders.orderkey > HAVING > SUM(lineitem.quantity) > 300 >) AS t1 LEFT OUTER JOIN >orders AS t2 USING(orderkey) > ORDER BY t2.custkey > Sort (cost=1739666.43..1739666.43 rows=600122 width=119) (actual time=1538897.23..1538897.47 rows=62 loops=1) -> Merge Join (cost=1344971.49..1682069.98 rows=600122 width=119) (actual time=1440886.58..1538886.03 rows=62 loops=1) -> Index Scan using orders_pkey on orders t2 (cost=0.00..324346.65 rows=150 width=115) (actual time=32.80..87906.98 rows=1455276 loops=1) -> Sort (cost=1344971.49..1344971.49 rows=600122 width=12) (actual time=1439550.31..1439550.73 rows=62 loops=1) -> Subquery Scan t1 (cost=1257368.92..1287375.04 rows=600122 width=12) (actual time=1222560.86..1439549.36 rows=62 loops=1) -> Aggregate (cost=1257368.92..1287375.04 rows=600122 width=12) (actual time=1222560.84..1439548.42 rows=62 loops=1) -> Group (cost=1257368.92..1272371.98 rows=6001225 width=12) (actual time=1219607.04..1369327.42 rows=6001225 loops=1) -> Sort (cost=1257368.92..1257368.92 rows=6001225 width=12) (actual time=1219607.00..1261208.08 rows=6001225 loops=1) -> Hash Join (cost=166395.00..520604.08 rows=6001225 width=12) (actual time=65973.31..769253.41 rows=6001225 loops=1) -> Seq Scan on lineitem (cost=0.00..195405.25 rows=6001225 width=8) (actual time=20.07..115247.61 rows=6001225 loops=1) -> Hash (cost=162645.00..162645.00 rows=150 width=4) (actual time=65943.80..65943.80 rows=0 loops=1) -> Seq Scan on orders (cost=0.00..162645.00 rows=150 width=4) (actual time=39.04..52049.90 rows=150 loops=1) Total runtime: 1539010.00 msec Thanks and regards ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] How do I concatenate row-wise instead of column-wise?
On Tuesday 16 Jul 2002 9:09 am, Marcus Claesson wrote: > I have a table like this: > SELECT * FROM old_tab; > id|descr > --- > 3|ddd > 3|ddd > 3|eee > 3|fff > SELECT * FROM new_tab; > id|descr > -- > 1|aaa;bbb > 2|ccc;bbb > 3|ddd;eee;fff > 4|bbb You'll want to write your own aggregate function - something like max() which will work over a range of values. This is easier than you might think. The only thing to be careful of is that order isn't guaranteed, so by default you could have "ddd;eee;fff" or "fff;ddd;eee" etc. Go to techdocs.postgresql.org and check the "Postgresql Cookbook" courtesy of Roberto Mello and also the "Postgresql Notes" by me. Also check the online manual and the mailing archives (try searching on "aggregate" and "catenate" or "concat"). HTH - Richard Huxton ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[SQL] Cascading deletions does not seem to work inside PL/PGSQL functions.
Hi folks, This problem has been troubling me for quite sometime and I would be very thankful for your help. I have included the complete commented script to recreate the problem in question. The problem is inside a plpgsql function i do not see the records in the slave tables getting deleted when i delete the corresponing referenced record from the master table. But things as expected inside a Transaction at the PSQL prompt. any help is very much appreciated. regds mallah. stuff in test_case.sql --- -- W A R N I N G --- scripts will delete tables t_master and t_slave and a function t_test() --- DROP TABLE t_master; --clean up stuff first. DROP TABLE t_slave; CREATE TABLE t_master (id integer primary key); CREATE TABLE t_slave (id integer references t_master on delete cascade on update cascade unique ); INSERT INTO t_master values (1); INSERT INTO t_slave values (1); -- Demonstrate that record in salve table do get -- deleted when the master record is deleted. BEGIN work; delete from t_master where id=1; select id from t_slave where id=1; -- <-- this selects returns no record. ROLLBACK; -- Same stuff tried inside a PL/PGSQL function... DROP FUNCTION t_test(); CREATE OR REPLACE FUNCTION t_test () RETURNS integer AS ' DECLARE rec RECORD; BEGIN DELETE FROM t_master where id=1; SELECT INTO rec id from t_slave where id=1; -- <-- this selects returns record. RAISE NOTICE ''id in slave table is %'' , rec.id ; RETURN 1; END; ' LANGUAGE 'plpgsql' ; select t_test(); --- the end --- -- Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace. -- W A R N I N G --- scripts will delete tables t_master and t_slave and a function t_test() --- DROP TABLE t_master; --clean up stuff first. DROP TABLE t_slave; CREATE TABLE t_master (id integer primary key); CREATE TABLE t_slave (id integer references t_master on delete cascade on update cascade unique ); INSERT INTO t_master values (1); INSERT INTO t_slave values (1); -- Demonstrate that record in salve table do get -- deleted when the master record is deleted. BEGIN work; delete from t_master where id=1; select id from t_slave where id=1; -- <-- this selects returns no record. ROLLBACK; -- Same stuff tried inside a PL/PGSQL function... DROP FUNCTION t_test(); CREATE OR REPLACE FUNCTION t_test () RETURNS integer AS ' DECLARE rec RECORD; BEGIN DELETE FROM t_master where id=1; SELECT INTO rec id from t_slave where id=1; -- <-- this selects returns record. RAISE NOTICE ''id in slave table is %'' , rec.id ; RETURN 1; END; ' LANGUAGE 'plpgsql' ; select t_test(); ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] How do I concatenate row-wise instead of column-wise?
Hi Marcus, It is simple , you need to write a function and define an aggregate using that function. in case you have already searched for the solution and not found here it is from this mailing list only: === Date: Tue, 14 May 2002 18:13:09 +0200 From: Mathieu Arnold <[EMAIL PROTECTED]> To: [EMAIL PROTECTED] Subject: [SQL] aggregate... Message-ID: <[EMAIL PROTECTED]> X-Mailer: Mulberry/2.2.1 (Win32) X-wazaaa: True, true MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline Precedence: bulk Sender: [EMAIL PROTECTED] Status: RO X-Status: O Hi I have this : CREATE FUNCTION "comma_cat" (text,text) RETURNS text AS ' SELECT CASE WHEN $2 IS NULL OR $2 = THEN $1 WHEN $1 IS NULL OR $1 = THEN $2 ELSE $1 || '', '' || $2 END ' LANGUAGE 'sql'; CREATE AGGREGATE list ( BASETYPE = text, SFUNC = comma_cat, STYPE = text, INITCOND = '' ); I can use it as : select user, list(email) from user join email using (id_user); user | list ---+- mat| [EMAIL PROTECTED], [EMAIL PROTECTED] isa| [EMAIL PROTECTED] === regds On Tuesday 16 July 2002 13:39, you wrote: > I have a table like this: > SELECT * FROM old_tab; > id|descr > --- > 1|aaa > 1|aaa > 1|bbb > 2|ccc > 2|bbb > 3|ddd > 3|ddd > 3|eee > 3|fff > 4|bbb > etc... > > And I want a new table where the descr is concatenated row-wise like this: > SELECT * FROM new_tab; > id|descr > -- > 1|aaa;bbb > 2|ccc;bbb > 3|ddd;eee;fff > 4|bbb > etc... > > This is the closest I get: > UPDATE new_tab SET descr = old_tab.descr||' ; '||old_tab.descr from > old_tab where old_tab.id=new_tab.id; > UPDATE 4 > SELECT * FROM new_tab ; > id | descr > +--- > 1 | aaa ; aaa > 2 | ccc ; ccc > 3 | ddd ; ddd > 4 | bbb ; bbb > etc... > > Thus, the concatenating operator never works on other rows than the > present. How can I get around that and still stick to the postgresql > syntax? > > Regards > Marcus > > > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster -- Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Indexing UNIONs
On Mon, Jul 15, 2002 at 17:31:24 -0700, Josh Berkus <[EMAIL PROTECTED]> wrote: > Stephan, > > > We had a discussion recently on -general about this. Right now the > > planner won't push the conditions down into the arms of the union because > > noone's been sure under what conditions the optimization is safe. > > So, if performance is horrible with the view, I should use a dummy table to > hold the Unioned data and index that instead? It wouldn't have to be a dummy table. You could have both sets of data in the same table. Since they seem to be related enough that you went to the trouble to give them compatible primary keys this may not be inappropiate (though you must have had some reason for keeping them separate). You can use a flag to indicate what the data type is. If you need fast access to the smaller part of the table, a partial index might work. If the column that didn't apply to the one table is always not null in the other table, you could use is null on that column as your flag. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Cascading deletions does not seem to work inside PL/PGSQL
On Tue, 16 Jul 2002, Rajesh Kumar Mallah. wrote: > Hi folks, > > This problem has been troubling me for quite sometime and > I would be very thankful for your help. > > I have included the complete commented script to recreate the problem in > question. > > The problem is inside a plpgsql function i do not see the records in the slave tables > getting deleted when i delete the corresponing referenced record from the master >table. > > But things as expected inside a Transaction at the PSQL prompt. It should get deleted, but it won't be deleted until the end of the user's sql statement (ie, not until after the function has finished). ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Indexing UNIONs
Bruno, > It wouldn't have to be a dummy table. You could have both sets of > data > in the same table. Per my original e-mail, this is not an option. Basically, the two tables have nothing in commmon *except* that events can be scheduled against either table. Otherwise, the two tables have vastly different data, which comes from completely different sources, and is related to a totally different set of dependant tables. So, no go. I run into this sort of thing a lot. Is it just the way I design databases, or is there a need for a more sophisticated model of relationality for SQL03? -Josh ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] A SQL Training
Hi,
Today, one of my teacher asked me a question for a practice... I could not
solve it :)
Here it comes:
We are given the name of the grandfather in a family. Let's call him A. A
has n sons, each son has children .
for example.
A -> Grandfather
/ \
/ \
B C
/\ / \ \
/ \ D E F
G H
...
We are given the data like ('A','B'),('C','D')...
Now, we do not know how many children are in the list. How could we list
this family tree in "tree" formt by only using SQL?
Best regards.
--
Devrim GUNDUZ
[EMAIL PROTECTED]
[EMAIL PROTECTED]
Web : http://devrim.oper.metu.edu.tr
-
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
datatype matrix (was: Re: [SQL] Sorry..)
Josh Berkus wrote: > Christopher, > > >>In the bad old days when we couldn't distinguish explicit from implicit >>cast functions, I was wary of adding new cast pathways. Too many >>implicit casts and you have no type system at all. But in 7.3 there >>should be no reason to object to an explicit-only cast from numeric >>to text or vice versa. > > > I'd suggest making the explicit cast of numeric to text be the exact > equivalent of: > > SELECT btrim(to_char(numeric, '999,999,999,999.99')) > or similar. > In this vain, is there someplace in the docs that has a type conversion table (matrix) that shows what datatype can be cast into what other datatype (both implicitly and explicitly)? I haven't seen one and it would be helpful for us newbies. Thanks! Kevin ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] A SQL Training
Devrim, > Today, one of my teacher asked me a question for a practice... I could not > solve it :) > > Here it comes: > > We are given the name of the grandfather in a family. Let's call him A. A > has n sons, each son has children . etc. In a month or so, I will be posting an article on advanced tree structure (based on Joe Celko's work) in PostgreSQL. Until then, buy a copy of SQL for Smarties, 2nd ed. by Joe Celko. He has two chapters on tree structure. -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] Need help on a troublesome query plan
On PostgreSQL Version 7.2.1 on Redhat Linux 7.1 Table bld_tb_bus_fact definition Column | Type | Modifiers -+-- +--- bus_fact_id| bigint| not null bus_fact_ts| timestamp with time zone | not null party_id | bigint| svc_id| bigint| not null bus_fact_data | text | not null bus_fact_typ_cd | character(10)| not null bus_fact_kywrd | character varying(300) | cont_id| bigint | perfby_id | bigint | Index "bld_x1_tb_bus_fact" Column | Type -+--- party_id| bigint bus_fact_typ_cd | character(10) cont_id | bigint btree With the following query on 5 records: explain SELECT bld_TB_BUS_FACT.BUS_FACT_ID AS id FROMbld_TB_BUS_FACT WHERE bld_TB_BUS_FACT.PARTY_ID=1320677 AND bld_TB_BUS_FACT.BUS_FACT_TYP_CD='MSG_SENT' AND bld_TB_BUS_FACT.CONT_ID=786448 AND bld_TB_BUS_FACT.BUS_FACT_KYWRD ILIKE '%MT-ID=3407979%' AND bld_TB_BUS_FACT.BUS_FACT_KYWRD ILIKE '%S-ID=1310723%' limit 1; psql:test.sql:9: NOTICE: QUERY PLAN: Limit (cost=0.00..2264.16 rows=1 width=8) -> Seq Scan on bld_tb_bus_fact (cost=0.00..2264.16 rows=1 width=8) EXPLAIN I don't understand why it's not using the defined index, even after performing VACUUM FULL ANALYZE on the table. I tried disabling seqscan but that didn't change anything. I'm open to suggestions anyone Thanks! Alain Lavigne - Data Administrator - ZAQ Interactive Solutions E-Mail: [EMAIL PROTECTED] 297 St-Paul, West - Montreal, Quebec, Canada - H2Y 2A5 Phone: 514-282-7073 ext: 371 - Fax: 514-282-8011 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Need help on a troublesome query plan
On Tue, 16 Jul 2002, Alain Lavigne wrote: > Index "bld_x1_tb_bus_fact" > Column | Type > -+--- > party_id| bigint > bus_fact_typ_cd | character(10) > cont_id | bigint > btree > > With the following query on 5 records: > > explain > SELECT bld_TB_BUS_FACT.BUS_FACT_ID AS id > FROMbld_TB_BUS_FACT > WHERE bld_TB_BUS_FACT.PARTY_ID=1320677 > AND bld_TB_BUS_FACT.BUS_FACT_TYP_CD='MSG_SENT' > AND bld_TB_BUS_FACT.CONT_ID=786448 > AND bld_TB_BUS_FACT.BUS_FACT_KYWRD ILIKE '%MT-ID=3407979%' > AND bld_TB_BUS_FACT.BUS_FACT_KYWRD ILIKE '%S-ID=1310723%' > limit 1; You'll need to either single quote or explicitly cast the constants you're comparing to the bigint columns. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Indexing UNIONs
On Tue, Jul 16, 2002 at 09:36:31 -0700, Josh Berkus <[EMAIL PROTECTED]> wrote: > Bruno, > > > It wouldn't have to be a dummy table. You could have both sets of > > data > > in the same table. > > Per my original e-mail, this is not an option. > > Basically, the two tables have nothing in commmon *except* that events > can be scheduled against either table. Otherwise, the two tables have > vastly different data, which comes from completely different sources, > and is related to a totally different set of dependant tables. > > So, no go. > > I run into this sort of thing a lot. Is it just the way I design > databases, or is there a need for a more sophisticated model of > relationality for SQL03? This sounds like a design issue. This makes it seem like the events should be broken out into their own table and the other two tables should get joined with the events table when needed. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Need help on a troublesome query plan
On Tue, 16 Jul 2002, Alain Lavigne wrote: > Thanks that worked, but why does that happen or maybe you could point > to the proper thread so I read up on it. It's an issue that the numeric constant gets cast to int4 somewhat prematurely. I don't have a pointer to a thread off hand, but if you search the archives you should find a couple. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: datatype matrix (was: Re: [SQL] Sorry..)
Kevin, > In this vain, is there someplace in the docs that has a type conversion > table (matrix) that shows what datatype can be cast into what other > datatype (both implicitly and explicitly)? I haven't seen one and it > would be helpful for us newbies. I don't think it exists. Hey, how about a volunteer to write one? -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Indexing UNIONs
Bruno, > This sounds like a design issue. This makes it seem like the events > should be broken out into their own table and the other two tables > should get joined with the events table when needed. > OK, I guess I'll have to get into detail: Table "cases" is the database's third largest table, with 100,000 records, plus three dependant tables and 19 attributes (fields). Table "trial groups" is a small table listing a few dozen "cases" which are aggregated for settlement bargaining. Thus, each "trial group" relates to one to many "cases". Beyond this relationship, trial groups has only 5 attributes and 2 dependant tables. Table "events", the largest table in the database, contains event schedule listing with 11 attributes and one dependant table as well as recursive relationships between events. Each event record can be (and Must be) related to either one Case or one Trial Group. Thus, I need to relate (in views and queries) each Event to the Union of Cases and Trial Groups. I just can't figure out how to do so without the database discarding the indexes on Cases in the process and things slowing to a crawl. -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[SQL] Newbie: Creative use of LIMIT??
Hello all, Being pretty new at SQL, I have a problem I don't know how to solve. Assuming I have the following two tables of people and their nicknames, and that I want to create a list containing UPTO 2 (or any value greater than 1) nicknames for EACH member of a specific family (Fam column), how would I do this? I know how to get ALL of the nicknames for the family: SELECT "MEMBERS"."Name", "NICKS"."Nickname" FROM "NICKS", (SELECT "PEOPLE"."ID", "PEOPLE"."Name" FROM "PEOPLE" WHERE "PEOPLE"."Fam" = 1) AS "MEMBERS" WHERE "NICKS"."ID" = "MEMBERS"."ID" ORDER BY "MEMBERS"."Name" but am totally clueless on how to impose a limit on the number of rows to get for each individual family MEMBER. Can it be done in a single query? I sure hope so. Any help? Shmuel A. Kahn PEOPLE Fam |ID | Name | Yada1 Yada2 -- 1 | 1 | George . 1 | 2 | Sally . 1 | 3 | Joe . 1 | 4 | Barbara . 2 | 5 | DarthVader . . . NICKS ID | Nickname -- 1 | Pops 1 | BigChief 1 | Honcho 2 | Mums 2 | YesMother 3 | JoeJoe 3 | Bumpkin 3 | Jug 4 | Barb . . -- You're just jealous because the voices only talk to me. Shmuel A. Kahn [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[SQL] Sequence name length
Hi,
Just a quick one on the length of the name of a sequence.
I have some table with long(ish) names like : eselect_maincategory
The primary key is named : maincat_id
When I create the table the sequence sql looks like
nextval('"eselect_maincategory_maincat_id_seq"'::text)
However the sequence created is named :
eselect_maincategory_maincat_id ( the '_seq' is lopped off )
This looke like a max of 31 characters.
Is a sequence name length limited to 31 characters or is it the tool I'm
using - phpPgAdmin, I wonder ?
Your time and thoughts are very much appreciated.
Thank you.
Rudi Starcevic.
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Sequence name length
Hi Rudi,
Unless you redefine it before compiling, postgres has a built-in limit of 31
characters for names. Increasing this has a performance penalty, but it
might happen for 7.3 due to some improvements in the performance area.
BTW, the best way to do a sequence primary key is lik ethis:
create table blah (
maincat_id SERIAL
);
Chris
> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED]]On Behalf Of Rudi Starcevic
> Sent: Wednesday, 17 July 2002 10:39 AM
> To: [EMAIL PROTECTED]
> Subject: [SQL] Sequence name length
>
>
> Hi,
>
> Just a quick one on the length of the name of a sequence.
>
> I have some table with long(ish) names like : eselect_maincategory
> The primary key is named : maincat_id
>
> When I create the table the sequence sql looks like
> nextval('"eselect_maincategory_maincat_id_seq"'::text)
>
> However the sequence created is named :
> eselect_maincategory_maincat_id ( the '_seq' is lopped off )
>
> This looke like a max of 31 characters.
>
> Is a sequence name length limited to 31 characters or is it the tool I'm
> using - phpPgAdmin, I wonder ?
>
> Your time and thoughts are very much appreciated.
> Thank you.
> Rudi Starcevic.
>
>
>
> ---(end of broadcast)---
> TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
>
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Sequence name length
Rudi Starcevic <[EMAIL PROTECTED]> writes: > Just a quick one on the length of the name of a sequence. > ... > However the sequence created is named : > eselect_maincategory_maincat_id ( the '_seq' is lopped off ) What version are you running? Anything recent will keep the '_seq' and lop elsewhere. > This looke like a max of 31 characters. Yeah, the default NAMEDATALEN is 32, allowing for 31 chars plus a terminating null. You can build a private version with larger NAMEDATALEN, and there are recurring discussions about increasing the default length. regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Sequence name length
> > However the sequence created is named : > > eselect_maincategory_maincat_id ( the '_seq' is lopped off ) > > What version are you running? Anything recent will keep the '_seq' > and lop elsewhere. Not if he's manually creating a sequence name that's too long - it will just truncate it methinks... Chris ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Sequence name length
Hi Tom, Here is the output from 'select version()' PostgreSQL 7.2 on i686-pc-linux-gnu, compiled by GCC 2.95.2 Cheers Rudi. Tom Lane wrote: >Rudi Starcevic <[EMAIL PROTECTED]> writes: > > >>Just a quick one on the length of the name of a sequence. >>... >>However the sequence created is named : >>eselect_maincategory_maincat_id ( the '_seq' is lopped off ) >> >> > >What version are you running? Anything recent will keep the '_seq' >and lop elsewhere. > > > >>This looke like a max of 31 characters. >> >> > >Yeah, the default NAMEDATALEN is 32, allowing for 31 chars plus a >terminating null. You can build a private version with larger >NAMEDATALEN, and there are recurring discussions about increasing >the default length. > > regards, tom lane > > > ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] A SQL Training
On Tue, 16 Jul 2002, Devrim GUNDUZ wrote:
You need to identify your problem: preorder traversal on a tree
using pointers for the fathers.
Then you have to prove (or prove the opposite) that SQL alone
is capable of expressing what you need.
BTW, Nested Sets are only good for "static" databases.
Pointers are good for intensively "dynamic" databases, but have some
performance problems (for instance traversing to the root).
A genealogical approach (just like Oleg's and Teodor's one in
contrib/tree) seems the most attractive.
Also you can have your own setup using postgresql arrays
(The arrays could contain the path of ids of the nodes from
the specific node to the root).
All these probably have little to do with your problem
(which is a computational theory problem), but maybe useful
in other cases.
>
> Hi,
>
> Today, one of my teacher asked me a question for a practice... I could not
> solve it :)
>
> Here it comes:
>
> We are given the name of the grandfather in a family. Let's call him A. A
> has n sons, each son has children .
>
> for example.
> A -> Grandfather
>/ \
> / \
> B C
> /\ / \ \
>/ \ D E F
> G H
> ...
>
> We are given the data like ('A','B'),('C','D')...
>
> Now, we do not know how many children are in the list. How could we list
> this family tree in "tree" formt by only using SQL?
>
> Best regards.
>
>
>
>
>
>
--
Achilleus Mantzios
S/W Engineer
IT dept
Dynacom Tankers Mngmt
Nikis 4, Glyfada
Athens 16610
Greece
tel:+30-10-8981112
fax:+30-10-8981877
email: [EMAIL PROTECTED]
[EMAIL PROTECTED]
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Cascading deletions does not seem to work inside PL/PGSQL functions.
On Tuesday 16 July 2002 21:41, you wrote: > On Tue, 16 Jul 2002, Rajesh Kumar Mallah. wrote: > > Hi folks, > > > > This problem has been troubling me for quite sometime and > > I would be very thankful for your help. > > > > I have included the complete commented script to recreate the problem in > > question. > > > > The problem is inside a plpgsql function i do not see the records in the > > slave tables getting deleted when i delete the corresponing referenced > > record from the master table. > > > > But things as expected inside a Transaction at the PSQL prompt. > > It should get deleted, but it won't be deleted until the end of the > user's sql statement (ie, not until after the function has finished). Hi thanks for the reply, Is it a bug? or is it expected , what if i do not want to fire sperate delete SQLs for the slave tables ? regds mallah. -- Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Large objects and Meta Data
Hi, I'm handling large objects from JDBC in a servlet environment. Every time we create a lo we also create a row in the zp_file table that holds additional information about the file, as well as the oid of the large object: Column | Type | Modifiers -+--+--- lo_oid | oid | not null name| character varying(1024) | created | timestamp with time zone | default 'now' type| character varying(256) | Whenever I "attach" an object to a file I need to point an oid from that table to the row in the zp_file table which in turn points to the lo. For instance I have the following test_up table: Column | Type | Modifiers +---+--- first | character(20) | last | character(20) | pic| oid | So whenever I create a row in test_up that "includes" a file/blob/lo, I first create the lo, then create the zp_file row that contains the meta info about the large object, and finally point the oid of the pic field to the row in the zp_file table. I am planning on adding foreign keys from pic to zp_file(oid) and from lo_oid to pg_largeobject(oid). I've also put the zp_file and blob creation in a transaction for both creation and when I drop the large object. Since this feels quite complicated, I was wondering if I'm missing any obvious shortcuts or other ways of doing this. Thanks, Dror -- Dror Matalon Zapatec Inc 1700 MLK Way Berkeley, CA 94709 http://www.zapatec.com ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
