[ILUG] Database question...

[Colin Rooney]

Check out the row_number function it might help you.
http://www.postgresql.org/docs/9.1/static/functions-window.html


On 17 December 2012 12:10, Braun Brelin <bbrelin at gmail.com> wrote:

> David,
>
> Thanks for the reply.  The insert will work, but the problem with doing an
> update here is that the column of data that I want to load  doesn't have a
> relationship with the other columns.  i.e. I just want to load the data
> starting at row 1 and going down the rows in sequential order.  I was
> hoping that there was some sort of clever technique to do this that I
> wasn't aware of.
>
> On Mon, Dec 17, 2012 at 12:06 PM, David Pintor <painterman at gmail.com>
> wrote:
>
> > Hi Braun,
> >
> > You need to do an update (not an insert), but you can only use one value
> > per statement as you're targeting only one column.
> >
> > If you need to update many rows with different values you need to use
> > conditions, depending on your needs. Something like:
> >
> > update my_table set item_amount_minimum_value = 10 where id < 100
> > update my_table set item_amount_minimum_value = 20 where id = 200
> > update my_table set item_amount_minimum_value = 30 where item_name like
> > '%bla%'
> > etc...
> >
> > Hope this helps.
> >
> > David
> >
> >
> >
> >
> >
> >
> >
> > On 17 December 2012 11:56, Braun Brelin <bbrelin at gmail.com> wrote:
> >
> >> Hi all,
> >>
> >> I've been searching on google and asking questions in forums with no
> real
> >> result, so I figured I'd try here:
> >>
> >>
> >> I'm using a postgres 9.1 database on Ubuntu Linux 12.04
> >>
> >> I have a table that looks like this:
> >>
> >> id serial primary key not null
> >> item_name varchar (40)
> >> item_amount integer
> >>
> >> I then want to add a new colum via alter table which is also an integer,
> >> such as item_amount_minimum_value
> >>
> >> Assuming that I already have data in the table for the other three
> >> columns,
> >> how can I populate the new column with data?
> >>
> >> I've tried doing this:  update my_table set item_amount_minimum_value =
> >> (10,5,2,10,20)
> >> but this fails as postgres thinks that the values are a row record and
> not
> >> a list of integers.
> >>
> >> I supposed I could do something like this:  insert into my_table
> >> (item_amount_minimum_value) values (10),(5),(2), however is it possible
> to
> >> do this with an update rather than an insert?
> >>
> >> Thanks,
> >>
> >> Braun Brelin
> >> --
> >> Irish Linux Users' Group mailing list
> >> About this list : http://mail.linux.ie/mailman/listinfo/ilug
> >> Who we are : http://www.linux.ie/
> >> Where we are : http://www.linux.ie/map/
> >>
> >
> >
> >
> > --
> > http://david.dposs.org
> >
> >
> --
> Irish Linux Users' Group mailing list
> About this list : http://mail.linux.ie/mailman/listinfo/ilug
> Who we are : http://www.linux.ie/
> Where we are : http://www.linux.ie/map/
>