How to insert first character of one column into another column?

Question

I have a table with more than 30.000 entries and have to add a new column (zip_prefixes) containing the first digit of the a zip code (zcta).

I created the column successfully:

alter table zeta add column zip_prefixes text;

Then I tried to put the values in the column with:

update zeta
set zip_prefixes = (
     select substr(cast (zctea as text)1,1) 
     from zeta)
     )

Of course I got:

error more than one row returned by a subquery used as an expression

How can I get the first digit of the value from zctea into column zip_prefixes of the same row?

Answer 1

No need for sub-select:

update zeta
  set zip_prefixes = substr(zctea, 1, 1);

Answer 2

update zeta set zip_prefixes = substr(zctea as text)1,1) There is no need for select query and casting

Answer 3

1. Consider not adding a functionally dependent column. It's typically cleaner and cheaper overall to retrieve the first character on the fly. If you need a "table", I suggest to add a VIEW.

2. Why the need to cast(zctea as text)? A zip code should be text to begin with.

3. Name it zip_prefix, not zip_prefixes.

4. Use the simpler and cheaper left():

CREATE VIEW zeta_plus AS
SELECT *, left(zctea::text, 1) AS zip_prefix FROM zeta;  -- or without cast?

---

If you need the additional column in the table and the first character is guaranteed to be an ASCII character, consider the data type "char" (with double quotes). 1 byte instead of 2 (on disk) or 5 (in RAM). Details:

• What is the overhead for varchar(n)?
• Any downsides of using data type "text" for storing strings?

And run both commands in one transaction if you need to minimize lock time and / or avoid a visible column with missing values in the meantime. Faster, too.

BEGIN;
ALTER TABLE zeta ADD COLUMN zip_prefix "char";
UPDATE zeta SET zip_prefixes = left(zctea::text, 1);
COMMIT;