I use this select on my SQLite database:
SELECT attributeX
FROM table
GROUP BY attributeX
HAVING COUNT(attributeY) = 0;
My table:
attributeX (type integer) | attributeY (type text)
----------------+-------
1452 | aaa
1452 | bbb
1452 |
1452 | bbb
1453 |
1453 |
1453 |
1454 | eee
1454 | fff
1455 | iii
....
I get result 1453
only when all items in column attributeY
are empty for 1453
. So in SQLite I get result 1453
.
I have the same database only with myschema
in PostgreSQL. I use this SELECT
:
SELECT attributeX
FROM myschema.table
GROUP BY attributeX
HAVING COUNT(attributeY) = 0;
and I get:
attributeX
------------
(0 rows)
How to select in PostgreSQL?
SQLite schema of this table:
CREATE TABLE table (
attribute1 INTEGER PRIMARY KEY,
attributeX INTEGER NOT NULL,
attribute2 TEXT NOT NULL,
attributeY TEXT
);
CREATE INDEX table_attributeY_idx ON table (attributeY);
CREATE INDEX table_attribute2_idx ON table (attribute2);
CREATE INDEX table_attributeX_idx ON table (attributeX);
PostgreSQL schema of this table:
Table "myschema.table"
Column | Type | Modifier | Storage | Desc
----------------+-----------------------+-------------------------------------------------------------------------+----------+-------
attribute1 | integer | not null implicitly nextval('myschema.table_attribute1_seq'::regclass) | plain |
attributeX | integer | not null | plain |
attribute2 | character varying(32) | not null | extended |
attributeY | text | | extended |
Indexy:
"table_attribute1_pkey" PRIMARY KEY, btree (attribute1)
"fki_table_attributeX_fkey" btree (attributeX)
Conditions for foreign key:
"table_attributeX_fkey" FOREIGN KEY (attributeX) REFERENCES myschema.table2(attributeX2) ON UPDATE RESTRICT ON DELETE CASCADE
Has OID: no
Insert:
INSERT INTO myschema.table (attributeX, attribute2, attributeY)
VALUES (%s, %s,%s)... (python script)
Best Answer
You explain:
But that's incorrect. Bold emphasis mine. The aggregate function
count
returns (per documentation):The same is true for SQLite (per documentation):
You obviously have one or more rows with a non-null value in
attributeY
in your Postgres table - probably an empty string''
.Test with:
Be sure to understand the difference between "empty" (
''
) andNULL
:Empty strings are character types (typically
text
,varchar
orchar
) with 0 characters (''
) - so basically "nothing", the equivalent of0
for a numeric data type.NULL
is possible for any data type and means "unknown". Some clients have a hard time making the difference clear in their display.Alternative query
To find
attributeX
where allattributeY
are either empty or NULL use this alternative query:The expression
attributeY <> ''
is onlytrue
for non-null, non-emptyattributeY
. The aggregate functionbool_or
returns (per documentation):Those are the results we exclude (
NOT ...
) and return the rest. Voilá.There are many other (less elegant) ways to achieve the same. Like:
More: