Re: [SQL] Big query problem
>
> DELETE FROM table WHERE col1='something' AND col2 IN
> ('aasdoijhfoisdfsdoif','sdfsdfsdfsadfsdf', ... );
>
> In the parantheses I have 6400 names, each about 20 characters. I'm
> using libpq from C. This did not work very well, but the result was
> very unexpected.
>
The conditional operator IN is or at least was known to be slow.
Did you think of an alternative approach as
CREATE TABLE to_delete_col2 ( ref_col2 ... ) ;
populate it with 'aasdoijhfoisdfsdoif','sdfsdfsdfsadfsdf', ...
and then
DELETE FROM table WHERE col1='something' AND
EXISTS (SELECT ref_col2 FROM to_delete_col2 WHERE table.col2 =
to_delete_col2.ref_col2 );
> My application has several threads, each opening its own connection to
> the database. The above query was run in a transaction followed by a
> COMMIT. There was no error from running the above query, but instead,
> it seems that the query was never run at all. As a side effect, every
> other connection to the database always got:
>
> NOTICE: current transaction is aborted, queries ignored until end of
> transaction block
>
> when trying to run a query. I thought that the transactions in
> different connections didn't have anything to do with each other.
>
>
> If I limited the number of names in the failing query to 3200, it
> worked well and as expected.
>
>
> Is there a limit in libpq of the length of a query? And if this is
> exceeded, shouldn't PQexec() give an error?
I agree!
>
I think you should post this to the HACKERS list. Maybe you'll get
a reply from there.
Regards, Christoph
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
[SQL] master-detail relationship and count
Hi folks.
I've got a master detail relationship where I have a railway route table
listing landmarks along the route, and a Links table listing URL's
associated with that landmark. Listed below:
How can I do a query showing the landmark ID, the landmark name, and a count
of links associated with that landmark. Below is a SQL statement that
although is illegal, gives a good idea of what I'm looking for.
select r.rtid, r.rtname, l.count(*) from route r, links l where l.lktype = 'R'
and l.lklid = r.rtid;
nymr=# \d route
Table "route"
Attribute | Type | Modifier
+---+--
rtid | integer | not null default
nextval('route_rtid_seq'::text)
rtmile | integer | not null
rtyards| integer | not null
rtname | character varying(40) |
rtspeed| integer |
rtgradient | integer |
rtsection | integer |
rtphone| character(1) |
rtcomments | text |
Indices: route_index,
route_rtid_key
nymr=# select r.rtid, l.count(*) from route r, links l where
nymr=# \d links
Table "links"
Attribute | Type |Modifier
---+---+-
lkid | integer | not null default
nextval('staff_sid_seq'::text)
lkdesc| character varying(40) |
lkurl | character varying(40) |
lktype| character(1) |
lklid | integer |
Index: links_lkid_key
lktype indicates the link type - 'R' indicates a route entry
lklid indicates the link ID. For a 'R' it is the rtid of the route entry
--
Gary Stainburn
This email does not contain private or confidential material as it
may be snooped on by interested government parties for unknown
and undisclosed purposes - Regulation of Investigatory Powers Act, 2000
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] master-detail relationship and count
On Fri, 29 Nov 2002, Gary Stainburn wrote:
> Hi folks.
>
> I've got a master detail relationship where I have a railway route table
> listing landmarks along the route, and a Links table listing URL's
> associated with that landmark. Listed below:
>
> How can I do a query showing the landmark ID, the landmark name, and a count
> of links associated with that landmark. Below is a SQL statement that
> although is illegal, gives a good idea of what I'm looking for.
>
> select r.rtid, r.rtname, l.count(*) from route r, links l where l.lktype = 'R'
> and l.lklid = r.rtid;
select r.rtid,r.rtname,subsel.cnt from route r,
(select r2.rtid as rid,count(lnk.lkid) as cnt from route r2,links lnk
where lnk.type='R'
and lnk.lklid = r2.rtid group by r2.rtid) as subsel
where r.rtid = subsel.rid
or something like that.
>
> nymr=# \d route
> Table "route"
> Attribute | Type | Modifier
>
>+---+--
> rtid | integer | not null default
> nextval('route_rtid_seq'::text)
> rtmile | integer | not null
> rtyards| integer | not null
> rtname | character varying(40) |
> rtspeed| integer |
> rtgradient | integer |
> rtsection | integer |
> rtphone| character(1) |
> rtcomments | text |
> Indices: route_index,
> route_rtid_key
>
> nymr=# select r.rtid, l.count(*) from route r, links l where
> nymr=# \d links
> Table "links"
> Attribute | Type |Modifier
> ---+---+-
> lkid | integer | not null default
> nextval('staff_sid_seq'::text)
> lkdesc| character varying(40) |
> lkurl | character varying(40) |
> lktype| character(1) |
> lklid | integer |
> Index: links_lkid_key
>
> lktype indicates the link type - 'R' indicates a route entry
> lklid indicates the link ID. For a 'R' it is the rtid of the route entry
> --
> Gary Stainburn
>
> This email does not contain private or confidential material as it
> may be snooped on by interested government parties for unknown
> and undisclosed purposes - Regulation of Investigatory Powers Act, 2000
>
>
> ---(end of broadcast)---
> TIP 4: Don't 'kill -9' the postmaster
>
==
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 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Analyze + Index
On Friday 29 Nov 2002 4:45 am, Rudi Starcevic wrote: > Hi, > > I think I've found what I need to know. > From techdocs.postgresql.org > > >> Every you run an INSERT, UPDATE, or DELETE on an indexed table, > > PostgreSQL must update all of the table's indexes to reflect the new > data (unlike some > > >> RDBMSs, PostgreSQL updates indexes immediately so they never get out > of synch with the data). Yep - the ANALYSE updates the statistics the planner uses. So - you should analyse when a noticable portion of your data has changed. If you had 3000 users and batch inserted 1000 users you'd probably want to ANALYSE. -- Richard Huxton ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] master-detail relationship and count
On Friday 29 Nov 2002 10:06 am, Gary Stainburn wrote: > Hi folks. > > I've got a master detail relationship where I have a railway route table > listing landmarks along the route, and a Links table listing URL's > associated with that landmark. Listed below: > > How can I do a query showing the landmark ID, the landmark name, and a > count of links associated with that landmark. Below is a SQL statement > that although is illegal, gives a good idea of what I'm looking for. > > select r.rtid, r.rtname, l.count(*) from route r, links l where l.lktype = > 'R' and l.lklid = r.rtid; You'll want to GROUP BY SELECT r.rtid, r.rtname, l.count(*) FROM route r, links l WHERE l.lktype='R' AND l.lklid=r.rtid GROUP BY r.rtid, r.rtname; -- Richard Huxton ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] master-detail relationship and count
As you can see from the extract below, your statement has worked for all
landmarks that have links, but ignores any landmarks with out links. How can
I adjust this so that all landmarks are listed, but with a zero count where
appropriate?
select r.rtid, r.rtname, subsel.cnt from route r,
(select r2.rtid as rid, count(lnk.lkid) as cnt from route r2,links lnk
where lnk.lktype='R'
and lnk.lklid = r2.rtid group by r2.rtid) as subsel
where r.rtid = subsel.rid;
[gary@larry gary]$ psql -d nymr On Fri, 29 Nov 2002, Gary Stainburn wrote:
> > Hi folks.
> >
> > I've got a master detail relationship where I have a railway route table
> > listing landmarks along the route, and a Links table listing URL's
> > associated with that landmark. Listed below:
> >
> > How can I do a query showing the landmark ID, the landmark name, and a
> > count of links associated with that landmark. Below is a SQL statement
> > that although is illegal, gives a good idea of what I'm looking for.
> >
> > select r.rtid, r.rtname, l.count(*) from route r, links l where l.lktype
> > = 'R' and l.lklid = r.rtid;
>
> select r.rtid,r.rtname,subsel.cnt from route r,
> (select r2.rtid as rid,count(lnk.lkid) as cnt from route r2,links lnk
> where lnk.type='R'
> and lnk.lklid = r2.rtid group by r2.rtid) as subsel
> where r.rtid = subsel.rid
>
> or something like that.
>
> > nymr=# \d route
> > Table "route"
> > Attribute | Type | Modifier
> > +---+
> >-- rtid | integer | not null default
> > nextval('route_rtid_seq'::text)
> > rtmile | integer | not null
> > rtyards| integer | not null
> > rtname | character varying(40) |
> > rtspeed| integer |
> > rtgradient | integer |
> > rtsection | integer |
> > rtphone| character(1) |
> > rtcomments | text |
> > Indices: route_index,
> > route_rtid_key
> >
> > nymr=# select r.rtid, l.count(*) from route r, links l where
> > nymr=# \d links
> > Table "links"
> > Attribute | Type |Modifier
> > ---+---+-
> > lkid | integer | not null default
> > nextval('staff_sid_seq'::text)
> > lkdesc| character varying(40) |
> > lkurl | character varying(40) |
> > lktype| character(1) |
> > lklid | integer |
> > Index: links_lkid_key
> >
> > lktype indicates the link type - 'R' indicates a route entry
> > lklid indicates the link ID. For a 'R' it is the rtid of the route entry
> > --
> > Gary Stainburn
> >
> > This email does not contain private or confidential material as it
> > may be snooped on by interested government parties for unknown
> > and undisclosed purposes - Regulation of Investigatory Powers Act, 2000
> >
> >
> > ---(end of broadcast)---
> > TIP 4: Don't 'kill -9' the postmaster
>
> ==
> 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]
--
Gary Stainburn
This email does not contain private or confidential material as it
may be snooped on by interested government parties for unknown
and undisclosed purposes - Regulation of Investigatory Powers Act, 2000
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] master-detail relationship and count
On Fri, 29 Nov 2002, Gary Stainburn wrote:
> As you can see from the extract below, your statement has worked for all
> landmarks that have links, but ignores any landmarks with out links. How can
> I adjust this so that all landmarks are listed, but with a zero count where
> appropriate?
Then, use LEFT OUTER JOIN ... USING (),
in combination with COALESCE().
(read the docs)
>
> select r.rtid, r.rtname, subsel.cnt from route r,
> (select r2.rtid as rid, count(lnk.lkid) as cnt from route r2,links lnk
> where lnk.lktype='R'
> and lnk.lklid = r2.rtid group by r2.rtid) as subsel
> where r.rtid = subsel.rid;
> [gary@larry gary]$ psql -d nymr rtid | rtname | cnt
> --++-
> 1 | The Grange | 1
> (1 row)
> [gary@larry gary]$
>
> Gary
>
> On Friday 29 Nov 2002 10:36 am, Achilleus Mantzios wrote:
> > On Fri, 29 Nov 2002, Gary Stainburn wrote:
> > > Hi folks.
> > >
> > > I've got a master detail relationship where I have a railway route table
> > > listing landmarks along the route, and a Links table listing URL's
> > > associated with that landmark. Listed below:
> > >
> > > How can I do a query showing the landmark ID, the landmark name, and a
> > > count of links associated with that landmark. Below is a SQL statement
> > > that although is illegal, gives a good idea of what I'm looking for.
> > >
> > > select r.rtid, r.rtname, l.count(*) from route r, links l where l.lktype
> > > = 'R' and l.lklid = r.rtid;
> >
> > select r.rtid,r.rtname,subsel.cnt from route r,
> > (select r2.rtid as rid,count(lnk.lkid) as cnt from route r2,links lnk
> > where lnk.type='R'
> > and lnk.lklid = r2.rtid group by r2.rtid) as subsel
> > where r.rtid = subsel.rid
> >
> > or something like that.
> >
> > > nymr=# \d route
> > > Table "route"
> > > Attribute | Type | Modifier
> > > +---+
> > >-- rtid | integer | not null default
> > > nextval('route_rtid_seq'::text)
> > > rtmile | integer | not null
> > > rtyards| integer | not null
> > > rtname | character varying(40) |
> > > rtspeed| integer |
> > > rtgradient | integer |
> > > rtsection | integer |
> > > rtphone| character(1) |
> > > rtcomments | text |
> > > Indices: route_index,
> > > route_rtid_key
> > >
> > > nymr=# select r.rtid, l.count(*) from route r, links l where
> > > nymr=# \d links
> > > Table "links"
> > > Attribute | Type |Modifier
> > > ---+---+-
> > > lkid | integer | not null default
> > > nextval('staff_sid_seq'::text)
> > > lkdesc| character varying(40) |
> > > lkurl | character varying(40) |
> > > lktype| character(1) |
> > > lklid | integer |
> > > Index: links_lkid_key
> > >
> > > lktype indicates the link type - 'R' indicates a route entry
> > > lklid indicates the link ID. For a 'R' it is the rtid of the route entry
> > > --
> > > Gary Stainburn
> > >
> > > This email does not contain private or confidential material as it
> > > may be snooped on by interested government parties for unknown
> > > and undisclosed purposes - Regulation of Investigatory Powers Act, 2000
> > >
> > >
> > > ---(end of broadcast)---
> > > TIP 4: Don't 'kill -9' the postmaster
> >
> > ==
> > 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]
>
> --
> Gary Stainburn
>
> This email does not contain private or confidential material as it
> may be snooped on by interested government parties for unknown
> and undisclosed purposes - Regulation of Investigatory Powers Act, 2000
>
>
==
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 6: Have you searched our list archives?
http://archives.postgresql.org
[SQL] import sql script
Hallo, I have a big sql script, which creates about 120 tables. much of them have FK to other tables. The order in this sql script is not correct. Tables which be create at least, while they have FK to other table, are defined bevore. Is there any way to import this structure to PostgreSQL without correcting the order in my script. Best Regards, Rafal ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] master-detail relationship and count
On 29 Nov 2002 at 13:16, Achilleus Mantzios wrote: > On Fri, 29 Nov 2002, Gary Stainburn wrote: > > > As you can see from the extract below, your statement has worked for all > > landmarks that have links, but ignores any landmarks with out links. How can > > I adjust this so that all landmarks are listed, but with a zero count where > > appropriate? > > Then, use LEFT OUTER JOIN ... USING (), > in combination with COALESCE(). > > (read the docs) When it comes to outer joins, this page is quite useful: http://www.postgresql.org/idocs/index.php?explicit-joins.html I was reading it yesterday when improving the speed of some JOINs. The JOIN went from 3440ms to about 18ms when following the advice on that page. I've put the queries and the explain output at http://www.freshports.org/tmp/outer-join.txt. This will be an example of an OUTER JOIN but not the master-detail situation which occurs elsewhere in the database. cheers -- Dan Langille : http://www.langille.org/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] master-detail relationship and count
I've worked out a way of doing it by vreating a view for the tally info as:
create view link_tally as
select lklid, lktype, count(*) from links group by lklid, lktype;
and then doing:
select r.rtid, r.rtname, l.count from route r
left outer join link_tally l on r.rtid = l.lklid and l.lktype = 'R';
(this works apart from the coalesce bit which I haven't worked out where to
put yet, and for the moment isn't important as NULL is okay as a result).
However, I still can't get it to work straight from the tables. The nearest
I'ev got is:
select r.rtid, r.rtname, subsel.cnt from route r,
(select r2.rtid as rid, count(lnk.lkid) as cnt from route r2, links lnk
where lnk.lktype='R' and lnk.lklid = r2.rtid group by r2.rtid) as subsel
left outer join subsel on r.rtid = subsel.rid;
which comes back with the error:
[gary@larry gary]$ psql On Fri, 29 Nov 2002, Gary Stainburn wrote:
> > As you can see from the extract below, your statement has worked for all
> > landmarks that have links, but ignores any landmarks with out links. How
> > can I adjust this so that all landmarks are listed, but with a zero count
> > where appropriate?
>
> Then, use LEFT OUTER JOIN ... USING (),
> in combination with COALESCE().
>
> (read the docs)
>
> > select r.rtid, r.rtname, subsel.cnt from route r,
> > (select r2.rtid as rid, count(lnk.lkid) as cnt from route r2,links lnk
> > where lnk.lktype='R'
> > and lnk.lklid = r2.rtid group by r2.rtid) as subsel
> > where r.rtid = subsel.rid;
> > [gary@larry gary]$ psql -d nymr > rtid | rtname | cnt
> > --++-
> > 1 | The Grange | 1
> > (1 row)
> > [gary@larry gary]$
> >
> > Gary
> >
> > On Friday 29 Nov 2002 10:36 am, Achilleus Mantzios wrote:
> > > On Fri, 29 Nov 2002, Gary Stainburn wrote:
> > > > Hi folks.
> > > >
> > > > I've got a master detail relationship where I have a railway route
> > > > table listing landmarks along the route, and a Links table listing
> > > > URL's associated with that landmark. Listed below:
> > > >
> > > > How can I do a query showing the landmark ID, the landmark name, and
> > > > a count of links associated with that landmark. Below is a SQL
> > > > statement that although is illegal, gives a good idea of what I'm
> > > > looking for.
> > > >
> > > > select r.rtid, r.rtname, l.count(*) from route r, links l where
> > > > l.lktype = 'R' and l.lklid = r.rtid;
> > >
> > > select r.rtid,r.rtname,subsel.cnt from route r,
> > > (select r2.rtid as rid,count(lnk.lkid) as cnt from route r2,links lnk
> > > where lnk.type='R'
> > > and lnk.lklid = r2.rtid group by r2.rtid) as subsel
> > > where r.rtid = subsel.rid
> > >
> > > or something like that.
> > >
> > > > nymr=# \d route
> > > > Table "route"
> > > > Attribute | Type | Modifier
> > > > +---+
> > > > -- rtid | integer | not null
> > > > default nextval('route_rtid_seq'::text)
> > > > rtmile | integer | not null
> > > > rtyards| integer | not null
> > > > rtname | character varying(40) |
> > > > rtspeed| integer |
> > > > rtgradient | integer |
> > > > rtsection | integer |
> > > > rtphone| character(1) |
> > > > rtcomments | text |
> > > > Indices: route_index,
> > > > route_rtid_key
> > > >
> > > > nymr=# select r.rtid, l.count(*) from route r, links l where
> > > > nymr=# \d links
> > > > Table "links"
> > > > Attribute | Type |Modifier
> > > > ---+---+-
> > > > lkid | integer | not null default
> > > > nextval('staff_sid_seq'::text)
> > > > lkdesc| character varying(40) |
> > > > lkurl | character varying(40) |
> > > > lktype| character(1) |
> > > > lklid | integer |
> > > > Index: links_lkid_key
> > > >
> > > > lktype indicates the link type - 'R' indicates a route entry
> > > > lklid indicates the link ID. For a 'R' it is the rtid of the route
> > > > entry --
> > > > Gary Stainburn
> > > >
> > > > This email does not contain private or confidential material as it
> > > > may be snooped on by interested government parties for unknown
> > > > and undisclosed purposes - Regulation of Investigatory Powers Act,
> > > > 2000
> > > >
> > > >
> > > > ---(end of
> > > > broadcast)--- TIP 4: Don't 'kill -9' the
> > > > postmaster
> > >
> > > ==
> > > Achilleus Mantzios
> > > S/W Engineer
> > > IT dept
> > > Dynacom Tankers Mngmt
> > > Nikis 4, Glyfada
> > > Athens 16610
> > > Greece
> > > tel:+30-10-8981112
> > > fax:+30-10-8981877
> >
[SQL] ALTER TABLE x DROP CONSTRAINT fkey
Pardon stupidiy, what is the right syntax for dropping a foreign key? Struggling here! TIA --- Thomas Good e-mail: [EMAIL PROTECTED] Programmer/Analyst phone: (+1) 718.818.5528 Residential Services fax: (+1) 718.818.5056 Behavioral Health Services, SVCMC-NY mobile: (+1) 917.282.7359 --Geistiges Eigentum ist Diebstahl! -- ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL]
I have a reference which is guaranteed not to have a valid reference (preferably NULL) until it is processed later. How can I defer a reference entry so that both NULL and an actual reference are valid in the reference field? The processing which I speak of takes place later and not in the same transaction (thus making DEFERRABLE useless, right?). A solution I thought of is to forget keys and just make it an INT8 since I am also guaranteed that its referenced item is never deleted. But certainly, there is a more reliable way to do it, right? In summary, what I want is field INT8 REFERENCES sometable NULL OK or field INT8 CHECK(SELECT id FROM someothertable WHERE field=id) (can I do this? this allows NULL right?) The rule is ref2 can either be a valid reference or NULL. CREATE TABLE thetable( ref2 INT8 REFERENCES someothertable, ... ); Thanks for any help. ><><><><><><><><>< AgentM [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL]
"A.M." <[EMAIL PROTECTED]> writes: > In summary, what I want is > field INT8 REFERENCES sometable NULL OK Er ... NULL *is* OK, unless you say field INT8 REFERENCES sometable NOT NULL regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL]
Wow. That's a real head-slapper. Thanks for the quick answer! On Friday, November 29, 2002, at 11:28 PM, Tom Lane wrote: "A.M." <[EMAIL PROTECTED]> writes: In summary, what I want is field INT8 REFERENCES sometable NULL OK Er ... NULL *is* OK, unless you say field INT8 REFERENCES sometable NOT NULL regards, tom lane ><><><><><><><><>< AgentM [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
