I am attempting to write a query to audit a table of IP addresses against a table of overlapping net blocks. Individual net blocks can be marked active or inactive via a BOOLEAN. The audit query …
Tag: postgresql
pg_restore: [directory archiver] could not open input file. Error while trying to restore DB
Error when trying to restore a backup file(.nb3). I did a database backups in Navicat, one backup for each schema. They are .nb3 files. I tried to restore my DB on a local server using pgAdmin. I got an error like this after choosing backup file : How do I restore the database on a local server? Answer NB3 NaviCat
How to query on multiple attributes in the same json object array?
I have a json array similar to this structure in a column of my database – { “id”: “123abc”, “Y/N”: “Y”, “Color”: “Purple”, “arr”: [ { “time”: 1210.55 “person”: “Sean” “action”: “yes” //…
Getting the all rows of with any of the lastest N tags
I need to get the all the records of the N latest Tags, i.e. ordered by timestamp. I’ve managed to construct the following query that retrieves the latest 20 tags. However when I try to join on the …
How do I populate a foreign key field with data instead of just foreign key id on select query?
I have 2 tables with class and subjects. class(id,name,student_count) subjects(id,name,class_id) I want to populate class_id with name,student_count, and id on the select query. How do I do this using SQL or Knex? I tried using join, but I get only one field not all fields in a row. The expected result would be: By the way, answer in Knex or
PostgreSQL DROP VIEW IF EXISTS
In PostgreSQL when I run the command DROP VIEW IF EXISTS view_name; I get the response: View IF dropped. Can anyone explain what this means? why the word ‘IF’ instead of a response like: View …
Update or Delete Violates foreign key constraint
I have two tables that look like the following. CREATE TABLE book ( book_id BIGSERIAL PRIMARY KEY, book_special_id character(10) NOT NULL DEFAULT random_string(10) UNIQUE, author_id …
How to resolve ambiguous match when chaining generated Jooq classes
I have defined my models in JPA and am writing some queries for my application and I am using JOOQ generated classes to join all the tables together to check if the requested resources actually belong to the requesting user. However, when I do this I get the following warning: This is my code This is the SQL it generates
Unable to divide to counts of two separate lists in SQL, keeps returning 1
I have one list of events. One event name is creating an account and another is creating an account with Facebook. I am trying to see what percentage of accounts created use Facebook. The code below …
Working of “between” operator for Varchar and Char datatypes in postgresql
I would like to use “between” operator on “varchar” and “char(5)” columns in a table. However, I don’t see any effect of “between” operator in the results. Please note that “code” is the column name I have a table with a column of type VARCHAR and I apply cast operation to convert it into “CHAR(5)” type. When I apply between