.html
Example:
select username, row_number() over() from my_user_table;
username | row_number
-+
admin | 1
everyone| 2
--
Andreas Joseph Krogh
Senior Software Developer
Hi.
I am trying to create a datefield using YEAR, MONTH and DAY fields of type
integer.
I tried this query, but it did not give good results:
select to_date(gwsyear::text || gwsmonth::text || gwsday::text,
'-MM-DD') FROM cshapes
"1966526-01-01"
"7991903-12-13"
"1962831-01-01"
"194611-01-01"
"1
Excellent. I did not see that.
Thank you
2010/10/6 Thomas Kellerer
> Andreas Forø Tollefsen, 06.10.2010 13:11:
>
> Hi.
>>
>> I am trying to create a datefield using YEAR, MONTH and DAY fields of type
>> integer.
>> I tried this query, but it did not giv
ndElement(String name) events to insert
the element's content into your db.
- --
Andreas Joseph Krogh
Senior Software Developer / CTO
Public key: http://home.officenet.no/~andreak/public_key.asc
- +-+
OfficeNet AS
Hi all!
I am working on a query to identify which group ids exists within a spatial
cell. In this case i have the GREG table which has polygon data and the
priogrid_land which have the cell polygon.
I want to identify which and how many GREG group ids exists within each of
the priogrid_land cells.
Great. Thanks. Do you have a suggestion on how to ignore the group id's with
0 as value?
I dont want these to be counted.
Thanks!
2011/2/17 bricklen
> On Thu, Feb 17, 2011 at 6:20 AM, Andreas Forø Tollefsen
> wrote:
> > Hi all!
> >
> > I have tried the below quer
Hi all,
This is probably quite simple. I want to calculate the ratio of each country
(gwcode) which experiences a 1 in one column (cf).
Structure:
gid; gridyear; gwcode; area; cf
I want to select the SUM(area) WHERE cf = 1 into one column, and SUM(area)
WHERE cf = 0 into another column and grou
Great. This works like I wanted.
Thanks!
r to give you an answer if you provided a query which worked before which now, in 9.1, gives you trouble.
--
Andreas Joseph Krogh - mob: +47 909 56 963
Senior Software Developer / CTO - OfficeNet AS - http://www.officenet.no
Public key: http://home.officenet.no/~andreak/public_key.asc
tomer.status = b.status_id
WHERE b.status_id > 0
But you can JOIN on SELECTs selecting arbitrary stuff.
--
Andreas Joseph Krogh mob: +47 909 56 963
Senior Software Developer / CTO - OfficeNet AS - http://www.officenet.no
Public key: http://home.officenet.no/~andreak/public_key.asc
I have the following query:
select lower(firstname) || ' ' || lower(lastname) from person
firstname and lastname are VARCHAR
lower() returns NULL when firstname OR lastname is NULL, is this correct?
This is 8.2devel from 24.08.2006.
--
Andreas Joseph Krogh <[EMAIL PROTECTED]>
a way to do
this with an index.
--
Andreas Joseph Krogh <[EMAIL PROTECTED]>
Senior Software Developer / Manager
gpg public_key: http://dev.officenet.no/~andreak/public_key.asc
+-+
OfficeNet AS| The most dif
On Wednesday 28 February 2007, Richard Huxton wrote:
> Andreas Joseph Krogh wrote:
> > Hi all!
> > Is there a simple way to add a "NOT NULL constraint" to a column without
> > using a trigger if another column is not null?
> > Something like this:
> &g
Hi all.
Anybody knows if the following query will use an index-scan in PG-8.3?
SELECT name FROM person WHERE name LIKE 'and%';
I know this works in "C"-locale, but I need it with UTF-8, probably the rest
of the world soon too...
--
Andreas Joseph Krogh <[EMAIL PROT
On Thursday 28 June 2007 17:20:56 Tom Lane wrote:
> Andreas Joseph Krogh <[EMAIL PROTECTED]> writes:
> > Anybody knows if the following query will use an index-scan in PG-8.3?
> > SELECT name FROM person WHERE name LIKE 'and%';
> > I know this works in "C
On Thursday 28 June 2007 17:54:59 Tom Lane wrote:
> Andreas Joseph Krogh <[EMAIL PROTECTED]> writes:
> > Anybody knows if queries of type "LIKE '%234%'" ever will be able to use
> > indexes, and if someone is working on it?
>
> Perhaps you are looki
INTO $1 (some_field) VALUES('some_value')
Any hints on how to use function-parameters as table-names like I'm trying to
above?
--
Andreas Joseph Krogh <[EMAIL PROTECTED]>
Senior Software Developer / Manager
+
7;andrea%'
INTERSECT SELECT t.id FROM test t WHERE t.field = 'firstname' AND t.value
LIKE 'jose%'
INTERSECT SELECT t.id FROM test t WHERE t.field = 'lastname' AND t.value
LIKE 'kro%';
id
1
(1 row)
Is there a way to make this more efficient
On Tuesday 31 July 2007 18:52:22 Josh Trutwin wrote:
> On Tue, 31 Jul 2007 17:30:51 +
>
> Andreas Joseph Krogh <[EMAIL PROTECTED]> wrote:
> > Hi all. I have the following schema:
> >
> > CREATE TABLE test (
> > id integer NOT NULL,
> > fi
t; to ensure that it
will use an index if one appropriate exists
--
Andreas Joseph Krogh <[EMAIL PROTECTED]>
Senior Software Developer / Manager
+-+
OfficeNet AS| The most difficult thing in the world is to |
Karensly
On Thursday 09 August 2007 22:00:54 Gregory Stark wrote:
> "Andreas Joseph Krogh" <[EMAIL PROTECTED]> writes:
> > I create an index:
> > CREATE INDEX person_lowerfullname_idx ON
> > person((lower(COALESCE(firstname, '')) || lower(COALESCE(lastname,
On Thursday 09 August 2007 22:57:35 Rodrigo De León wrote:
> On 8/9/07, Andreas Joseph Krogh <[EMAIL PROTECTED]> wrote:
> > Ooops, just fugured that out. But - it still doesn't use the index if I
> > remove the "varchar_pattern_ops".
>
> Huh?
>
> C
On Thursday 09 August 2007 22:38:46 Andreas Joseph Krogh wrote:
> On Thursday 09 August 2007 22:00:54 Gregory Stark wrote:
> > "Andreas Joseph Krogh" <[EMAIL PROTECTED]> writes:
> > > I create an index:
> > > CREATE INDEX person_lowerfullname_id
ent sort-order) use an index? Preferrably without having
to create 2^N indexes.
--
Andreas Joseph Krogh <[EMAIL PROTECTED]>
Senior Software Developer / Manager
+-+
OfficeNet AS| The most difficult thing in t
On Friday 10 August 2007 23:30:14 Tom Lane wrote:
> Andreas Joseph Krogh <[EMAIL PROTECTED]> writes:
> > Is there a way I can have multiple columns in the ORDER BY clause, each
> > with different ASC/DESC-order and still use an index to speed up sorting?
>
> A btree inde
On Saturday 11 August 2007 21:05:22 hubert depesz lubaczewski wrote:
> On Fri, Aug 10, 2007 at 04:53:12PM +0200, Andreas Joseph Krogh wrote:
> > I have the following test-case:
> >
> > CREATE TABLE test(
> > name varchar PRIMARY KEY,
> > value varchar NOT NUL
On Saturday 11 August 2007 21:55:49 Tom Lane wrote:
> Andreas Joseph Krogh <[EMAIL PROTECTED]> writes:
> > On Friday 10 August 2007 23:30:14 Tom Lane wrote:
> >> Reverse-sorted index columns are possible but not well supported in
> >> existing PG releases (you need
array
FROM item i, item_log il WHERE i.id = il.item_id GROUP BY il.price, i.id;
ERROR: subquery uses ungrouped column "il.id" from outer query
Any hints?
--
Andreas Joseph Krogh <[EMAIL PROTECTED]>
Senior Softwar
On Saturday 25 August 2007 17:10:57 Andreas Joseph Krogh wrote:
> Hi all.
> I have the following schema:
>
> create table item(
> id serial primary key
> );
>
>
> create table item_log(
> id serial primary key,
> item_id integer not null references i
On Saturday 25 August 2007 23:02:19 Ragnar wrote:
> On lau, 2007-08-25 at 17:55 +0200, Andreas Joseph Krogh wrote:
> > On Saturday 25 August 2007 17:10:57 Andreas Joseph Krogh wrote:
>
> [snip]
>
> > > count | item
Hi all. Any hint on how to format this interval as number of hour/seconds etc?
select age('2007-09-22 17:00'::timestamp, '2000-02-20 18:00'::timestamp);
age
---
7 years 7 mons 1 day 23:00:00
--
Andreas Joseph Krogh <[EMAIL PROT
On Sunday 16 September 2007 13:14:27 Andreas Kretschmer wrote:
> Andreas Joseph Krogh <[EMAIL PROTECTED]> schrieb:
> > Hi all. Any hint on how to format this interval as number of hour/seconds
> > etc? select age('2007-09-22 17:00'::timestamp, '20
On Sunday 16 September 2007 17:41:56 Tom Lane wrote:
> Andreas Joseph Krogh <[EMAIL PROTECTED]> writes:
> > On Sunday 16 September 2007 13:14:27 Andreas Kretschmer wrote:
> >> You can use extract(epoch, from ...) like this:
> >>
> >> test=*# select extrac
On Friday 12 October 2007 17:02:23 Jonah H. Harris wrote:
> On 10/12/07, Andreas Joseph Krogh <[EMAIL PROTECTED]> wrote:
> > Anybody knows if Oracle has an equivalent of PG's array_accum or
> > ARRAY(subselect) construct?
>
> Something like this:
>
> CR
n where to look in Oracle-docs are welcome.
--
Andreas Joseph Krogh <[EMAIL PROTECTED]>
Senior Software Developer / Manager
+-+
OfficeNet AS| The most difficult thing in the world is to |
Karenslyst Allé 11
m and over() which can be used to accomplish this:
SELECT tmp.*, max(rownum) over() as total_count
FROM (subquery) tmp
Does PG have any equivalent way?
--
Andreas Joseph Krogh <[EMAIL PROTECTED]>
Senior Software Developer / Manager
+-
count(*)"-query to count the totals, which is exactly
what I'm trying to avoid.
Oracle has a special rownum and over() which can be used to accomplish this:
SELECT tmp.*, max(rownum) over() as total_count
FROM (subquery) tmp
Does PG have any equivalent way?
--
Andreas Josep
On Monday 05 November 2007 15:18:22 Tom Lane wrote:
> Andreas Joseph Krogh <[EMAIL PROTECTED]> writes:
> > AFAICS the information about the *total* number of rows is in the
> > "result" somehow. When I execute a "limit 1" query with EXPLAIN ANALYZE,
>
On Monday 05 November 2007 16:27:03 Gregory Stark wrote:
> > Andreas Joseph Krogh <[EMAIL PROTECTED]> writes:
> >> On Monday 05 November 2007 15:18:22 Tom Lane wrote:
> >>> That's only an estimate. Since the query doesn't get executed to
> >>&
y, t.value from test1 t where t.key = 'A'
UNION
select t.username, t.key, t.value from test1 t where t.key = 'B'
UNION
select t.username, t.key, t.value from test1 t where t.value NOT IN (
select value from (
select t.username, t.key, t.value from test1 t where t.key =
Hi.
Is it considered "safe" to use 8.1's pg_dump to dump an 8.2-db and load it
into 8.1?
--
Andreas Joseph Krogh <[EMAIL PROTECTED]>
Senior Software Developer / Manager
+-+
OfficeNet AS| The mo
time=0.031..424.250 rows=200828 loops=1)
Total runtime: 2574.113 ms
(9 rows)
Can anybody point out to me why PG doesn't perform better on the last query?
--
Andreas Joseph Krogh <[EMAIL PROTECTED]>
Senior Software Developer / Manager
+--
would be nice if one could have elements in test.id_array to reference
elements in master.my_id, with all the benefits of ON DELETE | UPDATE etc.
I know I can accomplish this with triggers, but if there exists something
built-in I'd like to know.
--
Andreas Joseph Krogh <[EMAIL P
Does PG have any way of doing $subject without writing a plpgsql-function
which does it by querying the catalog manually?
I'm looking for an equivalent of "DROP TABLE IF EXISTS "
--
Andreas Joseph Krogh <[EMAIL PROTECTED]>
Senior Softwar
let me have a link.
It is very hard to help without you providing the schema for the tables/views
involved. It sounds like you don't have any indexes if you experience
performance-problems on queries like "select * from view_transaction where
member_id = 999 and receipt_no is null&
need?
Your tables, views and index definitions.
> Any other advise?
You haven't provided any information on how your tables/views look like and
what indexes you have defined. A rule of thumb is to define an index for each
column you join on.
--
Andreas Joseph Krogh <[EMAIL P
.
Note that usually INSERT, UPDATE and DELETE return an integer > 0, indicating
affected rows, but not always. There might be 0 affected rows, which is not
nessesarily an error.
--
Andreas Joseph Krogh <[EMAIL PROTECTED]>
Senior Software Developer / Manager
+--
have NULLs in some of the rows so your "NOT IN" doesn't work. I
suggest you rewrite to something like:
... WHERE (securitytypekey IS NOT NULL OR securitytypekey NOT IN (5,27))...
> Question: does a UNIQUE constraint create an index?
Yes.
> Maybe your fresh eyes will see som
E col NOT IN ()" doesn't match NULL-values for "col",
so these will both return "false" for NULL-value of "col":
WHERE col NOT IN (2,3)
WHERE col = 2
The reason is that NULL is "unknown", so testing against it a
.
I'm trying to make a function which finds all my old "$1" constraints and
replaces those names with proper names (_fkey).
So - anybody who knows a good solution for how to find all tables with
constraint-names that have foreign keys referencing a table's particluar colum?
--
out how to safely get all columns which reference test1.id?
-hackers; Any hints?
BTW:
andreak=# select version();
version
-------
PostgreSQL 8.3.3 on i486-pc-linux-gnu, compiled by GCC cc (GCC) 4.2.3 (Ubuntu
4.2.3-2ubuntu7)
--
Andreas Joseph Krogh <[EMA
On Friday 07 November 2008 21:09:33 Tom Lane wrote:
> Andreas Joseph Krogh <[EMAIL PROTECTED]> writes:
> > AFAICS this lists all tables which have a column named '?', which is not
> > what I'm after. I'm after listing all columns referencing a certain col
ou'll loose transactional safety the DB provides. That reason
alone is good enough to justify storing the files in a BLOB (BYTEA in PG).
The length(BYTEA)-function in PG can be used to retrieve its length. It is
IMMUTABLE and "constant time", which means you can apply a functio
QUERY PLAN
----
Seq Scan on onp_crm_activity_log (cost=0.00..319.29 rows=2968 width=4)
(actual time=14.542..15.794 rows=83 loops
position the cursor. This will probably not work correctly for all
> tranasaction isolation levels though but it will penalize only these
> cases that absolutely need it. The penalty will of course be
> heavier ;(
rescan can only work in serializable isolation, no?
In co
401 - 455 of 455 matches
Mail list logo
