Tag: postgresql

How to specify a limit on Postgres json_agg

common-table-expression json postgresql sql

I want a JSON output having the distinct values of a column and also a limited number of rows. This is the sample table that I have in a Postgres Database: Name Author Copies Sold —- …

Add the last appearance to table

postgresql sql

I have two tables (for example) table1 will contain stores, there are 2 types ‘candy store’ and ‘dental store’. Each row contains information about a customer’s purchase in a particular store table1 ( purchases ): table2 ( type ): I want my query to return a table like this: where [the last place] is the last store where client bought

Update returning * and select result as rows

postgresql sql

I want to run a few updates in a single query returning *, and select the output as rows. I currently have That does what I’m looking for but feels like it could be improved. I almost got it working like this but it displays as a single row not two separate rows Answer I think you simply want union

How do I process execution plan in PostgreSQL?

postgresql sql sql-execution-plan

In PostgreSQL, when I run: I get: How can I get the “Total Cost” from there? I tried: But I get the error: Answer explain always needs to go before a statement, you can’t put it in the middle of one. You can do what you want by wrapping this into a function: If you can’t use a function, you

User Permissions error for reading from view in postgres

postgresql sql

I am using postgres and have a new read-only user that has read permissions on all tables in a base schema. I was trying to SELECT some data from a particular view from base schema and was able to see the values from the view. To test the data count I had to make some changes in the view definition

Postgresql: Get columns of other table as array in a object as a column with condition

arrays postgresql sql

i have 2 tables, servers and virtual_machines. I want to see the virtual_machines as array in the servers where serverName equals the servers name Like this I have servers tables and virtual_machines tables I tried many options like bellow but i cant make object array in colum and postgre always add key into object. Thanks for any help Answer You

Fill in Missing Values in Query

postgresql sql

Say we have this dataset: Two students are enrolled in two different courses and attendance is taken for each student. I am interested in finding the attendance_dates for the students for which either one of them or both of them missed the respective subject. For the subject and the attendance_dates the students did not show up return NULL. For example:

postgreSQL LEFT and UPPER interaction

postgresql sql

Question: Write a query that returns the name, address, state, and zipcode from all purchases. For the names, return only the first 5 characters, and display them in all uppercase letters, sorted in alphabetical order. Code so far: I’m at a loss of where and how to use UPPER to capitalize all the characters in the first column that is

Laravel Migration: Combine multiple values in a new colum

database-migration laravel migration postgresql sql

i’m trying to make a migration using Laravel. The idea is to get the values of columns ‘code’ and ‘id’ in different tables, and merge them into a column ‘name’ with ‘-‘ separators. So in part it is a SQL problem. Currently i’m trying something like this. First migration to create the new column ‘name’ (works just fine) And second