Problems with array type?

Question

Problems with array type?

Navigation

#1 by (1 votes)
#2 by (1 votes)

1

I'm having trouble formulating a chart.

I have basically 2 fields: categorias and dados . Within categorias , the information should be arranged as follows: ['categoria 1', 'categoria 2', 'etc'] surrounded by quotation marks and separated by commas. Already within data, the information must come like this: [dado 1, dado 2, dado 3] without quotes.

I need to get both ( categorias and dados ) information from a select in a PostgreSQL database, but I only get success in the dados field since it never comes separated with quotation marks. How can I format to include the quotes?

Here is an example code for the data:

$query="SELECT colunaa from minha tabela";
$output=pg_query($conn,$query);
    ?>
    [<?php while ($result = pg_fetch_array($output)) {?>
                      <?php echo $result["colunaa"]?>,

                    <?php } ?>]

php postgresql

asked by anonymous 12.06.2018 / 13:22

2 answers

1

You can concatenate the quotation marks:

SELECT CONCAT('''', colunaa, '''') from minhatabela

I hope I have helped.

12.06.2018 / 13:33

Change values according to the selected option .Wav audio file conversion to .Mp3

score 1 · Accepted Answer

In PostgreSQL you can do something like:

CREATE TABLE tb_cores
(
    id INTEGER NOT NULL PRIMARY KEY,
    nome TEXT NOT NULL
);

INSERT INTO tb_cores ( id, nome ) VALUES ( 1, 'PRETO' );
INSERT INTO tb_cores ( id, nome ) VALUES ( 2, 'BRANCO' );
INSERT INTO tb_cores ( id, nome ) VALUES ( 3, 'VERMELHO' );
INSERT INTO tb_cores ( id, nome ) VALUES ( 4, 'VERDE' );
INSERT INTO tb_cores ( id, nome ) VALUES ( 5, 'AZUL' );

1) COM Aggregation (separated by commas):

SELECT string_agg( nome, ',' ) FROM tb_cores;

Output:

|                       string_agg |
|----------------------------------|
| PRETO,BRANCO,VERMELHO,VERDE,AZUL |

2) COM Aggregation (with quotation marks and comma separated):

SELECT CONCAT( $$'$$, string_agg( nome, $$','$$ ), $$'$$ ) FROM tb_cores;

Output:

|                                     concat |
|--------------------------------------------|
| 'PRETO','BRANCO','VERMELHO','VERDE','AZUL' |

3) WITHOUT aggregation, only quotes:

SELECT CONCAT( $$'$$, nome, $$'$$ ) FROM tb_cores;

Output:

|     concat |
|------------|
|    'PRETO' |
|   'BRANCO' |
| 'VERMELHO' |
|    'VERDE' |
|     'AZUL' |

SQLFiddle