Generating grouping column for records based on row order

I have Postgres data imported from a spreadsheet, where section headers have been imported along with the data. Is there a way to move the section headers to their own column? I have looked at window functions and I have tried a self join.

I am certain that the category names will always have a null "responsibility". It would have to rely on the order that they were inserted, there is no sort column. Is that reasonable?

I need to transform this:

into:

As @eshirvana already points out, there's no reliable way to do this task. Although if you still have no possibility of having an incremental value that uniquely identifies the order of your rows, you can exploit the "ctid" system value, that holds the order of insertion of your records inside that table (if you inserted in bulk, it's still unreliable to use this approach).

So goes how you solve the problem:

• regenerating your partition using a running sum on responsibility = NULL, ordering by "ctid"
• generating your category field as a max "need" value where responsibility is null
• removing the header records, by selecting all records where "responsibility" is not null

WITH cte AS (
    SELECT tab.*,
           SUM(responsibility IS NULL::INT) OVER(ORDER BY ctid) AS parts
    FROM tab
), cte2 AS (
    SELECT MAX(need) FILTER(WHERE responsibility IS NULL) OVER(PARTITION BY parts) AS category,
           cte.*
    FROM cte
)
SELECT * FROM cte2 WHERE responsibility IS NOT NULL

Check the demo here.

put it here since it won't fit in comments section :

as it's been mentioned here:
> If sorting is not chosen, the rows will be returned in an unspecified order. The actual order in that case will depend on the scan and join plan types and the order on disk, but it must not be relied on.

And to add a new column for insert date , you have to alter the table and add the column and fill it with proper values whether you insert or manually when you want to update them:

alter table [table_name] add column InsertDate timestamp

see documentation above for more information.

Keep in mind this is only one approach, not necessary the best approach.

seems like this was an example of the last non-null puzzle. the strategy is to derive a row_number() column to group the values you want to 'fill' down so that you create a derived last-non-null column for your category

SELECT *
FROM (
	WITH cte AS (
			SELECT *
				,MAX(CASE 
						WHEN category IS NOT NULL
							THEN rn
						END) OVER (
					ORDER BY rn ROWS UNBOUNDED PRECEDING
					) AS grp
			FROM (
				SELECT row_number() OVER (
						ORDER BY NULL
						) AS rn
					,responsibility
					,CASE 
						WHEN responsibility IS NULL
							THEN NULL
						ELSE need
						END AS _need
					,CASE 
						WHEN responsibility IS NULL
							THEN need
						END AS category
				FROM test
				) AS t2
			)
	SELECT MAX(category) OVER (
			PARTITION BY grp ORDER BY rn ROWS UNBOUNDED PRECEDING
			) AS _category
		,_need
		,responsibility
	FROM cte
	) AS t3
WHERE responsibility IS NOT NULL;

the demo is here