This is strange because the id column is an Int and should be default autoincrementing. The error message complains about int4. If I look in the database the only int4 column is the id field. Answer It seems your users are too active, if there is more than 2.147.483.647 comment ids, the Int id has reach it max value You
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 can I indicate the “current” column in an upsert in PostgreSQL?
I have the following upsert with which I have problems because the subqueries give me more than one result. The problem is that I don’t know how to indicate in the upsert to compare the value of the column that is currently being updated. The problem is after the DO UPDATE. Answer I think you are looking for the excluded
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
Postgres: column “all_comments.id” must appear in the GROUP BY clause or be used in an aggregate function
I am using with recursive to count the number of all descendants. That would be the following SQL query, but if I specify only root_id for group by, I get I get the following error. column “all_comments.id” must appear in the GROUP BY clause or be used in an aggregate function I don’t understand why this error occurs with all_comments.id.
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