Skip to content

Tag: postgresql

SQL: Flattening multiple rows in ugly data

I’m working on a fairly odd scenario in which I’ve got to compensate for a client’s database that isn’t very well-ordered. The data (example below) concerns some products that have a main serial number (serial) and between 0 and 4 additional alternate serials (alt_serial). Unfortunately, neither of these columns is a primary key, and each alternate serial number produces an

How to add ‘all’ CASE when group by CASES in SQL

I have a query like this: Which gives the result: How can I add a row for ‘all’ requests in CASES? If we add a when sp.provider=’prv1′ with no more detailed condition then all cases become one ‘all’ case because and other cases are ignored. Answer You can’t do that inside the CASE, as it behaves like a series of

How to get the average from multiple columns

I saw this answer how do I select AVG of multiple columns on a single row but I have some rows which is the value is 0 and it’s throwing an error division by zero when I try to use his ex. Answer In order to resolve the task, you can use CASE expression because you cannot divide by zero:

How to allow a gender field to accept only two values?

The question: Add a gender field. Ensure the field will only accept ‘M’ or ‘F’ and the default value should be ‘F’ PostgresSQL code: ERROR: syntax error at or near “Check” LINE 2: add Gender varchar(1) default ‘F’ ,Check (Gender = ‘M’ or G… How do i fix it? Answer Your approach is good, there is only a small syntax

Can I start the quarter of the year from the month I wanted to on PostgreSQL?

I am using PostgreSQL 14.5 I can classify the year by quarter using the Postgres keyword QUARTER as illustrated in the documentation just like SELECT EXTRACT(QUARTER FROM TIMESTAMP ‘2001-02-16 20:38:40’);. And it works. The default quarter starts with January. Which makes complete sense. I.e, quarter one is composed of January, February, and March. The others follow then. But, I wanted

Copy data on parent and child table

I have two database tables. What I need to do is to copy specific data from one storage to another, but also keep the mapping to the photos. First part I can do easily writing but after that newly inserted items does not have photos. Note, that document_id field is unique for not copied items. Answer Assuming id columns are

Output of non-existent values when grouping in sql

For example, i have a table with the data: Screenshot This table named “table”. I have the SQL query: And I get the result: Question: how do I write a query that would return all the values that are in the kind field (or any other field that can be in group by)? Even if this value is 0. For