Re: Extract from date field

On 18 June 2011 00:05, Thom Brown <thom(at)linux(dot)com> wrote:
> On 17 June 2011 20:12, James David Smith <james(dot)david(dot)smith(at)gmail(dot)com> wrote:
>> Hey,
>>
>> I realise it's probably poor form to reply to your own question, but I
>> thought I'd just say I've managed to do this! Well, question 2 anyway.
>> To extract a DAY from a date field, and keep a leading zero, I have
>> used the 'lpad' function like so:
>>
>> SELECT lpad (cast((EXTRACT(DAY from timestamp '2010-01-01')) as
>> varchar), 2, '0')
>>
>> Except that in my query it looks like this:
>>
>> (SELECT lpad (cast((EXTRACT(DAY from date_of_incident)) as varchar), 2, '0'))
>>
>> I thought I'd post this incase it's off use to anyone else.
>>
>> Just need to figure out how to convert it all to a integer now...
>
> Ah, I believe I've replied to this on Twitter, but I'll post here too.
>  You can use the very handy to_char function to convert your date into
> a formatted string. (see
> http://www.postgresql.org/docs/9.0/static/functions-formatting.html )
> So you'd end up with:
>
> ALTER TABLE camdengps2
> ADD COLUMN camdencrimes_link varchar;
> UPDATE camdengps2
> SET camdencrimes_link=(to_char(date_time, 'DDMMYYYY') || incident)::int
>
> Hope that solves it for you.

Just to touch on your original problem though. You said you were
trying to create a primary key out of those 2 columns. You can,
however, define a primary key using multiple columns like so:

ALTER TABLE my_table ADD PRIMARY KEY (column_1, column_2);

This would then enforce a unique constraint based on a combination of
those columns.

So if the above statement were run on a table with 3 columns, you could insert:

1,1,2
1,2,1
2,2,1
2,1,1

As the first 2 column value are never duplicated (and the 3rd isn't
checked). But you couldn't do:

1,1,2
1,1,1

As this would mean column_1 and column_2 combined would be repeated
thus violating the primary key to identify a row.

The reason this is relevant is because if you've created a new column
as a primary key based on a combination of data from 2 other columns,
you will always have to know the exact value for the new column rather
than relying on PostgreSQL calculating based on other columns. The
update works on the existing table because the primary key isn't
enforced so the value didn't already have to be there. But once you
add the primary key, you'll have to insert this value yourself when
you add a new row.

So essentially you'd have to do something like:

INSERT INTO camdengps2 (date_time, incident, camdencrimes_link)
VALUES ('2011-04-18', 4, (to_char('2011-04-18'::timestamp, 'DDMMYYYY')
|| incident))::int);

Or, depending on what you're trying to do, you may just wish to have
this value created on-the-fly in a view:

CREATE VIEW camdengps_full AS
SELECT (to_char(date_time, 'DDMMYYYY') || incident)::int AS
camdencrimes_link, date_time, incident
FROM camdengps2;

Then there would be no extra column to update as it's calculated every
time you query the view, although if you needed to return hundreds of
thousands of rows each time, this could be computationally expensive.

But since I don't actually know the ultimate purpose of your question,
this may or may not be relevant. I just thought I'd add it in case it
allows you to solve it differently.

Thom