postgres append text to a text cell

Question

I have a postgres db with text records in a row named "filmid". looks like this:

select * from films order by filmid;

ID  filmid
1   3
2   23
3   241

now what I want to do for testing is first to change one cell value:

UPDATE films SET filmid = '0000' WHERE externalid = '23';

but that doesn't change anyting.

Later I want to append text ("0000") to all of the cell elements from the row filmid so that it looks like this:

ID  filmid
1   30000
2   230000
3   2410000

But I can't figure out how to do that

Why are you storing numbers in a text column? That's a bad idea to begin with — user330315
– user330315, Commented Dec 22, 2019 at 15:06
because for other stuff thats also used with alphabetical chars — temporalis
– temporalis, Commented Dec 22, 2019 at 15:12

GMB · Accepted Answer · 2019-12-22 14:34:52Z

1

Do you want string concatenation?

UPDATE films SET filmid = filmid || '0000';

answered Dec 22, 2019 at 14:34

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

temporalis Over a year ago

thanks a lot, that's it. Only one problem I found out with that: the stings have blank spaces attached. so your query does this: 3 0000 instead of 30000 how can I get rid of the blank spaces?

GMB Over a year ago

@temporalis: ok, so SET filmid = trim(filmid) || '0000'

temporalis Over a year ago

how can I remove the 0000 from the prior query to redoo it with the new one?

Collectives™ on Stack Overflow

postgres append text to a text cell

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related