All Questions
Tagged with string postgresql
28 questions
2
votes
2
answers
2k
views
How to use replace text function in a column using another table as a "lookup table" reference?
Here's some examples to show what I'm after. The source table (the one to have its string values changed):
| string |
|-----------------------|
| abc${hello}123 |
| def${yolo}321 ...
1
vote
1
answer
1k
views
How to prevent CONCAT function adding space in Postgresql?
Postgresql-11
select tick_time, nano_secs,
concat( to_char(tick_time, 'MMDD HH24:MI:SS.US'),
to_char(nano_secs, '000') )
from ticks
order by tick_time, nano_secs limit 100;
I want to ...
- 413
5
votes
1
answer
229
views
Match .csv values as INTs. If one value from a group in the .csv matches one in another group, then merge strings
Here we have two sets of numbers. The problem is that I can't figure out how to get from the input to the output of numbers (DDL and DML below and also in the fiddle here).
current_data
1,2,3
1,4
1,5,...
- 67
0
votes
1
answer
2k
views
Calculating Average Value of JSONB array in Postgres
I have a column called "value" in my "answers" table.
| value |
|---------|
| [1,2] |
| [1] |
| [1,2,3] |
The type of "value" is "jsonb".
I want to get ...
user236222
7
votes
1
answer
2k
views
text column compares equal to where clause but does not select matching row
We are having trouble when querying a table in our production database. One text column will compare equal to a string we filter on in the where clause, but postgres will not select the row.
(We are ...
- 73
0
votes
1
answer
183
views
Levenshtein function returning different result based on limit value
The Levenshtein function is not working as I would have expect. Is there anything that I minsunderstood?
Here is the query:
SELECT c0.id, c0.engine_type, c0.mpg, c0.kwh, c0.price, c0.make, c0.model, ...
- 395
0
votes
1
answer
107
views
String index for a list of values using IN($1,$2): will a btree index work?
We have a query that needs to compare a value to a list of values in a where clause. The strings are known and discrete. Does IN use the = operator to compare values? Would a b-tree index work for ...
- 103
1
vote
1
answer
577
views
Count numbers in scientific notation (E-notation)
I'm trying to check if a part number field has scientific notation values and came up with this simple solution:
SELECT count(*) as overall,
count(*) filter (where partnumber ilike '%E+%') as ...
- 141
0
votes
1
answer
233
views
How do I send an array of strings to PG from PHP without the former becoming confused?
I want to do this, but for more than one string:
SELECT * FROM table WHERE label <> $1
That selects all records from table where the label column is different from a single string. It works.
...
user212833
3
votes
1
answer
5k
views
Is there some function in PostgreSQL to simply check if the input string is valid UTF-8 according to PG?
For several years now, I've been hunting this extremely quick-footed little bug which knows every hiding place in my metaphorical coding house. He lurks in the walls, sometimes takes months to show ...
- 31
3
votes
1
answer
5k
views
character varying vs. text as function parameter types in PostgreSQL
Is there any significant difference (in performance for example) between character varying vs. text as types for db function parameters? I have 2 usage examples:
Example 1:
CREATE OR REPLACE ...
- 437
2
votes
1
answer
2k
views
How to output string values in single quotes?
I'm trying to convert values stored in a json into its string representation.
The #>> operator works for this purpose for numbers, e.g.:
SELECT '{"a":[1,2,3],"b":["4","5","6"]}'::json#>>'...
- 3,830
3
votes
3
answers
5k
views
Is there a more concise way in Postgres to extract a portion of a string?
I'm using Postgres 9.5. I have a table with a column that records URLs. Sometimes the URL has a query string and sometimes it does not. I would like to extract the URL, minus any query string, so I ...
- 753
2
votes
1
answer
912
views
Is there any string compaction option for PostgreSQL?
I have a table like this:
CREATE TABLE BLOG(
CATEGORY CHAR(32)
);
Category is a column, which stored enum values.
For example, I have 1 millions of rows and 10 unique values of Category.
Let's ...
- 123
13
votes
2
answers
21k
views
Conditional string concatenation in PostgreSQL
I have a table parcels which currently contains the columns owner_addr1, owner_addr2, owner_addr3. Sometimes, one or both of the latter two fields is empty. I want to combine them into a single new ...
- 379