Home / Questions

Questions[postgresql]

Martin Hope
amblador
Asked: 2022-08-23 11:09:12 +0800 CST
  • 3

I have a Postgres table with a jsonb field where an array of json is stored:

PURCHASE TABLE

go buyer items_purchased
1 Bob [{"name": "Dell 123 Laptop Computer", "price": 1300, "productid": 1}, {"name": "Mechanical Keyboard", "price": 120, "productid": 2}]
two carol [{"name": "Virtual Keyboard", "price": 150,"productid": 3}, {"name": "Dell 123 Laptop Computer", "price": 1300,"productid": 1}, { "name": "LG Ultrawide Monitor", "price": 190, "productid": 8}]
3 ted [{"name": "Ergonomic Keyboard", "price": 90, "productid": 6}, {"name": "Dell 789 Desktop Computer", "price": 120, "productid": 7}]
4 Alice [{"name": "Dell 789 Desktop Computer", "price": 120, "productid": 7}, {"name": "Mechanical Keyboard", "price": 120, "productid": 2}]

Given for example a productid , I want to get all the records where that item was purchased. For example, for productid = 1 I look for this output:

go buyer items_purchased
1 Bob [{"name": "Dell 123 Laptop Computer", "price": 1300, "productid": 1}]
two carol [{"name": "Dell 123 Laptop Computer", "price": 1300, "productid": 1}]

What I have tried is the following:

select * from purchases where (items_purchased->>'productid')::integer = 1;

but it returns an empty table.

i also tried

select * from purchases where
items_purchased @> ANY (ARRAY ['{"productid":1}']::jsonb[]);

getting again an empty table.

I also tried various combinations of the following query

select * from purchases, jsonb_array_elements(items_purchased) with ordinality arr(item_object, position) where items_purchased->>'productid'::integer = 1;

but I always get syntax or explicit data conversion errors.

postgresql
Martin Hope
richard herrera
Asked: 2020-01-05 05:38:30 +0800 CST
  • 5

Good morning, I am performing a query in postgress and it throws an error:

"ERROR: reference to column «request_num» is ambiguous LINE 4: WHERE request_num in (3,6,9);",

select * from area
inner join empleado on area.num_solicitud = empleado.num_solicitud
inner join bien on area.num_solicitud = bien.num_solicitud
WHERE num_solicitud in (3,6,9);
postgresql
Martin Hope
fedorqui
Asked: 2020-10-17 05:36:22 +0800 CST
  • 4

Turns out I want to query my PostgreSQL database for a specific name. The problem is that I don't know if that name is uppercase or lowercase, so I have to try all the combinations:

SELECT * FROM personas WHERE nombre = 'Juan'
SELECT * FROM personas WHERE nombre = 'juan'
SELECT * FROM personas WHERE nombre = 'JUAN'

As you can see, it is somewhat tedious and something like MySQL does not work for me:

SELECT * FROM personas WHERE nombre LIKE '%Juan%'

Is there a way to perform the query and get the results regardless of whether they are in uppercase or lowercase?

postgresql
Martin Hope
Diana Carolina Hernandez
Asked: 2020-01-28 06:22:46 +0800 CST
  • 5

Yesterday I formatted the computer with Debian and I need to continue in my projects but I have not been able to use postgresql again, in the terminal I used:

caro@caro-pc:~$ sudo apt-get install postgresql postgresql-client postgresql-contrib libpq-dev
caro@caro-pc:~$ psql --version
psql (PostgreSQL) 9.5.0

But I don't remember what other steps follow, since if I do the following:

caro@caro-pc:~$ sudo-i -u postgres
postgres@caro-pc:~$ psql -V

I get: 

psql (PostgreSQL) 9.5.0

But if I do, for example: 

postgres@caro-pc:~$ psql -l

I get: 

psql: no se pudo conectar con el servidor: No existe el fichero o el directorio
¿Está el servidor en ejecución localmente y aceptando conexiones en el socket de dominio Unix «/var/run/postgresql/.s.PGSQL.5432»?

Please can you help me? Do I have to edit any configuration files?

postgresql