Search Postgresql Archives

Comparing epoch to timestamp

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Hello,

in PostgreSQL 9.5 I have a table with 67000 records:

 # \d words_nouns
           Table "public.words_nouns"
 Column  |           Type           | Modifiers 
---------+--------------------------+-----------
 word    | text                     | not null
 hashed  | text                     | not null
 added   | timestamp with time zone | 
 removed | timestamp with time zone | 
Indexes:
    "words_nouns_pkey" PRIMARY KEY, btree (word)
Check constraints:
    "words_nouns_word_check" CHECK (word ~ '^[А-Я]{2,}$'::text AND word !~ '[ЖШ]Ы'::text AND word !~ '[ЧЩ]Я'::text AND word !~ 'Ц[ЮЯ]'::text)
Triggers:
    words_nouns_trigger BEFORE INSERT OR UPDATE ON words_nouns FOR EACH ROW EXECUTE PROCEDURE words_trigger()

And a similar one words_verbs with 36000 records.

Is it a good idea to define the following custom function:

CREATE OR REPLACE FUNCTION words_get_added(
                in_visited integer,
                OUT out_json jsonb
        ) RETURNS jsonb AS
$func$
DECLARE
        _added text[];
BEGIN
        -- create array with words added to dictionary since in_visited timestamp
        IF in_visited > 0 THEN
                _added := (
                        SELECT ARRAY_AGG(hashed) 
                        FROM words_nouns 
                        WHERE EXTRACT(EPOCH FROM added) > in_visited
                        UNION
                        SELECT ARRAY_AGG(hashed) 
                        FROM words_verbs 
                        WHERE EXTRACT(EPOCH FROM added) > in_visited
                );

                IF  CARDINALITY(_added) > 0 THEN
                        out_json := jsonb_build_object('added', _added);
                END IF;
        END IF;
END
$func$ LANGUAGE plpgsql;

or should I better transform in_visited to a timestamp with timezone and compare to that?

I have tried the following, but am not sure how to interpret the result:

# explain select * from words_get_added(0);
                             QUERY PLAN                              
---------------------------------------------------------------------
 Function Scan on words_get_added  (cost=0.25..0.26 rows=1 width=32)
(1 row)

Thank you
Alex

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux