Tag: postgresql

How do I utilize a WHERE clause with a MONEY data type?

So to keep it simple, I’m working on a problem that requires a simple case of utilizing the WHERE clause to bring back records that have the field (we’ll call it ‘tax’) equal to $0.00 So as per usual, I thought I could do: WHERE tax = 0; But I keep getting an error. And I’m pretty sure it’s because

With PostgreSQL how to determine the average completion time given two time stamps while accounting for nulls

In my PostgreSQL database I have an invitations table with the following fields: Invitations: id, created_at, completed_at (timestamp) I am working to write a PostgreSQL query that tells me the …

Trigger delete on many-to-many middle table deletion

There are two tables A and B. As they have a many-to-many relation, there’s also table C. A —— id PK B —— id PK C —— id_A PK id_B PK Now, a row B only exists only exists when at least …

How to get specific objects value from JSONB array in PostgreSQL?

I have a column named people and it’s type is JSONB. Here is the sample data (1 row): {“addresses”: [{“street”:”cubuklu”, “valid?”: “true”} {“street”:”beykoz”, “valid?”:”false”} …

How to Join Tables on Datetime within a margin of seconds

I have 10 tables: Table1, Table2, …, Table10 Each one with the columns: id text datetime My task is to join the Table1.datetime with the rest of the 9 tables.datetime (+-3seconds on the 9 tables)…

Which the best way to use inheritance of tables in postgreSQL?

i want to use inheritance in my database (PostgreSQL) so i wrote this code but finally i found there are inherits in PostgreSQL CREATE SEQUENCE public.user_account_id_seq; CREATE TABLE public….

PostgreSQL result of first query used in second query

Sample Data: |lastmodified|Number1|Number2|password_mod| |2020-01-25 |250000 |100000 |true | |2020-01-26 |250000 |200000 |false | |2020-02-01 |300000 |200000 |false | |2020-02-…

Ideal Postgres Index For Json Data With Integer Timestamp

I have millions of records in this table using Amazon Aurora Postgres 10.7: Example row: {“id”: “abc”, “ts”: 1580879910, “data”: “my stuff”} I have these queries that take dozens of seconds: I’m trying to improve performance here, and these are all the indexes that I tried, but at most I get an INDEX SCAN in the query plan at best.

Accessing PostgreSQL Fields via Indexing

Is there a way in PostgreSQL to access fields without explicitly providing the column name? For example, instead of the statement: there’s a (possible) alternative of: Answer There is no such syntax in Postgres’ SQL. You could always resort to having code that dynamically constructs the query for you before executing it, but that’s about as good as it will