[SQL] Need some SQL help
I have a table id int statusint create_dt datetime When users log in and out I insert a row into this table throughout the day. I'm trying though to come up with a querie to tell me and ordering of users as they report in in the morning so i'm trying to say select each user and the earliest time the logged in in a give day, then order that by the create_dt column. Having trouble writing the SQL though ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] timestamp
I think I got it but I'm not sure if it's correct. I put the default as CURRENT_TIMESTAMP but I though I had read that was being taken out and only now was supported? Does anyone know? "Brian Ward" <[EMAIL PROTECTED]> wrote in message anphrb$24se$[EMAIL PROTECTED]">news:anphrb$24se$[EMAIL PROTECTED]... > How do I create a time stamp column in a table that default to the current > time > when a row is inserted? > I tried putting in now() as the default but I have something wrong > with the syntax or something I think > Any one have an example of a table creation script that has a timestamp > column? > > ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] timestamp
How do I create a time stamp column in a table that default to the current time when a row is inserted? I tried putting in now() as the default but I have something wrong with the syntax or something I think Any one have an example of a table creation script that has a timestamp column? ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] sum function
can somebody tell me how to solve the following problem.? when I use the sum function I get an error telling me that the provider returned E_FAIL status. I'm using VB 6 with ADO 2.7 and PSQL 7.2 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Get A Tree from a table
I like to do something like this:(build a tree from relationship) I have a table like this NODES__ ParentIDVarchar(20) ParentType varchar(20) ChildID varchar(20) ChildType varchar(20) __ What in the table 'A1', 'A', 'B1', 'B' 'A2', 'A', 'B2', 'B' 'A1', 'A', 'B3', 'B' 'B1', 'B', 'C1', 'C' 'B1', 'B', 'C2', 'C' 'C1', 'C', 'D1', 'D' 'A1', 'A', 'B4', 'B' 'B1', 'B', 'C5', 'C' -- now I like to get all nodes with 'A1' as root to get the result like this 'A1', 'A', 'B1', 'B' 'B1', 'B', 'C1', 'C' 'C1', 'C', 'D1', 'D' 'B1', 'B', 'C2', 'C' 'B1', 'B', 'C5', 'C' 'A1', 'A', 'B3', 'B' 'A1', 'A', 'B4', 'B' or I just like to get another tree start with 'B1' like this 'B1', 'B', 'C1', 'C' 'C1', 'C', 'D1', 'D' 'B1', 'B', 'C2', 'C' 'B1', 'B', 'C5', 'C' How can i make it by sql , and sql functions Thanks lot and regards. Scott ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] [GENERAL] arrays
On Sun, 29 Sep 2002, Mike Sosteric wrote: > On Sun, 29 Sep 2002, Bruce Momjian wrote: > > Apologies in advance if there is a more appropriate list. > > We are currently developing a database to host some complicated, XMl > layered data. We have chosen postgres because of its ability to store > multidimensional arrays. We feel that using these will allow us to > simplify the database structure considerably by storing some data in > multidimensional arrays. the long and the short of it is that arrays are useful to store data, but should not be used where you need to look up the data in them in a where clause. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] timestamp
On Sun, 2002-10-06 at 09:41, Brian Ward wrote: > How do I create a time stamp column in a table that default to the current > time > when a row is inserted? > I tried putting in now() as the default but I have something wrong > with the syntax or something I think > Any one have an example of a table creation script that has a timestamp > column? "comment_timestamp" timestamp(0) with time zone DEFAULT now() that's a live one from a table I have. -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 972-414-9812 E-Mail: [EMAIL PROTECTED] US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Need some SQL help
On Sun, 6 Oct 2002, Brian Ward wrote: > I have a table > id int > statusint > create_dt datetime > > When users log in and out I insert a row into this table throughout the day. > I'm trying though to come up with a querie to tell me and ordering of users > as they report in in the morning so i'm trying to say > select each user and the earliest time the logged in in a give day, then > order that by the create_dt column. > > Having trouble writing the SQL though select * from table order by id, create_dt If you want this information only for a specific date, when add in a where clause : where create_dt GT 'some date' Change Gt to the greater than character, which isn't working on this keyboard... ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] timestamp
On Sun, 6 Oct 2002, Brian Ward wrote: > How do I create a time stamp column in a table that default to the current > time > when a row is inserted? > I tried putting in now() as the default but I have something wrong > with the syntax or something I think > Any one have an example of a table creation script that has a timestamp > column? When you have an example which does not do what you want, it is better to provide that so we can see how to correct it. I am using : firstlogin timestamp default current_timestamp ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] timestamp
On Sun, 6 Oct 2002, Brian Ward wrote: And Brian, since this *is* a mailing list, it would be polite to use a valid email address. I suggest that you either unsubscribe or fix the email address. Mail to [EMAIL PROTECTED] bounces with the message: '550 Invalid recipient: <[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] enforcing with unique indexes..
Try a rule or a triger that checks for NOT EXISTS ( select 1 from eyp_listing where group_id = New.group_id and userid != New.userid) "Rajesh Kumar Mallah." wrote: > > Hi , > > can anyone tell me how can i enforce below in a table. > I want that no more that one distinct userid exists for a given group_id > in the table. > > ie i want 1 to 1 mapping between group_id and userid so that , there shud not be a > single group_id having more that one kind of userid. > > SELECT group_id from eyp_listing group by group_id having count(distinct userid) >> 1 ; > > always returns empty. > > can it be done with some sort of UNIQUE INDEX? > > Regds > MAllah. > > -- > Rajesh Kumar Mallah, > Project Manager (Development) > Infocom Network Limited, New Delhi > phone: +91(11)6152172 (221) (L) ,9811255597 (M) > > Visit http://www.trade-india.com , > India's Leading B2B eMarketplace. > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Get A Tree from a table
Scott, > NODES__ > ParentIDVarchar(20) > ParentType varchar(20) > ChildID varchar(20) > ChildType varchar(20) This looks difficult... mainly becuase, in your shema, a "parent" node can be its own child or grandchild, resulting in an endless loop. If that was not your intention, you may wish to think of using a different structure. There are two main ways for you to approach this problem in 7.2.x: 1) Buy Joe Celko's "SQL for Smarties" and read up on pure-SQL tree implementations, which he covers in far more depth than I want to here; 2) Explore the tree module in /contrib in your Postgresql source. I understand from Joe Conway that in 7.3, you will have another choice, as Postgres will offer support for Oracle's tree-like "IS CONNECTED BY" expression. Good luck! -Josh Berkus P.S. also, there's some articles on tree structures up at techdocs: http://techdocs.postgresql.org/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Get A Tree from a table
Scott Yaung wrote:
> I like to do something like this:(build a tree from relationship)
[snip]
> How can i make it by sql , and sql functions
> Thanks lot and regards.
There have been quite a few discussions of this topic in the past, so I would
suggest you search through the archives.
In 7.3 (currently in beta) you can use contrib/ltree or contrib/tablefunc.
Here's an example of using the connectby() function from contrib/tablefunc:
CREATE TABLE nodes(parentid varchar(20), parenttype varchar(20), childid
varchar(20), childtype varchar(20));
INSERT INTO nodes values('A1', 'A', 'B1', 'B');
INSERT INTO nodes values('A2', 'A', 'B2', 'B');
INSERT INTO nodes values('A1', 'A', 'B3', 'B');
INSERT INTO nodes values('B1', 'B', 'C1', 'C');
INSERT INTO nodes values('B1', 'B', 'C2', 'C');
INSERT INTO nodes values('C1', 'C', 'D1', 'D');
INSERT INTO nodes values('A1', 'A', 'B4', 'B');
INSERT INTO nodes values('B1', 'B', 'C5', 'C');
test=# SELECT * FROM connectby('nodes','childid','parentid','A1',0,'~') AS
t(childid varchar, parentid varchar, level int, branch text);
childid | parentid | level | branch
-+--+---+-
A1 | | 0 | A1
B1 | A1 | 1 | A1~B1
C1 | B1 | 2 | A1~B1~C1
D1 | C1 | 3 | A1~B1~C1~D1
C2 | B1 | 2 | A1~B1~C2
C5 | B1 | 2 | A1~B1~C5
B3 | A1 | 1 | A1~B3
B4 | A1 | 1 | A1~B4
(8 rows)
test=# SELECT * FROM connectby('nodes','childid','parentid','B1',0,'~') AS
t(childid varchar, parentid varchar, level int, branch text);
childid | parentid | level | branch
-+--+---+--
B1 | | 0 | B1
C1 | B1 | 1 | B1~C1
D1 | C1 | 2 | B1~C1~D1
C2 | B1 | 1 | B1~C2
C5 | B1 | 1 | B1~C5
(5 rows)
HTH,
Joe
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] SQL Error
Hallo,
I have following table:
CREATE TABLE "scheduler_action" (
"scheduler_action_id" numeric(30) NOT NULL,
"action_type" numeric(4) NOT NULL,
"priority" numeric(4) NOT NULL,
"referenced_id"numeric(30) NOT NULL,
"invocation_date" numeric(30) NOT NULL,
"is_done" numeric(1) NOT NULL,
PRIMARY KEY ("scheduler_action_id")
);
My quety look so:
select t0_o.scheduler_action_id from scheduler_action t0_o where
t0_o.is_done = 0 and t0_o.invocation_date <= 1034033214921
And I get following error:
ERROR: Unable to identify an operator '<=' for types 'numeric' and 'double
precision'
You will have to retype this query using an explicit cast
But if my query looks like:
select t0_o.scheduler_action_id from scheduler_action t0_o where
t0_o.is_done = 0 and t0_o.invocation_date <= '1034033214921'
"invocation_date" is numeric with p=30 and s=0.
Best Regards,
Rafal
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
[SQL] foreign key problem
I have two tables: create table table1 ( vers integer, table1_id text NOT NULL, desc text, PRIMARY KEY (rev, table1)id) ); create table table2 ( vers integer, othertble_id text NOT NULL, table1_id text, FOREIGN KEY (rev, othertable_id) REFERENCES othertable ON UPDATE CASCADE ON DELETE CASCADE, FOREIGN KEY (rev, table1_id) REFERENCES table1 ON UPDATE CASCADE ON DELETE CASCADE, PRIMARY KEY (rev, othertable_id, table1_id) ); As you can see, table2 has a foreign key reference to table1 and also includes on delete cascade (and on update cascade). There has been a lot of activity in this database on these tables in particular in the last several days and somehow we've ended up with rows in table2 that have table1_ids that do not exist in table1. How is this possible? I've tried to reproduce this, but haven't been able to yet. This has happened to use several times. Thanks for any help, -- Laurette Cisneros The Database Group (510) 420-3137 NextBus Information Systems, Inc. www.nextbus.com -- It's 10 o'clock... Do you know where your bus is? ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] foreign key problem
Laurette, > How is this possible? I've tried to reproduce this, but haven't been able > to yet. This has happened to use several times. Question 1: Can you experimentally create a record in table1, a matching record in table 2, and then delete the record in table1? It's possible that your "ON DELETE CASCADE" trigger got wiped out somehow. -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] foreign key problem
Laurette, > How is this possible? I've tried to reproduce this, but haven't been able > to yet. This has happened to use several times. Oh, forgot question 2: SELECT version()? -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] foreign key problem
On Mon, 7 Oct 2002, Laurette Cisneros wrote: > > I have two tables: > > create table table1 > ( vers integer, > table1_id text NOT NULL, > desc text, > PRIMARY KEY (rev, table1)id) > ); > > create table table2 > ( vers integer, > othertble_id text NOT NULL, > table1_id text, > FOREIGN KEY (rev, othertable_id) REFERENCES othertable > ON UPDATE CASCADE ON DELETE CASCADE, > FOREIGN KEY (rev, table1_id) REFERENCES table1 > ON UPDATE CASCADE ON DELETE CASCADE, > PRIMARY KEY (rev, othertable_id, table1_id) >); > > As you can see, table2 has a foreign key reference to table1 and also > includes on delete cascade (and on update cascade). > > There has been a lot of activity in this database on these tables in > particular in the last several days and somehow we've ended up with rows in > table2 that have table1_ids that do not exist in table1. > > How is this possible? I've tried to reproduce this, but haven't been able > to yet. This has happened to use several times. The only thing apart from bugs I can think of would be triggers or rules that forced the implicit deletes to have another behavior. It'd be helpful if you can get a representative sequence that reproduces it. ---(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] Probs "compiling" a function
Greetings, Having probs just "compiling" this fn and it comes back with a very stark error msg which is below. I'm using version 7.1.3 I run the script as: \i procedures/zff and get the error msg: psql:procedures/zff:51: ERROR: parser: parse error at or near "b" Line 51 is the line that starts LANGUAGE And the script is: CREATE FUNCTION bsub_insert(varchar,varchar,varchar,varchar,varchar, varchar,varchar,varchar,varchar,varchar,bool,date,int2,int4,varchar,int4) RETURNS integer AS ' DECLARE tmp_bsubRECORD; t_sub_id2 INTEGER; t_class_sun INTEGER; p_band_nm ALIAS FOR $1; BEGIN IF p_band_nm IS NULL THEN RETURN 0; END IF; IF $3 IS NULL THEN RETURN 0; END IF; IF $4 IS NULL THEN RETURN 0; END IF; IF $11 IS NULL THEN RETURN 0; END IF; IF $12 IS NULL THEN RETURN 0; END IF; IF $15 IS NULL THEN RETURN 0; END IF; SELECT INTO tmp_bsub * FROM sub_class WHERE sub_class.class_ds = upper($15); IF NOT FOUND THEN RETURN -1; END IF; t_class_sun := tmp_bsub.class_sun; INSERT INTO sub(band_commnty_nm,street_no,first_nm,surname,home_phone_no, work_phone_no,mobile_phone_no,email_tx,web_addr_tx,sub_comment, voluntr_fl,sub_expiry_dt,card_cnt,receipt_no,sub_type_ind, class_sun,street_sun) VALUES(upper($1),$2,upper($3),upper($4),$5,$6,$7,$8,$9,upper($10),$11, $12,$13,$14,/'B/',t_class_sun,$16); GET DIAGNOSTICS t_sub_id2 = RESULT_OID; SELECT INTO tmp_bsub * FROM sub WHERE oid = t_sub_id2; RETURN tmp_bsub.sub_no; END; ' LANGUAGE 'plpgsql'; Thanks for any help as this is driving me made and I've rewritten it checked all the b's and compared it to other fns I've written and nothing I'm using here I haven't already used. Cheers...Gordon ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Problems Formulating a SELECT
I am trying to formulate a SELECT and could use some suggestions. >From the TABLES below I would like to find ALL contigs which contain the same clones except that one (or more) has read='y' and the other(s) have read='x'. Or stated another way: find all contigs composed of (at least) both (x and y) reads from the same clone. For example: In the data below, the contig '20020630.488.1'(contig_id:13805) is composed of 2 clones (clone_id='12018' and '12019') which are 894027G09.x and 894027G09.y, respectively. Example: TABLE clone 'A''B' clone_id12018 12019 project 894 894 plate 27 27 row G G col 9 9 readx y Table clone_contig: clone_idcontig_id 12018 13805 12019 13805 TABLE contig: contig_id 13805 assembly20020630 ace 488 ver 1 CREATE TABLE clone ( clone_id SERIAL PRIMARY KEY, project INTEGER REFERENCES library(project) NOT NULL, plate INTEGER NOT NULL, row CHAR(1) NOT NULL, col INTEGER NOT NULL, read CHAR(1) NOT NULL, ver INTEGER NOT NULL, seq TEXT NOT NULL, L INTEGER NOT NULL, Qvals TEXT NOT NULL, TL INTEGER NOT NULL, MQAT INTEGER NOT NULL, Qstart INTEGER NOT NULL, Qend INTEGER NOT NULL, gb_id INTEGER REFERENCES gb(gb_id) NULL, unigene BOOLEAN NULL, UNIQUE (project,plate,row,col,read,ver) ); CREATE TABLE contig ( contig_id SERIAL PRIMARY KEY, assembly DATE NOT NULL, ace INTEGER NOT NULL, ver INTEGER NOT NULL, length INTEGER NOT NULL, seq TEXT NOT NULL, UNIQUE (assembly,ace,ver) ); CREATE TABLE clone_contig( clone_id INTEGER REFERENCES clone(clone_id) ON DELETE CASCADE, contig_id INTEGER REFERENCES contig(contig_id) ON DELETE CASCADE, UNIQUE(clone_id,contig_id) ); regards, Charles ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] Debug information
Hallo, I'm working on porting Oracle schema to PostgreSQL. Now I habe the whole schema in PostgreSQL. But there are some problems in our application in some sql queries. I get following errors. ERROR: Unable to identify an operator '<=' for types 'numeric' and 'double precision' You will have to retype this query using an explicit cast Its possible to display the queries in debug print out ftom PostgreSQL with queries which failed? If yep, how? Best Regards, Rafal ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
