Multiple values in single Postgres database table column

Question

I recently took over as db admin for an app I am unfamiliar with. I was poking around the development db instance and found a table with multiple values in a single column. I am not sure how to query on this column as what I have tried thus far has not worked.

I have tried various characters between the column name and what looks like a key value pair in the column value itself. I am trying to select based on the state :name value. None of the below have worked and I have not yet found the postgres doc that addresses this.

select * from district where state = 'California';

select * from district where state:name = 'California';

select * from district where state_name = 'California';

select * from district where state[name] = 'California';

select * from district where state[:name] = 'California';
select * from district where state[0] = 'California';

Below is a specific entry from the table districts

id         | 8
name       | 13
state      | --- + | :name: California + | :abbr: CA + |
created_at | 2011-12-08 04:31:15.104002
updated_at | 2011-12-08 04:31:15.104002

That is a custom format, that does not follow anything I have seen. You will have to parse that yourself using strpos() and substr(). There is no built-in function to deal with such a "structured string". What will obviously work for the example at hand is where state like '%California%' — user330315
– user330315, Commented Nov 17, 2020 at 16:54
@Abelisto output below select format('%L', state) from district where id = 8; ERROR: relation "district" does not exist LINE 1: select format('%L', state) from district where id = 8; — MrNiceGuy
– MrNiceGuy, Commented Nov 17, 2020 at 16:58
@a_horse_with_no_name Thanks, this query works. Moving on to how to parse in ruby rails. — MrNiceGuy
– MrNiceGuy, Commented Nov 17, 2020 at 16:59

Laurenz Albe · Accepted Answer · 2020-11-17 16:58:17Z

1

Try a regular expression:

WHERE name  ~ '\mCalifornia\M'

answered Nov 17, 2020 at 16:58

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

Collectives™ on Stack Overflow

Multiple values in single Postgres database table column

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related