Re: Extract from date field
От | Thom Brown |
---|---|
Тема | Re: Extract from date field |
Дата | |
Msg-id | BANLkTi=OjYup98PuXMiY_BxikDLhoVjAHA@mail.gmail.com обсуждение исходный текст |
Ответ на | Re: Extract from date field (Thom Brown <thom@linux.com>) |
Ответы |
Re: Extract from date field
|
Список | pgsql-novice |
On 18 June 2011 00:05, Thom Brown <thom@linux.com> wrote: > On 17 June 2011 20:12, James David Smith <james.david.smith@gmail.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
В списке pgsql-novice по дате отправления: