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)