Re: [SQL] OID Perfomance - Object-Relational databases
Just a thought, but couldn't you create a sequence that is referenced by
multiple tables, ie:
CREATE SEQUENCE test_seq;
CREATE TABLE tblclients {
client_id default nextval('test_seq'::text),
...
}
CREATE TABLE tblbills {
bill_id default nextval('test_seq'::text),
...
}
and so on...
I have never tried this, but i don't see why it wouldn't work
Michael Fork - CCNA - MCP - A+
Network Support - Toledo Internet Access - Toledo Ohio
On Tue, 3 Oct 2000, Josh Berkus wrote:
> Tom,
>
> > By and large I'd recommend using a serial column in preference to OIDs,
> > though, for two reasons:
> >
> > 1. dump/restore is more practical that way (don't have to worry about
> >saving/reloading OIDs).
> >
> > 2. counter overflow problems hit you only per-table, not
> >per-installation.
>
> Hmmm ... for some tables, switching to Serial would work. However, one
> of the things I've done is add universal mod_data (modification stats)
> and notes tables, which have to relate via OID because they relate to
> 5-7 different tables. To wit:
>
> CREATE TABLE notes AS (
> ref_OID OID,
> staff_OID OID REFERENCES staff,
> note_date DATE,
> note_text TEXT
> )
>
> And the ref_oid relates to any of 5 different tables, thus allowing a
> single table to hold notes on clients, candidates, bills, etc. Very
> elegant, and using serials instead of the OID not possible.
>
> SO I'm concerned about the problems you mentioned above. pg_dump has a
> -o option; are there problems with this? And how liekly are counter
> overflow problems?
>
> Josh Berkus
>
>
>
> --
> __AGLIO DATABASE SOLUTIONS___
> Josh Berkus
>Complete information technology [EMAIL PROTECTED]
> and data management solutions (415) 436-9166
>for law firms, small businesses fax 436-0137
> and non-profit organizations. pager 338-4078
> San Francisco
>
Re: AW: [SQL] Table Attribute Help
I have been trying to do exactly that, with this being the closest I can
come:
football=# SELECT a.attname, t.typname, a.attlen, a.atttypmod FROM
pg_class c, pg_attribute a, pg_type t WHERE c.relname = 'tblplayer' AND
a.attnum > 0 AND a.attrelid = c.oid AND a.atttypid = t.oid ORDER BY
a.attnum;
attname| typname | attlen | atttypmod
---+-++---
play_id | int4| 4 |-1
play_name | varchar | -1 |34
play_username | varchar | -1 |20
play_password | varchar | -1 |20
play_online | bool| 1 |-1
I assume that attlen is the length in bytes of the field, with -1 being
variable length. Those who have a variable length have their length + 4
in the atttypmod field. So here is the query I used and its output for
this type of result (its a biggie):
number | attribute |type |modifier
+---+-+
1 | play_id | int4| not null default nextval('tb...
2 | play_name | varchar(30) | not null
3 | play_username | varchar(16) | not null
4 | play_password | varchar(16) | not null
5 | play_online | bool| default 'f'
SELECT a.attnum as number,
a.attname as attribute,
CASE WHEN t.typname = 'varchar' THEN
t.typname || '(' || a.atttypmod - 4 || ')'
ELSE
t.typname
END as type,
CASE WHEN a.attnotnull = 't' THEN
'not null '::text ELSE ''::text
END || 'default ' ||
CASE WHEN a.atthasdef = 't' THEN
substring(d.adsrc for 128)::text
ELSE ''::text END as modifier
FROM pg_class c,
pg_attribute a,
pg_type t,
pg_attrdef d
WHEREc.relname = '<>' AND
a.attnum > 0 AND
a.attrelid = c.oid AND
a.atttypid = t.oid AND
c.oid = d.adrelid AND
d.adnum = a.attnum
UNION ALL
SELECT a.attnum as number,
a.attname as attribute,
CASE WHEN t.typname = 'varchar' THEN
t.typname || '(' || a.atttypmod - 4 || ')'
ELSE
t.typname
END as type,
CASE WHEN a.attnotnull = 't' THEN
'not null '::text
ELSE
''::text
END as modifier
FROM pg_class c,
pg_attribute a,
pg_type t
WHEREc.relname = '<>' AND
a.attnum > 0 AND
a.attrelid = c.oid AND
a.atttypid = t.oid AND
a.attname NOT IN (SELECT a.attname
FROM pg_class c,
pg_attribute a,
pg_attrdef d
WHERE c.relname = '<>' AND
a.attnum > 0 AND
a.attrelid = c.oid AND
a.atttypid = t.oid AND
c.oid = d.adrelid AND
d.adnum = a.attnum)
ORDER BY a.attnum;
-
Michael Fork - CCNA - MCP - A+
Network Support - Toledo Internet Access - Toledo Ohio
On Mon, 9 Oct 2000, Brian C. Doyle wrote:
> That is great thank you.
>
> How would I grab the attribute type for an attribute with it so that the
> out put would look like
>
>attname atttype
> -- --
> userid varchar(30)
>
> I know that is not correct but is it possible to get that out put
>
>
> At 05:27 PM 10/9/00 +0200, you wrote:
> >yes it's possible,
> >
> >SELECT pg_attribute.attname
> >FROM pg_class, pg_attribute
> >WHERE
> >pg_class.relname = 'xxx' and pg_attribute.attrelid = pg_class.oid
> >
> >and pg_attribute.attnum>=1 order by pg_attribute.attnum;
> >
> >xxx is your tablename!
> >
> >
> >-Ursprüngliche Nachricht-
> >Von: Brian C. Doyle [mailto:[EMAIL PROTECTED]]
> >Gesendet: Montag, 9. Oktober 2000 17:21
> >An: [EMAIL PROTECTED]
> >Betreff: [SQL] Table Attribute Help
> >
> >
> >Hello all,
> >
> >I am trying to find a query to retrive the attributes of a table as in \d
> >tablename but as a select command. Is this possible?
>
Re: [SQL] count( distinct x )
I think you want SELECT count(distinct(area)) FROM areapostcode WHERE postcode LIKE 'BS1%' Michael Fork - CCNA - MCP - A+ Network Support - Toledo Internet Access - Toledo Ohio On Mon, 27 Nov 2000, Anthony wrote: > Apologies if this has been asked b4, but got this result when > attemplting to search the archives on the website > > Not Found > > The requested URL /mhonarc/pgsql-sql/search.cgi was not found on this > server. > > > Apache/1.3.12 Server at postgresql.rmplc.co.uk Port 80 > > The problem I have is with this statement: > > select count( distinct area ) from areapostcode where postcode like > 'BS1%' > > the above statement fails with > ERROR: parser: parse error at or near "distinct" > > I am not the greatest when it comes to SQL, but the pgsql docs implied > that the above would work. > > What I am trying to do is get a count of the no of matches from the > statement below > select distinct area from areapostcode where postcode like 'BS1%' > > Not the count of: > select area from areapostcode where postcode like 'BS1%' > > Can anyone help? > > TIA > Bap. >
Re: [SQL] Problem with function...
I don't think you return should have '' around them, i.e. RETURN distance; Michael Fork - CCNA - MCP - A+ Network Support - Toledo Internet Access - Toledo Ohio On Mon, 18 Dec 2000 [EMAIL PROTECTED] wrote: > Hi, > > I hope my question is appropriate for this list. I'm trying to create > a function that calculates the distance between a pair of > latitude/longitude points. This is what I have: > > /* latlon_distance.pgsql > * by Jamu Kakar <[EMAIL PROTECTED]>, Dec 18, 2000. > * > * Calculates the distance between 2 lat/lon pairs. Syntax: > * distance (lat1, lon1, lat2, lon2) where parameters are in decimal degrees. > */ > > CREATE FUNCTION distance (float8, float8, float8, float8) RETURNS float8 AS ' > DECLARE > radius constant float8 := 6378; > distance float8; > lat1 ALIAS FOR $1; > lon1 ALIAS FOR $2; > lat2 ALIAS FOR $3; > lon2 ALIAS FOR $4; > BEGIN > distance := radius * acos ((sin (radians (lat1)) * sin (radians (lat2))) + >(cos (radians (lat1)) * cos (radians (lat2)) * > cos (radians (lon1) - radians (lon2; > RETURN ''distance''; > END; > ' LANGUAGE 'plpgsql'; > > When I try a: > > select distance(49.0,-122.0,50.0,-123.0) as distance; > > I get: > > ERROR: Bad float8 input format 'distance' > > I've tried a variety of ways of specifying the values and I've hunted > through the mailing lists but haven't turned up anything useful. Any > help would be appreciated. > > Thanks, > Jamu. > > -- > Jamu Kakar (Developer)Expressus Design Studio, Inc. > [EMAIL PROTECTED] 708-1641 Lonsdale Avenue > V: (604) 988-6999 North Vancouver, BC, V7M 2J5 >
Re: [SQL] unreferenced primary keys: garbage collection
One other method is to setup up the foreign keys as ON DELETE RESTRICT, then outside of your transaction block issue a DELETE FROM address WHERE add_id = 1; If there are still records in the other tables referencing this record, it will error out and nothing will happen, however if no related records are left, the delete will succeed (you have to do it outside of transaction, otherwise I belive it will rollback on the error if other rows are found to be referencing the primary key) Michael Fork - CCNA - MCP - A+ Network Support - Toledo Internet Access - Toledo Ohio On Tue, 23 Jan 2001, Forest Wilkinson wrote: > Jan, > > Thanks for the reply, but your solution is rather unattractive to me. It > requires that, any time a reference to an address id is changed, five > tables be searched for the address id. This will create unwanted overhead > every time a change is made. In order to make those searches even > remotely fast, I'd have to add indexes to every one of those tables, which > will mean an additional performance hit on table inserts. Moreover, if a > new table is created that references address ids, and the maintainer at > the time forgets to rewrite those trigger functions, the system will > break. > > I'd much rather be able to simply attempt a delete of any given address, > relying on referential integrity to prevent the delete if the address is > still being referenced. I don't see why postgres has to treat such a > situation as a fatal error. If postgres issued (for example) a warning > instead of an error here, I'd be home free! Hasn't there been some talk > on the lists about this lately? > > Forest > > Jan Wieck wrote: > >> While this behaviour makes sense in your case, it's not > >> subject to referential integrity constraints. You could > >> arrange for it with custom trigger procedures, checking all > >> the five tables on DELETE or UPDATE on one of them. > > Forest Wilkinson wrote: > >> > I have a database in which five separate tables may (or may not) reference > >> > any given row in a table of postal addresses. I am using the primary / > >> > foreign key support in postgres 7 to represent these references. > >> > > >> > My problem is that, any time a reference is removed (either by deleting or > >> > updating a row in one of the five referencing tables), no garbage > >> > collection is being performed on the address table. That is, when the > >> > last reference to an address record goes away, the record is not removed > >> > from the address table. Over time, my database will fill up with > >> > abandoned address records. >
Re: [SQL] no value for numeric filed in SQL statement causes anerror
You can do it one of two ways, dynamically build your queries and leave
out fields that are blank (they will be set to the default value or, if
none is specified, NULL), i.e.:
INSERT INTO table (name, city) VALUES ('roger rabbit', 'carrot city');
or, assign a default value to the id variable, and update the variable if
the form field is not blank, leaving you with:
INSERT INTO table (name, id, city) VALUES ('roger rabbit', <>, 'carrot city');
You cannot put a column with any of the number types (int4, numeric,
etc.) into the field list and leave it blank, a value *must* be specified.
Michael Fork - CCNA - MCP - A+
Network Support - Toledo Internet Access - Toledo Ohio
On Wed, 31 Jan 2001 [EMAIL PROTECTED] wrote:
> Hi there
>
> I have a table with char and int fields. From my form I get no values back
> for int fields when they are left blank. This causes the SQL insert to
> fail.
>
> field type
> name char
> id int2
> city char
>
> insert into table (name,id,city) values ('roger rabbit',,'carrot city')
>
> The default for int/float fields is 0. Since I reuse the code of the
> referring page I don't like to have all fields assigned 0 at the beginning.
>
> Who can help ???
>
> Thanks ... jr
>
>
> PFISTER + PARTNER, SYSTEM - ENGINEERING AG
> Juerg Rietmann
> Grundstrasse 22a
> 6343 Rotkreuz
> Switzerland
>
> phone: +4141 790 4040
> fax: +4141 790 2545
> mobile: +4179 211 0315
>
>
Re: [SQL] const cast ?
You can create a function with the IsCacheable attribute...
CREATE FUNCTION my_date_part(date) RETURNS int4 AS '
SELECT date_part('year', $1);
' LANGUAGE 'sql' WITH iscachable();
(This can be found in the create function docs, or in Bruce's book -- both
available on the website)
Michael Fork - CCNA - MCP - A+
Network Support - Toledo Internet Access - Toledo Ohio
On Fri, 2 Feb 2001 [EMAIL PROTECTED] wrote:
> Hello Postgres Users and Developers,
>
> I have the following scenario:
> create table t (i int);
> create index ti on t(i);
>
> Now this table is filled with some values and the table is vacuum analyzed.
>
> Now I would like to run queries on this table which should use the index
> whenever possible, so they execute fast.
>
> If I try a simple query like: "select * from t where i=4" the index is used.
> A query like: "select * from t where i=abs(4)" is using the index too.
> But if I use more complex functions like the following:
> "select * from t where i=date_part('year', '2001-01-01'::date)"
> a sequential scan on the table is performed.
>
> Now I conclude that the planner/optimizer does not recognize that the
> date_part() function returns the same value upon each execution.
>
> What I would like to know: Could we use some const-cast, so the optimzer gets
> a hint in optimizing the query ?
> I think of something like:
> "select * from t where i=date_part('year', '2001-01-01'::date)::const"
>
> Would this be hard to implement, or are there any theoretical issues which
> permit this. My thoughts are, that if the user declares something as const,
> although it might not always be const, the database should not worry about
> the complete truth and just assume the statement as const.
>
> Or Is this feature available already, and I have just missed the correct
> keyword?
>
> --
> --
> ---> [EMAIL PROTECTED]
>
[GENERAL] Re: [SQL] Re: SQL Join - MySQL/PostgreSQL difference?
You could try this to see if it makes a difference
UPDATE tblpemdruglink SET monographid = substr(monographid, 1,
length(monographid) - 1)
Michael Fork - CCNA - MCP - A+
Network Support - Toledo Internet Access - Toledo Ohio
On Wed, 7 Feb 2001, Brice Ruth wrote:
> I believe we're getting closer to the problem here. When I run the
> first query below, I get 0 results, which should be wrong. When I run
> the query:
>
> SELECT length(monographid) FROM tblpemdruglink WHERE drugid='DG-5039';
>
> I get 5 returned to me, even though the string is only 4 ('2008').
> However, after doing:
>
> UPDATE tblpemdruglink SET monographid=trim(monographid);
>
> and rerunning the query above, I still get 5, not 4. Is something being
> imported incorrectly by the COPY ... FROM? Any way for me to tell if
> there are other non-printing characters in there?
>
> Thank you all for the continued support on this.
>
> Regards,
> Brice Ruth
>
> Michael Fork wrote:
> >
> > Run the following query:
> >
> > SELECT fdb.versionid, fdb.category, pem.drugid FROM tblfdbmono fdb,
> > tblpemdruglink pem WHERE fdb.monographid = pem.monographid ORDER BY 1, 2,
> > 3;
> >
> > is anything returned? If not, that is your problem (no rows exists with
> > matching monographid's). If information is returned, however, pick an
> > arbitrary row, and plug the data into the following query (you will have
> > a valid where clause then):
> >
> > SELECT fdb.sequencenumber, fdb.sectioncode, fdb.linetext FROM tblfdbmono
> > fdb, tblpemdruglink pem WHERE fdb.monographid = pem.monographid AND
> > fdb.versionid = '<>' AND fdb.category = '<>' AND
> > pem.drugid = '<>'
> >
> > Also, you may want to try qualifying your table names, i.e.:
> >
> > SELECT fdb.sequencenumber, fdb.sectioncode, fdb.linetext FROM tblfdbmono
> > fdb, tblpemdruglink pem WHERE fdb.monographid=pem.monographid AND
> > fdb.versionid='FDB-PE' AND fdb.category='PEM' AND pem.drugid='DG-5039';
> >
> > Michael Fork - CCNA - MCP - A+
> > Network Support - Toledo Internet Access - Toledo Ohio
> >
> > On Tue, 6 Feb 2001, Brice Ruth wrote:
> >
> > > FYI: Here are the table definitions:
> > >
> > > CREATE TABLE TBLPEMDRUGLINK
> > > (
> > > DRUGID VARCHAR(10) NOT NULL,
> > > MONOGRAPHID VARCHAR(10) NOT NULL,
> > > CONSTRAINT PK_TBLPEMDRUGLINK PRIMARY KEY (DRUGID, MONOGRAPHID)
> > > );
> > >
> > > CREATE TABLE TBLFDBMONO
> > > (
> > > VERSIONID VARCHAR(10) NOT NULL,
> > > CATEGORYVARCHAR(10) NOT NULL,
> > > MONOGRAPHID VARCHAR(10) NOT NULL,
> > > SEQUENCENUMBER SMALLINT NOT NULL,
> > > SECTIONCODE VARCHAR(1),
> > > LINETEXTVARCHAR(255),
> > > CONSTRAINT PK_TBLFDBMONO PRIMARY KEY (VERSIONID, CATEGORY,
> > > MONOGRAPHID, SEQUENCENUMBER)
> > > );
> > >
> > > Running the following query:
> > >
> > > Query1: SELECT sequencenumber,sectioncode,linetext
> > > Query1: FROM tblfdbmono fdb, tblpemdruglink pem WHERE
> > > Query1:fdb.monographid=pem.monographid AND
> > > Query1:fdb.versionid='FDB-PE' AND
> > > Query1:fdb.category='PEM' AND
> > > Query1:pem.drugid='DG-5039';
> > >
> > > returns 0 rows.
> > >
> > > However, the following two queries produce results:
> > >
> > > Query2: SELECT * FROM tblpemdruglink WHERE drugid='DG-5039';
> > >
> > > Query3: SELECT * FROM tblfdbmono WHERE
> > > Query3: monographid='2008' AND
> > > Query3: versionid='FDB-PE' AND
> > > Query3: category='PEM';
> > >
> > > To my knowledge, Query1 is the join that should produce the same results
> > > as the manual join represented by queries 2 & 3.
> > >
> > > What's going on?
> > >
> > > -Brice
> > >
> > > Ian Harding wrote:
> > > >
> > > > Brice Ruth wrote:
> > > >
> > > > > Greetings.
> > > > >
> > > > > I'm working with a product provided by a third part that interfaces to
> > > > > data housed in a database of your choice. Previously, my choice was
> > &
[GENERAL] Re: [SQL] Re: SQL Join - MySQL/PostgreSQL difference?
Run the following query: SELECT fdb.versionid, fdb.category, pem.drugid FROM tblfdbmono fdb, tblpemdruglink pem WHERE fdb.monographid = pem.monographid ORDER BY 1, 2, 3; is anything returned? If not, that is your problem (no rows exists with matching monographid's). If information is returned, however, pick an arbitrary row, and plug the data into the following query (you will have a valid where clause then): SELECT fdb.sequencenumber, fdb.sectioncode, fdb.linetext FROM tblfdbmono fdb, tblpemdruglink pem WHERE fdb.monographid = pem.monographid AND fdb.versionid = '<>' AND fdb.category = '<>' AND pem.drugid = '<>' Also, you may want to try qualifying your table names, i.e.: SELECT fdb.sequencenumber, fdb.sectioncode, fdb.linetext FROM tblfdbmono fdb, tblpemdruglink pem WHERE fdb.monographid=pem.monographid AND fdb.versionid='FDB-PE' AND fdb.category='PEM' AND pem.drugid='DG-5039'; Michael Fork - CCNA - MCP - A+ Network Support - Toledo Internet Access - Toledo Ohio On Tue, 6 Feb 2001, Brice Ruth wrote: > FYI: Here are the table definitions: > > CREATE TABLE TBLPEMDRUGLINK > ( > DRUGID VARCHAR(10) NOT NULL, > MONOGRAPHID VARCHAR(10) NOT NULL, > CONSTRAINT PK_TBLPEMDRUGLINK PRIMARY KEY (DRUGID, MONOGRAPHID) > ); > > CREATE TABLE TBLFDBMONO > ( > VERSIONID VARCHAR(10) NOT NULL, > CATEGORYVARCHAR(10) NOT NULL, > MONOGRAPHID VARCHAR(10) NOT NULL, > SEQUENCENUMBER SMALLINT NOT NULL, > SECTIONCODE VARCHAR(1), > LINETEXTVARCHAR(255), > CONSTRAINT PK_TBLFDBMONO PRIMARY KEY (VERSIONID, CATEGORY, > MONOGRAPHID, SEQUENCENUMBER) > ); > > Running the following query: > > Query1: SELECT sequencenumber,sectioncode,linetext > Query1: FROM tblfdbmono fdb, tblpemdruglink pem WHERE > Query1:fdb.monographid=pem.monographid AND > Query1:fdb.versionid='FDB-PE' AND > Query1:fdb.category='PEM' AND > Query1:pem.drugid='DG-5039'; > > returns 0 rows. > > However, the following two queries produce results: > > Query2: SELECT * FROM tblpemdruglink WHERE drugid='DG-5039'; > > Query3: SELECT * FROM tblfdbmono WHERE > Query3: monographid='2008' AND > Query3: versionid='FDB-PE' AND > Query3: category='PEM'; > > To my knowledge, Query1 is the join that should produce the same results > as the manual join represented by queries 2 & 3. > > What's going on? > > -Brice > > Ian Harding wrote: > > > > Brice Ruth wrote: > > > > > Greetings. > > > > > > I'm working with a product provided by a third part that interfaces to > > > data housed in a database of your choice. Previously, my choice was > > > MySQL - apparently it handled certain queries too slowly, so I'm giving > > > PostgreSQL a shot. Here's the query: > > > > > > SELECT > > > a.Number, > > > a.Code, > > > a.Text > > > FROM > > > b, > > > a > > > WHERE > > > (b.Id = a.Id) AND > > > (VersionId = 'key1') AND > > > (Category = 'key2') AND > > > (b.d_Id = 'key3') > > > ORDER BY > > > a.Number; > > > > > > (my apologies: I had to 'mangle' the table/column names because of NDA) > > > > > > So my question is this: would this query operate differently in MySQL > > > than in PostgreSQL? The reason I ask is that this query in MySQL > > > returns results, yet in PostgreSQL it does not. I read a post about > > > PostgreSQL not supporting outer joins, but I don't have enough > > > experience with SQL to determine if this is such a query or not. Please > > > > > > advise. > > > > > > Any help will be (as always) sincerely appreciated. > > > > > > -- > > > Brice Ruth > > > WebProjkt, Inc. > > > VP, Director of Internet Technology > > > http://www.webprojkt.com/ > > > > It should work the same in both. The only thing I notice is that not all > > the field names are qualified with table names or aliases. That can lead > > to ambiguity, but the query would blow up on both databases if that were a > > problem. > > > > Ian > > -- > Brice Ruth > WebProjkt, Inc. > VP, Director of Internet Technology > http://www.webprojkt.com/ >
Re: [SQL] Wierd postgres Problem
Your missing your fields types, i.e.: CREATE TABLE media_received ( comp_id SERIAL NOT NULL, dept_id INT4NOT NULL, date_recTIMESTAMP DEFAULT 'now', that should fix your problem... Michael Fork - CCNA - MCP - A+ Network Support - Toledo Internet Access - Toledo Ohio On Tue, 13 Feb 2001, Najm Hashmi wrote: > Hi All, > I am trying to define a new set of tables an I am getting this strange > syntex problem on date, or timestamp data types. I am also getting error on > not null constranit as well... Postgres is behaving strangely first > definations with not null > > cmdb=# create table media_received ( > cmdb(# comp_id not null, > cmdb(# dept_id not null, > cmdb(# date_rec timestamp default 'now', > cmdb(# units int4 default 0, > cmdb(# media_type varchar(64), > cmdb(# enqued int4 check (enqued= cmdb(# encoded int4 check(encoded= cmdb(# insys int4 check(insys= cmdb(# constraint media_rec_pk primary key(comp_id,dept_id,date_rec), > cmdb(# constraint media_dept_fk foreign key(dept_id) references department, > cmdb(# constraint media_comp_fk foreign key(comp_id) references company > cmdb(# ); > ERROR: parser: parse error at or near "not" > > Second definition without not null and data type timestamp, > cmdb=# create table media_received ( > cmdb(# comp_id, > cmdb(# dept_id, > cmdb(# date_rec timestamp, > cmdb(# units int4 default 0, > cmdb(# media_type varchar(64), > cmdb(# enqued int4 check (enqued= cmdb(# encoded int4 check(encoded= cmdb(# insys int4 check(insys= cmdb(# constraint media_rec_pk primary key(comp_id,dept_id,date_rec), > cmdb(# constraint media_dept_fk foreign key(dept_id) references department, > cmdb(# constraint media_comp_fk foreign key(comp_id) references company > cmdb(# ); > ERROR: parser: parse error at or near "timestamp" > 3rd def with date as data type: > cmdb=# create table media_received ( > cmdb(# comp_id, > cmdb(# dept_id, > cmdb(# date_rec date, > cmdb(# units int4 default 0, > cmdb(# media_type varchar(64), > cmdb(# enqued int4 check (enqued= cmdb(# encoded int4 check(encoded= cmdb(# insys int4 check(insys= cmdb(# constraint media_rec_pk primary key(comp_id,dept_id,date_rec), > cmdb(# constraint media_dept_fk foreign key(dept_id) references department, > cmdb(# constraint media_comp_fk foreign key(comp_id) references company > cmdb(# ); > ERROR: parser: parse error at or near "date" > > is something wrong with my table definition syntex? > All kind of help is appreciated. > Regards, Najm > >
Re: [SQL] plpgsql grief
> Thus your only way to get stuff back from EXECUTE is to save > the results you want to a temporary table (using CREATE > TABLE AS ...), and read them back using a query. Not > high-performance, but it gets the job done. > I believe this statement is incorrect, quoting Michael Ansley <[EMAIL PROTECTED]> from a previous e-mail: create function testfunc (text) returns int4 as ' declare sql varchar; begin sql=''SELECT id AS res2 FROM ''||$1 ; execute sql ; return res2; end; ' language 'plpgsql' ; Please note the AS syntax rather than INTO. This was a misconception on my part, which has obviously lasted longer than I'd hoped ;-) I believe this was the consensus reached on the hacker's list. Michael Fork - CCNA - MCP - A+ Network Support - Toledo Internet Access - Toledo Ohio
Re: [SQL] Help Retrieving Latest Record
SELECT * FROM basket ORDER BY date desc LIMIT 1; and SELECT * FROM basket WHERE fruit = 'apple' ORDER BY date desc LIMIT 1; Michael Fork - CCNA - MCP - A+ Network Support - Toledo Internet Access - Toledo Ohio On Fri, 16 Feb 2001, Steve Meynell wrote: > Hi, I am not sure if this went through the first time so here it is > again. > > > Ok what I am trying to do is select out of the database the latest > record meeting a certain criteria. > > Example: > > Number |Fruit | Date > 15Apples July 20, 1999 > 20OrangesJune 7, 2000 > 13 PearsJan 31, 2000 > 17 Apples April 10, 1999 > Now what I need to do is select the oranges out because the date is the > latest one, something like: > > select * from basket where max(date); > This would yield me: > 20OrangesJune 7, 2000 > > I know this doesn't work but I need something like it. > or something like > > select * from basket where max(date) and fruit='Apples'; > This would yield me: > 15Apples July 20, 1999 > > Thank you in advance, > > > -- > Steve Meynell > Candata Systems > > >
Re: [SQL] distinct date problem
This should work:
SELECT distinct(date_part('year', )) FROM <>;
Michael Fork - CCNA - MCP - A+
Network Support - Toledo Internet Access - Toledo Ohio
On Sun, 25 Feb 2001, Lotus118 wrote:
> Hi,
> I'm having difficulty figuring this one out:
> I have a table with record entries which all have a date attribute, looking
> something like '2000-11-11'. What I want from my data base is a list of
> all the years that are present in this attribute in the relation. So for
> example, if I had a couple of records like:
> 2000-11-05
> 1988-02-09
> 1999-10-06
> 2001-08-08
> 1999-09-27
> 2001-04-04
> I would want the query to return:
> 2000-11-05
> 1988-02-09
> 1999-10-06
> 2001-08-08
> So that I get only the distict years.
> How does one go about this?
> Thanks,
> Lotus
>
Re: [SQL] Optimizing Query
Did you run VACUUM ANALYZE after running CLUSTER? Michael Fork - CCNA - MCP - A+ Network Support - Toledo Internet Access - Toledo Ohio On Mon, 5 Mar 2001, Justin Long wrote: > Ok, now I have another question... it doesn't seem to be accessing the index. > > explain select k.kbid,k.title from knowledge k , kbwords w0 , kbwords w1 > WHERE ((k.kbid=w0.kbid and w0.wordid=42743) AND (k.kbid=w1.kbid and > w1.wordid=85369)) > > NOTICE: QUERY PLAN: > > Merge Join (cost=32339.30..35496.97 rows=19262538 width=24) >-> Merge Join (cost=16530.24..16668.77 rows=233274 width=20) > -> Sort (cost=15809.06..15809.06 rows=8257 width=4) >-> Seq Scan on kbwords w1 (cost=0.00..15271.85 rows=8257 > width=4) > -> Sort (cost=721.18..721.18 rows=2825 width=16) >-> Seq Scan on knowledge k (cost=0.00..559.25 rows=2825 > width=16) >-> Sort (cost=15809.06..15809.06 rows=8257 width=4) > -> Seq Scan on kbwords w0 (cost=0.00..15271.85 rows=8257 width=4) > > Note the sequential scans... there is a wordindex where w0.wordid=42743... > why isn't it doing an indexscan? wouldn't that be more efficient? > > Justin > > > At 04:45 PM 3/5/2001 -0500, you wrote: > >Yes. > > > > > On Mon, Mar 05, 2001 at 04:07:57PM -0500, Bruce Momjian allegedly wrote: > > > > Have you tried VACUUM ANALYZE and CLUSTER? > > > > > > I assume CLUSTER still drops all indexes except the one you're clustering > > > on? > > > > > > Mathijs > > > -- > > > It's not that perl programmers are idiots, it's that the language > > > rewards idiotic behavior in a way that no other language or tool has > > > ever done. > > > Erik Naggum > > > > > > > > >-- > > Bruce Momjian| http://candle.pha.pa.us > > [EMAIL PROTECTED] | (610) 853-3000 > > + If your life is a hard drive, | 830 Blythe Avenue > > + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 > > > > Justin Long Network for Strategic Missions > [EMAIL PROTECTED] 1732 South Park Court > http://www.strategicnetwork.org Chesapeake, VA 23320, USA > Reality Check e-zine: [EMAIL PROTECTED] > > Law: Never retreat. Never surrender. Never cut a deal with a dragon. > Corollary: No armor? Unclean life? Then do not mess in the affairs > of dragons, for you are crunchy and taste good with ketchup. > ---(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] Comparing dates
I am just wildly guessing here, but you initially stated that you queried on '02-03-2001' (Which I read as February 3, 2001 -- and I belive postgres does as well) which returned 60 results, and on '03-03-2001' (March 3, 2001), which returned 70 results. However, that is *not* the query your wrote out, you wrote date_date >= '2001-03-02' (which I would read as March 2, 2001) and date_date <= '2001-03-03' (March 3, 2001) -- which is two entirely different date ranges, and, hence, why you see the seemingly incorrect results. Try this: SELECT count(*) FROM table WHERE date_date = '03-02-2001'::date; SELECT count(*) FROM table WHERE date_date = '03-03-2001'::date; The sum of the above two, should match the count for each of the next two SELECT count(*) FROM table WHERE date_date >= '03-02-2001'::date AND date_date <= '03-03-2001'::date; SELECT count(*) FROM table WHERE date_date >= '03-02-2001'::date AND date_date < '03-04-2001'::date; Michael Fork - CCNA - MCP - A+ Network Support - Toledo Internet Access - Toledo Ohio 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])
[GENERAL] Re: [SQL] Date question
This will do it: mfork=# SELECT to_char(now() + '1 Year'::interval, '-MM-DD'); to_char 2002-03-06 (1 row) Michael Fork - CCNA - MCP - A+ Network Support - Toledo Internet Access - Toledo Ohio On Tue, 6 Mar 2001, Boulat Khakimov wrote: > Hi, > > Im a little bit stuck here. > > Does anyone know how to get date in format '-MM-DD' of a date one > year from now. > So for example today is '2001-03-06' I need to get date 12 months from > now > which will be '2002-03-06' in todays case... > > In mysql I used DATE_ADD(CURDATE(), INTERVAL 12 MONTH) , but that > doesnt work in PG. > > > Regards, > Boulat Khakimov > > > -- > Nothing Like the Sun > ---(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] Re: [GENERAL] MySQLs Describe emulator!
try starting psql with the -E option -- this displays all queries used internally to the screen, i.e.: bash-2.04$ psql -E * QUERY * SELECT usesuper FROM pg_user WHERE usename = 'mfork' * Welcome to psql, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help on internal slash commands \g or terminate with semicolon to execute query \q to quit mfork=# \d test * QUERY * SELECT relhasindex, relkind, relchecks, reltriggers, relhasrules FROM pg_class WHERE relname='test' * * QUERY * SELECT a.attname, t.typname, a.attlen, a.atttypmod, a.attnotnull, a.atthasdef, a.attnum FROM pg_class c, pg_attribute a, pg_type t WHERE c.relname = 'test' AND a.attnum > 0 AND a.attrelid = c.oid AND a.atttypid = t.oid ORDER BY a.attnum * Table "test" Attribute | Type | Modifier ---+--+-- t | text | d | date | So to get the info displayed with \d, execute the query: SELECT a.attname, t.typname, a.attlen, a.atttypmod, a.attnotnull, a.atthasdef, a.attnum FROM pg_class c, pg_attribute a, pg_type t WHERE c.relname = '<>' AND a.attnum > 0 AND a.attrelid = c.oid AND a.atttypid = t.oid ORDER BY a.attnum Michael Fork - CCNA - MCP - A+ Network Support - Toledo Internet Access - Toledo Ohio On Tue, 6 Mar 2001, Boulat Khakimov wrote: > Hi, > > 1) "\d table" can only be used in psql, you cant run a query like that > using libpq for example > > 2) as a programmer I need to be able to find out as much info as > possible about any given field >which is what "describe" for in mySQL. > > Regards, > Boulat Khakimov > > > -- > Nothing Like the Sun > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [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
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] Still don't know how to build this string ?
The following function will convert a given list into a comma delimited string (the argument should be the typ): CREATE FUNCTION dima_list(int4) RETURNS text AS ' DECLARE recrecord; list text; BEGIN list := ''''; FOR rec IN SELECT diam FROM zylinder WHERE typ = $1 list := list || rec.diam || ''; '' END LOOP; RETURN list; END; ' LANGUAGE 'plpgsql'; Note that PL/PGSQL must be installed first, which can be done by typing createlang plpgsql at a shell prompt as a postgres super user. Michael Fork - CCNA - MCP - A+ Network Support - Toledo Internet Access - Toledo Ohio On Mon, 26 Mar 2001 [EMAIL PROTECTED] wrote: > Hello there > > I have still the same problem. Any help would really be appreciated ! > Thanks ... jr > > Is it possible (and I think it is) to do the following : > > I have a table with diameters and types. I need to build a comma separated > string. > > typ diam > 01800 > 01840 > 01870 > 011120 > 02760 > 02780 > 02800 > 02900 > 031200 > 031234 > 031352 > > select diam from zylinder where typ='01' > > should produce the string "800,840,870,1120" > > > > > PFISTER + PARTNER, SYSTEM - ENGINEERING AG > Juerg Rietmann > Grundstrasse 22a > 6343 Rotkreuz > Switzerland > > phone: +4141 790 4040 > fax: +4141 790 2545 > mobile: +4179 211 0315 > > > > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > > http://www.postgresql.org/search.mpl > ---(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] counting distinct rows on more than one column
In 7.0.3, I believe the following would work: SELECT count(distinct(a || b)) FROM t; if subselects in from were supported in 7.0.3 as they are in 7.1, you could do: SELECT count(*) FROM (SELECT DISTINCT a,b FROM t) FROM x Michael Fork - CCNA - MCP - A+ Network Support - Toledo Internet Access - Toledo Ohio On Wed, 28 Mar 2001, Dirk Lutzebaeck wrote: > > Hi, > > on 7.0.3 want to COUNT > > SELECT DISTINCT a,b FROM t; > > I can't find a solution because any combination with > count with more than one column gives syntax errors. > > One solution would be to set a view: > > CREATE VIEW v AS SELECT DISTINCT a,b FROM t; > > and then > > SELECT count(a) FROM v > > but views don't support distinct in v7.0.3 > > Ok I could use a temporary table but my select distinct tends to give > large results. > > Any clues? > > Dirk > > > > ---(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] serial type; race conditions
If you are looking to have every number accounted for, something like this
will work:
INSERT INTO table (serial_col) SELECT nextval('seq_serial_col');
UPDATE table SET foo = 'bar' , ... WHERE serial_col = (SELECT
currval('seq_serial_col'));
then, if the update fails, the number will be accounted for in the
table (Note that you could not use not null on any of the columns).
Michael Fork - CCNA - MCP - A+
Network Support - Toledo Internet Access - Toledo Ohio
On Thu, 29 Mar 2001, postgresql wrote:
> How does currval work if you are not inside a transaction. I have
> been experimenting with inserting into a table that has a sequence.
> If the insert fails (not using a transaction) because of bad client input
> then the next insert gets the proper next number in the sequence.
>
> given sequence 1,2,3,4,5 exists
> insert into table date 1/111/01 (obviously wrong) insert fails...
> try again with good data, insert succeeds and gets number 6 in the
> sequence.
>
> i'm getting what I want. A sequence number that does not increment
> on a failed insert. However, how do I get the assigned sequence
> number with currval when I am not using a transaction? What
> happens when multiple users are inserting at the same time?
>
> I am trying to create a sequence with out any "missing" numbers. If
> there is a failure to insert, and a sequence number is "taken". I want
> the empty row.
>
> Thanks, it is getting clearer
>
> Ted
>
>
> -Original Message-
> From: Bruce Momjian <[EMAIL PROTECTED]>
> To: [EMAIL PROTECTED]
> Date: Mon, 26 Mar 2001 16:47:37 -0500 (EST)
> Subject: Re: [SQL] serial type; race conditions
>
> > > Hi,
> > >
> > > I'm using serial fields to generate IDs for almost all object in my
> > > database. I insert an empty row, get the CURRVAL() of the
> sequence
> > > and then update to that value.
> > >
> > > I had understood (and now, I can't find the reference to back this
> > up)
> > > that serial is implemented in such a way that race conditions
> between
> > > DB connections can't happen.
> > >
> > > Is this true?
> >
> > Safe. See FAQ item. currval is for your backend only.
> >
> > --
> > Bruce Momjian| http://candle.pha.pa.us
> > [EMAIL PROTECTED] | (610) 853-3000
> > + If your life is a hard drive, | 830 Blythe Avenue
> > + Christ can be your backup.| Drexel Hill, Pennsylvania
> > 19026
> >
> > ---(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 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
>
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
RE: [SQL] Re: DB porting questions...
On Wed, 18 Apr 2001, Diehl, Jeffrey wrote: > Querying the entire database is difficult, and very important to me. > I tried to setup a "view of a union" scheme. That isn't supported. > Right now I am using a perl function to rewrite my sql in such a way > that it queries any of the tables I want and coelesces the output. > Aggregate functions even work with this method. To get around this same problem, I created a base table from which all other tables were inherited. Doing this allows for you to query on 1 table, or all data easily. i.e. CREATE TABLE wwwlogs (id INT4, url TEXT); CREATE TABLE wwwlogs_041801 INHERITS (wwlogs); CREATE TABLE wwwlogs_041701 INHERITS (wwlogs); HTH... Michael Fork - CCNA - MCP - A+ Network Support - Toledo Internet Access - Toledo Ohio > Querying the entire database is difficult, and very important to me. I > tried to setup a "view of a union" scheme. That isn't supported. Right now > I am using a perl function to rewrite my sql in such a way that it queries > any of the tables I want and coelesces the output. Aggregate functions even > work with this method. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[SQL] Index Scans
Oops, ignore that first post :) I am unable to get the following query to use an index scan, any suggestions? Query - SELECT username, acctsessiontime, acctinputoctets, acctoutputoctets FROM radacct WHERE ((tstamp >= '06-04-2000 00:00:00-04') AND (tstamp < '06-05-2000 00:00:00-04') AND (acctstatustype = 'Stop')) ORDER BY username ASC; Indexes (I created this many for testing only) --- CREATE INDEX idx_radacct_1 ON radacct (username,acctstatustype,tstamp); CREATE INDEX idx_radacct_2 ON radacct (username,acctstatustype); CREATE INDEX idx_radacct_3 ON radacct (username,tstamp); CREATE INDEX idx_radacct_4 ON radacct (acctstatustype,tstamp); CREATE INDEX idx_radacct_5 ON radacct (tstamp); CREATE INDEX idx_radacct_6 ON radacct (acctstatustype); CREATE INDEX idx_radacct_7 ON radacct (username); CREATE INDEX idx_radacct_8 ON radacct (tstamp,acctstatustype); Here is the output of explain on the queries: radius=# VACUUM ANALYZE; VACUUM radius=# set enable_seqscan=on; SET VARIABLE radius=# explain **INSERT QUERY** NOTICE: QUERY PLAN: Sort (cost=16528.92..16528.92 rows=5588 width=24) -> Seq Scan on radacct (cost=0.00..16181.12 rows=5588 width=24) EXPLAIN radius=# set enable_seqscan=off; SET VARIABLE radius=# explain **INSERT QUERY** NOTICE: QUERY PLAN: Sort (cost=17500.82..17500.82 rows=5588 width=24) -> Index Scan using idx_radacct_8 on radacct (cost=0.00..17153.01 rows=5588 width=24) EXPLAIN Thanks Michael Fork - CCNA - MCP - A+ Network Support - Toledo Internet Access - Toledo Ohio
Re: [SQL] Re: Simple search question
Judging by the PHP Manual -- http://www.php.net/manual/function.pg-getlastoid.php -- it appears that the pg_GetLastOid command returns the last OID for the given result, and therefore would be unaffected by other inserted records. >From the manual: int pg_getlastoid (int result_id) -- can be used to retrieve the Oid assigned to an inserted tuple if the result identifier is used from the last command sent via pg_Exec() and was an SQL INSERT. This function will return a positive integer if there was a valid Oid. It will return -1 if an error occured or the last command sent via pg_Exec() was not an INSERT. Michael Fork - CCNA - MCP - A+ Network Support - Toledo Internet Access - Toledo Ohio On Tue, 13 Jun 2000, Alex wrote: > Hi, > > > What language is your "script" written in? Based on some previous posts, > > I guess you're talking about a PHP script. How did you add the tuple? > > I'd guess with a pg_Exec(connect_id,"INSERT "). From reading the doc, > > I think that you can get the row just inserted by using the pg_fetch_row() > > function, passing it the result from the pg_Exec and asking for row 0. > > OK, I just tried pg_fetch_array() and the above didn't work. > > > I have not yet gotten anything running with PHP (lack of time to "play"), > > For someone who hasn't had time to play, you certainly know alot about php :) > > If you need any examples, I'd be willing to post them, time permitting. > I thought libpq was easy to use, this is very straightforward. I have been > 'playing' with it for a few days, and it is my development tool of choice > now. Of course, this is because of faster development time, don't expect > Yahoo! to replace their cgi with PHP just yet. > > > so I can't test this. If it doesn't work, I'd try using pg_GetLastOid() > > to get the OID of the inserted row. The use the pg_Exec and SELECT > > * WHERE OID=oid-value, followed by pg_fetch_row(). > > Thanks John, and the other person that replied to my email (I know it is a > bit of a stupid question, but in such an unpopulated list, I don't think > there's any reason to post privately). > This leads to another question. If someone adds another row during this, > what will happen? > > Thanks, > Alex >
