[SQL] Timestamp accuracy
Hi,
The user manual said the Timestamp type has a microsecond accuracy, but how can
I display the results of a query with microseconds ?
Without formatting the output has an accuracy of 10^-2 second, and with
formatting it is even worse since the formatting strings do not accept anything
under the second.
SQL example:
> create table test_ts (ts timestamp);
> insert into test_ts values ('2001-2-2 00:00:00.123456789');
> select * from test_ts;
ts
---
2001-02-02 00:00:00.12+01
Florian
Re: [SQL] Suggestion for psql: --file -
On Fri, Feb 02, 2001 at 04:16:05PM +0100, Peter Eisentraut wrote: > Albert REINER writes: ... > > P.S: BTW, the man page (7.0.2) of psql is not very clear: it took me > > some experimentation to find out that you have to do "\set VARIABLE" > > interactively or give "--set VARIABLE=" to set a variable that does > > not take a value. > > Suggested new wording? What about: -v, --variable, --set variable[=[value]] Performs a variable assignment, like the \set internal command. Note that you must separate name and value, if any, by an equal sign on the command line. To unset a variable, leave off the equal sign and the value. To just set a variable without a value, use the equal sign but leave off the value. These assignments are done during a very early state of startup, so variables reserved for internal purposes might get overwritten later. Albert. -- -- Albert Reiner <[EMAIL PROTECTED]> Deutsch * English * Esperanto * Latine --
[SQL] Bug with rules in 7.0.3?
Hi there... I've spotted something weird in 7.0.3 with rules. By now I've
realised I probably need to use a trigger to do what I have in mind, but
even so, there no way I can explain the behaviour I am getting with a
rule.
Given this SQL script:
CREATE TABLE menu (
menu_id SERIAL PRIMARY KEY,
nameTEXT,
price integer
);
INSERT INTO menu(name, price) VALUES ('Beer', 5);
INSERT INTO menu(name, price) VALUES ('Vodka', 10);
INSERT INTO menu(name, price) VALUES ('Scotch', 8);
CREATE TABLE orders (
order_idSERIAL PRIMARY KEY,
menu_id INTEGER REFERENCES menu,
price INTEGER NOT NULL DEFAULT -1
);
CREATE RULE fix_order_price AS
ON INSERT TO orders
DO
UPDATE orders
SET price = M.price
FROM menu M
WHERE M.menu_id = new.menu_id
AND new.price = -1;
INSERT INTO orders (menu_id) VALUES (2);
SELECT * FROM orders;
Here's what happens:
% createdb buggy
CREATE DATABASE
% psql buggy < ~/pg.bug
NOTICE: CREATE TABLE will create implicit sequence 'menu_menu_id_seq' for SERIAL
column 'menu.menu_id'
NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'menu_pkey' for table
'menu'
CREATE
INSERT 259680 1
INSERT 259681 1
INSERT 259682 1
NOTICE: CREATE TABLE will create implicit sequence 'orders_order_id_seq' for SERIAL
column 'orders.order_id'
NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'orders_pkey' for table
'orders'
NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
CREATE
CREATE 259722 1
INSERT 0 3
order_id | menu_id | price
--+-+---
1 | 2 |-1
2 | 2 |-1
3 | 2 |-1
(3 rows)
How the heck can one insert and update generate three rows?
--
Tod McQuillin
Re: [SQL] Bug with rules in 7.0.3?
Tod McQuillin <[EMAIL PROTECTED]> writes: > How the heck can one insert and update generate three rows? Looks like a rule rewriter bug to me. It seems to be fixed in 7.1; I get regression=# SELECT * FROM orders; order_id | menu_id | price --+-+--- 1 | 2 |-1 (1 row) which is the correct result given that rules are executed before the original query. (Which is why you need a trigger for this...) regards, tom lane
Re: [SQL] Suggestion for psql: --file -
Albert REINER writes: > > Suggested new wording? > > What about: Works for me. Thanks. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
[SQL] Archival of Live database to Historical database
Hello all I have been watching with some interest the "problem" of not being able to work with more than one database. My muse is aimed at creating an incremental backup for a very low usage database - say several hundred transactions per day Back in my days of DOS about v3 I used to use the >> pipe which appends to an existing file. If the pgsql command 'COPY TO' was extended or a similar command created to allow it, transactions could be written to a delimited text file/s held on a dedicated drive. For my requirements, such a facility, coupled with regular backups, would give me all the security I require. It would also provide a partial solution for Stef Telford. Such a solution would require regular maintenance, however, the period depending on the rate of growth of the files and the frequency of backups. Definitely for the smaller system. My knowledge of computer programming is very limited, so that I cannot take any such idea any further. regards Max Wood From: "Stef Telford" <[EMAIL PROTECTED]> > Hello everyone, > I have hit on a limit in my knowledge and i am looking for > some guidance. Currently I have two seperate databases, one for > live data, the other for historical data. The only difference really > being that the historical data has a Serial in it so that the tables > can keep more than one 'version history'. > > What i would like to do, is after my insert transaction to the > live database, i would like the information also transferred to the > historical one. Now. I can do this via perl (and i have been doing it > this way) and using two database handles. This is rather clumsy and > I know there must be a 'better' or more 'elegant' solution. Not really (AFAIK) - this crops up fairly regularly but there's no way to do a cross-database query. You could use rules/triggers to set a "dirty" flag for each record that needs copying - but it sounds like you're already doing that. If you wanted things to be more "real-time" you could look at LISTEN/NOTIFY - Richard Huxton
Re: [SQL] PL/pgSQL: possible parsing or documentation bug?
"Albert REINER" <[EMAIL PROTECTED]> writes:
> create Function IdOfPossiblyNewAuthor(text) returns int4 as '
> declare
> id int4;
> begin
> select id into id from author where name = $1;
> raise notice ''ID found: %'', id;
> if id is null then
> insert into author (name) values ($1);
> select currval(''author_id_seq'') into id;
> raise debug ''Author inserted. ID: %'', id;
> end if;
> return id;
> end;
> ' language 'plpgsql' with (IsCachable);
> Logically it is clear which "id" should be parsed as the variable,
> which as author.id,
No, it is not so clear. Consider the following:
declare
x int4;
y int4;
begin
x := ...;
select x + f1 into y from tab1 where ...;
The intent here is clearly to find a value tab1.f1 in tab1 and then
add the local variable x to form a value for the local variable y.
In general plpgsql will try to match an unqualified name to a variable
before it will consider whether it might be a field name. If you don't
want that, qualify the field name:
select author.id into id from author where name = $1;
Feel free to submit documentation updates to make this clearer...
regards, tom lane
Re: [SQL] Bug with rules in 7.0.3?
On Sat, 3 Feb 2001, Tom Lane wrote: > I get > > regression=# SELECT * FROM orders; > order_id | menu_id | price > --+-+--- > 1 | 2 |-1 > (1 row) > > which is the correct result given that rules are executed before the > original query. (Which is why you need a trigger for this...) OK. I think that Bruce's book is inaccurate then. In section D.19 (p. 299), also reproduced on the web at http://www.postgresql.org/docs/aw_pgsql_book/node490.html, this example is given: All new employees must make 5,000 or less: CREATE RULE example_5 AS ON INSERT TO emp WHERE new.salary > 5000 DO UPDATE emp SET salary = 5000 WHERE emp.oid = new.oid; -- Tod McQuillin
[SQL] Fw: C function for use from PLpgSQL trigger
Hello all, I posted this (see below) Friday to the interfaces list with no response. Does anyone know if what I'm trying to do is possible, or should I just write the entire thing in a C function trigger? The latter would be unfortunate because I think it would be nice to be able to extend PLpgSQL using C functions like this. Anyway, any help or advice will be much appreciated! Thanks, Joe > Hi, > > I'm trying to create a C function that I can call from within a PLpgSQL > trigger function which will return a list of all the values in the NEW > record formatted suitably for an insert statement. I can't come up with a > way to do this directly from within PLpgSQL (i.e. iterate through an > arbitrary number of NEW.attribute). > > Can anyone tell me how I can either pass the NEW record to the C function > (this produces an error message 'NEW used in a non-rule query') or gain > access to the trigger tuple from within my C function? It seems that > although PLpgSQL is called as a trigger, the C function is called as a > regular function (makes sense) and thus has no access to the trigger tuple > (my problem). > > Any help or guidance is greatly appreciated! > > Thanks, > > Joe Conway >
