[GENERAL] Better way to process boolean query result in shell-like situations?
Hi, I regularly run into the problem that I want to query a PostgreSQL database in a script/program and depending on a boolean result do one thing or the other. A typical example would be a Puppet Exec that creates a user only if it does not exist yet. But unfortunately psql always returns with the exit code 0 if the query was run without errors. In a shell script I can use a query that returns an empty string for failure and something else for success and then test that à la: | if [ -n "$(psql -Atc "[…]") ]; then echo Success.; fi but for example in Puppet this requires putting around '/bin/bash -c "[…]"' with yet another level of quoting. The best idea I had so far was to cause a runtime error (here with the logic reversed: If the user exists, psql re- turns failure, otherwise success): | [tim@passepartout ~]$ psql -c "SELECT usename::INT FROM pg_user WHERE usename = 'tim';"; echo $? | FEHLER: ungültige Eingabesyntax für ganze Zahl: »tim« | 1 | [tim@passepartout ~]$ psql -c "SELECT usename::INT FROM pg_user WHERE usename = 'does-not-exist';"; echo $? | usename | - | (0 rows) | 0 | [tim@passepartout ~]$ But this (in theory) could fail if usename could be con- verted to a number, and for example 'a'::INT will fail al- ways. Are there better ways? The environment I am most interested in is 9.3 on Ubuntu Trusty. Tim P. S.: I /can/ write providers or inline templates for Pup- pet in Ruby to deal with these questions; but here I am only looking for a solution that is more "univer- sal" and relies solely on psql or another utility that is already installed. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] PostgreSQL 8.4 packages for Fedora 11?
Hi, I'm in the process of upgrading a Fedora 10 box to 11. So far, Fedora's repository carries only 8.3.7. I am a bit he- sitant to use the yum.pgsqlrpms.org repository's packages as I like to keep the number of repositories as small as possi- ble. So: - Is there any ETA for official Fedora 11 packages? - If I'd use yum.pgsqlrpms.org's packages, can I later re- place them with Fedora packages without any surprises? Thanks in advance, Tim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PostgreSQL 8.4 packages for Fedora 11?
Tom Lane wrote: >> I'm in the process of upgrading a Fedora 10 box to 11. So >> far, Fedora's repository carries only 8.3.7. I am a bit he- >> sitant to use the yum.pgsqlrpms.org repository's packages as >> I like to keep the number of repositories as small as possi- >> ble. So: >> - Is there any ETA for official Fedora 11 packages? > Yes: never. The Fedora package series for F-11 will be 8.3.x and > nothing but. F-12 will have 8.4.x. That's a pity. >> - If I'd use yum.pgsqlrpms.org's packages, can I later re- >> place them with Fedora packages without any surprises? > Well, that would be going back a major PG version, which is just as > problematic as going forward a major version, in fact more so. > If you don't mind a dump/initdb/reload cycle then you can try it, > but there's no guarantee an 8.4 pg_dump file will load into 8.3 > without some manual editing. No, I meant replacing yum.pgsqlrpms.org's packages with sometime-in-the-future-released Fedora 11 PostgreSQL 8.*4* packages. With the situation at hand, I will install the yum.pgsqlrpms.org repository and, come Fedora 12, decide then how to proceed. Thanks, Tim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Field name problem
Alan Chandler wrote: > [...] > So I tried to load the same database on my home machine > using pg_restore. This runs version 8.4.0, and it failed > loading the restore with and error at this field "over" > because, I presume, its a reserved word in SQL. > Is there anyway, I can access any of my other backups and > load the data in a current database? a) Install an old PostgreSQL server, restore to it, change the attribute name, dump from it, restore to a new ser- ver. b) Restore the backup to an SQL file, load it into a suit- able editor, change (very carefully) "over" to the new attribute name and restore that file to a new server. Tim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Looping through string constants
David Kerr wrote:
> I'd like to loop through a group of constant string values using plpgsql
> The best analog i can think of would be in a shell script
> #!/usr/bin/ksh
> for a in a b c d e; do
> echo $a
> done
> ./a.ksh
> a
> b
> c
> d
> e
> Is there some tricky way I can make that happen in postgres?
> (I don't want to put the values in a table =) that would be too easy!)
If you do not want to use arrays, you can always use:
| FOR r IN SELECT a FROM (VALUES ('a'), ('b'), ('c'), ('d'), ('e')) AS t(a)
ORDER BY a LOOP
| RAISE NOTICE '%', r.a;
| END LOOP;
Tim
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] bytea corruption?
Nathan Jahnke wrote:
> [...]
> my $encodeddata = $data;
> $encodeddata =~ s!(\\|[^ -~])!sprintf("\\%03o",ord($1))!ge; #prepare
> data for bytea column storage
> [...]
> my $insert_sth = $connection->prepare('insert into testtable (data)
> values (?) returning id');
> $insert_sth->execute($encodeddata);
> my $ref = $insert_sth->fetchrow_hashref;
> my $id = $ref->{id};
> my $getall_sth = $connection->prepare('select * from testtable where id=?');
> $getall_sth->execute($id);
> my $newref = $getall_sth->fetchrow_hashref;
> my $newdata = $newref->{data};
> $newdata =~ s!\\(?:\\|(\d{3}))!$1 ? chr(oct($1)) : "\\"!ge; #decode
> bytea column storage format
> [...]
> hash of data changes ... if you uncomment the $data = '123abc' line
> you can see that it works with those six bytes fine, and it also works
> with most other binary data, just not this binary data. any insight
> would be appreciated. thanks.
Why do you encode/decode the data in your own application a
second time? It is already encoded by DBD::Pg.
Tim
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Query and the number of row result
bilal ghayyad wrote: > I am talking in case I am writing a script for a function, > and I need to know the number of the returned rows of the > query, then I will do IF statement based on that number, > how? > [...] Presuming that you are talking about a function written in PL/pgSQL, you will have to count them yourself or issue a second query "SELECT COUNT(*) FROM [...]". For the special case that you want to find out whether no row at all was found, you can look at "IF (NOT) FOUND". Tim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] maximum count of contiguous years
gorsa wrote: > [...] > is there a select statement containing 'AND award_year BETWEEN 1994 > AND 2002' that could generate the following? > scholar_idconsistent_yrs > 1 4 > 2 5 > 3 2 You could either do some wild fancy query where you parti- tion the data by scholar_id, then by award_year, then filter on the condition that the sum of award_year and RANK() (?) less one equals the current award_year, find the maximum of those, ... ... or you could just write a short function in your ap- plication (or a set-returning PL/pgSQL function if your ap- plication is dumb). Tim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] comment on constraint
Andreas Kretschmer wrote: > There is a question in the german pg-forum: > It is possible to add a comment on a constraint, but \dd doesn't display > that comment. There is also a old question in this mailing-list without > an answer: > http://archives.postgresql.org/pgsql-general/2003-07/msg01448.php > I think, this is a bug, isn't it? Why? The man page for psql clearly says: |\dd[S] [ pattern ] | Shows the descriptions of objects matching the | pattern, or of all visible objects if no argu- | ment is given. But in either case, only objects | that have a description are listed. By default, | only user-created objects are shown; supply a | pattern or the S modifier to include system ob- | jects. ‘‘Object’’ covers aggregates, functions, | operators, types, relations (tables, views, in- ^^^ | dexes, sequences), large objects, rules, and | triggers. [...] ^ So no comments are shown for constraints or table columns or ... Tim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] where clauses and selects
Scott Frankel wrote: > Is it possible to perform a select in the where clause of a statement? > I have a situation where I've got one arm tied behind my > back: I can only have a single table in the select and from > clauses, but the where clause appears to be freed from that > restriction. > Given a statement as follows: > SELECT foo.foo_id, foo.name > FROM foo, bar > WHERE foo.bar_id = bar.bar_id > AND bar.name = 'martini'; > I'm looking for a way to recast it so that the select and > from clauses refer to a single table and the join > referencing the second table occurs in the where clause. > [...] Something along the lines of: | SELECT foo.foo_id, foo.name FROM foo WHERE foo.bar_id = (SELECT bar.bar_id FROM bar WHERE bar.name = 'martini'); should do the trick. > I've explored the "where exists" clause, but it's not > supported by the application toolkit I'm using. AFAIK, I've > only got access to where ... Dump it. Seriously. There are so many fine things you can do with a full-fledged database - you certainly do not want to be restricted in this regard. Tim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] UPDATE statement with syntax error doesn't raise a warning?
Mirko Pace wrote: > I've ran an update statement like this (obviously wrong, I know!): > update my_table > set boolean_field = true AND > my_notes = 'something' > where id in >(select id from my_table order by random() limit 4000); > in my psql client and I had a "UPDATE 4000" result but, correctly, anything > was changed in my_table. > So... why pg didn't raise a warning about syntax issue? Because there is no syntax error? "TRUE AND my_notes = 'something'" is a valid expression (and equivalent to "my_notes = 'something'"). Tim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Procedure for feature requests?
Hi, suppose I thought that PostgreSQL would benefit greatly from a "generate_series(DATE, DATE[, INT]) RETURNS DATE" function - where do I suggest such a thing? Here on -general? On -hackers? Directly edit http://wiki.postgresql.org/wiki/Todo>? Suppose the feature request was not a trivial one, but maybe a "DEPENDS ON " clause for "CREATE FUNCTION" to allow PostgreSQL to deny requests to drop a table/view/ function that is needed by a function - where would I pro- pose that? TIA, Tim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Procedure for feature requests?
Alvaro Herrera wrote: > [...] >> Suppose the feature request was not a trivial one, but >> maybe a "DEPENDS ON " clause for "CREATE FUNCTION" >> to allow PostgreSQL to deny requests to drop a table/view/ >> function that is needed by a function - where would I pro- >> pose that? > On -hackers, just like any other feature request, trivial or not. Thanks. Any particular form? A quick glance at the archives did not reveal any feature requests that were not accompa- nied by a patch :-). Tim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Procedure for feature requests?
Sam Mason wrote: >> suppose I thought that PostgreSQL would benefit greatly from >> a "generate_series(DATE, DATE[, INT]) RETURNS DATE" function > 8.4 has a generate_series(timestamp,timestamp,interval) which would seem > to be a bit more flexible than you want. Yes, I know :-). But as "generate_series(A, B, C)" can also be written as "A + generate_series(0, (C - B) / C) * C" (or something "flexible" like that :-)), a "generate_series(DATE, DATE)" would inter alia get rid off the need to cast the result from TIMESTAMP to DATE and to explicitly specify "'1 day'". Just a small, trivial enhance- ment for a popular use case :-). Tim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Procedure for feature requests?
Sam Mason wrote: >> > 8.4 has a generate_series(timestamp,timestamp,interval) which would seem >> > to be a bit more flexible than you want. >> Yes, I know :-). But as "generate_series(A, B, C)" can also >> be written as "A + generate_series(0, (C - B) / C) * C" (or >> something "flexible" like that :-)), a > For things as complicated as timestamps I'm not sure if this is such a > trivial transform. If you can figure out the limit then it seems easy, > though I'm not sure how you'd do that. What limit? >> "generate_series(DATE, DATE)" would inter alia get rid off >> the need to cast the result from TIMESTAMP to DATE and to >> explicitly specify "'1 day'". Just a small, trivial enhance- >> ment for a popular use case :-). > Interesting, I tend to aim for maximum expressiveness not ease of > expressiveness. It would be somewhat easy to add the above if you want > though: > CREATE FUNCTION generate_series(date,date) > RETURNS SETOF date > IMMUTABLE LANGUAGE sql AS $$ > SELECT generate_series($1::timestamp,$2::timestamp,interval '1 > day')::date; > $$; > or I suppose you could use the integer series generation: > SELECT $1 + generate_series(0,$2 - $1); If I didn't know that, I would not have characterized the feature request as "trivial". > Hum, now I'll have to see which is "better". > That second version seems to be slightly quicker (20 to 30%, for ranges > from a year up to a century respectively) so you may prefer it, but the > difference is going to be in the noise for any query I've ever used > generate_series for. Which of my mails made you think that I was not satisfied with PostgreSQL's current performance? "generate_series(DATE, DATE)" would just be syntactic sugar, and I like sweets. Tim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] What's wrong with this regexp?
Nick wrote: > SELECT TRUE WHERE '/steps/?step=10' ~ '^\/steps\/\?step=10$' > Im guessing its an escape issue, but where am I going wrong? You need to double-escape the question mark: Once for the string literal, once for the regular expression (and you do not need to escape the slashes). This gives: | tim=# SELECT TRUE WHERE '/steps/?step=10' ~ E'^/steps/\\?step=10$'; | bool | -- | t | (1 Zeile) | tim=# Tim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Query to find contiguous ranges on a column
Peter Hunsberger wrote:
> [...]
> I have one solution that joins the table against itself and does
> (among other things) a subselect looking "not exists col +1" and "not
> exists col -1" on the two instances of the table to find the start and
> end. This is, as you might guess, is not very efficient (my actual
> data is some 6 million+ rows) and I'm guessing there has to be
> something more efficient with windowing or possibly grouping on min
> and max (though I can't see how to make sure they are part of a
> contiguous set). Anyone have any ideas?
You can either use a PL/pgSQL function ("SETOF TEXT" just
for the convenience of the example):
| CREATE FUNCTION SummarizeRanges () RETURNS SETOF TEXT AS $$
| DECLARE
| CurrentFirst INT;
| CurrentLast INT;
| CurrentRecord RECORD;
| BEGIN
| FOR CurrentRecord IN SELECT col FROM t ORDER BY col LOOP
| IF CurrentFirst IS NULL THEN
| CurrentFirst := CurrentRecord.col;
| CurrentLast := CurrentRecord.col;
| ELSIF CurrentRecord.col = CurrentLast + 1 THEN
| CurrentLast := CurrentRecord.col;
| ELSE
| RETURN NEXT CurrentFirst || ', ' || CurrentLast;
| CurrentFirst := CurrentRecord.col;
| CurrentLast := CurrentRecord.col;
| END IF;
| END LOOP;
| IF CurrentFirst IS NOT NULL THEN
| RETURN NEXT CurrentFirst || ', ' || CurrentLast;
| END IF;
| RETURN;
| END;
| $$ LANGUAGE plpgsql;
or a recursive query (which I always find very hard to com-
prehend):
| WITH RECURSIVE RecCols (LeftBoundary, Value) AS
| (SELECT col, col FROM t WHERE (col - 1) NOT IN (SELECT col FROM t)
|UNION ALL SELECT p.LeftBoundary, c.col FROM RecCols AS p, t AS c WHERE
c.col = p.Value + 1)
| SELECT LeftBoundary, MAX(Value) AS RightBoundary FROM RecCols
| GROUP BY LeftBoundary
| ORDER BY LeftBoundary;
Could you run both against your data set and find out which
one is faster for your six million rows?
Tim
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Procedure for feature requests?
Sam Mason wrote: >> >> "generate_series(A, B, C)" can also >> >> be written as "A + generate_series(0, (C - B) / C) * C" >> > >> > If you can figure out the limit then it seems easy, >> > though I'm not sure how you'd do that. >> What limit? > Sorry, I was calling the second parameter to generate_series the "limit". > Calculating "(C - B) / C" isn't easy for timestamps, whereas it's easy > for dates. I believe this is why there's a specific version for the > former but not the latter. (I obviously meant "(B - A) / C" :-).) Is it? I would assume that you just have to convert A, B and C to seconds (since epoch) and then use a normal integer division. > [...] >> "generate_series(DATE, DATE)" would just be syntactic sugar, >> and I like sweets. > We all do, but in software it's got to be balanced against the overhead > of maintaining support for these functions. My knowledge of PostgreSQL's codebase is nonexistent, so I do not know how unstable it is. Tim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Current state of XML capabilities in PostgreSQL?
Grzegorz Jaśkiewicz wrote: >> why would you store data thats wrapped in two copies of its fieldname along >> with other punctuation? wouldn't it make more sense to decompose your XML >> source into proper tables so proper indexes and relational sql queries can >> be made? otherwise, every query turns into a massive sequential scan and >> parsing operation. > you can always have index on xpath() ... > and than use same expression in WHERE, and postgresql will use index. Interesting. I had thought that it was not possible to index on XML columns because no comparison operators were defined: | tim=# CREATE TEMPORARY TABLE tmpXML (x XML PRIMARY KEY); | FEHLER: Datentyp xml hat keine Standardoperatorklasse für Zugriffsmethode »btree« | HINT: Sie müssen für den Index eine Operatorklasse angeben oder eine Standardoperatorklasse für den Datentyp definieren. yet an array of XML works: | tim=# CREATE TEMPORARY TABLE tmpXML (x XML[] PRIMARY KEY); | HINWEIS: CREATE TABLE / PRIMARY KEY erstellt implizit einen Index »tmpxml_pkey« für Tabelle »tmpxml« | CREATE TABLE | tim=# INSERT INTO tmpXML (x) VALUES (array_append (ARRAY[]::XML[], XMLPARSE (DOCUMENT 'Manual...'))); | INSERT 0 1 though only once: | tim=# INSERT INTO tmpXML (x) VALUES (array_append (ARRAY[]::XML[], XMLPARSE (DOCUMENT 'Manual 2...'))); | FEHLER: konnte keine Vergleichsfunktion für Typ xml ermitteln So an index on xpath() should not be possible. Tim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Query to find contiguous ranges on a column
Peter Hunsberger wrote: > [...] >> or a recursive query (which I always find very hard to com- >> prehend): >> | WITH RECURSIVE RecCols (LeftBoundary, Value) AS >> | (SELECT col, col FROM t WHERE (col - 1) NOT IN (SELECT col FROM t) >> | UNION ALL SELECT p.LeftBoundary, c.col FROM RecCols AS p, t AS c WHERE >> c.col = p.Value + 1) >> | SELECT LeftBoundary, MAX(Value) AS RightBoundary FROM RecCols >> | GROUP BY LeftBoundary >> | ORDER BY LeftBoundary; >> Could you run both against your data set and find out which >> one is faster for your six million rows? > Turns out the server is v 8.3, looks like I need to get them to > upgrade it so I get recursive and windowing :-(. If this happens any > time soon I'll let you know the results. > Many thanks. After some tests with a data set of 7983 rows (and 1638 ran- ges): Don't! :-) The recursive solution seems to be more than double as slow as the iterative. I'll take it to -per- formance. Tim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] XPath PostgreSQL 8.4
Karl Koster wrote:
> It looks like I have to abandon xml2 functions in PostgreSQL
> 8.4. The problem is I can't seem to find an incantation of
> xpath that will perform the same thing. I have tried the
> following snippet:
> select xpath('/trade/trade-info/id/text()', cast(xml as
> xml))[1] as id from risk.trade_table
> which, from the documentation should give me the first (and
> only in this case) xml node text value for the XPath
> expression. Instead I get the following error message from
> the SQL parser:
> ERROR: syntax error at or near "["
> LINE 1: ...h('/trade/trade-info/id/text()', cast(xml as xml))[1] as id ...
> ^
> ** Error **
> ERROR: syntax error at or near "["
> SQL state: 42601
> Character: 62
> When I run the select statement without an array index, it
> correctly returns a single column of arrays of length one
> (expected) for the XPath node text value. Can anyone shine a
> light on what I am doing wrong?
> The build of the 8.4 database I am using is 8.4.1.9250.
You have to put brackets around the function call:
| select (xpath('/trade/trade-info/id/text()', cast(xml as xml)))[1] as id from
risk.trade_table;
Tim
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] XPath PostgreSQL 8.4
I wrote:
> [...]
> You have to put brackets around the function call:
> | select (xpath('/trade/trade-info/id/text()', cast(xml as xml)))[1] as id
> from risk.trade_table;
... or, after a look in the dictionary, whatever you call
"(" and ")" :-).
Tim
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] How to list user-specific configuration parameters?
Hi, how can I list the user-specific configuration parameters, i. e. those set by "ALTER ROLE name SET ..."? Tim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to list user-specific configuration parameters?
Alvaro Herrera wrote: >> how can I list the user-specific configuration parameters, >> i. e. those set by "ALTER ROLE name SET ..."? > Get them from the pg_authid catalog. > 8.5 alpha2 has a new \drds command in psql for that purpose. Thanks! Tim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Procedure for feature requests?
Sam Mason wrote: > [...] >> I would assume >> that you just have to convert A, B and C to seconds (since >> epoch) and then use a normal integer division. > The problem is that the Gregorian calender is far too complicated. For > example, think what would happen with an interval of "months". It > doesn't help converting to seconds because the length of a month in > seconds changes depending on which year the month is in and which > month you're actually dealing with. This makes any definition of > "division" I've ever been able to think of ill defined and hence the > above calculation won't work. Yep, you would probably need some safety margin and add a "WHERE" clause. I should have thought about that earlier as I recently stumbled (again) over why "INTERVAL / INTERVAL" was not defined. Tim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Procedure for feature requests?
Sam Mason wrote:
>> > any definition of "division" I've ever been able to think of [is]
>> > ill defined
>> Yep, you would probably need some safety margin and add a
>> "WHERE" clause. I should have thought about that earlier as
>> I recently stumbled (again) over why "INTERVAL / INTERVAL"
>> was not defined.
> Not sure what you mean by a "safety margin", but I don't think it would
> help much. Hours are defined using seconds (they're *always* 3600
> seconds long, but, say, a day isn't *always* 24 hours long) so I don't
> see what a safety margin would do.
A month can last 28 to 31 days and a year 365 to 366 days,
but for example:
| tim=# SELECT EXTRACT('epoch' FROM '1 month'::INTERVAL) / 60.0 / 60 / 24;
| ?column?
| --
|30
| (1 Zeile)
| tim=#
You would have to adjust the result of "(EXTRACT('epoch'
FROM B) - EXTRACT('epoch' FROM A)) / EXTRACT('epoch' FROM
C)" by a factor of 31/30 (30/28? 28/30?) and then chop off
timestamps after B with a "WHERE" clause.
JFTR: Hours can of course also be 3601 (or theoretically
3599) seconds long, but not in PostgreSQL :-).
Tim
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Quick Date/Time Index Question
"David Johnston" wrote:
> I have a database field that stores a timestamp to second+ precision;
> however, I want to search against it only to day precision. If I leave the
> field in second precision and try to "WHERE field BETWEEN date0 AND date0" I
> get no results (OK, fine) but then I cast the field to date "WHERE
> field::date BETWEEN date0 AND date0" and get the expected results. So now I
> want to index "field::date" by I cannot create a functional index on
> "field::date" OR "CAST(field AS date)" OR "date_trunc('day',field)" due to
> either syntax (::) or non-IMMUTABLE function errors (cast; date_trunc).
> Is there some other way to create an index on only the "date" portion of the
> field? Is it even necessary since any index ordered on timestamp is also,
> by definition, order on date as well?
Not necessarily a direct answer, but have you tried querying
"WHERE field >= date0 AND field < (date0 + 1)"?
Tim
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Quick Date/Time Index Question
Tom Lane wrote:
>> I have a database field that stores a timestamp to second+ precision;
>> however, I want to search against it only to day precision. If I leave the
>> field in second precision and try to "WHERE field BETWEEN date0 AND date0" I
>> get no results (OK, fine) but then I cast the field to date "WHERE
>> field::date BETWEEN date0 AND date0" and get the expected results.
> Try "WHERE field BETWEEN date0 AND date0+1". When comparing a date to a
> timestamp, the date is considered to represent midnight of its day, so
> you're testing for a zero-width range there.
Dare I to say it? :-) Not quite true:
| tim=# SELECT t
| tim-#FROM (VALUES ('2011-01-01 00:00:00'::TIMESTAMP),
| tim(# ('2011-01-02 00:00:00'::TIMESTAMP)) AS d (t)
| tim-#WHERE t BETWEEN '2011-01-01'::DATE AND ('2011-01-01'::DATE + 1);
| t
| -
| 2011-01-01 00:00:00
| 2011-01-02 00:00:00
| (2 Zeilen)
| tim=#
So you would have to assert that no timestamp will ever fall
on midnight.
Tim
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Function to Table reference
(anonymous) wrote: > Is there a way to find which functions are being used by table. > Ex :- If there are functions fnc_a, fnc_b, fnc_c and table A is used in > fnc_a and fnc_c, How can we find that ? can you please help? Basically, you can't. Functions are more or less black boxes to PostgreSQL. Tim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] question about unique indexes
Alban Hertroys wrote: > [...] > None of these solutions are pretty. It should be quite a common problem > though, how do people normally solve this? Partial indexes? Doesn't look pretty either though: | tim=# \d DE_Postcodes | Tabelle »public.de_postcodes« | Spalte | Typ | Attribute | --+-+--- | postcode | integer | not null | city | text| not null | suffix | text| | street | text| not null | first| integer | | last | integer | | Indexe: | "de_postcodes_key1" UNIQUE, btree (postcode, city, suffix, street, first, last) WHERE suffix IS NOT NULL AND first IS NOT NULL AND last IS NOT NULL | "de_postcodes_key2" UNIQUE, btree (postcode, city, suffix, street, first) WHERE suffix IS NOT NULL AND first IS NOT NULL AND last IS NULL | "de_postcodes_key3" UNIQUE, btree (postcode, city, suffix, street, last) WHERE suffix IS NOT NULL AND first IS NULL AND last IS NOT NULL | "de_postcodes_key4" UNIQUE, btree (postcode, city, suffix, street) WHERE suffix IS NOT NULL AND first IS NULL AND last IS NULL | "de_postcodes_key5" UNIQUE, btree (postcode, city, street, first, last) WHERE suffix IS NULL AND first IS NOT NULL AND last IS NOT NULL | "de_postcodes_key6" UNIQUE, btree (postcode, city, street, first) WHERE suffix IS NULL AND first IS NOT NULL AND last IS NULL | "de_postcodes_key7" UNIQUE, btree (postcode, city, street, last) WHERE suffix IS NULL AND first IS NULL AND last IS NOT NULL | "de_postcodes_key8" UNIQUE, btree (postcode, city, street) WHERE suffix IS NULL AND first IS NULL AND last IS NULL | "de_postcodes_postcodecity" btree (postcode, city) | tim=# Tim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] last and/or first in a by group
Dino Vliet wrote: > I want to know if postgresql has facilities for getting the first and or the > last in a by group. > Suppose I have the following table: > resnr,dep,arr,cls,dbd meaning reservationsnumber, departure station, arrival > station, the class of the reservation and the daysbeforedeparture and records > like: > xxx,NYC,BRA,C,80 > xxx,NYC,BRA,M,75 > xxx,NYC,BRA,Q,50 > yyy,WAS,LIS,T,55 > zzz,NYC,LIS,Z,40 > zzz,NYC,LIS,J,39 > I want to select only the most recent records being: > xxx,NYC,BRA,Q,50 > yyy,WAS,LIS,T,55 > zzz,NYC,LIS,J,39 > How would you accomplish this? > I googled and found this: > http://www.postgresonline.com/journal/index.php?/archives/68-More-Aggregate-Fun-Whos-on-First-and-Whos-on-Last.html > I hope there are alternatives because I don't have to program this myself. > The other option is that I load this data in SAS and do it there but I only > have SAS at work and want to do this at home. You can either use window functions in PostgreSQL 8.4 (cf. "FIRST_VALUE() OVER (...)"/"LAST_VALUE() OVER (...)") or use the "DISTINCT ON" syntax: | tim=# SELECT DISTINCT ON (resnr) resnr, dep, arr, cls, dbd FROM TestTable ORDER BY resnr, dbd; | resnr | dep | arr | cls | dbd | ---+-+-+-+- | xxx | NYC | BRA | Q | 50 | yyy | WAS | LIS | T | 55 | zzz | NYC | LIS | J | 39 | (3 Zeilen) | tim=# Tim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to return an Int4 when subtracting dates/timestamps
Andre Lopes wrote: > I need to return an Int4 when I do this king of select > [code] > select CURRENT_DATE - '2009-12-31' from tbl_sometable > [/code] > This select returns an Interval. How can I return an Integer? Like '138' That expression returning an interval would be contradictory to the documentation and a simple test: | tim=# SELECT CURRENT_DATE - '2009-12-31'; | ?column? | -- | 138 | (1 Zeile) | tim=# SELECT CURRENT_DATE::TIMESTAMP - '2009-12-31'; | ?column? | -- | 138 days | (1 Zeile) | tim=# So how far away from "this kind of select" is your actual query? Tim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Hiding data in postgresql
Hector Beyers wrote: > thank you for your replies yesterday on this topic. I have one more question > though: > Does someone have any ideas how I can hide data without the meta data > noticing? To explain further, I would like to save some collection of data > where the meta-data does not see it. I am trying to do some security through > obscurity. It is for research purposes. > Maybe to save populate a table with 1000 rows, but the meta-data only knows > of about 500 of them? Only on an export of a dump can you find the data > again. > [...] Before delving deeper into this, you should get your termi- nology straight: What do you mean by "meta-data"? What do you mean by "export of a dump"? What do you mean by "without the meta data noticing"? Tim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to fetch values at regular hours?
Goran Hasse wrote:
> [...]
> I would like to do;
> freescada=> select * from counter_log_view where name='CNT-3' and timestamp
> < '2010-05-23 18:00:00' order by timestamp desc limit 1;
> name | timestamp | count
> ---+---+---
> CNT-3 | 2010-05-23 17:53:18.58674 |43
> (1 rad)
> freescada=> select * from counter_log_view where name='CNT-3' and timestamp
> < '2010-05-23 19:00:00' order by timestamp desc limit 1;
> name | timestamp | count
> ---++---
> CNT-3 | 2010-05-23 18:53:19.151988 |50
> (1 rad)
> freescada=> select * from counter_log_view where name='CNT-3' and timestamp
> < '2010-05-23 20:00:00' order by timestamp desc limit 1;
> name | timestamp | count
> ---++---
> CNT-3 | 2010-05-23 19:53:19.683514 |51
> (1 rad)
> In one query. Is this possible in *any* way?
Sure:
| SELECT DISTINCT ON (DATE_TRUNC('hour', timestamp)) name, timestamp, count
| FROM counter_log_view
| ORDER BY DATE_TRUNC('hour', timestamp), timestamp DESC;
Tim
P. S.: Naming columns "timestamp" and "count" will lead to
trouble :-).
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to fetch values at regular hours?
Goran Hasse wrote:
> Yes timestamp and count - is not good names for columns...
> I tried something like;
> select name,date_trunc('hour',timestamp),timestamp,count from
> counter_log_view where name='CNT-3' and timestamp < '2010-05-23 20:00:00'
> order by timestamp limit 10;
> name | date_trunc | timestamp | count
> ---+-++---
> CNT-3 | 2010-05-23 15:00:00 | 2010-05-23 15:43:17.411386 |23
> CNT-3 | 2010-05-23 15:00:00 | 2010-05-23 15:53:17.45934 |24
> CNT-3 | 2010-05-23 16:00:00 | 2010-05-23 16:03:17.489321 |24
> CNT-3 | 2010-05-23 16:00:00 | 2010-05-23 16:13:17.586089 |24
> CNT-3 | 2010-05-23 16:00:00 | 2010-05-23 16:23:17.69116 |25
> CNT-3 | 2010-05-23 16:00:00 | 2010-05-23 16:33:17.795955 |28
> CNT-3 | 2010-05-23 16:00:00 | 2010-05-23 16:43:17.89265 |28
> CNT-3 | 2010-05-23 16:00:00 | 2010-05-23 16:53:17.989268 |30
> CNT-3 | 2010-05-23 17:00:00 | 2010-05-23 17:03:18.1447 |33
> CNT-3 | 2010-05-23 17:00:00 | 2010-05-23 17:13:18.199568 |35
> (10 rader)
> Seems promising... But then I would like to select only the last from
> date_trunc...
> Hm...
Why did you not use the query I posted:
> [...]
>> Sure:
>> | SELECT DISTINCT ON (DATE_TRUNC('hour', timestamp)) name, timestamp, count
>> | FROM counter_log_view
>> | ORDER BY DATE_TRUNC('hour', timestamp), timestamp DESC;
> [...]
Is copy & paste too much effort?
Tim
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Please help me write a query
Nikolas Everett wrote: > Sorry. Here is the setup: > CREATE TABLE test (id BIGSERIAL PRIMARY KEY, state1 INT NOT NULL, state2 INT > NOT NULL, timestamp TIMESTAMP); > INSERT INTO test (state1, state2, timestamp) VALUES (1, 1, now() - interval > '12 hours'); > INSERT INTO test (state1, state2, timestamp) VALUES (1, 2, now() - interval > '11 hours'); > INSERT INTO test (state1, state2, timestamp) VALUES (1, 1, now() - interval > '10 hours'); > INSERT INTO test (state1, state2, timestamp) VALUES (2, 1, now() - interval > '9 hours'); > INSERT INTO test (state1, state2, timestamp) VALUES (1, 1, now() - interval > '8 hours'); > I want to write a query that spits out: > state1 | timestamp > + > 1 | now() - interval '12 hours' > 2 | now() - interval '9 hours' > 1 | now() - interval '8 hours' > Standard grouping destroys the third row so that's out. No grouping at all > gives repeats of state1. Is this what partitioning is for? Partitioning usually means splitting data across several tables for faster access which is probably not what you want here. A simple solution would be to use LAG() and discard rows where the current value is equal to the preceding value: | SELECT state1, timestamp | FROM | (SELECT id, | state1, | state2, | LAG(state1) OVER (ORDER BY timestamp) AS prevstate1, | timestamp FROM test) AS SubQuery | WHERE state1 IS DISTINCT FROM prevstate1 | ORDER BY timestamp; Tim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Is it possible to make the order of output the same as the order of input parameters?
Andreas Kretschmer wrote:
>> I have a simple query like:
>> SELECT * FROM customer WHERE id IN (23, 56, 2, 12, 10)
>> The problem is that I need to retrieve the rows in the same order as the set
>> of
>> ids provided in the select statement. Can it be done?
> No. The only way is:
> select * from ... where id in (...) order by case when id=23 then 1,
> case when id=56 then 2 end, case when id=2 then 3 end, ...
Or, quick 'n' dirty:
| SELECT * FROM customer
| WHERE id IN (23, 56, 2, 12, 10)
| ORDER BY POSITION(':' || id || ':' IN ':23:56:2:12:10:');
When using CASE, make sure you read the documentation to the
end: I stumbled upon "CASE id WHEN 23 THEN 1 WHEN 56 THEN 2
WHEN [...] END" only just recently by pure chance :-).
Tim
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Is it possible to make the order of output the same as the order of input parameters?
Sam Mason wrote: >> > SELECT c.* >> > FROM customer c, ( >> > SELECT *, row_number() OVER () >> > FROM (VALUES (23), (56), (2), (12), (10)) x) x(val,ord) >> > WHERE c.id = x.val >> > ORDER BY x.ord; >> Wow, that's really cool and a nice case for row_number(). > Just thinking about it now; do SQL's semantics say it'll always do > the right thing? PG does in a couple of quick tests (i.e. one where > customer is a small table and PG prefers a seqscan and where it's larger > and prefers an index scan) but I'm not sure if this could change. PostgreSQL's documentation on VALUES has at least no guaran- tee of the order of data. I'd prefer David's solution :-). Tim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Is it possible to make the order of output the same as the order of input parameters?
Stephen Frost wrote: >> > Just thinking about it now; do SQL's semantics say it'll always do >> > the right thing? PG does in a couple of quick tests (i.e. one where >> > customer is a small table and PG prefers a seqscan and where it's larger >> > and prefers an index scan) but I'm not sure if this could change. >> PostgreSQL's documentation on VALUES has at least no guaran- >> tee of the order of data. I'd prefer David's solution :-). > Uhm, that's why there's an explicit ORDER BY.. I seriously doubt that > would ever be violated. If there was an approach suggested which didn't > include an ORDER BY *somewhere*, I'd be suspect of it. The query read: | SELECT c.* | FROM customer c, ( | SELECT *, row_number() OVER () | FROM (VALUES (23), (56), (2), (12), (10)) x) x(val,ord) | WHERE c.id = x.val | ORDER BY x.ord; and the question is whether "VALUES (1), (2), (3)" will al- ways return "(1)", "then" "(2)", "then" "(3)" and whether "ROW_NUMBER() OVER ()" will always keep that order intact. Tim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Some insight on the proper SQL would be appreciated
Andy Colson wrote: >> thanks very much Andy. Very elegant. >> I do need to presere the users that have<5 entries though, so I think I can >> modify your function to do that as well. > Oh, duh! because nothing is less than 1900-01-01... my > date math sucks. It should probably return '2100-01-01' or > something. If you're using stored functions, you could (and should be- cause the whole table is probably read anyhow) also code a function that reads all entries, resets a counter at the start and whenever the user changes, increments it on every row and returns all rows where the counter is less than five. Tim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Variables in SQL scripts
"Machiel Richards" wrote: > [...] > I did some googling as well and found something I tested using > a simple method: > - I created an sql script to set the variable Name DB > o \set DB > o \c :DB > - I connected to postgresql using the postgres database and ran the > sql script which seemed to work fine as it then connected me to the > database. > However will this method work with the above situation as well or are there > other ways of doing this? > [...] Why don't you try it? Tim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Cognitive dissonance
Bruce Momjian wrote: > [...] > + # single-page text > + postgres.txt: postgres.html > + $(LYNX) -force_html -dump -nolist -stdin $< > $@ ^^ > + > [...] Isn't that unnecessary/wrong as the filename is supplied on the command line? Tim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] how to alias a table
Ivan Sergio Borgonovo wrote: > I'm refactoring some code and I'll find helpful to be able to alias > tables. > What I'd like to do would be to refer to the same table with an > alias in the code and later substitute the alias with an actual VIEW. > Of course I could define a view as > select * from original_table > right from the start but I'm worried this may incur in some overhead > I currently can't afford. Have you tried it? Tim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to force select to return exactly one row
"Andrus" wrote:
> Autogenerated select statement contains 0 .. n left joins:
> SELECT somecolumns
> FROM ko
> LEFT JOIN t1 ON t1.primarykey= ko.t1foreignkwey
> ...
> LEFT JOIN tn ON tn.primarykey= ko.tnforeignkwey
> WHERE ko.primarykey='someprimarykeyvalue';
> This select can return only 0 or 1 rows depending if ko row with primary key
> 'someprimarykeyvalue' exists or not.
> Problem:
> if there is no searched primary key row in ko database, select should also
> return empty row.
> To get this result I added right join:
> SELECT somecolumns
> FROM ko
> RIGHT JOIN (SELECT 1) _forceonerow ON true
> LEFT JOIN t1 ON t1.primarykey= ko.t1foreignkwey
> ...
> LEFT JOIN tn ON tn.primarykey= ko.tnforeignkwey
> WHERE ko.primarykey is null or ko.primarykey='someprimarykeyvalue';
> but it still does not return row if primary key row 'someprimarykeyvalue'
> does not exist.
> How to force this statement to return one row always ?
It's a bit difficult to decipher what you're looking for
(what do you mean by "empty row"?), but you may want to try
something along the lines of:
| SELECT v.primarykey, ko.somecolumns
| FROM (VALUES ('someprimarykeyvalue')) AS v (primarykey)
| LEFT JOIN ko ON v.primarykey = ko.primarykey
| LEFT JOIN t1 ON t1.primarykey = ko.t1foreignkey
| [...]
| LEFT JOIN tn ON tn.primarykey = ko.tnforeignkey;
Whether that suits your needs depends very much on the data
structure and the tools you use.
Tim
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to force select to return exactly one row
Brett Mc Bride wrote: > How about: > SELECT * from ( > SELECT somecolumns > FROM ko > LEFT JOIN t1 ON t1.primarykey= ko.t1foreignkwey > ... > LEFT JOIN tn ON tn.primarykey= ko.tnforeignkwey > WHERE ko.primarykey='someprimarykeyvalue' > UNION ALL > SELECT default_value > ) > LIMIT 1; > [...] ... with a proper "ORDER BY" clause. Tim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to force select to return exactly one row
Brett Mc Bride wrote: > My understanding of UNION ALL is that it won't sort the rows...? > [...] It doesn't, but that's not promised for every data set, ev- ery PostgreSQL version, every phase of the moon. To quote http://www.postgresql.org/docs/8.4/interactive/queries-union.html>: | UNION effectively appends the result of query2 to the result | of query1 (although there is no guarantee that this is the ^^^ | order in which the rows are actually returned). Furthermore, ^ | it eliminates duplicate rows from its result, in the same | way as DISTINCT, unless UNION ALL is used. SQL deals with (unordered) sets, and therefore any use of "LIMIT" without "ORDER BY" indicates a bug waiting to bite you when you least expect it. Tim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Equivalent to "use database" in postgre
javijava wrote: > i'm newby in postgre sql world. > i need to know how to do a simple script that create a database,the y > select it (in other languajes using USE) and after create tables with this > database. > How can I say "use name_database" on postgre sql? You must specify the database to use on connect; if you want to use psql for your script, you can use "\c name_database" à la: | CREATE DATABASE testdatabase; | \c testdatabase | CREATE TABLE testtable (testcolumn int); Tim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Postgres table contents versioning
John Gage wrote: > Is there an equivalent of svn/git etc. for the data in a > database's tables? > Can I set something up so that I can see what was in the > table two days/months etc. ago? > I realize that in the case of rapidly changing hundred > million row tables this presents an impossible problem. > The best kludge I can think of is copying the tables to a > directory and git-ing the directory. If you're looking at this from a disaster recovery point of view, you should read up on PostgreSQL's PITR capabilities. If you need the information in your application, you should google for "temporal databases" on how to amend your table structures. Tim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Filtering by tags
Anders Steinlein wrote: > No one with any response on this? > [...] Insert a "LEFT JOIN" in the first subquery? Tim (too lazy to test :-)) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] loading many queries from a file
Szymon Guz wrote: > I've got a file with many SQL queries, also some function definitions and so > on. I'd like to load it to database, but using some library like > JDBC/ODBC/DBI, not using the obvious psql. Do you know how I could load > those many queries? Usually there could be loaded only one query, I saw that > psql parses the file looking for the semicolon. > Is there any other way than parsing the file and loading queries one by one? Is your file "friendly" or arbitrary? With DBI, you can exe- cute several commands in one do() call, but you cannot split one command across several calls. The PostgreSQL frontend/ backend protocol seems to allow that by a cursory look, but that's *very* far from JDBC/ODBC/DBI. If there are no guarantees on the format of your file, I would try to adapt psql's psqlscan.l & Co. Tim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Query to "balance" rows across multiple tables
Mike Christensen wrote: > [...] > Here's the catch: I want to DELETE any row (in either table) that has > zero quantity, since I no longer need this data (plus I have a CHECK > constraint on this value and require it to be positive anyway).. > Is there actually a way to do this in a SQL function? Thanks! Probably. But I wouldn't take that path: If your function has a bug, you'll have /no/ record of what went wrong, but only some numbers that may or may not be correct. So I'd rather use a more elaborate table structure where you can track when you bought/planned to use/used what quan- tity of items, and then use SUM() & Co. to report what you own and what you need to buy. Tim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] how to remove a for-loop from programming language and put it into the query?
Pedro Zorzenon Neto wrote:
> [...]
> So, I need to get a report of all diagnostics of all hardware on
> december 25th.
> (external programming language)
> for ($i = 1; $i < 500; $i++) {
> // return me the "most recent" diag_value from a hardware_id $i
> // at the desired timestamp
> runquery("select diag_value from diagnose_logs where ts <= '2009-12-25
> 23:59:59' and hardware_id = $i order by ts desc limit 1");
> }
> Currently I have an index on diagnose_logs(ts,hardware_id)
> I have 3 milion registers of 500 different hardware_id.
> The time to run 500 times this query is long... about 1 minute. When I
> need a montly day-by-day report of 500 hardwares, it takes about half an
> hour.
> can I turn this for-loop into a single query to run in postgres?
Another month, another case for "DISTINCT ON":
| SELECT DISTINCT ON (hardware_id)
| hardware_id, diag_value
| FROM diagnose_logs
| WHERE ts <= '2009-12-25 23:59:59'
| ORDER BY hardware_id, ts DESC;
BTW, I'd prefer "WHERE ts < '2009-12-26'" as otherwise you
don't catch a timestamp '2009-12-25 23:59:59.5' (not to
speak of leap seconds).
Tim
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] to_date function
Carlos Henrique Reimer wrote: > I've a Linux box running postgresql 8.2.17 and facing some strange results > from the to_date function. > As you can see in the following tests the problem occurs when the template > used includes upper and lower case characters for the minute (Mi or mI). > Am I using the incorrect syntax or is it a bug? > [...] In general, the template patterns are case-sensitive (cf. "month" vs. "Month" vs. "MONTH"). So "mI" will probably be interpreted as a literal "m" and "I" meaning "last digit of ISO year" which isn't what you want. So use "MI" and be happy. Tim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] problem with table structure
Miguel Vaz wrote: > [...] > * sites (generic): > id_site > name > description > x > y > * site_natural > id > id_site > altitude > * site_arqueology > id > id_site > id_category > id_period > But i seem to be missing something. How can i have this in a way that its > easy to list only "arqueology sites" for example. I feel the solution is > simple enough, even for me, but its eluding me. Any help in the right > direction would be very appreciated. You mean archaeological sites that are not also natural sites? | SELECT * FROM site_arqeuology | WHERE id_site NOT IN | (SELECT id_site FROM site_natural); There are numerous other ways to do this, i. e., with "LEFT JOIN", "EXCEPT", etc. Tim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] problem with table structure
Miguel Vaz wrote: > I was looking for an opinion on the actual table structure. :-) How should i > build the data set? Is my second example ok? The first is the long version > but i wanted to put together all the common fields to both types of "sites" > and then (maybe) build tables to accomodate the specific fields so there are > no empty columns on the table if i put everything in the same table. > [...] That's way too fuzzy for good advice. Few people build data sets on archaeological sites, and even those probably don't use all the same structure. Tim (not telepathic) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] problem with table structure
Miguel Vaz wrote:
> Thank you for the opinion, Alban. The names are the least of my worries, i
> typed them without thinking. And its portuguese. :-)
> If, using that design, i had a different table with something like arq_types
> { id_arq_type, descr } that i could somehow connect to the generic table
> (the one with the common fields), how could i go about querying those tables
> for all the results of a specific type, for example? Or maybe i could add a
> "table_name" field on that arq_type table?
> Tim:
> Dont consider this to be strictly for archeology, i mean in a generic sense
> that if we have several data sets with common fields, if we could divide
> them into several tables, one with common fields, and the others with fields
> related to each type. My doubt was regarding how to have a separate table
> with "types" that could be used to help query the "common fields table" and
> fetch the corresponding table of that specific type. I understand its a bit
> ungrateful for you guys to understand what i mean, considering that i am
> probably making things even more confusing. :-)
> [...]
I think the main problem is that you haven't stated your ex-
perience with SQL (or databases in general). Your questions
above ("somehow connect to the generic table", "go about
querying those tables") indicate that you seem to be lacking
basic knowledge. In this case, it won't help you, us or your
database to ask how to structure your data; you should read
a tutorial, and then choose a structure that you understand
and that works for you.
But at the moment, you're basically saying: "I'd like to
build a vehicle; I haven't decided yet whether it should
take me to the next pub or the moon. Which screws should I
use?"
Tim
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Postgresql 8.4, XPath and name() function
ced45 wrote:
> I have trouble using XPath name() function in a XML field.
> For example, when I execute the following query :
> SELECT XPATH('name(/*)', XMLPARSE(DOCUMENT 'value'))
> I would like to get "unit", but I just get an empty array ({}).
> How can I get "unit" ?
AFAIK, this is not related to PostgreSQL, but inherent to
XPath in that it returns elements from the document that
fulfill the XPath expression *unchanged*.
So you will probably have to tackle your problem from an-
other angle.
Tim
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Need help doing a CSV import
Craig Ringer wrote: >> I am in the process of moving a FoxPro based system to PostgreSQL. >> We have several tables that have memo fields which contain carriage >> returns and line feeds that I need to preserve. I thought if I converted >> these into the appropriate \r and \n codes that they would be imported as >> carriage returns and line feeds, but instead they are stored in the >> database as \r and \n. > PostgreSQL doesn't process escapes in CSV import mode. > You can reformat the data into the non-csv COPY format, > which WILL process escapes. Or you can post-process it after > import to expand them. Unfortunately PostgreSQL doesn't > offer an option to process escapes when "CSV" mode COPY is > requested. > I posted a little Python script that reads CSV data and > spits out COPY-friendly output a few days ago. It should be > trivially adaptable to your needs, you'd just need to change > the input dialect options. See the archives for the script. Another option is a small Perl script or something similar that connects to both the FoxPro and the PostgreSQL database and transfers the data with parameterized "INSERT". The ad- vantage of this is that you have tight control of charsets, date formats, EOL conventions & Co. and do not have to won- der whether this and that file is in this and that stage of the conversion process, the disadvantage is obviously that you lose any speed benefit of bulk "COPY". Tim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Postgresql 8.4, XPath and name() function
Craig Ringer wrote:
> [...]
>>> I would like to get "unit", but I just get an empty array ({}).
>>> How can I get "unit" ?
>> AFAIK, this is not related to PostgreSQL, but inherent to
>> XPath in that it returns elements from the document that
>> fulfill the XPath expression *unchanged*.
> My (poor) understanding is that XPath can be used as an
> expression language and as a selector specifier language.
> You can observe this in XSLT, where
>
> uses XPath as a selector of elements, and:
>
> uses XPath as an expression language, returning the output
> of a given XPath expression or function not just the
> "matched"/"not matched" status.
> I found this very confusing myself when learning XSLT, and
> it's possible I'm still misunderstanding it somewhat, but
> it's clear that XPath can be used in more than one way.
> [...]
Okay, that's maybe due to my XML socialization: An XPath ex-
pression to me has always been something you use in
"xsl:template" and "xmllint --shell", as in "xsl:value-of" &
Co. you have also access to other functions. It's even in
the specification :-):
| [...] The primary purpose
| of XPath is to address parts of an XML [XML] document. [...]
(from: http://www.w3.org/TR/xpath/>)
Tim
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Need help doing a CSV import
David Fetter wrote: > [...] >> Another option is a small Perl script or something similar >> that connects to both the FoxPro and the PostgreSQL database >> and transfers the data with parameterized "INSERT". The ad- >> vantage of this is that you have tight control of charsets, >> date formats, EOL conventions & Co. and do not have to won- >> der whether this and that file is in this and that stage of >> the conversion process, the disadvantage is obviously that >> you lose any speed benefit of bulk "COPY". > You can do your transformations and hand the stream off to the COPY > interface. See the pg_putcopydata() section of the DBD::Pg manual for > examples. :) Eh, yes, but then you have to do all the escaping yourself and the simplicity of "get values A, B, C from this connec- tion and pass it onto that" goes away :-). Now if there'd be a "pg_putcopydata(array of arrayrefs)" ... :-). Tim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] coalesce seems to give strange results
Richard Yen wrote: > Ah, I see what you mean. If there's no rows to return, then there's no > coalesce-ing to do... That's right, /but/ if you use a sub-select, you can achieve something similar: | tim=# SELECT COALESCE((SELECT ROUND(EXTRACT(epoch FROM now() - query_start)) | tim(#FROM pg_stat_activity | tim(#WHERE current_query = ' in transaction'), | tim(# 0); | coalesce | -- | 0 | (1 Zeile) | tim=# > sorry for the spam. > [...] That wasn't spam :-). Tim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to distribute quantity if same product is in multiple rows
(anonymous) wrote: > Order contains same product in multiple rows. > I tried to calculate undelivered quantity using script below but it produces > wrong result: > delivered quantity is substracted from both rows, not distributed. > How to distibute undelivered quantity according to row quantity in every row > ? > Can it be done using SQL or should SCAN loop in plpgsql used? > [...] It can be done in SQL: "SUM(kogus) OVER (PARTITION BY toode ORDER BY ID) - kogus" (*1) will give you the running sum of the product up to that row. You can then subtract that value from the delivered quantity to calculate the delivered quan- tity for the current row. But doing so automatically is probably bad. For example, if a user has a purchase order with one position of two pieces and one position of four, it is very likely that when a shipment of four pieces arrives, the latter position shall be marked as delivered. So I would leave the decision to the user. Tim (*1) In PostgreSQL 9.0, you might be able to use "ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING" instead (untested). -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to distribute quantity if same product is in multiple rows
(anonymous) wrote: >>It can be done in SQL: "SUM(kogus) OVER (PARTITION BY toode >>ORDER BY ID) - kogus" (*1) will give you the running sum of >>the product up to that row. You can then subtract that value >>from the delivered quantity to calculate the delivered quan- >>tity for the current row. >> But doing so automatically is probably bad. For example, >>if a user has a purchase order with one position of two >>pieces and one position of four, it is very likely that when >>a shipment of four pieces arrives, the latter position shall >>be marked as delivered. So I would leave the decision to the >>user. > If four pieces arrived, first position of 2 pieces should > marked as delivered. > Second position of 4 pieces shoudl be marked as partialli > delivered by setting undelivered quantity > of this row to 2 > How to use your suggestion for this ? Que? You take the query above, join it in the "UPDATE" and set the delivered quantity to the minimum of the ordered quantity and "taitmkogus - sumkogus". > How to implement this is PostgreSql 8.1,8.2, 8.3 ? > [...] An example for calculating running sums without window functions can be found at http://archives.postgresql.org/pgsql-sql/2001-07/msg00152.php>. I would rather use a PL/pgSQL function in this case, though. Tim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] [pgeu-general] pgday.eu
Andreas 'ads' Scherbaum wrote: >> I think we should spend some money and buy some inexpensive cameras >> and tripods along with a bunch of memory cards for next year, and >> record everything. I know other PUGs/conferences series have done that >> with good results. > On FOSDEM this attempt failed two years in a row. Last year the reason > was: the camera had to be on the top level line with seats. Else it > would only record parts of the room, eeither only the speaker and not > the projector wall or vise versa. But this camera position resulted in a > very bad light situation and in addition the sun protection darkened the > room even more. > You not only need a camera, you also need someone operating this camera > all the time. At another conference, the camera's focus was on the speaker the whole time and the slides were edited in later, thus making them more legible. Of course, this requires either a "camera" operator who switches the video input when appro- priate or two recordings that are edited in post-production. Tim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Books, the lulu.com scam
Thom Brown wrote: >> So I bought this "book" thinking it was a PDF file which I am fine with. >> Its not. They download an .acsm file that will only work with their >> proprietary Windoze software. >> I am really angry with lulu.com about this. > I'm not sure I understand the purpose of them providing an electronic > book copy of the PostgreSQL documentation since it's already available > as both a PDF (A4 and US) and Windows help file (CHM) for free: > http://www.postgresql.org/docs/manuals/ Apparently, you can make money out of it. Tim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] JOIN Record returning Function
Daniel Schuchardt wrote: > i have a function that returns a record. > myfunc(IN id INTEGER) RETURNS RECORD. > in that function the record is build from some subquery's in > dependence of data. > Now i need to join that function to its correponding main > table that holds the id. > SELECT myfunc.* FROM maintable JOIN myfunc(maintable.pk) ON > true WHERE maintable.field=statisticdata; > ERROR: invalid reference to FROM-clause entry for table "maintable" > TIP: There is an entry for table "maintable", but it cannot > be referenced from this part of the query. > so far so good, thats clear. But does anyone know a tricky > solution for that problem? "SELECT myfunc(pk) FROM maintable WHERE field = statisticdata;"? Tim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Literals in foreign key definitions
Alban Hertroys wrote: > [...] > Now the intent here is to restrict foreign keys referencing the base class to > unitclass records that describe a baseclass and to restrict foreign keys > referencing a derived class to unitclass records that do NOT describe a > baseclass. > Basically I'm trying to disallow derived classes to be derived of other > derived classes. > I can of course add a few triggers to force that constraint, but I think it > would be nice if the above syntax could be made to work. Or is this already > in 8.4 or 8.5 or is this a can of worms? Does the SQL spec disallow it? If you want to avoid triggers, another, simpler approach is to have a otherwise superfluous column "is_baseclass" with a default "TRUE" and constraints "NOT NULL" and "CHECK(is_baseclass)" and then use a "normal" foreign key constraint. I usually find that easier to read as it's more familiar. Tim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Verify a record has a column in a plpgsql trigger
Mike Ginsburg wrote: > [...] > Thanks for the help! I'll look into the exceptions to see > how expensive they are. On a related note, I was just told > by our sysadmins that pg 8.4 might not be installed by the > time this needs to be rolled out, leaving me in a bind since > I have been using "EXECUTE ... USING" queries. A sample of > my trigger is below: > FOR colRow IN SELECT attname FROM pg_catalog.pg_attribute > WHERE attnum >> 0 AND attrelid = TG_RELID LOOP > EXECUTE 'SELECT ($1).' || colRow.attname || '::text' INTO n USING NEW; > EXECUTE 'SELECT ($1).' || colRow.attname || '::text' INTO o USING OLD; > IF n <> o THEN >q := 'INSERT INTO change_log (...) VALUES (...); >EXECUTE q; > END IF; >END LOOP; > Any insight on a way I can grab NEW.(colRow.attname) without EXECUTE USING? Wouldn't it be *much* easier to just have /two/ trigger functions? Your "editor" columns probably don't pop up and disappear randomly. Tim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Using readline for frequently used queries
Hi,
depending on the database, I use some "dashboard queries"
rather frequently. To ease executing them, I've put:
| $include /etc/inputrc
| $if psql
| "\e[24~": "\fSELECT * FROM DashboardQuery;\n"
| $endif
in my ~/.inputrc ("\e[24~" is [F12]).
Obviously, this only works if a) the current line and
b) the query buffer are empty. Before I try and err: Has
anyone put some thought in how to reliably do that? My first
impulse would be C-a, C-k, "\r", C-j, but this doesn't solve
the problem if psql is in "quote mode" (e. g. the previous
line contained an opening "'" or '"').
TIA,
Tim
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Using readline for frequently used queries
I wrote:
> depending on the database, I use some "dashboard queries"
> rather frequently. To ease executing them, I've put:
> | $include /etc/inputrc
> | $if psql
> | "\e[24~": "\fSELECT * FROM DashboardQuery;\n"
> | $endif
> in my ~/.inputrc ("\e[24~" is [F12]).
> Obviously, this only works if a) the current line and
> b) the query buffer are empty. Before I try and err: Has
> anyone put some thought in how to reliably do that? My first
> impulse would be C-a, C-k, "\r", C-j, but this doesn't solve
> the problem if psql is in "quote mode" (e. g. the previous
> line contained an opening "'" or '"').
I found that C-c (SIGINT) makes a clean sweep but it doesn't
work as a readline macro as it gets handled by psql. So I
opted for:
| "\e[24~": "\C-a\C-k\\r\n\fSELECT * FROM DashboardQuery;\n"
which works well enough except for "quote mode".
Tim
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Domain for regular expressions?
Hi,
is there a "proper" domain for regular expressions? At the
moment I'm using:
| CREATE DOMAIN RegularExpression AS TEXT CHECK('' ~ VALUE OR '' !~ VALUE);
which works.
Tim
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Null vs. Empty String in Postgres 8.3.8
Peter Hunsberger wrote: >>> I still don't get it. I do want a zero for the subversion_flags to be >>> stored in the table. But it returned an error because it didn't like >>> subversion_flags='' in the UPDATE SQL statement. >>> subversion_flags | integer | not null default 0 >> Right. '' is not 0. the old version of pgsql converted '' to 0 for >> you, incorrectly. Now if you want 0 you need to say 0. > Or, since you have the default, set it to null (Which may be what > you thought you where doing?) Setting it to NULL does not set it to the default value. You have to use the keyword DEFAULT for that. Tim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
