Re: [GENERAL] last update time of a table
pg wrote: I have some pulldown menus in a VB app which extract data from a remote site with slow connection. And the data in those tables for pulldowns changes rarely. So if the pulldown has to extract the data and transmit it thru slow connection, the pulldown will take a few seconds to be in action, which is a little bit annoying, especially if the data is the same as in the array of client. Probably you'll do it better storing a local cached copy of the pulldown data (in the VB side) and having a background process to refresh it only when the server notifies that it has changed. hth cl. ---(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: [GENERAL] user defined variable per session
I need to create user defined variable in every database session. In Sybase ASA is equivalent: CREATE VARIABLE name TYPE; I need use this in views. Is it possible??? What about test= \set myvar 5 test= select :myvar; ?column? -- 5 (1 row) hth cl. ---(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
[GENERAL] marking record origin in views
Hello list I have two tables with identical structure, one holds 'correct' data (from an application standpoint) and the other has data 'in error'. Anyway, I need sometimes to query both tables at the same time, so I constructed an elementary view create view v1 as select * from t1 union select * from t2; But I would like to have an extra field (in the view) with the table name of the particular record source. How can this be done? thanks cl. ---(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: [GENERAL] rounding timestamps
Joe Conway wrote:
Is this what you wanted?
regression=# select to_char(timestamp(0) '2003-10-24
15:30:59.999','MMDDHH24MISS');
to_char
20031024153100
(1 row)
Yes! Exactly!
See:
http://www.postgresql.org/docs/current/static/datatype-datetime.html
Shame on me. I've must read that page more times than I can remember. I
never realized that I could use the precision qualifier to do a cast (and
round):
comp_20031117= create table ts (ts timestamp without time zone);
CREATE TABLE
comp_20031117= insert into ts values ('2003-10-24 15:30:59.999');
INSERT 406299 1
comp_20031117= select * from ts;
ts
-
2003-10-24 15:30:59.999
(1 row)
comp_20031117= select to_char (ts ::timestamp(0), 'MMDDHH24MISS') from
ts;
to_char
20031024153100
(1 row)
thank you very much Joe
cl.
PS. Alvaro, your solution was what I was implementing already, but yes it's
ugly, that's why I gave it a second round. Thanks anyway.
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] slicing records
Jan Wieck wrote: Oh, it's one of these _don't ask me why_ things ... well, then what is the target legacy system? ... hehe. Of course, don't ask me why is my own way of saying I don't know why! :-) If there is a total upper maximum for the object length and it's not way too obscenely large, then you can create a view that get's you this: [snip] See attached sample script. I didn't know if you really wanted this fancy whole|start|middle|end string or if that was supposed to be the data of the fragment itself. Please notice that the view in the sample is configured for data sized up to 100 characters. No, the destination system actually needs the labels as a flag of the fragment position or if it's a fragment at all (i.e. not 'whole'). Actually, your view/functions seem to almost fit my original need, I think they'll just need minor touch up. Thanks a lot Jan, really nice code. cheers cl. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] Client authentication
Hello We need to deny access to the database for regular users, while allowing access to admins and a variety of application scripts. If we use passwords, everything is fine while interactive, but could not devise a way for scripts to handle them. We also tried to set up ident authentication, but Solaris is having a hard time managing this. Any hints as to how could this be done? TIA cl. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] psql prompt
Hello The command prompt for psql defaults to the database name, but is there a way to change it to some other string/value? TIA, cl. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] absolute value fro timestamps
Bruce Momjian wrote: Why would you want an abolute value of a negative interval? Because I'm trying to match pairs of records that satisfy certain criteria, one of which is that both records have a timestamp that *may* be slightly offset between them, so I substract the two and the result must be no greater than the allowed offset. I don't know which record has the greater timestamp, so I don't know the sign of the substraction in advance. This works: test= select -(interval '-1'); ?column? -- 01:00:00 (1 row) so I suppose you could create a function or CASE statement to get the absolute value. In the meantime I implemented it the following way: \set maxoffset 4 select ... where abs(extract(epoch from age(m1.ts, m2.ts))) :maxoffset ... Which I think is more compact. Anyway, it would be nice to be able to write directly abs(age(m1.ts, m2.ts)) IMHO. thanks cl. ---(end of broadcast)--- TIP 8: explain analyze is your friend
[GENERAL] adding SERIAL to a table
Hello
Now perhaps thisis a bit dumb,
but...
I just populated a new table via \copy.
After that, I realize that perhaps is a good thing to have a row identifier in
it, so I try
clapidus= alter table tickets add
column rid serial;NOTICE: ALTER TABLE will create implicit sequence
"tickets_rid_seq" for SERIAL column "tickets.rid"ERROR: adding columns
with defaults is not implemented
So my next guess is to definea test
tablefrom scratch, this time with the serial field in place. Next I try
the \copy:
clapidus= create table test(rid serial,
col_a text);NOTICE: CREATE TABLE will create implicit sequence
"test_rid_seq" for SERIAL column "test.rid"CREATE TABLEclapidus= \d
test
Table "test"Attribute | Type
|
Modifier---+-+---rid
| integer | not null default
nextval('public.test_rid_seq'::text)col_a |
text |
clapidus= \copy test from
stdin23 a
record45 another record\.clapidus=
select * from test ;rid |
col_a-+ 23 |a record 45 | another
record(2 rows)
Now the first character from stdin is a
tab, in a try to let thesequence come into action:
clapidus= \copy test from
stdin still another
record\.ERROR: invalid input syntax for integer: ""PQendcopy:
resetting connection
Grrr. Third attempt:
clapidus= \copy test from stdin with
null as 'NULL'NULL still another one\.ERROR:
null value for attribute "rid" violates NOT NULL constraintPQendcopy:
resetting connection
So? Is there a way to add the sequence to
an existing table?
Or, alternatively, is there a way to issue
a \copy command while letting the sequence fill in the serial
field?
thanks in advance
cl.
Re: [GENERAL] Commercial postgresql
Lamar Owen wrote: Nitpik: that should be 24/7/52, since there aren't 365 weeks in a year. Oh, great. It's just that 7*52 = 364. That leaves us with a full day to idle and still honor the SLA, right? Sorry, couldn't resist :) cl. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] [ADMIN] a problem
Robert Treat wrote: On Fri, 2003-08-22 at 05:01, sharvari N wrote: hello How do i change the definition of a column? one of the columns width is not sufficient to store the data. I want to change the width. how to do that in postgres? I tried doing alter table + change/modify. both of them doesn't work in postgres. you have to hack the system tables for this, though i can't seem to recall the exact field name this morning. the query is certainly in the archives as i've answered this one before, if you were too lazy to look it up i guess i will be too ;-) OK, Sharvari, it was me who asked this same thing a couple of weeks ago, so I'm transcribing here the fine advice from our guru Tom: -- Claudio Lapidus [EMAIL PROTECTED] writes: I need to modify a column which is currently defined as varchar(30) to varchar(40). I can see from the docs (and trial) that I cannot directly alter a column this way, so I intend to do the following: ALTER TABLE t1 ADD COLUMN duplicate varchar(40); UPDATE t1 SET duplicate=original; ALTER TABLE t1 DROP COLUMN original; ALTER TABLE t1 RENAME duplicate TO original; But I'm worried about messing up things if I run this queries while the database is live, i.e. there are other processes writing to the table. As you should be. if I enclose the above into a BEGIN/COMMIT pair, would you say it is safe to run concurrently with other transactions? Yes, because the first ALTER will take an exclusive lock on table t1, which will be held through the rest of the transaction. So it will be safe a fortiori. However, if the table is large you may regret holding an exclusive lock for all the time it takes to do that UPDATE. Personally, being a database hacker, I would solve this problem with a quick modification of the atttypmod field that expresses the column length: UPDATE pg_attribute SET atttypmod = 40 + 4 -- +4 for varchar overhead WHERE attrelid = (SELECT oid FROM pg_class WHERE relname = 't1') AND attname = 'original'; Since you are increasing the length limit, and it's varchar not char, there is nothing that need be done to the data itself, so this is sufficient. I would strongly recommend practicing on a scratch database until you are sure you've got the hang of this ;-). Also you might want to do a BEGIN first, and not COMMIT until you're sure \d display of the table looks right. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings _ The new MSN 8: advanced junk mail protection and 2 months FREE* http://join.msn.com/?page=features/junkmail ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Buglist
Bruno Wolff III wote: On Fri, Aug 22, 2003 at 12:17:41 +0530, Shridhar Daithankar [EMAIL PROTECTED] wrote: Idea of autovacuum is to reduce load on vacuum full. If you set shared_buffers higher and FSM properly for he update/delete load, autovacuum is expected to catch most of the dead tuples in shared cache only. If it is successful in doubling the frequency on vacuum full, that's a big win, isn't it? If you run a normal vacuum often enough, you shouldn't need to regularly run vacuum full. Hmm, here we have a certain table, sort of FIFO, rows get inserted all the time, lay there for a couple of hours and get deleted the other end around. We run normal vacuum almost constantly, but the table keeps growing. We had to implement a 'vacuum full' once a week to keep it under control. cl. ---(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: [GENERAL] Knowing how many records I just inserted
How can I get that back out to bash, if I'm doing psql scripting? An env. variable wouldn't work, since it would go away when psql terminates. Capture the stdout of the psql command and pipe it through cut in order to get only the third word of output. your_shell_variable=`psql -c 'insert into t1 select attrs from t2' | cut -d' ' -f3` hth, cl. TIA -- +-+ | Ron Johnson, Jr.Home: [EMAIL PROTECTED] | | Jefferson, LA USA | | | | I'm not a vegetarian because I love animals, I'm a vegetarian | | because I hate vegetables!| |unknown | +-+ ---(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 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] 7.4dev or beta version
Hello I'd like to give a try at the new version, referred in many places as 7.4dev but I'm unable to find a version named this way for download. Is it the same as dev/postgresql-snapshot.tar.gz ? Or is somewhere already a 7.4beta? When will the 7.4beta become available? cl. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Convert TimeStamp to Date
template1=# insert into t values ('1993-08-10 17:48:41');
INSERT 16980 1
So we are talking about August 10th, right?
template1=# select f1, date(f1), f1::date, cast(f1 as date) from t;
f1 |date| f1 | f1
-+++
1993-08-10 17:48:41 | 1993-08-11 | 1993-08-11 | 1993-08-11
(1 row)
Here all casts give Aug. 11th, same as on my 7.3.2 (tested right now). This
is one day *more* than expected, not 'the previous date' as the original
poster said. Perhaps some sort of rounding here?
cl.
---(end of broadcast)---
TIP 8: explain analyze is your friend
