Comparing epoch to timestamp

From: Alexander Farber <alexander(dot)farber(at)gmail(dot)com>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: Comparing epoch to timestamp
Date: 2017-10-30 10:08:22
Message-ID: CAADeyWi8h_g2ESYJqY28fuSgCP3rjUDrKQfo9X7JYeShqkw8wg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Aron Widforss 2017-10-30 11:29:57 Re: Fwd: SPI_palloc problem
Previous Message Ron Johnson 2017-10-29 21:55:25 Re: Old pg_clog files