I’m working on a Rails application that utilizes the Postgres JSON data type. I have a JSON column called data in a table called reports. Let’s say I have multiple entries like this: What I would like to do is return the different combinations of entries that have the same album, src, and backgrou…
Tag: postgresql
Postgres constraint for unique datetime range
My table has two columns: startsAt endsAt Both hold date and time. I want to make following constraint: IF both columns are NOT NULL then range between startsAt and endsAt must not overlap with other ranges (from other rows). Answer You can keep your separate timestamp columns and still use an exclusion const…
What’s the proper index for querying structures in arrays in Postgres jsonb?
I’m experimenting with keeping values like the following in a Postgres jsonb field in Postgres 9.4: I’m executing queries like: How would I create an index on that data for queries like the above to utilize? Does this sound reasonable design for a few million rows that each contain ~10 events in t…
PostgreSQL: Create table if not exists AS
I’m using PostgreSQL and am an SQL beginner. I’m trying to create a table from a query, and if I run: CREATE TABLE table_name AS (….query…) it works just fine. But then if I add ‘if not …
PostgreSQL:How get last rows from a select query
See the below example, And if I Execute Will Get So,How Can I Get the Last 2 Rows in select * from data? What I’m expecting is…. Answer You have two options according to your need i.e, Or LIMIT and OFFSET if you want the 4th and 5th row just offset the first 3 so that the 4th row becomes
How to iterate over a record when the columns are dynamic
I have this function in postgres which takes PVH_COLS_DYNA that contains the columns that are going in to the query: CREATE OR REPLACE FUNCTION DRYNAMIC_DATA_F(PVH_COLS_DYNA VARCHAR) RETURNS numeric …
Can you define a custom “week” in PostgreSQL?
To extract the week of a given year we can use: However, I am trying to group weeks together in a bit of an odd format. My start of a week would begin on Mondays at 4am and would conclude the following Monday at 3:59:59am. Ideally, I would like to create a query that provides a start and end date,
How to allow only one row for a table?
I have one table in which I would like only one entry. So if someone is trying to insert another row it shouldn’t be allowed, only after someone deleted the previously existing row. How do I set a rule for a table like this? Answer A UNIQUE constraint allows multiple rows with NULL values, because two N…
Convert oracle.sql.timestamptz to postgresql timestamp with timezone
Sorry, but i`m noob and i need your advices. I have some result set from oracle with timestamptz, i get string value of this timestamptz which like 2014-1-10 13.47.56.0 7:0 and then I need put it in postgresql-request in some function which takes timestamp with timezone. How can I convert this this string to …
Get column names which do not match a particular ending string
I have a table with a structure like this: abc_col | abcd | ab_col | | | | Some column names end in _col and some do not. abc or ab or abcd are random column name …