Skip to content
Advertisement

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

Advertisement