select * from depesz;

Let’s imagine following situation:

create table test (id int4 primary key, priority int4);
insert into test (id)
select distinct (random() * 100000000)::int4 from generate_series(1,1000);

Table test will now contain some (up to 1000) records, with random ids.

Now, we want to update first 3 records (ordered by id) to have following values in priority:

1. 10000
2. 5000
3. 1000

- MORE -

Let’s assume you have very simple table with users:

# \d users
Table "public.users"
Column | Type | Modifiers
-----------+---------+----------------------------------------------------
id | integer | not null default nextval('users_id_seq'::regclass)
username | text | not null
is_active | boolean | not null default true
Indexes:
"users_pkey" PRIMARY KEY, btree (id)
"users_username_key" UNIQUE, btree (username)

And you’d like to count all users, and know how many of them are active …

- MORE -

cortilap @ freenode’s #postgresql asked about how to create a check() that will allow only one of the columns to be not null.

it doesn’t sound cool, let’s see:

with 2 columns (a,b) you make a check: check ( (a is not null and b is null) or (a is null and b is not null) or (a is null and b is null))

whoa. and what about 3 columns? 4?

of course it creates some questions about the schema, but is there a way to do it? without such long checks?

one solution is to make a function to check it. but perhaps a simpler solution is possible?

luckily all of the fields are ints.

a quick think, and here we go:

check (coalesce(a*0, 1) + coalesce(b*0, 1) + coalesce(c*0, 1) > 1)

and what is the field was text? same thing, but instead of doing “X”*0, i would do “length(X)*0″

faber4 on irc asked about how to get ascii-based sorting, while his postgresql was initdb’ed with utf-8 based locale (en_US.UTF-8 to be exact).

what can we do about it?

- MORE -

luckymurali_81 on freenodes #postgresql had a problem. his query returns data in wrong order. what can we do about it?

- MORE -

mage_ from #postgresql had interesting problem today.

he has a table with 2 date fields, and he wants to have list of all years from both fields. together. as one list.

his approach:

select date_part('year', date1) from test
union
select date_part('year', date2) from test;

is hardly satisfactory - it takes too long.

any way to speed it up?
- MORE -