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
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
"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
(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.
(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
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(#
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,
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 (
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 wou
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 rel
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 generi
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
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 "arqueo
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
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
> runque
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
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 on
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-gene
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
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
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 effe
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;
> [...]
...
"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
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 *
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
"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 a
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
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
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 abo
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 *
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, tim
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 | dat
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-
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
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
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 daysbefor
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
|
(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 Postgre
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.
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
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
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 li
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 "
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
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 myfu
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 p
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 fail
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"
>> wa
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". I
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 (pgs
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
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 (
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 i
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 A
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
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 genera
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 effi
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 g
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
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 "generat
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,
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,
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 bu
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.
>
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/msg0144
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 fanc
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 ha
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($encod
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 i
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
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 an
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 pa
72 matches
Mail list logo
