On Friday, February 6, 2015, Adrian Klaver adrian.kla...@aklaver.com
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
On Fri, Feb 6, 2015 at 9:55 AM, Tim Smith randomdev4+postg...@gmail.com
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
On Fri, Feb 6, 2015 at 10:23 AM, Adrian Klaver adrian.kla...@aklaver.com
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
On Thu, Feb 5, 2015 at 4:01 PM, Tim Smith randomdev4+postg...@gmail.com
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
On Thu, Feb 5, 2015 at 4:29 PM, Tim Smith randomdev4+postg...@gmail.com
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,
On Thu, Jan 29, 2015 at 1:22 PM, Sterpu Victor vic...@caido.ro 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
On Thursday, January 22, 2015, tsunghan hsieh tsunghan.hs...@gmail.com
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
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.
I'm not sure
On Wed, Jan 21, 2015 at 4:09 PM, Bryn Jeffries bryn.jeffr...@sydney.edu.au
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
On Tue, Dec 30, 2014 at 8:54 AM, Adrian Klaver adrian.kla...@aklaver.com
wrote:
On 12/30/2014 07:43 AM, David G Johnston wrote:
Tom Lane-2 wrote
Bernd Helmle lt;
mailings@
gt; writes:
--On 29. Dezember 2014 12:55:11 -0500 Tom Lane lt;
tgl@.pa
gt; wrote:
Given the lack of
On Mon, Dec 29, 2014 at 8:49 AM, Adrian Klaver adrian.kla...@aklaver.com
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
On Mon, Dec 29, 2014 at 9:39 AM, Adrian Klaver adrian.kla...@aklaver.com
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 script. I imagined
On Mon, Dec 29, 2014 at 9:49 AM, David Johnston david.g.johns...@gmail.com
wrote:
On Mon, Dec 29, 2014 at 9:39 AM, Adrian Klaver adrian.kla...@aklaver.com
wrote:
On 12/29/2014 07:59 AM, David Johnston wrote:
Anyway, the third undocumented bug is that --single-transactions gets to
send its
Copying -bugs to gain broader attention and opinions.
On Mon, Dec 29, 2014 at 10:06 AM, Adrian Klaver adrian.kla...@aklaver.com
wrote:
On 12/29/2014 08:49 AM, David Johnston wrote:
On Mon, Dec 29, 2014 at 9:39 AM, Adrian Klaver
adrian.kla...@aklaver.com mailto:adrian.kla...@aklaver.comwrote
On Mon, Dec 29, 2014 at 3:07 PM, Adrian Klaver adrian.kla...@aklaver.com
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
example of why tests
On Mon, Dec 29, 2014 at 3:37 PM, Adrian Klaver adrian.kla...@aklaver.com
wrote:
On 12/29/2014 02:28 PM, David Johnston wrote:
On Mon, Dec 29, 2014 at 3:07 PM, Adrian Klaver
adrian.kla...@aklaver.com mailto:adrian.kla...@aklaver.comwrote:
On 12/29/2014 09:38 AM, David Johnston wrote
On Mon, Dec 29, 2014 at 4:38 PM, Adrian Klaver adrian.kla...@aklaver.com
wrote:
On 12/29/2014 02:55 PM, David Johnston wrote:
On Mon, Dec 29, 2014 at 3:37 PM, Adrian Klaver
adrian.kla...@aklaver.com mailto:adrian.kla...@aklaver.comwrote:
On 12/29/2014 02:28 PM, David Johnston wrote
On Mon, Dec 29, 2014 at 5:09 PM, Adrian Klaver adrian.kla...@aklaver.com
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 non-SQL error. As Viktor stated, SQL errors abort
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
On Thu, Dec 18, 2014 at 3:02 PM, Robert DiFalco robert.difa...@gmail.com
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
On Monday, December 8, 2014, Huang, Suya suya.hu...@au.experian.com wrote:
-Original Message-
From: pgsql-general-ow...@postgresql.org javascript:; [mailto:
pgsql-general-ow...@postgresql.org javascript:;] On Behalf Of David G
Johnston
Sent: Monday, December 08, 2014 1:18 PM
To:
Please send replies to the list.
On Friday, December 5, 2014, Ian Harding harding@gmail.com
javascript:_e(%7B%7D,'cvml','harding@gmail.com'); 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
On Fri, Nov 28, 2014 at 4:17 AM, Andrus kobrule...@hot.ee 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.
On Fri, Nov 28, 2014 at 9:40 AM, Andrus kobrule...@hot.ee 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.???[...]))
On Fri, Nov 28, 2014 at 10:47 AM, Andrus kobrule...@hot.ee 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
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
On Wednesday, November 26, 2014, Adrian Klaver adrian.kla...@aklaver.com
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
starts
On Thu, Nov 13, 2014 at 5:47 PM, Tom Lane t...@sss.pgh.pa.us wrote:
David G Johnston david.g.johns...@gmail.com 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
On Sunday, November 9, 2014, Adrian Klaver adrian.kla...@aklaver.com
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 most
, David Johnston
david.g.johns...@gmail.com javascript:; wrote:
On Sunday, November 9, 2014, Adrian Klaver adrian.kla...@aklaver.com
javascript:;
wrote:
On 11/09/2014 10:14 AM, David G Johnston wrote:
Adrian Klaver-4 wrote
Thank you for all comments and suggestions.
More
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 jorgearev...@libregis.org
wrote:
Hello David, many thanks for your responses,
Sorry for not providing the content of the fill_table3_function, but it
just
On Wed, Oct 29, 2014 at 11:53 AM, Tom Lane t...@sss.pgh.pa.us wrote:
Jorge Arevalo jorgearev...@libregis.org writes:
This is the result of EXPLAIN ANALYZE
QUERY
PLAN
On Wed, Oct 29, 2014 at 12:14 PM, Jorge Arevalo jorgearev...@libregis.org
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
On Friday, September 19, 2014, Alban Hertroys haram...@gmail.com wrote:
On 19 Sep 2014, at 3:50, Robert Nix rob...@urban4m.com javascript:;
wrote:
Thanks, David.
I have read that page many times but clearly I have forgotten this:
• Constraint exclusion only works when the
On Wed, Aug 6, 2014 at 10:08 AM, Jeff Janes jeff.ja...@gmail.com 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.
- 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. Pgscript is a
On Fri, Aug 1, 2014 at 6:22 PM, Chris Travers chris.trav...@gmail.com
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;
On Tue, Jul 22, 2014 at 9:46 AM, Anil Menon gakme...@gmail.com 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
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 from QRY where country =
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 connection can be
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
Re-posting, see quote
David Johnston wrote
Pavel Stehule wrote
2014-04-04 14:16 GMT+02:00 Tjibbe lt;
tjibbe@
gt;:
Thanks that works!
Little bit confusing ERROR.
yes, it could be - but hard to fix it, because it is based on cooperation
two worlds - plpgsql and SQL
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 is
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
chr_pos
138
140
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 value. My table looks like this:
SELECT chr_pos
FROM
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
Jeff Janes wrote
On Wed, Apr 2, 2014 at 12:00 PM, Bala Venkat lt;
akpgeek@
gt; 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 milli
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 seems)
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 the
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
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
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
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 further
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 have
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
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 type
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
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
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 achieve
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 shows psql: FATAL:
Michael Paquier wrote
On Fri, Mar 7, 2014 at 3:32 PM, leo lt;
dazhoufei@
gt; 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 lt;
owen@
gt; 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,
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 delete
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 not exist
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 :
?xml version=1.0?
-
settings
-
F999
amperage
16
/amperage
amperagesteps/
/F
/settings
I
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 single
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 indices. However, I was ask to instead put
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'),
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 simply want
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 C. In my _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 the
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 (load0 )
);
insert into budget values ( 'programmer', 3 );
insert into budget values ( 'analyst', 1.5 );
Actual
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 another
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,
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 altering
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 composite type
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:
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:
ERROR: query has
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-less
Reece Hart wrote
On Thu, Feb 6, 2014 at 6:37 PM, David Johnston lt;
polobo@
gt; wrote:
Doubtful.
Yeah, that's what I had assumed too.
The question is motivated entirely by what I think would make it easier
for
users. In principle it's not difficult to give people a password (as I
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 it
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
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
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 events where
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
scenes sc on
Tom Lane-2 wrote
Craig Ringer lt;
craig@
gt; 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).
Sorry,
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
'
attributes
duration
2
/duration
maxlicenses
2
/maxlicenses
paymentrequired
true
/paymentrequired
Sameer Kumar wrote
On Thu, Jan 9, 2014 at 1:26 AM, George Weaver lt;
gweaver@
gt; 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
Can you
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
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 more detail.
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 was
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 appreciated.
Cast
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
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 client? There
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?
tried to
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:
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 BY in the
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 does not
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 that
the object
1 - 100 of 813 matches
Mail list logo
