I have a PostgreSQL table of the following format:
uid | defaults | settings
-------------------------------
abc | ab, bc | -
| |
pqr | pq, ab | -
| |
xyz | xy, pq | -
I am trying to list all the uids which contain ab in the defaults column. In the above case, abc and pqr must be listed.
How do I form the query and loop it around the table to check each row in bash?
@user000001 already provided the bash part. And the query could be:
SELECT uid
FROM tbl1
WHERE defaults LIKE '%ab%'
But this is inherently unreliable, since this would also find 'fab' or 'abz'. It is also hard to create a fast index.
Consider normalizing your schema. Meaning you would have another 1:n table tbl2 with entries for individual defaults and a foreign key to tbl1. Then your query could be:
SELECT uid
FROM tbl1 t1
WHERE EXISTS
(SELECT 1
FROM tbl2 t2
WHERE t2.def = 'ab' -- "default" = reserved word in SQL, so I use "def"
AND t2.tbl1_uid = t1.uid);
Or at least use an array for defaults. Then your query would be:
SELECT uid
FROM tbl1
WHERE 'ab' = ANY (defaults);
It's not really about bash but you can call your query command using psql. You can try this format:
psql -U username -d database_name -c "SELECT uid FROM table_name WHERE defaults LIKE 'ab, %' OR defaults LIKE '%, ab'
Or maybe simply
psql -U username -d database_name -c "SELECT uid FROM table_name WHERE defaults LIKE '%ab%'
-U username is optional.
ab if ab is under uid
'*, ab' should have been '%, ab'.Use awk:
awk -F\| '$2~/ab/{print $1}' file
Explanation:
-F\| sets the field seperator to the | character$2~/ab/ we filter the lines that contain "ab" in the second column.print $1 we print the first column for the lines matched.