I created a table in postgres that includes various columns that based on an import from a csv. Since the csv can be re-uploaded with some changes and I don’t want the re-upload to create duplicates on rows that didn’t change, I added a constraint and unique index with majority of the columns incl…
Tag: postgresql
Group query in subquery to get column value as column name
The data i’ve in my database: I ran a query to group by id and status to get the below result: I want to use the above query as subquery to get the result below, where the distinct status are column name. Any other approach to get the final data is also appreciated. Let me know if need more info.
Postgres not returning data on array_agg query as below
The problem arise when there are no data for books in specific library. Consider a following working scenario. Table name: library Table name: books Now when I do query like below: The query generates output for library 1 & 2, but not for library 3. Why and how to solve this issue? (Generate an empty list…
PostgreSQL – How to format an int as an hexa
What’s a short/simplest way of formatting an int to an hexa, with leading zeroes? For example I need to format: My query needs many of these conversions I would appreciate a short piece of code instead of a verbose one. Just need to deal with positive numbers. Answer I’m not sure it’s the sh…
Combine two columns and perform a where operation on the result
I have two columns, one for ‘firstName’ and one for ‘lastName’. I’m trying to perform a where operation on the concatenation of those two columns to match for a full name. Here is what I’ve tried: This just returns a blank array. Example Data: firstName lastName Clayton Smi…
Count the amount of likes a user received on each post sql
I have two tables. Likes table which contains it’s like_id (PK), vole_id, user_id, and created_at timestamp. And another table called voles that contain vole_id (PK), and user_id, as well as a bunch …
ERROR: invalid input syntax for type numeric (SQL)
I’ve created a data set with Mockaroo and then added the file with cmd. But I am getting this error. ERROR: invalid input syntax for type numeric: The table definiton is this : create table car( id …
Insert data into Postgresql with duplicate values
I need to insert dataset in postgresql. the problem is that there are many duplicate values in the request. As the result database should involve Or nothing if this dataset is in database. How should I make my query? P.S. The table is: Answer If you don’t want duplicates inserted, then add a unique inde…
Is there a better way to execute this SQL query?
I have written this SQL query to get data for each customer in my database. As you can see, I’m trying to get the total of unpaid orders and the total of orders to my query. My goal at the end is to get only the users with unpaids orders (I think i will have to make it with a
Subselect on multiples values with like operator
How to subselect on multiple values where like operator is included? I build Table B with the ‘%’ to get all values from Table A with the like-operator. I can get the I-values from Table A with SELECT * FROM TABLE A WHERE VAL LIKE (‘I33.%’) but i build Table B to go through the whole t…