On Friday, February 6, 2015, Adrian Klaver
wrote:
> On 02/06/2015 10:26 AM, Tim Smith wrote:
>
>> Re:So, you have an input parameter named "session_id" and a query with
>> a column named "session_id" - this is the problem.
>>
>> Well, I'll re-try with a revised function, but surely the database
>
On Fri, Feb 6, 2015 at 10:23 AM, Adrian Klaver
wrote:
>
>> CREATE FUNCTION validateSession(session_id char(64),client_ip
>> inet,user_agent char(40),forcedTimeout bigint,sessionTimeout bigint)
>> RETURNS json AS $$
>>
>
> CREATE OR REPLACE FUNCTION public.validatesession(s_id character,
> clien
On Fri, Feb 6, 2015 at 9:55 AM, Tim Smith
wrote:
> >Unfortunately the function definition is not given and that is where you
> are seeing the error.
> > To figure this out we will need to see the function.
>
> Geez, there's just no satisfying some people ! ;-)
>
> I did actually show you my func
On Thu, Feb 5, 2015 at 4:29 PM, Tim Smith
wrote:
> You're most welcome to look at my view definition view if you don't
> believe me
>
> View definition:
> SELECT a.session_id,
> a.session_ip,
> a.session_user_agent,
> a.session_start,
> a.session_lastactive,
> b.user_id,
On Thu, Feb 5, 2015 at 4:01 PM, Tim Smith
wrote:
> > returning more than one row? v_row can only hold one row at a time.
>
> Absolutley not. (a) My where clause is a primary key (b) I have
> checked it manually, it only returns one row
>
> >You really need to provide error messages
>
> Yes, wel
On Thu, Jan 29, 2015 at 1:22 PM, Sterpu Victor wrote:
> It works as you sugested, this is the syntax I used:
> SELECT * FROM
> (SELECT 1 AS t1, 2 AS t2) AS t1
> LEFT JOIN (SELECT * FROM atc WHERE id = '1231222') AS t2 ON (null)
>
> Thank you.
>
>
You will notice that everyone responding to you is
On Thursday, January 22, 2015, tsunghan hsieh
wrote:
> Hi
>
> I have a table which just has one column as following in Original Table. I
> wanna duplicate all of data for few times and with same order as following
> in New Table. Is there anyone who can help me? Thanks
>
> Han
>
> Original Table
>
>
> On Wed, Jan 21, 2015 at 4:09 PM, Bryn Jeffries <
> bryn.jeffr...@sydney.edu.au> wrote:
>
>>
>> Maybe what we need in ODBC libs and the like is a "protected
>> statement" that follows the same construction as a prepared statement but
>> additionally checks catalogs to validate identifiers.
>>
On Wed, Jan 21, 2015 at 4:09 PM, Bryn Jeffries
wrote:
> Paul Jungwirth wrote
> > I'm not sure how to make a prepared statement that lets you name a
> > column when you execute it. Maybe someone else can chime in if that's
> > possible.
>
> David J. responded
> > You cannot. By definition paramet
On Tue, Dec 30, 2014 at 8:54 AM, Adrian Klaver
wrote:
> On 12/30/2014 07:43 AM, David G Johnston wrote:
>
>> Tom Lane-2 wrote
>>
>>> Bernd Helmle <
>>>
>>
>> mailings@
>>>
>>
>> > writes:
>>>
--On 29. Dezember 2014 12:55:11 -0500 Tom Lane <
>>>
>> tgl@.pa
>>>
>>
>> > wrote:
>>>
On Mon, Dec 29, 2014 at 5:09 PM, Adrian Klaver
wrote:
> On 12/29/2014 03:56 PM, David Johnston wrote:
>
>>
> So you think psql should issue "COMMIT;" even if it is exiting due to
>>
> "ON_ERROR_STOP"?
>>
>
> I say yes, if it is a
On Mon, Dec 29, 2014 at 4:38 PM, Adrian Klaver
wrote:
> On 12/29/2014 02:55 PM, David Johnston wrote:
>
>> On Mon, Dec 29, 2014 at 3:37 PM, Adrian Klaver
>> mailto:adrian.kla...@aklaver.com>>wrote:
>>
>> On 12/29/2014 02:28 PM, David Johnston wrote:
>&g
On Mon, Dec 29, 2014 at 3:37 PM, Adrian Klaver
wrote:
> On 12/29/2014 02:28 PM, David Johnston wrote:
>
>> On Mon, Dec 29, 2014 at 3:07 PM, Adrian Klaver
>> mailto:adrian.kla...@aklaver.com>>wrote:
>>
>> On 12/29/2014 09:38 AM, David Johnston wrote:
>&g
On Mon, Dec 29, 2014 at 3:07 PM, Adrian Klaver
wrote:
> On 12/29/2014 09:38 AM, David Johnston wrote:
>
>>
>> This is one of those glass half full/empty situations, where it is
>> down to the eye of the beholder. I would also say this a perfect
>> exampl
Copying -bugs to gain broader attention and opinions.
On Mon, Dec 29, 2014 at 10:06 AM, Adrian Klaver
wrote:
> On 12/29/2014 08:49 AM, David Johnston wrote:
>
>> On Mon, Dec 29, 2014 at 9:39 AM, Adrian Klaver
>> mailto:adrian.kla...@aklaver.com>>wrote:
>>
>>
On Mon, Dec 29, 2014 at 9:49 AM, David Johnston
wrote:
> On Mon, Dec 29, 2014 at 9:39 AM, Adrian Klaver
> wrote:
>
>> On 12/29/2014 07:59 AM, David Johnston wrote:
>>
>>>
>>> Anyway, the third undocumented bug is that --single-transactions gets to
&g
On Mon, Dec 29, 2014 at 9:39 AM, Adrian Klaver
wrote:
> On 12/29/2014 07:59 AM, David Johnston wrote:
>
>>
>> Anyway, the third undocumented bug is that --single-transactions gets to
>> send its COMMIT even if ON_ERROR_STOP
>> takes hold before the end of the sc
On Mon, Dec 29, 2014 at 8:49 AM, Adrian Klaver
wrote:
> On 12/28/2014 05:04 PM, David G Johnston wrote:
> > Adrian Klaver-4 wrote
> >> On 12/28/2014 10:06 AM, Viktor Shitkovskiy wrote:
> >>> I include my own scripts. Each of them creates some table or makes some
> >>> changes to existing tables.
Hi!
When psql (libpq) connects it uses a combination of defaults, environment
variables, command line arguments, and possibly a pg_service file to figure
out where it is going to connect, and how.
Specifying the option "--list-conninfo" as an option would cause psql to
simply output all of the va
On Thu, Dec 18, 2014 at 3:02 PM, Robert DiFalco
wrote:
> Is the intersect any better than what I originally showed? On the ROW
> approach, I'm not sure where the context for that is coming from since it
> may not be in the intersection. Consider n1 and n2 are NOT friends but they
> have >0 mutual
On Monday, December 8, 2014, Huang, Suya wrote:
> -Original Message-
> From: pgsql-general-ow...@postgresql.org [mailto:
> pgsql-general-ow...@postgresql.org ] On Behalf Of David G
> Johnston
> Sent: Monday, December 08, 2014 1:18 PM
> To: pgsql-general@postgresql.org
> Subject: Re: FW:
Please send replies to the list.
On Friday, December 5, 2014, Ian Harding > wrote:
>
>
> On Fri, Dec 5, 2014 at 5:37 PM, David G Johnston <
> david.g.johns...@gmail.com> wrote:
>
>> Ian Harding wrote
>> > On Fri, Dec 5, 2014 at 4:55 PM, Ian Harding <
>>
>> > harding.ian@
>>
>> > > wrote:
>> >> I
On Fri, Nov 28, 2014 at 10:47 AM, Andrus wrote:
> Hi!
>
> Thank you.
>
> Instead of defining an xpath for fields define one that captures the xml
>> pertaining to the data that would belong to
>> a single record.How to create single xpath or xsl which assigns values to
>> all columns in Postgres
On Fri, Nov 28, 2014 at 9:40 AM, Andrus wrote:
> Hi!
>
> Thank you.
>>Subquery the xpath expression to unnest it and apply a LIMIT 1
> > UPDATE tbl SET ... = (SELECT xpath( tbl.???[...] ) LIMIT 1)
>
> I used unnest() :
>
Sorry, I meant to say (SELECT unnest(xpath(tbl.???[...])) LIMIT 1
On Fri, Nov 28, 2014 at 4:17 AM, Andrus wrote:
> Hi!
>
> You have to process this in two passes. First pass you create a table of
>> documents by unnesting the non-optional >Document elements. Second pass you
>> explode each individual row/document on that table into its components.
>>
>
> Thank
On Wednesday, November 26, 2014, Adrian Klaver
wrote:
> On 11/26/2014 12:34 PM, David Johnston wrote:
>
>>
>> I guess what is confusing to me is the transition between the text
>> mode and the constructor mode is not clear. In particular the page
>>
> I guess what is confusing to me is the transition between the text mode
> and the constructor mode is not clear. In particular the page starts with
> examples using the constructor mode but then goes to explanations that
> actually apply to the text mode before getting back to explaining the
> co
On Thu, Nov 13, 2014 at 5:47 PM, Tom Lane wrote:
> David G Johnston writes:
> > Tom Lane-2 wrote
> >> In the meantime, I assume that your real data contains a small
> percentage
> >> of values other than these two? If so, maybe cranking up the statistics
> >> target would help. If the planner
Yes, that is what I was referring to. The Nabble.com website showed them.
http://postgresql.1045698.n5.nabble.com/SSL-Certificates-in-Postgres-9-3-and-Windows-7-td5826230.html
David J.
On Sunday, November 9, 2014, Magnus Hagander wrote:
> On Sun, Nov 9, 2014 at 11:37 PM, David Johns
On Sunday, November 9, 2014, Adrian Klaver
wrote:
> On 11/09/2014 10:14 AM, David G Johnston wrote:
>
>> Adrian Klaver-4 wrote
>>
>>> Thank you for all comments and suggestions.
>>>
>>> More comments/suggestions will have to wait until the missing pieces are
>>> filled in.
>>>
>>
>> I read m
On Wed, Oct 29, 2014 at 12:14 PM, Jorge Arevalo
wrote:
>
> SELECT value1,value2,value3,value4, value5, hstore(ARRAY['field9',
> 'field10', 'field11', 'field12', 'field13', 'field14'], ARRAY[field9,
> field10, field11, field12, field13, field14]) as metadata, value7, (select
> array((select row(f1
On Wed, Oct 29, 2014 at 11:53 AM, Tom Lane wrote:
> Jorge Arevalo writes:
>
> > This is the result of EXPLAIN ANALYZE
>
> >QUERY
> > PLAN
> >
>
List preference is to inline post or, at worse, bottom post. Please do not
top post.
On Wed, Oct 29, 2014 at 11:06 AM, Jorge Arevalo
wrote:
> Hello David, many thanks for your responses,
>
> Sorry for not providing the content of the fill_table3_function, but it
> just executes 3 insert queries
On Friday, September 19, 2014, Alban Hertroys wrote:
> On 19 Sep 2014, at 3:50, Robert Nix >
> wrote:
>
> > Thanks, David.
> >
> > I have read that page many times but clearly I have forgotten this:
> >
> > • Constraint exclusion only works when the query's WHERE clause
> contains constants
>
>
> > > - What are the differences among PL/SQL, PL/PGSQL and pgScript.
> >
> > The first two are languages you write functions in. pgScript is simply
> an
> > informal way to group a series of statements together and have them
> execute
> > within a transaction.
> >
>
> AFAICT, this isn't true
On Wed, Aug 6, 2014 at 10:08 AM, Jeff Janes wrote:
> On Tue, Aug 5, 2014 at 4:30 PM, David G Johnston <
> david.g.johns...@gmail.com> wrote:
>
>>
>> Anyway, you should probably experiment with creating a multi-column index
>> instead of allowing PostgreSQL to BitmapAnd them together. Likely the
On Fri, Aug 1, 2014 at 6:22 PM, Chris Travers
wrote:
> On Fri, Aug 1, 2014 at 12:19 PM, David G Johnston <
> david.g.johns...@gmail.com> wrote:
>
>> Vik Fearing wrote
>> >> CREATE testfunction(test) returns int language sql as $$ select 1; $$;
>> >> SELECT testfunction FROM test;
>> >>
>> >> That
On Tue, Jul 22, 2014 at 9:46 AM, Anil Menon wrote:
> Am a bit confused -which one comes first?
>
> 1) the 'data'||currval('id01_col1_seq') is parsed first : which means it
> takes the current session's currval
> 2) then the insert is attempted which causes a sequence.nextval to be
> performed whi
>
> > with QRY as (select C1.country, C1.state, sum(C1.population)
> > from places C1
> > group by 1, 2
> >order by 3 DESC
> > limit 10)
> >
> > select * from QRY
> > union
> > select 'others' as "country", '' as "state", sum(population)
> > from places
> > where not exists (select 1 f
>
>
>> 9.3 - On an idle connection the value of query is the last executed query
>> -
>> which in this case is some form session cleanup command before returning
>> the
>> connection to the pool.
>>
>>
> So, it is a normal behavior in Postgres.
>
> One more thing that bothers me, why this idle con
Re-posting, see quote
David Johnston wrote
>
> Pavel Stehule wrote
>> 2014-04-04 14:16 GMT+02:00 Tjibbe <
>> tjibbe@
>> >:
>>
>>> Thanks that works!
>>>
>>> Little bit confusing ERROR.
>>>
>>
>> yes
Based on your first question a customer id itself is not a valid designator;
you have to specify (or link) in the group as well.
Not tested but should work:
FOREIGN KEY (template, group) REFERENCES customer (cust_id, group)
Depends on whether you want to allow cross-group associations if you nee
Jim Garrison wrote
> Assuming I can pause the driving Java code between queries in a job, is
> there any way to connect from PGAdmin (or another tool) and view the state
> of tables in the in-progress transaction? If this is not currently
> possible, how difficult would it be to do?
What you want
Andy Colson wrote
> On 4/3/2014 11:09 AM, David Johnston wrote:
>> Andy Colson wrote
>>> On 4/3/2014 10:27 AM, Schnabel, Robert D. wrote:
>>>> I'm trying to figure out how to count the number of rows within a fixed
>>>> range of the current row val
Andy Colson wrote
> On 4/3/2014 10:27 AM, Schnabel, Robert D. wrote:
>> I'm trying to figure out how to count the number of rows within a fixed
>> range of the current row value. My table looks like this:
>>
>> SELECT chr_pos
>> FROM mutations_crosstab_9615_99
>> WHERE bta = 38
>> LIMIT 10
>>
>> c
Jeff Janes wrote
> On Wed, Apr 2, 2014 at 12:00 PM, Bala Venkat <
> akpgeek@
> > wrote:
>
>> We are using postgres 9.0. When I looked at the pg_stat_activity table.
>>
>> I have some rows where there is difference of 2 hours between
>> backend_start and xact_start
>>
>> But there is only few m
loc wrote
> Setting the serial column to null to auto increment would also
> work for me.
Can you set it to a literal value DEFAULT? Only helps for the insert case
(not copy) but that is the mechanism that is used to specify a column and
ask for the default.
David J.
--
View this message in
Bui, Michelle P wrote
> #variable_conflict use_variable
> DECLARE
> v_status TEXT;
> BEGIN
> RETURN QUERY SELECT category, v_status as status, count (tool_id) AS
> tool_count
> FROM
> (SELECT distinct category, tool_id, 'active' as v_status
Seriously? Just pick a different alias for t
Andrew Sullivan-8 wrote
> On Tue, Apr 01, 2014 at 01:37:17PM -0700, Si Chen wrote:
>> You are right. That was the problem. I tried the query from
>> http://wiki.postgresql.org/wiki/Lock_Monitoring and found a COMMIT
>> transaction that was blocking it.
>>
>> I restarted postgresql again, and (it
Andrew Sullivan-8 wrote
>> So currently I've changed my code to use RETURNING and then I'm ordering
>> the results based on a secondary column that I know the order of. This
>> works, but seems clunky, so I'm wondering if there's a nicer way.
>
> This is probably what I'd do, assuming that "furthe
Si Chen-2 wrote
> I have two different postgresql servers running slightly [different]
> versions.
Versions 9.0 and 9.2 are NOT slightly different. These are two MAJOR
RELEASES (which allow for API changes) apart (i.e., one major release in
between - 9.1)
The release notes for 9.2 note this par
Leonardo M. Ramé-2 wrote
> select lag.id, lag.idtask, lag.code, lag.lg from (select idtask, code, id,
> lag(code, -1) over () as lg from tasks_test) as lag
First you want to include an ORDER BY in the OVER(...) clause, and probably
a PARTITION BY as well.
Then you move that to a sub-query (for e
Leonardo M. Ramé-2 wrote
> Hi, I'm looking for help with this query.
>
> Table Tasks:
>
> IdTask StatusCode StatusName
> --
> 1 R Registered
> 1 S Started
> 1 D Dictated
> 1 F Finished
> 1 T
Nithya Soman wrote
> Hi
>
> Could you please provide any method (query or any logfile) to check
> max connections happened during a time interval in psql DB ?
Only if the time interval desired in basically zero-width (i.e.,
instantaneous). The "pg_stat_activity" view is your friend in this.
You
Brian Crowell wrote
> Hello, it's me, a Postgres n00b again. I'm dealing with a query that
> scans a rather large table (94,000,000 tuples or so) and just picks
> out certain rows and sums them:
>
> select dci.snapshot_time as "time", round(sum(dci.todays_pl)::numeric,0)
> as pl
> from dbo._pl_dat
Glenn Pierce wrote
> I cannot work out how to pass a literal for the array of timerange types.
> '{(15:11:21, 18:11:21)}'::timerange[] does not work for example.
> Also I can not pass NULL for this parameter I get
since NULL can take on any type if you pass it literally you have to specify
the typ
Torsten Förtsch wrote
> Hi,
>
> I have a volatile function that returns multiple rows. It may also
> return nothing. Now, I want to write an SQL statement that calls this
> function until it returns an empty result set and returns all the rows.
>
> What's the best (or at least a working) way to a
I'm trying to follow the discussion on -hackers and decided I'd try putting
everything I'm reading into my own words. It is probable some or even all
of the following is simply wrong so please do not go acting on it without
other people providing supporting evidence or comments. I am a database
u
Kevin Goess wrote
> Can anybody help me understand what these statistics are suggesting,
> what's
> actually going on on this box/in postgresql? What is it writing to disk,
> and why? Is it just writing out new/changed rows, or what?
Not a clue on the statistics but most likely you are seeing ch
fluxh wrote
> I have the same problem. I have a log like patrick keshishian. I have not
> a backup.
>
> I don't know that do.
>
> Help me please!!!
>
> Information:
>
> - Server with RAID1 on SAS HD Hot-plug.
> - Ubuntu Server 12.04 x64.
> - Postgresql 9.1.
>
> When I do "psql" PostgreSQL show
Michael Paquier wrote
> On Fri, Mar 7, 2014 at 3:32 PM, leo <
> dazhoufei@
> > wrote:
>>I just complete my HA configuration on Redhat 6.4 enterprise:
>> Clusterware: Pacemaker 1.1.8 ( CMAN .0.12.1, corosync 1.4.1 )
>> Resource manager: PCS 0.9.26
>> PostgreSQL 9.3.3.1
Israel Brewster-2 wrote
> So my question is, aside from someone going in and mucking about in the
> wee hours of the morning, what could possibly cause this behavior? What
> sort of event could cause all data to be deleted from the table, and the
> sequence to be reset? Especially while there is an
Merlin Moncure-2 wrote
> On Tue, Mar 4, 2014 at 1:49 PM, Owen Hartnett <
> owen@
> > wrote:
>
>> It looks like I should be able to use the window function to do this,
>> but
>> I've been unsuccessful. The following runs, but doesn't seem to have any
>> effect:
>>
>> select owner_id, array_agg(
Evan Martin wrote
> Hi All,
>
> I have a database schema where if row is deleted from one table the rows
> it references in another table should also be deleted, unless still
> referenced by something else.
>
> Eg. Table A has foreign key to table B. When I delete a row from A I
> also want to
sparikh wrote
> Thanks David for your quick response. My original objective was to get the
> value of a particular node in xml which is stored as text datatype in the
> table.
>
> For example :
>
>
>
> -
>
> -
>
>
> 16
>
>
>
>
> I want to extract the value of amperage as '16'. Like that
sparikh wrote
> Hi,
>
> My current database version is "PostgreSQL 9.1.11 on
> x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat
> 4.4.7-3), 64-bit".
>
> I am trying to use function xpath in my query and got following error.
>
> "ERROR: function xpath(unknown, text) does
David Johnston wrote
>
> Eliot Gable-4 wrote
>> I advocated creating a separate mapping table which
>> maps the ID of these records to the other ID we are searching for and
>> performing a JOIN on the two tables with appropriate foreign key
>> relationships and
Eliot Gable-4 wrote
> I advocated creating a separate mapping table which
> maps the ID of these records to the other ID we are searching for and
> performing a JOIN on the two tables with appropriate foreign key
> relationships and indices. However, I was ask to instead put the list into
> a singl
Susan Cassidy-3 wrote
> Someone said something about
> "Yes, except those that are inside the aggregate."
> but I don't have an aggregate specified.
So every column then...
As soon as you add "group by" the rule becomes - every column is either a
group determinate or is aggregated. If you simpl
Susan Cassidy-3 wrote
> I have a large query:
>SELECT distinct on (srs.scan_run_id) srs.scan_run_id,
> srs.run_request_number, srs.container_id, srs.manifest_id,
> srs.scan_system_name_id,
>srs.scan_site_name_id, srs.scan_site_nickname_id,
> to_char(srs.start_time, 'MM/DD/YY HH24:MI:SS'
James Harper wrote
>>
>> You probably should define your domain like this:
>>
>>CREATE DOMAIN myvarchar varchar(42);
>>
>
> That's what I thought, so it won't do what I want. I need to be able to
> set the length at the time of declaration.
>
> So suppose I wanted to implement myvarchar in
bobspero wrote
> I went through postgres website since yesterday and can not upgrade my
> postgresql from 9.1 to anything greater. At first I did pgupgrade and was
> told to install postgres-xc, when I installed it, it removed 9.1 and I
> could not log into it. I was able to recover it and when I t
Andrus Moor wrote
> Budget table contains jobs with loads:
>
> create temp table budget (
> job char(20) primary key,
> load numeric(4,1) not null check (load>0 )
> );
> insert into budget values ( 'programmer', 3 );
> insert into budget values ( 'analyst', 1.5 );
>
George Ant wrote
> Thank you for your Response!
>
> Inserting a sub-select into the array seems to be the solution that I
> want, but it gives me this error--> subquery must return only one column
>
> Any help?
>
> Kind Regards,
> George Ant
In both cases you want to be storing a single compo
George Ant wrote
> Hey Guys,
>
> Thank you for your replies! Your suggestions worked fine!! :) Also my code
> looks a lot cleaner now!
>
> Kind Regards,
> George Ant.
Have you considered just creating a view, or even retrieval functions, the
generate these "object forms" on the fly instead of
alexandros_e wrote
> You must a) join the 2 tables on the orderID ... where orderID=15 and then
> GROUP BY the result by the order ID and concat the orderlines by a custom
> aggregate function like:
> http://www.postgresql.org/message-id/db7789b.0309131210.625da...@posting.google.com
Maybe back in
George Ant wrote
> Hey Guys,
>
> I have a table(Orders_object_table) of this type:
>
> CREATE TYPE orders_type AS
>(orderid integer,
> amount amount_type,
> customerid integer,
> orderdate date,
> orderlines orderlines_type[]);
>
> and I am trying to insert data from anothe
Adrian Klaver-3 wrote
> On 02/07/2014 11:08 AM, Joe Van Dyk wrote:
>> I'd like to have join_collapse_limit=20 for all users that belong to a
>> certain group. Is there a way to do that without having to alter all the
>> roles that are in that group?
>
> From what I see in the docs no:
>
> http:/
Reece Hart wrote
> On Thu, Feb 6, 2014 at 6:37 PM, David Johnston <
> polobo@
> > wrote:
>
>> Doubtful.
>>
>
> Yeah, that's what I had assumed too.
>
> The question is motivated entirely by what I think would make it easier
> for
> us
Reece Hart wrote
> I'd like to provide public access, without a password, to a database
> hosted
> on Amazon RDS.
>
> I'm familiar with using pg_hba.conf to enable trust (no) authentication
> for
> a user. pg_hba.conf is not available to DBAs on RDS.
>
> Is there any other way to achieve password
jvsrvcs wrote
> I tried that but get an error
> v_start_date date;
> v_minus_one_year date;
>
> I have v_start_date to start with and want to subtract one year and put
> into v_minus_one_year
>
> select v_start_date - interval '1 yr' as v_minus_one_year;
>
>
>
> --- I get:
>
> ERRO
CS DBA wrote
> Hi All;
>
> We have a client running Pentaho to migrate data.
>
> They recently upgraded Pentaho which upgraded their JDBC driver from 8.4
> (postgresql-8.4-703.jdbc4.jar) to 9.1 (postgresql-8.4-703.jdbc4.jar).
> They have a test set which updates 1000 rows, with the old driver
Evan Martin wrote
> In a nutshell: I think the difficulty of dropping functions is
> inconsistent with the difficulty of dropping other objects and I'd like
> to see this inconsistency fixed.
>
> So I don't agree with the suggestion of matching function names using a
> regex, since that's not s
Tom Lane-2 wrote
> I wonder whether we shouldn't address this by adding a few examples
> of that type of trick to the docs. Not sure where, though ...
Probably the Wiki would be a better place to put this kind of material. A
link to there from "21. Managing Database" would seem to be most
approp
Susan Cassidy-3 wrote
> I have a column that contains items like
> 'absolute root'
> 'root 3'
> 'root 4'
> 'root 5'
> 'scene 1'
> 'scene 2'
> 'scene 3'
>
> and I would like them to sort in that order.
>
> I tried:
> select sti.description, sc.description from scene_thing_instances sti join
> scen
Raphael Bauduin wrote
> Hi,
>
> I'm trying to understand what happens here:
>
> I have atype product defined:
>
> [...]
>
> which I'm trying to use in this query calling json_populate_recordset
>
> =# select q.* from (select json_populate_recordset(null::product,
> event->'products') from eve
Tom Lane-2 wrote
> Craig Ringer <
> craig@
> > writes:
>> I just want us to allow, by default, implicit casts FROM text (not TO
>> text) using the input function for all PostgreSQL's validated
>> non-standard types (and XML due to limited deployment of SQL/XML support
>> in client drivers).
>
>
Panneerselvam Posangu wrote
> to be specific, this is the SQL.
> SELECT to_number((SELECT
> array_to_string((SELECT xpath('/attributes/duration/text()',(SELECT
> XMLPARSE
> (CONTENT
> '
>
>
> 2
>
>
> 2
>
>
> true
>
>
> true
>
>
> 0
>
>
> ')),ARRAY[ARRAY['',
> '']])
>
>
> ),'','
Panneerselvam Posangu wrote
> Hi,
> When we run a SQL statement in Postgres 9.2 we get an error.
> Error : Could not register XML namespace with name "" and URI "" SQL State
> XX000
> In the SQL state we use xpath function. Any reason why this is happening..
> Thanks,Panneer
No. Try providing mor
Brooke Beacham wrote
> (without having to replicate/maintain a table of columns separately from
> the system catalog)
Just create the friggin' table and wrap whatever logic you want in a view
(or functions) so that you at least get usable results/defaults for any
columns you haven't added.
Any
Sameer Kumar wrote
> On Thu, Jan 9, 2014 at 1:26 AM, George Weaver <
> gweaver@
> > wrote:
>
>> ARRAY_TO_STRING( REGEXP_MATCHES('12-70510','^[0-9,0-9.0-9]+')
>> development(# , ',')
>
>
> I guess this part of your statement will return 1,2, which is a set
>
>
Nelson Green wrote
> My apologies, I was not completely clear. I will not know any of the
> columns in advance. The most recent insert is the result of user input
> from
> a web form, so I won't know what project or what user generated the last
> insert. That was why I wandered if that information
Adrian Klaver-3 wrote
>>
>>
>> In the real function I'm writing, the columns to be used in the string
>> being created are pulled from a configuration table, so their types
>> could be anything. So casting the quote_literal() calls is not really an
>> option here.
>>
>> Any help would be appreciate
john.tiger wrote
> select * from users where jsondata->>'username' = 'jdoe' works but
> returns field names, etc besides the data row.how can we get json
> data back ?
>
> select row_to_json from users where jsondata->>'username'='jdoe' didn't
> work.
>
> any ideas ? thks.
Using what clie
Using psql 9.3 against a 9.0 server.
I send queries to the server via a windows based client which uses and sends
"\r\n" for the newline. When running this query:
SELECT procpid, current_query, client_addr, xact_start, query_start FROM
pg_stat_activity
The contents of current_query display as:
john.tiger wrote
> 4) su - postgres
> > password --> submitted old one, worked
> $ psql
>
> psql: could not connect to server: No such file or directory
> Is the server running locally and accepting
> connections on Unix domain socket
> "/var/run/postgresql/.s.PGSQL.5432"?
>
>
Quick thoughts:
On both tables:
Convert your date-time varchar fields into a single epoch/integer field.
Create an index of that epoch/integer field.
David J.
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/bulk-loading-table-via-join-of-2-large-staging-tables-tp57
David Johnston wrote
> Or feel free to peruse the release notes for 9.2, this behavior change
> should be documented if intentional.
Reading said notes it appears that the "returns NULL" behavior compensates
for a concurrent DROP of an existing/known OID. Since your issue is tha
rummandba wrote
> HI,
>
> I am working on Postgresql 9.1.3.
> I executed the following query and got an error:
> select relname, pg_size_pretty(pg_table_size(relname::text)) as s from
> pg_stat_user_tables order by pg_table_size(relname::text) desc limit 10;
> ERROR: relation "tab_20130206" doe
Torsten Förtsch wrote
> Hi,
>
> assuming I have a query that computes a running sum like this:
>
> select id, buy_price, sum(buy_price) over (order by id) sum
> from fmb
>where 202300<=id and id<=202400
>order by id;
>
> Do I need the ORDER BY clause at the end? Or does the ORDER B
1 - 100 of 815 matches
Mail list logo
