As stated in the title, should I reset a PG sequence to MAX(id) or to MAX(id)+1 after a data import? — is what SELECT MAX(id) FROM my_table or — SELECT MAX(id)+1 FROM my_table ALTER SEQUENCE my_table_id_seq RESTART WITH what; If I pick a table and do SELECT MAX(id) FROM my_table, I get x, then I… Read More Should I reset the sequence to MAX(id) or MAX(id)+1 after a data import?
I want to show records where url is null and typeurl of type1 & type2 also null. Output should be productname : Oil ProductName Url Type TypeUrl Shampoo null Type1 null Shampoo null Type2 http://www.domain.com Conditioner null Type1 null Conditioner null Type2 http://www.domain.com Oil null Type1 null Oil null Type2 null postgresql query I want… Read More How to show all 3 urls are null in following table in postgresql/sql
Consider a table like so: CREATE TABLE items ( e uuid, v jsonb ) I insert the following values: INSERT INTO items (e, v) VALUES (‘9a70439e-33c0-4b34-91f5-efac20b58301’, ‘"92cb730c-8b4f-46ef-9925-4fab953694c6"’), (’92cb730c-8b4f-46ef-9925-4fab953694c6′, ‘"Bob"’), (’92cb730c-8b4f-46ef-9925-4fab953694c6′, ’52’); Note how for v, I have actually stringified text and numbers — not an object. Now, what if I wanted to write a query… Read More Cast top-level JSONB field as UUID
Suppose the following setup, CREATE SCHEMA IF NOT EXISTS my_schema; CREATE TABLE IF NOT EXISTS my_schema.category ( id serial PRIMARY KEY, category_name VARCHAR (255) NOT NULL, subcategories BIGINT[] DEFAULT ARRAY[]::BIGINT[] ); INSERT INTO my_schema.category VALUES (1, ‘Pickup/dropoff’, ‘{}’), (2, ‘Electrician’, ‘{}’), (3, ‘Plumber’, ‘{}’), (4, ‘Around the house’, ‘{2,3}’), (5, ‘Personal’, ‘{4}’); CREATE TABLE IF… Read More How to recursively join table onto all records of another table?
hi im trying to get the total of two columns stored to a name then get a condition but i having error on the ‘Rebound’ name on line 3 the offreb and defreb has a integer type and some values are stored as 0 (zero) SELECT team, CONCAT(firstname,’ ‘,lastname) AS name, SUM(offreb + defreb) AS… Read More sum of two columns assigned to a condition
I have some tables all_cars(id, name, image, price) cars_info(car_id, ps, engine, sit_place, fsk) cars_details(car_id, info) cars_price(car_id, time_zone, km, price) I want to join all tables like this: const d = await pool.query(` SELECT all_cars.id, all_cars.name, all_cars.image, all_cars.price, cars_info.car_id, cars_info.ps, cars_info.engine, cars_info.sit_place, cars_info.fsk, cars_details.car_id, cars_details.info, cars_price.car_id, cars_price.time_zone, cars_price.km, cars_price.price FROM all_cars INNER JOIN cars_info ON all_cars.id… Read More error: column "car_id" must appear in the GROUP BY clause or be used in an aggregate function
I have a user table with the fields id and email (among others). The email address isn’t case insensitive, a problem we want to fix, but we have some duplicate values in there (duplicate except the case, so currently we have user@example.com and User@example.com in there). I am now trying to get an overview of… Read More How to find duplicate case insensitive records in Postgresql?
Something like: SELECT * FROM table WHERE something IN (‘{"val1","val2"}’::text[]); I tried it with array_to_string(). SELECT * FROM table WHERE something IN (array_to_string(‘{"val1","val2"}’::text[])); But I guess that makes it to this: SELECT * FROM table WHERE something IN (‘val1,val2’); –one single string I guess the single values must also be surrounded with apostrophes. Is that… Read More Is it possible to adjust an array in PostgreSQL to fit with an IN operator?
I have been struggling to figure out how to put a CASE statement in WHERE in Postgresql. I need to convert the string to a date (as seen in line 3). This works fine. When I try to pull CURRENT_DATE in the WHERE statement I run into errors. Is this the best way to do… Read More CASE WHEN statement in WHERE clause Postgresql
I am trying to query all unique city names for one particular state without pulling other states. This is what I am trying to do: SELECT DISTINCT city_name FROM table WHERE state = ‘California’; This does not work because there is a San Diego in other states aside from California. How would I query this… Read More Querying all unique city names in one state and no where else