FAQ
Dear all,

I want to make a primary key for my table based upon a few other
columns. I do this with the following code:

ALTER TABLE camdengps2
ADD COLUMN camdencrimes_link varchar;
UPDATE camdengps2
SET camdencrimes_link='' || EXTRACT(YEAR FROM date_time) || '0' ||
EXTRACT(MONTH FROM date_time) || '' || EXTRACT(DAY FROM date_time) ||
'' || incident

Essentially I am truncating the year, month, day and another field
called 'Incident'. However I am finding two problems with this:

1) I would like the result to be an integer rather than a varchar.
However when I set the field to be integer rather than varchar the SET
query doesn't work. I guess I maybe need to CAST the date as an
integer somehow?

2) When I extract the DAY from the date field, instead of the 2nd of
the month becoming '02' it actually becomes '2'. I would like it to be
'02'.

Any help greatly appreciated...

Best wishes

James

Search Discussions

  • James David Smith at Jun 17, 2011 at 7:13 pm
    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...

    James

    On 17 June 2011 12:11, James David Smith wrote:
    Dear all,

    I want to make a primary key for my table based upon a few other
    columns. I do this with the following code:

    ALTER TABLE camdengps2
    ADD COLUMN camdencrimes_link varchar;
    UPDATE camdengps2
    SET camdencrimes_link='' || EXTRACT(YEAR FROM date_time) || '0' ||
    EXTRACT(MONTH FROM date_time) || '' || EXTRACT(DAY FROM date_time) ||
    '' || incident

    Essentially I am truncating the year, month, day and another field
    called 'Incident'. However I am finding two problems with this:

    1) I would like the result to be an integer rather than a varchar.
    However when I set the field to be integer rather than varchar the SET
    query doesn't work. I guess I maybe need to CAST the date as an
    integer somehow?

    2) When I extract the DAY from the date field, instead of the 2nd of
    the month becoming '02' it actually becomes '2'. I would like it to be
    '02'.

    Any help greatly appreciated...

    Best wishes

    James
  • Thom Brown at Jun 17, 2011 at 11:05 pm

    On 17 June 2011 20:12, James David Smith 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.

    --
    Thom Brown
    Twitter: @darkixion
    IRC (freenode): dark_ixion
    Registered Linux user: #516935

    EnterpriseDB UK: http://www.enterprisedb.com
    The Enterprise PostgreSQL Company
  • James David Smith at Jun 18, 2011 at 5:02 pm
    Thanks Thom.

    What does the ' ::int ' bit do at the end?





    On Saturday, 18 June 2011, Thom Brown wrote:
    On 17 June 2011 20:12, James David Smith 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.

    --
    Thom Brown
    Twitter: @darkixion
    IRC (freenode): dark_ixion
    Registered Linux user: #516935

    EnterpriseDB UK: http://www.enterprisedb.com
    The Enterprise PostgreSQL Company
  • Thom Brown at Jun 18, 2011 at 5:24 pm

    On 18 June 2011 18:02, James David Smith wrote:
    Thanks Thom.

    What does the ' ::int  ' bit do at the end?
    That casts the whole lot to an int value so that it can be assigned to
    the column you're updating. Otherwise you could end up with an error
    message saying the types don't match.

    So ::int is equivalent to cast(column as int).

    Thom
  • Thom Brown at Jun 18, 2011 at 5:55 pm

    On 18 June 2011 00:05, Thom Brown wrote:
    On 17 June 2011 20:12, James David Smith 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
  • Thom Brown at Jun 18, 2011 at 6:01 pm

    On 18 June 2011 18:54, Thom Brown wrote:
    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);
    Correction, that last bit shouldn't be incident but just 4 on its own
    in order to demonstrate my point.

    --
    Thom Brown
    Twitter: @darkixion
    IRC (freenode): dark_ixion
    Registered Linux user: #516935

    EnterpriseDB UK: http://www.enterprisedb.com
    The Enterprise PostgreSQL Company

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-novice @
categoriespostgresql
postedJun 17, '11 at 11:12a
activeJun 18, '11 at 6:01p
posts7
users2
websitepostgresql.org
irc#postgresql

2 users in discussion

Thom Brown: 4 posts James David Smith: 3 posts

People

Translate

site design / logo © 2022 Grokbase