Linked Questions
28 questions linked to/from Best way to check for "empty or null value"
226
votes
9
answers
458k
views
How to concatenate columns in a Postgres SELECT?
I have two string columns a and b in a table foo.
select a, b from foo returns values a and b. However, concatenation of a and b does not work. I tried :
select a || b from foo
and
select a||', '...
- 7,283
312
votes
3
answers
403k
views
IN vs ANY operator in PostgreSQL
What is the difference between IN and ANY operator in PostgreSQL?
The working mechanism of both seems to be the same. Can anyone explain this with an example?
- 10.8k
47
votes
3
answers
26k
views
Any downsides of using data type "text" for storing strings?
According to the PostgreSQL Documentation, they support 3 data-types for character data:
character varying(n), varchar(n) variable-length with limit
character(n), char(n) fixed-length, ...
- 1,436
24
votes
2
answers
40k
views
How to match not null + not empty?
I have to do some queries on a messy database. Some columns are filled with either null or an empty string. I can do a query like this:
select * from a where b is not null and b <> '';
But is ...
- 31.5k
25
votes
2
answers
24k
views
array_length() of an empty array returning NULL
I'm developing some stored proceduces in PL/pgSQL and some of them are giving me some problems. The sprocs I'm developing receive by parameter an array which I use in a FOR LOOP to get all its ...
- 385
1
vote
2
answers
8k
views
Test column for special characters or only characters / numbers
I tried finding special characters using generic regex attributes and NOT LIKE clause but have been getting confusing results. The research suggested that it does not work the way it works in SQL ...
- 149
2
votes
4
answers
1k
views
Split comma separated values into target table with fixed number of columns
I have a table with a single column in a Postgres 13.1 database. It consists of many rows with comma-separated values - around 20 elements at most.
I want to split the data into multiple columns. But ...
- 33
1
vote
1
answer
8k
views
Check if value is not null and not empty in a select query
I have created a function in Postgresql and specified the returned type as TABLE (id uuid, data boolean).
This is the code that I have tried:
BEGIN
RETURN QUERY SELECT table.id, (table.data <&...
- 173
-1
votes
1
answer
2k
views
Postgres "ERROR: syntax error at or near" but no apparent syntax error
I'm running into kind of an odd problem. I'm trying to make a view in PostgreSQL, but I'm getting the error mentioned in my title. From what I can tell I don't have any syntax errors, but Postgres is ...
- 35
0
votes
2
answers
3k
views
Split column into columns using split_part returning empty
I have a column stored as text in postgres 9.6. It's an address and some of the rows have format like BUILD NAME, 40
I saw this answer which looks like what i want, if i run;
select split_part('...
- 1,887
3
votes
2
answers
701
views
How to allow multiple blanks (empty strings) using a unique index?
I have a unique index like so in my migration
add_index :table, :name, unique: true
Now the unique constraint allows for multiple nil values however I also want blank values (empty strings "&...
- 2,059
0
votes
1
answer
885
views
How to select by null or zero length?
I have query:
select text_b, id from articles where title is not null;
But i want to show results where text_b is not null and length of text_b > 0.
How to do it?
- 13.6k
0
votes
1
answer
1k
views
Cast to int instead of decimal?
I have field that has up to 9 comma separated values each of which have a string value and a numeric value separated by colon. After parsing them all some of the values between 0 and 1 are being set ...
- 43
1
vote
2
answers
893
views
SQL: "condition is not true" pattern as replacement for "is null or not (condition)"
In SQL (specifically Postgres):
The clause where not foo='bar' in case foo is null evaluates into some sort of null, causing the row is not included in the result.
On the other hand, the clause ...
- 1,043
0
votes
1
answer
1k
views
Postgresql "Column must appear in the GROUP BY clause or be used in an aggregate function" when using CASE expression inside ORDER BY clause
I get the column "measurementResults.value" must appear in the GROUP BY clause or be used in an aggregate function error for this query:
SELECT avg("measurementResults"."value&...
- 13