Postgresql – Specific text search in SQL

postgresql

For an assignment, we are doing select statements using Shakespeare's works in PG Admin. I must find which character, and in what work, said a specific phrase. I'm stuck with the following code:

select workid, charid from paragraph

where plaintext like 'rose'

In which I'm trying to find who said "A rose by any other name would smell as sweet." Any tips?

Best Answer

Figured it out. Because the line given isn't the same as written, the following query worked:

select workid, charid, wordcount from paragraph
where plaintext like '%rose%'
and plaintext like '%other%'
and plaintext like '%name%'
and plaintext like '%smell%'
and plaintext like '%sweet%';

Related Solutions

Postgresql – Syntax error when trying to import database from two PostgreSQL databases

You seem to have omitted the new version of the upgraded server, but I strongly suspect it's still on 8.1 since the ALTER SEQUENCE ... OWNED BY clause was added in 8.2 (Compare ALTER SEQUENCE docs on 8.2 to ALTER SEQUENCE docs on 8.1).

You really need to understand that 8.4 and 8.1 aren't slightly different versions, they're massively different. It's like saying that Windows Vista and Windows 7 are "slightly different" versions because they're Windows versions 6.0 and 6.1 respectively. Or that Mac OS X 10.4 and 10.7 are "slightly different"... when they're more incompatible than not.

Yes, I know the version numbering in PostgreSQL is stupid; it's a historical oddity we seem to be stuck with despite periodic attempts to change it. See the version policy document linked in the prior answer for more information about what the versioning means, and read the release notes for a better understanding.

Postgresql – Combine a generated series with each row from a select

You need to do a left outer join on the generate_series with an ON clause that matches the date of each report.

Without a schema or sample data it's a bit fiddly to cook up an example modification of your actual query.

Here's a simplified example to show you how it works:

CREATE TABLE sparse_dates(
    bakedgood text primary key,
    firstbaked date not null
);

INSERT INTO sparse_dates (bakedgood, firstbaked) VALUES
('tart', '2012-02-01'),
('baguette', '2012-02-01'),
('cookie', '2012-03-01'),
('macaron', '2012-08-01');

To get the number of baked goods first baked on a given month:

SELECT monthtimestamp, count(bakedgood) 
FROM generate_series( 
    (SELECT min(firstbaked) FROM sparse_dates),
    (SELECT max(firstbaked) FROM sparse_dates),
    INTERVAL '1' MONTH) 
    AS monthtimestamp
LEFT OUTER JOIN sparse_dates ON (monthtimestamp = firstbaked)
GROUP BY monthtimestamp;

with output like:

     monthtimestamp     | count 
------------------------+-------
 2012-02-01 00:00:00+08 |     2
 2012-03-01 00:00:00+08 |     1
 2012-04-01 00:00:00+08 |     0
 2012-05-01 00:00:00+08 |     0
 2012-06-01 00:00:00+08 |     0
 2012-07-01 00:00:00+08 |     0
 2012-08-01 00:00:00+08 |     1
(7 rows)

Best Answer

Related Solutions

Postgresql – Syntax error when trying to import database from two PostgreSQL databases

Postgresql – Combine a generated series with each row from a select

Related Question