[SQL] Max Conncection limit?
We start a server and initiate 30 connections(with the libpq C interface) which are stored in a stack to use and and to be reused. After awhile I try to start another server that will also try to initiate 30 connections, for each connection I then get a PQErrorMessage like that : "Sorry, too many connections" if I do a ps -ax at that time I get a lot of /usr/bin/postgres 192.168.0.1 user name passwd idle what I usually do is restart postgres but I wish I didnt have to do that... That is not an elegant solutuion any ways. What can I do? Thanks in advance. Regards begin:vcard n:Hashmi;Najm x-mozilla-html:FALSE org:Mondo-Live.com;www.flipr.com adr:;; version:2.1 email;internet:[EMAIL PROTECTED] x-mozilla-cpt:;6144 fn:Najm Hashmi end:vcard ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Max Conncection limit?
On Tue, Mar 13, 2001 at 11:33:10AM -0500, Najm Hashmi wrote: > We start a server and initiate 30 connections(with the libpq C interface) > which are stored in a stack to use and and to be reused. > > After awhile I try to start another server that will also try to initiate 30 > connections, for each connection I then get a PQErrorMessage like that : > "Sorry, too many connections" > > if I do a ps -ax at that time I get a lot of /usr/bin/postgres 192.168.0.1 > user name passwd idle > > what I usually do is restart postgres but I wish I didnt have to do that... > That is not an elegant solutuion any ways. > What can I do? Thanks in advance. Regards Have a look at the runtime-config.html in the admin section of the manual: MAX_CONNECTIONS (integer) Determines how many concurrent connections the database server will allow. The default is 32. There is also a compiled-in hard upper limit on this value, which is typically 1024 (both numbers can be altered when compiling the server). This parameter can only be set at server start. Regards, Frank ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Comparing dates
Hello - It seems that using BETWEEN would work well, especially for finding dates between two other dates. WHERE date_date BETWEEN '03-02-2001'::date and '03-03-2001'::date --d > On Tue, 6 Mar 2001, Markus Fischer wrote: > > > Hello, > > > > I've a SELECT statement on many joined Tabled and one of them has > > a date column called 'date_date'. When I fetch a date e.g. > > '02-03-2001', I get, say, 60 results back. When I now perform the > > same query with another date, lets take '03-03-2001', I get back > > about 70 results. > > > > When I now modify my query to get both results in one I write > > > > SELECT > > > > FROM > > .. > > AND > > date_date >= '2001-03-02' > > AND > > date_date <= '2001-03-03' > > AND > > > > > > I think I should get back the rows for both days, 60 + 70 makes > > 130 to me. But what I get back is even smaller then 60. I > > allready tried TO_DATE conversion, an OR construct but always > > the same result. > > > > Is there something special to know when comparing/working with > > date-datetypes ? > > > > > > kind regards, > > Markus > > > > -- > > Markus Fischer, http://josefine.ben.tuwien.ac.at/~mfischer/ > > EMail: [EMAIL PROTECTED] > > PGP Public Key: http://josefine.ben.tuwien.ac.at/~mfischer/C2272BD0.asc > > PGP Fingerprint: D3B0 DD4F E12B F911 3CE1 C2B5 D674 B445 C227 2BD0 > > > > ---(end of broadcast)--- > > TIP 2: you can get off all lists at once with the unregister command > > (send "unregister YourEmailAddressHere" to [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]) ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Poor document!!
I cannot believe that PostgreSQL has a so poor document, I cannot found any useful information. I afraid I have to select MySQL. Does anybody know if PostgreSQL support Unicode and Full-Text Index? I couldn't found any information about these from http://www.postgresql.org! Thanks Noodle ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] default value syntax - pg compared to?
Hello,
do you happen to know, if there is any sqlserver that WON'T handle
inserting default value if it is not specified in the INSERT field list?
I am particularly interested in ORACLE, INFORMIX and MYSQL .
Eg.
CREATE TABLE test
(
id integer default 5,
fld1 text
);
INSERT INTO test (fld1) VALUES ('blahblah');
So, is there any sqlserver that under these conditions will NOT
make id = 5 for the inserted row?
Background:
There is a package called phprojekt where the developer has
created some workarounds for different handlings of auto-
incrementing fields (sequences) for the above databases.
The only place he uses them, however, is in the INSERT
stataments (and he does not need to know the new id value for the
inserted row).
So I suggested he rewrite INSERTs explicitly specifying field
names and excluding the id field, rather than putting a server-
specific "default value" token (which postgres does not support
anyway). Will it work for the above other dbs?
TIA for answers!
Emils
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] SQL problem
Hello:
I have a table with pluviometrical data
meteo (rain float, day date)
I want to select the the day of maximum value for each year.It should be
something like :
select max(rain),day from meteo group by date_part('year', day);
but it obiously doesn't work.
I thought of doing it with aggregates, but it's far too complicated. Is
there an 'easy' way to do this?
--
Salva
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
[SQL] Re: SQL problem
Here's one approach:
create view vw_maxrain as select max(rain) as rain, date_part('year',day) as
year from meteo group by year;
select day, meteo.rain from meteo, vw_maxrain where
meteo.rain=vw_maxrain.rain;
"Salvador Mainé" <[EMAIL PROTECTED]> wrote in message
[EMAIL PROTECTED]">news:[EMAIL PROTECTED]...
> Hello:
>
> I have a table with pluviometrical data
>
> meteo (rain float, day date)
>
> I want to select the the day of maximum value for each year.It should be
> something like :
>
>
> select max(rain),day from meteo group by date_part('year', day);
>
>
> but it obiously doesn't work.
> I thought of doing it with aggregates, but it's far too complicated. Is
> there an 'easy' way to do this?
>
>
>
>
> --
> Salva
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Re: [DOCS] Extending PostgreSQL Using C
On Wed, 7 Mar 2001, Tom Lane wrote: > Hannu Krosing <[EMAIL PROTECTED]> writes: > > Boulat Khakimov wrote: > >> ERROR: Can't find function encrypt in file /[full path here]/encrypt.so > > > Can _postgres_ user read /[full path here]/encrypt.so ? > > Presumably so. If he were unable to load the .so file, he'd be getting > a different error message. This message indicates that he got past the > load step, but dl_sym is unable to resolve the symbol "encrypt". > > I asked about the symbol names shown by nm(1), but got no answer ... It should be noted that encrypt() is a function already declared in unistd.h, which the OP is including. Could that be causing some problem since the OP is using the same name? Nishad -- "Underneath the concrete, the dream is still alive" -- Talking Heads ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re:[SQL] Comparing Dates
David, Please post your tabledefs and the full query definition. Aside from the need for an explicit typecast (i.e. '2000-03-02'::date) and the lack of clarity on month vs. day (March 2 or February 3?), seeing the whole picture would help. -Josh Berkus __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 565-7293 for law firms, small businessesfax 621-2533 and non-profit organizations. San Francisco ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[SQL] help
help me pls. i got an error like this when i do an insert to a table, my table dont have bpchar type of field. ERROR: Unable to identify an operator '=' for types 'bpchar' and 'varchar' You will have to retype this query using an explicit cast ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] Re: [DOCS] Extending PostgreSQL Using C
Boulat Khakimov wrote: > Hi, > > Im writing a C function for PG to do one way encryption using crypt. > Here is the source code > > it gives me this error > > ERROR: Can't find function encrypt in file /[full path here]/encrypt.so > > > Why do I get this error > Any ideas? Can _postgres_ user read /[full path here]/encrypt.so ? --- Hannu ---(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] default value syntax - pg compared to?
Emils,
> do you happen to know, if there is any sqlserver that WON'T handle
> inserting default value if it is not specified in the INSERT field
> list?
> I am particularly interested in ORACLE, INFORMIX and MYSQL .
MS SQL 6.5, 7.0 and 2000 all support default values on incremental
fields ... with some caveats:
1. Unlike PostgreSQL, MSSQL server will not permit you to override an
"Identity Value" auto-incrementing field;
2. MSSQL 6.5 and 7.0 do not support user-defined functions as
default-values (or, for that matter, user-defined functions at all).
Also, you should be aware that the pgODBC driver for Win32 clients does
not recognize PostgreSQL sequences correctly when called as default
vaues for other than a SERIAL field (i.e. if you use the DEFAULT
NEXTVAL('sequence') construction rather than the SERIAL type, pgODBC
will error out on INSERTS).
-Josh Berkus
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
Complete information technology [EMAIL PROTECTED]
and data management solutions (415) 565-7293
for law firms, small businessesfax 621-2533
and non-profit organizations. 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] I need an aggregate with 2 parameters
Hello:
I have a table with pluviometrical data
meteo (rain float, day date, oid station)
I want to select the the day of maximum value for each year for a given
measurement station. It should look like :
select max(rain),day from meteo group by date_part('year', day) where
station=7654765;
though it obiously doesn't work.
I think that an aggregate function would be the best way to do this,
because the table is quite large and I need indexes for date and also
for stations, so adding a new one for the rain is too much. But the
aggregate should look like:
max_rain(rain, day) and return the day corresponding to the maximum
rain, So the query would be something like:
select max(rain), max_rain(rain, day) from meteo group by
date_part('year', day) where station=47654765;
I've tried to define a composite type for rain and day and the neccesary
functions to make the aggregate, but I cannot find the correct way. Can
someone help me? Is this sollution possible?
--
Salvador Maine
http://www.ronincoders.com
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] ecpg on 7.0
Hi, I installed postgresql 7.0 from RedHat CD. According to the official 7.0 doc, ecpg comes with the installation. However, I could not find ecpg (by searching directories and using whereis). I also cannot find doc on how to invoke ecpg. The postgresql seems OK. I can initdb, postmast, insert tuples, and query the database. Please help. Thanks, John [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] help
help me pls. i got an error like this when i do an insert to a table, my table dont have bpchar type of field. ERROR: Unable to identify an operator '=' for types 'bpchar' and 'varchar' You will have to retype this query using an explicit cast it seems 1 field in my table has bpchar type, but if i view the table definition all are varchar and numeric. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] I need an aggregate with 2 parameters
This should work:
SELECT day, rain FROM meteo WHERE rain = (SELECT max(rain) FROM meteo
WHERE date_part('year', day) = '2001')
Michael Fork - CCNA - MCP - A+
Network Support - Toledo Internet Access - Toledo Ohio
On Thu, 8 Mar 2001, Salvador [iso-8859-1] Mainé wrote:
> Hello:
>
> I have a table with pluviometrical data
>
> meteo (rain float, day date, oid station)
>
> I want to select the the day of maximum value for each year for a given
> measurement station. It should look like :
>
> select max(rain),day from meteo group by date_part('year', day) where
> station=7654765;
>
> though it obiously doesn't work.
>
> I think that an aggregate function would be the best way to do this,
> because the table is quite large and I need indexes for date and also
> for stations, so adding a new one for the rain is too much. But the
> aggregate should look like:
>
> max_rain(rain, day) and return the day corresponding to the maximum
> rain, So the query would be something like:
>
> select max(rain), max_rain(rain, day) from meteo group by
> date_part('year', day) where station=47654765;
>
> I've tried to define a composite type for rain and day and the neccesary
> functions to make the aggregate, but I cannot find the correct way. Can
> someone help me? Is this sollution possible?
>
> --
> Salvador Maine
> http://www.ronincoders.com
>
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] help
What is the schema of the table in question, does it have any references to other tables and what is an example insert statement? On Wed, 7 Mar 2001, chard wrote: > > help me pls. > i got an error like this when i do an insert to a table, my table dont > have bpchar type of field. > > ERROR: Unable to identify an operator '=' for types 'bpchar' and 'varchar' > You will have to retype this query using an explicit cast > > > > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html > ---(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] I need an aggregate with 2 parameters
Salvador =?iso-8859-1?Q?Main=E9?= <[EMAIL PROTECTED]> writes: > I want to select the the day of maximum value for each year for a given > measurement station. This is exactly the sort of thing that SELECT DISTINCT ON is meant for. See the "weatherReports" example in the SELECT man page, http://www.postgresql.org/devel-corner/docs/postgres/sql-select.html 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] help
chard <[EMAIL PROTECTED]> writes: > i got an error like this when i do an insert to a table, my table dont > have bpchar type of field. > ERROR: Unable to identify an operator '=' for types 'bpchar' and 'varchar' > You will have to retype this query using an explicit cast > it seems 1 field in my table has bpchar type, but if i view the table > definition all are varchar and numeric. I'll bet you have a foreign-key constraint between a varchar field and a bpchar field in another table. 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])
[SQL] Cannot declare arrays?
Folks, I'm trying to delcare an array (of type Numeric) within a PL/pgSQL function. No matter what syntax I try, however, I get a compile error. I've tried: DECLARE level_array NUMERIC(8,2)[]; level_array NUMERIC[](8,2); level_array NUMERIC[]; level_array[] NUMERIC(8,2); and even: level_array MONEY[100]; Please help! -Josh Berkus -- __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] SQL Dummy Needs Help
I'm pretty new to SQL and can't figure out how to write what should be a simple query of two tables. Could someone here possibly help me out? Table 1 has two fields I'm interested in: TITLE_NO and EFFECT_DATE Table 2 also has two fields of interest:TITLE_NO and PAIDTO_DATE TITLE_NO is unique in Table 1, meaning each TITLE will have a unique EFFECT_DATE. Table 2 represents an accounting history of each TITLE, so for each TITLE_NO there may be one or more PAIDTO_DATE values. In both Tables the _DATE fields are stored as 9-character strings in the fomat MMDD. In all cases, the MM and DD values in Table 2 should be identical with those in Table 1. My intention is to produce a list that will contain the TITLE_NO of each TITLE where the MMDD value for EFFECT_DATE differ from any of the PAIDTO_DATE values for that TITLE_NO. The list must contain ONLY the PAIDTO_DATE values that differ, and the corresponding TITLE_NO. Sorry I can't explain this a little more technically, but if anyone can advise me, that would be fabulous. Thanks, Terry ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] SQL Dummy Needs Help
Well, you should probably get yourself a good SQL book :) but here's a try (untested). How about something like: SELECT DISTINCT title_no, paidto_date FROMtable1, table2 WHERE table1.title_no = table2.title_no AND table1.effect_date <> table2.paidto_date; Again, untested - try it and see. -- Andrew J Perrin - Ph.D. Candidate, UC Berkeley, Dept. of Sociology (Soon: Asst Professor of Sociology, U of North Carolina, Chapel Hill) [EMAIL PROTECTED] - http://www.unc.edu/~aperrin On Fri, 9 Mar 2001, Alder wrote: > I'm pretty new to SQL and can't figure out how to write what should be a > simple query of two tables. Could someone here possibly help me out? > > Table 1 has two fields I'm interested in: TITLE_NO and EFFECT_DATE > Table 2 also has two fields of interest:TITLE_NO and PAIDTO_DATE > > TITLE_NO is unique in Table 1, meaning each TITLE will have a unique > EFFECT_DATE. Table 2 represents an accounting history of each TITLE, so for > each TITLE_NO there may be one or more PAIDTO_DATE values. In both Tables > the _DATE fields are stored as 9-character strings in the fomat MMDD. > In all cases, the MM and DD values in Table 2 should be identical with those > in Table 1. > > My intention is to produce a list that will contain the TITLE_NO of each > TITLE where the MMDD value for EFFECT_DATE differ from any of the > PAIDTO_DATE values for that TITLE_NO. The list must contain ONLY the > PAIDTO_DATE values that differ, and the corresponding TITLE_NO. > > Sorry I can't explain this a little more technically, but if anyone can > advise me, that would be fabulous. > > Thanks, > Terry > > > > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html > ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] How does this query work.....?
Hi, I am using the following query to find the attributes of a given table, and their datatypes: select typname,attname from pg_class c, pg_attribute a,pg_type t where relname = 'table_name' and attrelid = c.oid and atttypid = t.oid and attnum > 0; Can anybody explain how this query actually works - I cannot figure it. Thanks in advance. Rob Burne.
[SQL] error joining 2 views containing GROUP BYs
Dear Friends,
I am having a few problems using a select query which joins two views
containing aggregate functions (see query 1 below). Entering this query in
MS Access through ODBC (linking the tables in) I get the correct result of:
uidtotalansweredcorrecttotaltimeweekno
22152275
22352245
Using Psql I get the result:
uid | totalanswered | correct | totaltime | weekno
-+---+-+---+
221 |10 | 10 |54 | 5
223 |10 | 10 |48 | 5
(2 rows)
I have read similar posts where people have had problems with views
containing aggregate functions so I realise that there remains work to be
done on this aspect, but my question is:
Does anyone know how I can get this query to work? Can I make the query be
interpreted in the same way as ODBC?
Is the problem the same as
http://www.postgresql.org/mhonarc/pgsql-sql/2000-11/msg00175.html which Tim
Lane explained the problem "the rewriter effectively
expands them in-line" (Tim Lane)?
Any help or tips would be greatly appreciated.
David Morgan.
drop table Users;
create table Users
(
UID int4 PRIMARY KEY,
Name text,
Address text,
TelNo text,
EmailAddress text,
FavClub text,
DOB date,
Password text,
Language text
);
drop table QuAnswered;
CREATE TABLE "quanswered" (
"uid" int4 DEFAULT 0 NOT NULL,
"qid" int4 DEFAULT 0 NOT NULL,
"aid" int4 DEFAULT 0,
"tstamp" timestamp DEFAULT "timestamp"('now'::text),
"ttaken" float4,
PRIMARY KEY ("uid", "qid")
);
drop table Questions;
CREATE TABLE "questions" (
"qid" int4 DEFAULT 0 NOT NULL,
"aid" int4 DEFAULT 0,
"queng" text,
"quwel" text,
"weekno" int2 DEFAULT 0,
PRIMARY KEY ("qid")
);
INSERT INTO "users" VALUES (221,'james stagg','23 manai
way\015\012cardiff','029
20315273','[EMAIL PROTECTED]','cardiff','1974-04-15',NULL,'english');
INSERT INTO "users" VALUES (223,'jim','mill
lane','sdkfj','asdgl','rhymmny','199
5-10-01',NULL,'english');
INSERT INTO "questions" VALUES (201,936,'Against which country did Neil
Jenkins
win his first Welsh cap?','201. Yn erbyn pa wlad yr enillodd Neil Jenkins ei
gap
cyntaf dros Gymru?',5);
INSERT INTO "questions" VALUES (202,366,'Who beat Fiji in the Quarter Finals
of
the 1987 World Cup?','202. Yn erbyn pa wlad y collodd Ffiji yn Rownd Wyth
Olaf C
wpan y Byd 1987?',5);
INSERT INTO "questions" VALUES (203,26,'From which club did Pat Lam join
Northam
pton?','203. I ba glwb yr oedd Pat Lam yn chwarae cyn iddo ymuno gyda
Northampto
n?',5);
INSERT INTO "questions" VALUES (204,821,'In which country was Japan`s scrum
half
Graeme Bachop born?','204. Ym mha wlad y ganwyd mewnwr Siapan, Graeme
Bachop',5
);
INSERT INTO "questions" VALUES (205,369,'Who is Scotland`s most capped
outside h
alf?','205. Enwch y chwaraewr sydd wedi ymddangos yn safle`r maswr i`r Alban
y n
ifer fwyaf o weithiau? ',5);
INSERT INTO "quanswered" VALUES (221,201,936,'2001-03-07 10:43:09+00',6);
INSERT INTO "quanswered" VALUES (221,202,366,'2001-03-07 10:43:20+00',8);
INSERT INTO "quanswered" VALUES (221,203,785,'2001-03-07 10:47:15+00',6);
INSERT INTO "quanswered" VALUES (221,204,589,'2001-03-07 10:47:21+00',2);
INSERT INTO "quanswered" VALUES (221,205,257,'2001-03-07 10:47:29+00',5);
INSERT INTO "quanswered" VALUES (223,201,375,'2001-03-07 10:48:14+00',7);
INSERT INTO "quanswered" VALUES (223,202,544,'2001-03-07 10:48:22+00',4);
INSERT INTO "quanswered" VALUES (223,203,26,'2001-03-07 10:48:30+00',6);
INSERT INTO "quanswered" VALUES (223,204,972,'2001-03-07 10:49:42+00',3);
INSERT INTO "quanswered" VALUES (223,205,369,'2001-03-07 10:49:55+00',4);
DROP VIEW all_ans;
CREATE VIEW all_ans as
SELECT qa.uid, sum(qa.ttaken) as TotalTime, count(qa.aid) as TotalAnswered,
qu.
weekno
FROM quanswered qa, questions qu
WHERE qa.qid=qu.qid
GROUP BY qa.uid, qu.weekno;
DROP VIEW cor_ans;
CREATE VIEW cor_ans AS
SELECT qa.uid, count(qa.uid) AS correct, qu.weekno
FROM questions qu, quanswered qa
WHERE ((qu.aid = qa.aid)
AND (qu.qid = qa.qid))
GROUP BY qa.uid, qu.WeekNo;
Query 1
---
SELECT all_ans.uid, all_ans.totalanswered, cor_ans.correct,
all_ans.totaltime, all_ans.weekno
FROM all_ans, cor_ans
WHERE all_ans.weekno= cor_ans.weekno
AND all_ans.uid=cor_ans.uid;
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Re: [GENERAL] MySQLs Describe emulator!
On Tue, Mar 06, 2001 at 10:38:43AM -0500, Michael Fork wrote: > try starting psql with the -E option -- this displays all queries used > internally to the screen, i.e.: Sorry, hadn't read this one before posting... Thanks to the "moderating" it'll all be out of synch anyway but.. Patrick ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] default value syntax - pg compared to?
On Mon, 12 Mar 2001 20:52:02 + (UTC), [EMAIL PROTECTED] ("Josh
Berkus") wrote:
>1. Unlike PostgreSQL, MSSQL server will not permit you to override an
>"Identity Value" auto-incrementing field;
That's almost correct. You cannot *update* an identity column, but you
can override it on insert if you use
SET IDENTITY_INSERT tablename ON
create table foo (id integer identity, txt varchar(10))
go
insert into foo (txt) values ('a')
insert into foo (txt) values ('b')
set identity_insert foo on
insert into foo (id, txt) values (10, 'c')
set identity_insert foo off
insert into foo (txt) values ('b')
select * from foo
id txt
--- --
1 a
2 b
10 c
11 d
(4 row(s) affected)
.02 by
Carl van Tast
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://www.postgresql.org/search.mpl
Re: [SQL] SQL Dummy Needs Help
* Alder <[EMAIL PROTECTED]> menulis:
> I'm pretty new to SQL and can't figure out how to write what should be a
> simple query of two tables. Could someone here possibly help me out?
>
> Table 1 has two fields I'm interested in: TITLE_NO and EFFECT_DATE
> Table 2 also has two fields of interest:TITLE_NO and PAIDTO_DATE
>
> TITLE_NO is unique in Table 1, meaning each TITLE will have a unique
> EFFECT_DATE. Table 2 represents an accounting history of each TITLE, so for
> each TITLE_NO there may be one or more PAIDTO_DATE values. In both Tables
> the _DATE fields are stored as 9-character strings in the fomat MMDD.
It's probably better to store them as type date instead. That way you
can use functions like date_part() to extract the month and day.
You could use string functions to extract, say, the last 4 characters to
get the MMDD value, but that can get messy if someone accidentally
stores a date in YYMMDD format.
> In all cases, the MM and DD values in Table 2 should be identical with those
> in Table 1.
>
> My intention is to produce a list that will contain the TITLE_NO of each
> TITLE where the MMDD value for EFFECT_DATE differ from any of the
> PAIDTO_DATE values for that TITLE_NO. The list must contain ONLY the
> PAIDTO_DATE values that differ, and the corresponding TITLE_NO.
>
> Sorry I can't explain this a little more technically, but if anyone can
> advise me, that would be fabulous.
If you change your tables to store the dates as type date instead of
type string, then you can do something like the following:
SELECT table2.title_no, table2.paidto_date
FROM table1, table2
WHERE table1.title_no=table2.title_no
AND (date_part('month', table1.effect_date) !=
date_part('month', table2.paidto_date)
OR date_part('day', table1.effect_date) !=
date_part('day', table2.paidto_date));
(At least, I think that will work, but I never trust any SQL I write
without thoroughly testing it first..;)
--
Cliff Crawford
He who sacrifices his conscience to ambition
burns a picture to obtain the ashes. - Chinese proverb
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://www.postgresql.org/search.mpl
[GENERAL] Re: [SQL] Permissons on database
Are you looking for "grant all to public" or "grant select to public"? - Original Message - From: "Roland Roberts" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Wednesday, March 07, 2001 3:40 PM Subject: Re: [SQL] Permissons on database > > "bk" == Boulat Khakimov <[EMAIL PROTECTED]> writes: > > bk> How do I grant permissions on everything in the selected > bk> databes? > > bk> GRANT doesnt take as on object database name nor does it > bk> accept wild chars > > Attached is some Perl code I wrote long ago to do this. This > particular code was done for Keystone, a problem tracking database and > it would do a "GRANT ALL". Modify it as needed. Last I checked it > worked with both PostgreSQL 6.5.x and 7.0.x > > > > roland > -- >PGP Key ID: 66 BC 3B CD > Roland B. Roberts, PhD RL Enterprises > [EMAIL PROTECTED] 76-15 113th Street, Apt 3B > [EMAIL PROTECTED] Forest Hills, NY 11375 > ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] How does this query work.....?
pg_class holds the relation information (tables, etc) pg_attribute holds attribute information (attname), it keeps the oid of the relation it's on in attrelid and the oid of the type as atttypid pg_type holds type information (typname) The attnum>0 is to limit the check to user attributes. There are additional attributes (oid, xmin, etc...) defined on the tables that have attnum<0 and you usually don't care about that. On Fri, 9 Mar 2001 [EMAIL PROTECTED] wrote: > Hi, > > I am using the following query to find the attributes of a given table, and > their datatypes: > > select typname,attname > from pg_class c, pg_attribute a,pg_type t > where relname = 'table_name' and > attrelid = c.oid and > atttypid = t.oid and > attnum > 0; > > Can anybody explain how this query actually works - I cannot figure it. > Thanks in advance. > > Rob Burne. > ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[ADMIN] Re: Use of the LIMIT clause ?
Spy <[EMAIL PROTECTED]> writes: > Tom Lane a écrit : >> Is that actually how MySQL interprets two parameters? We treat them >> as count and offset respectively, which definition I thought was the >> same as MySQL's. > But MySQL's syntax is different, as found on > http://www.mysql.com/doc/S/E/SELECT.html : > "SELECT [STRAIGHT_JOIN] [SQL_SMALL_RESULT] [SQL_BIG_RESULT] > [SQL_BUFFER_RESULT] > [...] > [LIMIT [offset,] rows]" That's annoying; looks like we do it backwards from MySQL. Can anyone confirm that this is how MySQL behaves (maybe it's a typo on this documentation page)? Should we consider changing ours if it is different? Are there any other RDBMSes that implement two-parameter LIMIT? 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] SQL Dummy Needs Help
* Cliff Crawford <[EMAIL PROTECTED]> menulis:
>
> SELECT table2.title_no, table2.paidto_date
> FROM table1, table2
> WHERE table1.title_no=table2.title_no
> AND (date_part('month', table1.effect_date) !=
> date_part('month', table2.paidto_date)
> OR date_part('day', table1.effect_date) !=
> date_part('day', table2.paidto_date));
Ooops, I keep confusing C and SQL operators...the "!=" above should be "<>".
--
Cliff Crawford
He who sacrifices his conscience to ambition
burns a picture to obtain the ashes. - Chinese proverb
---(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] error joining 2 views containing GROUP BYs
"david morgan" <[EMAIL PROTECTED]> writes: > I am having a few problems using a select query which joins two views > containing aggregate functions (see query 1 below). I don't think there's any chance of making that work in pre-7.1 Postgres. Sorry :-(. As a workaround, consider selecting the grouped data into temp tables and then joining the temp tables. 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
[SQL] != and <> operators
Hi! I've for a while now been using the != operator in my sql queries, but in almost all examples I see <> instead. Are there any good reason to use any instead of the other? I prefer !=, but if you convince me, I'll change to what you other guys are using ;-) Regards, Patrik Kudo ech`echo xiun|tr nu oc|sed 'sx\([sx]\)\([xoi]\)xo un\2\1 is xg'`ol Känns det oklart? Fråga på! ---(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
