what is the difference between the select count(*) and select count(true)? so is there any different between the count(*) and count(true) which one should I use? can you give me situation example for each one that is better option to choose? Answer The result of both is the same, but count(*) is slightly faster than count(true). That is because in
Tag: postgresql
In PostgreSQL, how can I convert some boolean columns into an array of those, that are true?
Given this table How can I select it into this result? Answer You can convert the whole row into a JSON, then use a JSON Path query to get only those keys (=columns) that are true: to_jsonb(t) converts the whole row into a JSON value. The JSON Path expression splits that into key/value pairs and then aggregates the keys of
autoincrement number function-postgres
i have a table like this: id person 20 adams 20 george 40 jina 46 rico 80 naya 90 john 90 peter 90 richard i want to find a way to select a new_id starting from 1 and increazing +1 every time id is different. for example i want a select with a result like this: new_id id person 1
How to Count from sql as a value in Npsql
I am using Npgsql to access postgresql database and I am running a query: The query will return value 4, but my a gets -1.what is the problem here and how can I solve it? Answer It looks like you are using Dapper. If so, you want QueryFirst not Execute, as Execute just returns the number of rows
How to specify information from the table being inserted on PostgreSQL upsert?
I am trying to insert values from a temporary table (NewThings) with columns Thing and Counts into my main table (MyThings) with columns Thing and Count. If a thing does not exist in the main table I want to insert a new row, otherwise I want to add to the counter. However, I am struggling with the correct syntax on
How to use `ROW_NUMBER` but grouping by days in PostgreSQL?
I have a table (t1) like this: (and so on) What I want to do is to get a new autoincrement column based on day index, starting by 1. I guess I could achieve that by using something like ROW_NUMBER: But with this query I get the following: Which is in a sense something similar to what I’m looking for,
User function return query and insert into
I trying to make function, that merge where select query return result as value(not table) and insert into that use value from select statement. The function where argument gets from user, and this argument provide into select query on table_foo that return value id where I try set and use in insert into to diff table(table_bar). So I little bit
Postgres – sort and compare strings
I have the below table: I want to concatenate all A strings and all B strings, sort them and compare the 2. This gives me the concatenation: This is the output: As seen, both of these strings are same if we sort them and compare. How can I do this in Postgres? Here is the fiddle to try: http://sqlfiddle.com/#!15/b78281/6 Answer
Get the average difference between two values in time period
What I have: A single table with ids and prices for date and time. What I want to do: To get the average difference of price for every id between the first and 15 minute of every day. Conditions: I also want to use the last available price in 15 minutes if the 15 is missing. For example if 15
Postgres Issue: prompt has changed
test=# SELECT COUNT(id) FROM person’ test’# Answer psql is smart enough to guide us. Look at this simple session: The whole statement looks like without n’s